0

I'm trying to select POIs from around given location within some distance. I tried so far two approaches I found online, but unfortunately none seems to be working - I'm getting no rows despite the fact I can find records from that area by hand.

My current code is:

(1)

SELECT * FROM public.planet_osm_point
WHERE ST_DWITHIN(public.planet_osm_point.way, 
   ST_TRANSFORM(ST_SETSRID(ST_MAKEPOINT(40.6978,-73.9792),4326),3857), 50);

(2)

SELECT *
FROM public.planet_osm_point
WHERE ST_DWithin(
  ST_SetSRID(ST_Point(40.6978,-73.9792), 3857),
  ST_Transform(public.planet_osm_point.way, 3857),
  500
)
LIMIT 200;
  • 1
    what is the coordinates system for the public.planet_osm_point.way ? – Shiko Jan 1 '18 at 20:55
  • 1
    You can get the SRID of osm_point.way using Find_SRID, I think the issue is probably because of difference between coordinates system used. postgis.refractions.net/documentation/manual-1.5/Find_SRID.html – Shiko Jan 1 '18 at 21:14
  • 4
    There aren't many points in Antarctica ({lon=40.70, lat=-73.98}). Did you intend to have a point in Manhattan ({lon=-73.98, lat=40.70})? – Vince Jan 1 '18 at 21:51
  • Once you follow @Vince's suggestion, you'll hit the next issue which is that as written your queries will not take advantage of spatial indexes, so you'll have subpar performance. – Paul Ramsey Jan 1 '18 at 22:51
  • 1
    In the 2nd query, the coordinates you enter are likely in 4326, not in 3857. As it is written, you are looking around a point a few meters away from 0;0. – JGH Jan 1 '18 at 23:11
0

As it turned out there are at least two solutions to my question.

First of all, thanks to Vince's comment I started to lok for POIs in Manhattan where I wanted.

Second, as pointed out by Shiko and JGH I have been using different coordinate systems.

The proper query for #1 should be:

SELECT * FROM public.planet_osm_point
WHERE ST_DWITHIN(public.planet_osm_point.way, 
   ST_TRANSFORM(ST_SetSRID(ST_MAKEPOINT(-73.9792,40.6978),4326),4326), 0.5);

And for #2:

SELECT *
FROM public.planet_osm_point
WHERE ST_DWithin(
  ST_SetSRID(ST_Point(-73.9792,40.6978), 4326),
  ST_Transform(public.planet_osm_point.way, 4326),
  0.5
)
LIMIT 5000;

Please note that I also significantly decreased 3rd argument from 50 to 0.5. Since my type is geometry and not geography this parameter is not distance in meters but 'distance_of_srid' and > 1 starts for SRID 4326 is producing too much results.

However, looking for other clues in different questions/answers I found also idea to add extra index of type geography to be helpful. Indeed, after proceeding as described in this answer it works with meters now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.