[postgis-users] Finding points in a circle

[Simon Greener]

Niklas,

What values for theradius do you give st_dwithin given that you don't know the values of the radius value in the table? What if you supply a radius of 1,000KM and there happens to be a point outside this whose radius is 1,500KM?

regards
SImon
>
> 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
>>
>>



-- 
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
  Email: simon at spatialdbadvisor.com
  Voice: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3