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.

asked 15 Nov '15, 20:57

EinFreierNick's gravatar image

EinFreierNick
1214512
accept rate: 50%

You could likely skip the "osmfilter" step by writing a good style file for the osm2pgsql import.

(15 Nov '15, 22:27) Frederik Ramm ♦

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.

permanent link

answered 15 Nov '15, 22:26

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
70.0k806341097
accept rate: 24%

executing that commands give me the following error message:

ERROR: Operation on mixed SRID geometries * Error *

ERROR: Operation on mixed SRID geometries SQL state: XX000

(16 Nov '15, 16:50) EinFreierNick

if i switch positions for 4326 and 3857, the query executes - but delivers no results no matter how high i set the meteres

(16 Nov '15, 17:45) EinFreierNick

You might have imported your database in 900913 not 3857 - try switching 3857 in my example to 900913. Both are equal but PostGIS doesn't know.

(16 Nov '15, 19:02) Frederik Ramm ♦

Ah :) This works. Execution time for the first execution was 72ms. Every execution afterwards was 23-25ms. That is a time that allows me to go further :) THANK YOU.

(16 Nov '15, 20:05) EinFreierNick
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×143
×120
×17

question asked: 15 Nov '15, 20:57

question was seen: 3,304 times

last updated: 16 Nov '15, 20:05

powered by OSQA