3

First Time here. Anyway, I have a database that I want to use to store user locations. I want to be able to get all the users with in say 15 miles. How do I do this. I'm using PostGIS 2.1. My SQL query is SELECT * FROM locations WHERE ST_DWithin(usercoord, ST_GeomFromText('POINT(%s %s)', 4326), %s) AND NAME!=%s; usercoord is he geometry table with a SRID of 4326. I tried I'm using Python to execute the query. %s is just a place holder. The third %s is for the distance. I tried reading but I got even more confused. should the distance be in radians or should the distance be in km? I tried making it into radians. However, it didnt look like it worked. I put in 3959 in distance and got all of the users in then table. I have Users in Michigan, US and Pompei, Italy so that can't be in Km or Mi. But then I tried to convert the miles into radians and that didnt select the people with in 15 miles. So how should I get all the users with in x miles?

3

ST_DWithin uses units which match your geometry's CRS. Since your geometry has a CRS of 4326 the distances used will be in degrees - not very helpful! However, if you use 'geography' types rather than 'geometry', then ST_DWithin always uses metres for distance.

So, you'll need to first convert your geometry into a geography type, so that the measurements used by ST_DWithin will be in metres:

SELECT * FROM locations WHERE ST_DWithin(usercoord::geography,  ST_GeomFromText('POINT(%s %s)', 4326)::geography, %s) AND NAME!=%s;
  • Its, working better. However it isn't very accurate. I Have two points that are 24 km apart but when I try to query on a 24.09 km radius, it doesn't show up. Is there away to improve the accuracy. it appears that the distance is 26 km about.and by that I mean it is the distance at which they show up. – rady Dec 10 '14 at 23:58
  • 1
    Have you verified with ST_Distance(usercoord::geography, ST_GeogFromText('POINT(%s %s)') ) what the answers are you are getting. You could be running into a bug. Which version of PostGIS are you running. There was an issue with ST_DWithin for geography with 2.1.1 and below as I recall. – LR1234567 Dec 11 '14 at 1:41
  • @LR1234567 I haven't tried that and I;m running 2.1.1 i think. I know its 2.1 but not sure of the other number. – rady Dec 11 '14 at 5:19
  • I had a similar problem for getting distances in my application. I decided to go with the google projection (SRID:900913) which has meters as units. The reason why I changed from SRID:4326 was that the cast to geography costs around 100ms each cast. I had a total of just 3 casts, summing up to 300ms just casting geometries. Once I switched the projection the query runs blazing fast with 23ms, which is a tenth of the old query with casts – Benjamin Dec 13 '14 at 16:12
  • @Benjamin How accurate is it? – rady Dec 15 '14 at 20:16

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.