[postgis-users] Finding points in a circle

[Simon Greener]

Tony,

Two approaches:

1. Persisted geometry

Add a geometry column (eg circle) to the table and create a polygon via:

update <your table> a
set a.circle = ST_Buffer(ST_MakePoint(<circle x>, <circle y>, [<z>], [<m>]), radius, [integer - number of segments in the circle]);

The query the data as follows:

select *
  from <your table> a
 where ST_Contains(a.circle,ST_MakePoint(<random x>, <random y>));

2. Dynamic, expensive, query

Simply construct a full-table-scan query and execute the test using ST_Point_Inside_Circle()

SELECT *
  FROM <your table> a
 WHERE ST_point_inside_circle(ST_MakePoint(<random x>, <random y>), a.<centre_x_column>,a.<centre_y_column>, a.<radius_column>).

regards
Simon


On Tue, 14 Apr 2009 15:27:18 +1000, Tony Elmore <tonyelmore at me.com> 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