I imported OSM data using osm2pgsql osm (slim) to gis posetgres Now I want to get all roads close to a point. I use this query:

SELECT * FROM planet_osm_line
ORDER BY ST_SetSrid(way, 4326) <-> ST_SetSrid(ST_Point(48.8523,2.3466), 4326) LIMIT 20;

Its give me results but from a point much southeast then the point I asked (center of Paris) I think that something with the SRID but can't find the problem.

Any ideas? Thanks

asked 29 May '18, 11:56

altopalo's gravatar image

altopalo
536812
accept rate: 0%

edited 29 May '18, 11:57


You want to

SELECT * FROM planet_osm_line
ORDER BY way <-> ST_Transform(ST_SetSrid(ST_Point(48.8523,2.3466), 4326), 3857) LIMIT 20;

Your query would only work if you had used -l (ell) during import.

Caveat: Your query does not necessarily give you the results in the order "nearest to farthest" because you are using a projected coordinate system; you can experiment by having PostGIS give you the ST_Distance of st_transform(way,4326)::geography and ST_Point(48.8523,2.3466)::geography to see this. It is possible that a way that is exactly 50.1 metres north of your point is displayed before a way that is exactly 49.9 metres east.

(edit: removed a suggestion to use ST_DWITHIN, as I see that the KNN operator is already index-aware.)

permanent link

answered 29 May '18, 13:03

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
70.0k806321097
accept rate: 24%

edited 29 May '18, 13:07

Tn'x, great help. For what I need, I don't really need the nearest order , just some roads around a point. I think I'll use the ST_DWITHIN as you suggest to get all roars in some distance from point instead.

(30 May '18, 11:14) altopalo
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:

×205
×114
×34
×1

question asked: 29 May '18, 11:56

question was seen: 492 times

last updated: 30 May '18, 11:14

powered by OSQA