What's the best way of searching for misplaced semicolons in OSM data?

Question

Normally the answer is "taginfo", but (as you'd expect) that does explicit processing of semicolons. If I search for http://taginfo.openstreetmap.org.uk/search?q=bar%3Bcafe%3Btoilets#values I get the one expected value back, but I'm not aware of a way of searching for any values (or keys for that matter) containing multiple semicolons.

In postgres (with a "normal" rendering database) I can do:

gis=# SELECT amenity FROM planet_osm_polygon WHERE (amenity LIKE '%;%;%');
                  amenity
-------------------------------------------
 bar;cafe;toilets
 private_hire_cars;coach_hire;minibus_hire
(2 rows)

but obviously that requires a local database and doesn't search for semicolons in keys - presumably there's a way to search the "tags" array in "planet_osm_ways"? Obviously for "planet_osm_polygon" I'd also need to look at "planet_osm_point" and "planet_osm_line", and for "planet_osm_ways" I'd need to look at "planet_osm_nodes" too (ignoring relations for now).

Maybe there's a way of doing it with Overpass?

EDIT: This was prompted by this taginfo discussion, as well as the related iD issue and someone "correcting" name_X tags in Ireland (the link to which I've lost).

Answer 1

3

If you have an osm2pgsql/hstore database and want to operate on the keys, you have to explode the hstore into regular rows with the each operator before then applying a normal query, like so:

gis=# SELECT osm_id, key
FROM
  (SELECT osm_id,(each(tags)).key as key FROM planet_osm_point) sub 
WHERE key like '%;%;%';

permanent link

answered 21 Jan '16, 15:25

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

No hstore here currently (and no "tags" column on planet_osm_point, though there is one on e.g. planet_osm_nodes) - will have to try it after reload.

(21 Jan '16, 19:13) SomeoneElse ♦

Answer 2

Overpass does indeed have regex search for keys: http://overpass-turbo.eu/s/dTS

way[~";"~"."];
out geom;

(~"." will match any value, some value match is required when matching a key)

In a similar way, you can also query for nodes or relations with at least one semicolon.

Edit: To check for values with at least 2 semicolons, the following regular expression might come in handy: ".+;(.+;)+.+" - use it either for keys or values.

[bbox:{{bbox}}];
way[~"."~".+;(.+;)+.+"];
out geom;

In any case it is recommended to also specify a suitable key like e.g. amenity, otherwise tags like opening_hours will produce quite a lot of hits.

Answer 3

2

Taginfo doesn't have a way to do searches like that (mostly for performance reasons), but you can download the taginfo database and do the query yourself. The database is in Sqlite format and quite big, but once you have it, usage is simple and reasonably fast if you know SQL:

sqlite> select count(*) from tags where value like '%;%;%';
205966

permanent link

answered 22 Jan '16, 07:09

Jochen Topf
5.2k●5●50●74
accept rate: 31%

What's the best way of searching for misplaced semicolons in OSM data?

Follow this question

Related questions