postgress gis cordinates query

Question

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

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

postgress gis cordinates query

Follow this question

Related questions