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

postgress gis cordinates query

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

query gis postgress osm2pgsql

asked 29 May '18, 11:56

altopalo
53●8●10●13
accept rate: 0%

edited 29 May '18, 11:57

One Answer:

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.)

answered 29 May '18, 13:03

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

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