Compare values with Overpass

Question

I'd like to find Objects where certain Keys have the same value. Irish streets normally have two names, one in Irish and one in English. In osm the have three tags for that, name, name:en and name:ga. I'd like to find all objects having all three tags where name doesn't match with name:en or name:ga.

Accepted Answer

It's not possible with Overpass API today, but with a bit of post-processing you can get the results in a few minutes.

The Overpass QL query below returns relevant nodes/way/rel having a name, name:en and name:ga tag in CSV output format (tab separator character).

To get the data, just click on the following overpass turbo link: http://overpass-turbo.eu/s/9FN and then hit "Export" -> "raw data directly from Overpass API". This should automatically open LibreOffice or Excel, depending on your local machine. When importing the CSV file, make sure to specify "tab" as separator character.

There are only 11120 rows returned by the query, about 300 of them match your search criterion. Just add a formula to all rows to compare different names and you're probably all set.

[timeout:1800]
[bbox:51.08282186160976,-12.8759765625,55.986091533808384,-1.86767578125]
[out:csv(::id, ::type, name, "name:en", "name:ga")];

( node[name]["name:en"]["name:ga"];
  way[name]["name:en"]["name:ga"];
  rel[name]["name:en"]["name:ga"];
);
out;

Overpass QL result in CSV format, as returned by Overpass API:

@id @type   name    name:en name:ga
661291  node    Sutton  Sutton  Cill Fhionntain
661448  node    Killester   Killester   Cill Easra
661999  node    Glenageary  Glenageary  Gleann na Caorach
662088  node    Booterstown Booterstown Baile an Bhóthair
664528  node    Shankill    Shankill    Seanchill
665844  node    Cowper  Cowper  Cúipéar
666663  node    Milltown    Milltown    Baile An Mhuilinn
12646992    node    Carrickmines    Carrickmines    Carraig Mhaighin
12647002    node    Carrickmines    Carrickmines    Carraig Mhaighin
12647038    node    Cherrywood  Cherrywood  Coill na Silíní
19223931    node    Windy Arbour    Windy Arbour    Na Glasáin
26608599    node    Cork Airport    Cork Airport    Aerfort Chorcaí
26608601    node    Shannon Airport Shannon Airport Aerfort na Sionainne
28650950    node    Cooanmore Point Cooanmore Point Gob an Chuain Mhóir
28652693    node    Killaspug Point Killaspug Point Rinn Chill Easpaig

[...]

Answer 2

5

Overpass isn't the only tool out there. Here's an alternative :

Install spatialite using your platform's prefered install method
download a country extract from geofabrik
import the extract into an sqlite db using spatialite "spatialite_osm_raw -o input.pbf -d output.sqlite -jo"
enter the database: "sqlite3 output.sqlite"
run this query: "select t1.way_id,t1.v,t2.v,t3.v from osm_way_tags t1 left join osm_way_tags t2 on (t1.way_id=t2.way_id) left join osm_way_tags t3 on (t1.way_id=t3.way_id) where t1.k='name' and t2.k='name:ga' and t3.k='name:en' and t1.v <> t2.v and t1.v <> t3.v;"

This give a result like this:

4934506|Suir Road Bridge|Bóthar na Siúire|Suir Road
4934592|Macartney Bridge|Sráid Bhagóid Íochtarach|Baggot Street Lower
4934663|Rialto Bridge|Cuarbóthar Theas|South Circular Road
13769663|School Lane|Ascaill Na gCrann|Trees Avenue
16315628|Trimleston Gardens|Gairdíní Baile Trimble|Trimbleston Garde

permanent link

answered 29 May '15, 18:21

Vincent de P... ♦
17.3k●18●152●249
accept rate: 19%

edited 29 May '15, 18:22

That looks good. I will definately try it. Thanks.

(29 May '15, 18:23) Ogmios

1

Downloading an extract (100MB) and creating an sqlite db from it (= 2,x GB(!)) seems a bit heavy to me for 11000 relevant objects.

As an alternative to using a Geofabrik country extract, I'd really recommend to start with a small Overpass extract instead: http://overpass-turbo.eu/s/9FQ -> Export -> raw data directly from Overpass API. The download will be around 12MB uncompressed OSM data (query takes about 1 minute), the resulting sqlite DB file size will be just 19MB.

Also, it would be good to have some sql statements for nodes and relations as well to make the example complete.

(31 May '15, 17:23) mmd

Didn't have an estimate of the number of matches until you wrote your answer. Overpass and spatialite are both great tools with strenghts and weaknesses, it's good to know them both.

(31 May '15, 21:39) Vincent de P... ♦

You don't have to install it now - just use the Wikidata+OSM sparql query - answer below :)

(06 Sep '17, 08:35) nyuriks

Answer 3

2

For those searching for a solution via internet search:

It's now possible within Overpass:

http://overpass-turbo.eu/s/EYC

// return keys that don't have same values
[bbox:{{bbox}}];
nwr[highway=footway][name]["name:en"]["name:ga"]
(if: t["name"] != t["name:en"] && t["name"] != t["name:ga"]); 
out geom;

I'm sure it can be developed further.

permanent link

answered 04 Jan '19, 21:39

DaveF
3.3k●84●98●133
accept rate: 16%

edited 04 Jan '19, 21:41

Answer 4

1

I can't answer your query but are you aware that we have our own instance of overpass at http://overpass-turbo.openstreetmap.ie/

There are some folks in the Irish chatroom who might be able to assist also

http://wiki.openstreetmap.org/wiki/WikiProject_Ireland#Contacting_Irish_Mappers

permanent link

answered 29 May '15, 14:56

DaCor
1.3k●1●11●29
accept rate: 2%

Thats neat but it throws an error with my query: "Error: runtime error: open64: 111 Connection refused /home/roles/overpass/db//osm3s_v0.7.52_osm_base Dispatcher_Client::3"

(29 May '15, 17:06) Ogmios

Answer 5

You can use OSM+Wikidata service to find it.

#defaultView:Map
SELECT ?osmid ?name ?nameen ?namega ?loc {
  # subjects must have all 3 tags: name, name:en, name:ga
  ?osmid osmt:name ?name ;
         osmt:name:en ?nameen ;
         osmt:name:ga ?namega .

  # "name" is not the same as "name:ga" or "name:en"
  FILTER (?name != ?nameen && ?name != ?namega)

  # Limit to those that are within 500km around Ireland "center"
  # The center is taken from Wikidata Q27 (Ireland), P625 (location)
  wd:Q27 wdt:P625 ?irelandCenter .
  SERVICE wikibase:around {
    ?osmid osmm:loc ?loc .
    bd:serviceParam wikibase:center ?irelandCenter .
    bd:serviceParam wikibase:radius "500" .
  }
} LIMIT 100

Compare values with Overpass

Follow this question

Related questions