1

I'm super new with this subject and was assigned the task of creating a query to find objects around a given point using some type of radius search using Postgresql with PostGis. After some research I came across the ST_DWithin method which is perfect for this.

I'm currently having some issues with the query. Here is how it looks like:

SELECT * FROM waypoints WHERE ST_DWithin(lonlat, '002000000100000f11c05e9335158b82804047dcae85b9e8c4', 10)

This is a table that stores lonlat using SRID=3857 and I'm creating the second geometry using FACTORY.point(circle.fetch('lon'), circle.fetch('lat')) from Ruby RGEO

Now the documentation states that if I'm using geometries then the third parameter distance_of_srid should be in the units of the geometries.

The problem is that both lonlat and the point are using SRID 3857 which from what I understood correctly, uses units in meters. Logically I would expect that distance_of_srid would accept distances in meters. But when I send the query I get waypoints that are kilometers away not 10 meter away. But if I break it down to say .01 then it brings waypoints that are within 10 meters or at least that's what it seems.

I feel like I'm lacking some knowledge to overcome this but not sure what it is or where is a good place to get more acquainted with this subject. If someone could help me with this issue or point me to a better direction that would be awesome!

Update:

Based on a comment below I tried doing the following:

ST_DWithin(ST_Transform(archived_listings.lonlat, 4326)::geography, ST_GeogFromText('SRID=4326;POINT(-122.300115 47.724076)'), 10000.0)

But unfortunately I'm getting nothing with 10 km as before I was getting a house that was closer than that.

  • 2
    The geometry you are using is not in EPSG:3857, it's pretty clearly in EPSG:4326. select st_astext('002000000100000f11c05e9335158b82804047dcae85b9e8c4') – Paul Ramsey Feb 2 '18 at 19:26
  • @PaulRamsey Thanks for the quick reply! How did you know its EPSG:4326, when I run the query I just get "POINT(-122.300115 47.724076)". Also when I create the Factory I use Geo::Geos::CAPIFactory.create(srid: 3857) – Kunashu Feb 2 '18 at 19:41
  • that is lat/lon because if it was in metres it would be on Null Island – Ian Turton Feb 3 '18 at 10:04
  • @IanTurton couldn't get what you meant by your comment. Can you please elucidate? – psr Feb 3 '18 at 10:36
  • See en.m.wikipedia.org/wiki/Null_Island – user30184 Feb 3 '18 at 11:27
1

After some further debugging and taking into account the comments in the question. I realized that the data stored in the lanlot column was actually SRID=4326 even do the column states that it's a SRID=3857 geometry.

I came to this conclusion because when I plugged the coordinates as a 3857 in a map I was placed very close to the null island. But when I put the same coordinates as 4326 I was place in the correct location.

Since we already had over 1 million entries on the production database there was no time to change the column so the query ended up been something like:

SELECT * FROM waypoints 
WHERE ST_DWithin(ST_SetSRID(lonlat, 4326)::geography, 
                 ST_GeomFromText('POINT(-122.300045 47.723779)', 4326)::geography, 
                 10)

And it works!!

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.