[postgis-users] Finding points in a circle

[nicklas.aven at jordogskog.no]

Hi
 
No you don't have to make a circle first. One way is to just do a radius search from your point with the given radius. 
If you have one column with latitude and one with longitude and one with radius then this should work
 
select * from thetable where st_dwithin(randompoint,ST_MakePoint(theLongitud, theLatitud), theradius);

The query might get slow if you have a big table as you descibed.
Then it might be a good idea to create a geometry column and store the latlongs as points.
 
SELECT AddGeometryColumn( 'public', 'thetable', 'thepoint_lonlat', 4269, 'POINT', 2 ); 
The above code will create a geometry column named thepoint_longlat in the table zctas that validates to make sure the inputs are 2-dimensional points in SRID 4269 (NAD83 longlat). 
the above is copied from www.bostongis.org
 
and then fill thegeometry with data like:
update thetable setthepoint_lonlat= st_makepoint(ST_MakePoint(theLongitud, theLatitud))
 
then you can make a gist-index on the column.
 
and I think in your case it could work with making a gist index like
 
create index idx_lat_long_radius
on thetable
using gist(st_buffer(thepoint_lonlat, theradius))
 
then you should get boundingboxes stored for each point/radius-circle
 
Then you can run the above query:
select * from thetable where st_dwithin(randompoint,thepoint_lonlat, theradius);
 
I'm not good in indexes, but I think this will be a fast solution.
 
/Nicklas
 

2009-04-14 Tony Elmore wrote:

Admittedly, I have not spent a tremendous amount of time with this yet 
>- so I'm not really asking for a solution here, but would like some 
>feedback to determine if I'm on the right track.
>
>Is this scenario possible using postGIS?
>
>i have many items (hundreds of thousands) with each item containing a 
>lat / lng and a radius (each item could have a different radius). The 
>circle created by the lat / lng and radius for each item would in all 
>likelihood overlap with other items.
>
>Given a random point, is it possible to retrieve all of the rows where 
>the random point is inside the circle created from the lat / lng and 
>radius?
>
>I would assume I should create the circle for each item first and 
>store that as geometry.
>
>Could someone please verify that this scenario is appropriate for 
>postgis.
>
>Regards,
>Tony
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090414/eb672526/attachment.html>