How to performantly find data in osm postgrsql database?

Question

Hello everyone.

In case that on the way to the select something went wront, here are the steps i used to populate my database:

1) I downloaded the germany-latest.osm from geofabrik and used osmfilter on it, to reduce the amount of data. Because less data can't be bad, right? The used command is:

osmfilter D:\osm\germany-latest.osm --keep= --keep-ways="water= or waterway= or natural= or leisure= or landuse= or landcover=" --keep-relations="water= or natural= or leisure= or landuse= or landcover= or waterway=" -o=D:\osm\germany-filtered.osm

2) I importet the data into a default installation of postgrsql + postgis with cygwin. The used command, which didn't show any errors is:

osm2pgsql.exe d:\osm\germany-filtered.osm -d osm -U postgres -P 5432 -S d:\osm\default.style --hstore --keep-coastlines --host localhost

Now i have, as far as i can tell by looking into it with pgAdmin, a database with all the data i want to use. But accessing those data is terrible slow and i have no idea why. Is it that I'm not using an ssd? Is something wrong with my SQL command? Either way, it takes 48-50s to get data, no matter how often i've executed it before. Shouldn't some form of caching kick in when i fire the same request repeadetly? On the good side, after a bit of playing around the data aquired by that sql command seems to be correct :-).

Select * from planet_osm_polygon z where ST_DWITHIN(Geography(ST_Transform(z.way,4326)), T_GeographyFromText('POINT(7.46362209 51.47892401)'),500);

What I'm trying to get is "everything within 500m of coordinate" and i would like to get this kinda faster than those 48-50s... especially since theres going to be more than 1 concurrent request at a time.

Accepted Answer

PostGIS will normally use spatial indexes to speed up these types of queries. These indexes have been created by osm2pgsql but your query is formulated in a way that renders them ineffective. Hence PostGIS must actually compute the difference between your point and every single polygon in the database.

Your database is in Mercator metres since you haven't specified "-l" on import. Therefore, do

SELECT * FROM planet_osm_polygon 
WHERE ST_DWITHIN(way, 
   ST_TRANSFORM(ST_SETSRID(ST_MAKEPOINT(7.46362209,51.47892401),4326),3857), 500);

to utilize the index. If you are worried about the discrepancy between Mercator metres and real metres, you can still use a more precise calculation based on ST_DWITHIN or ST_DISTANCE with geography data types, but only after you have filtered out the candidate objects with the index - i.e. you might use a distance of 1000 in the query above, and then add your WHERE condition at the end.

Use the EXPLAIN command to have PostGIS tell you whether it can use an index. EXPLAIN is a science unto itself but as a rule of thumb, anything that says "sequential scan" in there means slowness.

How to performantly find data in osm postgrsql database?

Follow this question

Related questions