4

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?

ndawson
  • 27,620
  • 3
  • 61
  • 85
rady
  • 141
  • 1
  • How is your data stored? geometry or geography type? http://gis.stackexchange.com/questions/6681/what-are-the-pros-and-cons-of-postgis-geography-and-geometry-types – Mapperz Dec 10 '14 at 21:16
  • Geometry I do belive – rady Dec 10 '14 at 21:25

1 Answers1

4

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;
ndawson
  • 27,620
  • 3
  • 61
  • 85
  • 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. – Regina Obe Dec 11 '14 at 01: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 05: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
  • @rady Sorry I do not know, but for my application the precision has not the highest priority. Being a few hundred meters off does not really matter to me. Maybe this: http://gis.stackexchange.com/questions/74274/st-shortestline-gives-different-results-for-4326-and-900913-projections-which-i helps you to answer the question – Benjamin Dec 16 '14 at 17:01