This is a static archive of our old OSM Help Site. Please post any new questions and answers at community.openstreetmap.org.

Overpass API: Efficient location name query

0

Hi,

I'm looking for the most performant Overpass QL query to search for:

  • a part of a location name
  • inside a given country
  • with one of the tags "place", "natural"

I tried (Country "de", location "Zugspitze"):

[out:json][timeout:30];
area["ISO3166-1"="DE"][admin_level=2];
node["name"~"Zugspitze"];
out;

I noticed:

  • part name queries like the one above or regular expression name queries are very slow
  • actually the fastest one is just to search for node["name"="Zugspitze"] without the area restriction
  • any restriction makes the query slower

E.g. I tried to limit the results like this (to places and naturals):

[out:json][timeout:30];
area["ISO3166-1"="DE"][admin_level=2];
node["name"~"Zugspitze"][~"(place|natural)"~"."];
out;

My very simple use case: I want to give the user the possibility to search for a location and limit the results to certain place/natural types (like cities or mountains). It's a simple location search like we see on many mapping portals - and all of them are way faster than the above. How is this done with Overpass? Or is there a completely different way I'm missing?

Thanks!

asked 20 Sep '20, 19:15

Lukey78's gravatar image

Lukey78
31●2●2●3
accept rate: 0%

One Answer:

2

Overpass is a very generic query engine and it emphasizes flexibility over performance. Since everyone is using the same Overpass instance and people have vastly different requirements, optimizing towards one use case would often make a different use case slower.

You will get the best performance by importing OSM data into a PostGIS database and preprocessing/ indexing it according to your special use case.

answered 20 Sep '20, 20:15

Frederik%20Ramm's gravatar image

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

Ok, that makes sense. Thank you.

I created a location database with some additional tag info from a database I use for my map server (created with osm2pgsql) like this:

CREATE TABLE location ("osm_id" BIGINT PRIMARY KEY, "name" TEXT, "ele" TEXT, "lat" FLOAT, "lon" FLOAT, "way" geometry(Point,3857), country_code VARCHAR(3), "place" TEXT, "natural" TEXT, "tourism" TEXT);

INSERT INTO location SELECT osm_id,name,ele,ST_Y(ST_Transform(way, 4326)) AS lat,ST_X(ST_Transform(way, 4326)) AS lon, way, null, "place", "natural", "tourism" FROM planet_osm_point WHERE name IS NOT NULL and ("place" OS NOT NULL or "natural" IS NOT NULL or "tourism" IS NOT NULL);

CREATE INDEX location_name_idx ON location (lower(name));

Now I just need to find a way to insert the country_code.

I could use the Nominatim API of Overpass to query the ISO3166-1 code for all coordinates with an is_in query based on areas of admin_level=2.

Or is there a better way to do that on the SQL/Postgis level?

(22 Sep '20, 20:26) Lukey78

A standard osm2pgsql load without --hstore will not give you a country code column on the country boundaries. You'd either have to add that to the config, or use --hstore on import. Then, something like

UPDATE location 
SET country_code = tags->'ISO3166-1' 
FROM planet_osm_polygon 
WHERE st_contains(planet_osm_polygon.way, location.way)
AND admin_level='2' 
AND boundary='administrative';

should add country codes to your location table.

There's a little thing I neglected to say in my initial reply; some POIs that you might be interested in could be mapped as an area, not as a point. You might want to repeat your INSERT INTO... query with planet_osm_polygon as the source, this time using st_centroid(way) instead of just way - this will then reduce the polygons to points and add them into your location table.

(22 Sep '20, 22:16) Frederik Ramm ♦

That is very helpful, thank you for the fast and comprehensive answer!

(23 Sep '20, 19:37) Lukey78

OSZAR »