6

I've read several questions + answers here on SO about this theme, but I can't understand which is the common way (if there is one...) to find all the points whithin a "circle" having a certain radius, centered on a given point.

In particular I found two ways that seem the most convincing:

select id, point 
from my_table 
where st_Distance(point, st_PointFromText('POINT(-116.768347 33.911404)', 4326)) < 10000;

and:

select id, point 
from my_table 
where st_Within(point, st_Buffer(st_PointFromText('POINT(-116.768347 33.911404)', 4326), 10000));

Which is the most efficient way to query my database? Is there some other option to consider?

davioooh
  • 23,742
  • 39
  • 159
  • 250

3 Answers3

8

Creating a buffer to find the points is a definite no-no because of (1) the overhead of creating the geometry that represents the buffer, and (2) the point-in-polygon calculation is much less efficient than a simple distance calculation.

You are obviously working with (longitude, latitude) data so you should convert that to an appropriate Cartesian coordinate system which has the same unit of measure as your distance of 10,000. If that distance is in meter, then you could also cast the point from the table to geography and calculate directly on the (long, lat) coordinates. Since you only want to identify the points that are within the specified distance, you could use the ST_DWithin() function with calculation on the sphere for added speed (don't do this when at very high latitudes or with very long distances):

SELECT id, point 
FROM my_table 
WHERE ST_DWithin(point::geography,
                 ST_GeogFromText('POINT(-116.768347 33.911404)'),
                 10000, false);
Patrick
  • 29,357
  • 6
  • 62
  • 90
2

I have used following query

SELECT *, ACOS(SIN(latitude) * SIN(Lat)) + COS(latitude) * COS(Lat) * COS(longitude) - (Long)) ) * 6380 AS distance FROM Table_tab WHERE ACOS( SIN(latitude) * SIN(Lat) + COS(latitude) * COS(Lat) * COS(longitude) - Long )) * 6380 < 10

In above query latitude and longitude are from database and lat, long are the points from we want to search.

WORKING : it will calculate the distance(In KM) between all the points in database from search points and check if the distance is less then 10 KM. It will return all the co-ordinates within 10 KM.

Kishor N R
  • 1,521
  • 1
  • 17
  • 23
2

I do not know how postgis does it best, but in general:

Depending on your data it might be best to first search in a square bounding box (which contains the search area circle) in order to eliminate a lot of candidates, this should be extremely fast as you can use simple range operators on lon/lat which are ideally indexed properly for this. In a second step search using the radius.

Also if your limit max points is relatively low and you know you have a lot of candidates, you may simply do a first 'optimistic' attempt with a box inside your circle, if you find enough points you are done !

Christophe Roussy
  • 16,299
  • 4
  • 85
  • 85