<html>
<head>
<title></title>
<meta content="MSHTML 6.00.6000.16809" name="GENERATOR"></meta>
</head>
<body>
<div>Hi</div>
<div> </div>
<div>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. </div>
<div>If you have one column with latitude and one with longitude and one with radius then this should work</div>
<div> </div>
<div>select * from thetable where st_dwithin(randompoint,ST_MakePoint(theLongitud, theLatitud), theradius);</div>
<div><br />
The query might get slow if you have a big table as you descibed.</div>
<div>Then it might be a good idea to create a geometry column and store the latlongs as points.</div>
<div> </div>
<div>
<span style="FONT-FAMILY: Times New Roman">SELECT AddGeometryColumn( 'public', 'thetable', 'thepoint_lonlat', 4269, 'POINT', 2 ); </span>
<p>The above code will create a geometry column named <i>thepoint_longlat</i> in the table zctas that validates to make sure the inputs are 2-dimensional points in SRID 4269 (NAD83 longlat). </p></div>
<div>the above is copied from <a href="http://www.bostongis.org">www.bostongis.org</a></div>
<div> </div>
<div>and then fill thegeometry with data like:</div>
<div>update thetable set
<span style="FONT-FAMILY: Times New Roman">thepoint_lonlat= st_makepoint(
<span style="FONT-FAMILY: Times New Roman">ST_MakePoint(theLongitud, theLatitud))</span></span></div>
<div>
<span style="FONT-FAMILY: Times New Roman">
<span style="FONT-FAMILY: Times New Roman"> </span></span></div>
<div>then you can make a gist-index on the column.</div>
<div> </div>
<div>and I think in your case it could work with making a gist index like</div>
<div> </div>
<div>create index idx_lat_long_radius</div>
<div>on thetable</div>
<div>using gist(st_buffer(
<span style="FONT-FAMILY: Times New Roman">thepoint_lonlat, theradius))</span></div>
<div>
<span style="FONT-FAMILY: Times New Roman"> </span></div>
<div>
<span style="FONT-FAMILY: Times New Roman">then you should get boundingboxes stored for each point/radius-circle</span></div>
<div>
<span style="FONT-FAMILY: Times New Roman"> </span></div>
<div>
<span style="FONT-FAMILY: Times New Roman">Then you can run the above query:</span></div>
<div>
<span style="FONT-FAMILY: Times New Roman">select * from thetable where st_dwithin(randompoint,
<span style="FONT-FAMILY: Times New Roman">thepoint_lonlat</span>, theradius);</span></div>
<div>
<span style="FONT-FAMILY: Times New Roman"> </span></div>
<div>
<span style="FONT-FAMILY: Times New Roman">I'm not good in indexes, but I think this will be a fast solution.</span></div>
<div>
<span style="FONT-FAMILY: Times New Roman"> </span></div>
<div>
<span style="FONT-FAMILY: Times New Roman">/Nicklas</span></div>
<div> </div>
<div><br />
2009-04-14 Tony Elmore wrote:<br />
<br />
Admittedly, I have not spent a tremendous amount of time with this yet <br />
>- so I'm not really asking for a solution here, but would like some <br />
>feedback to determine if I'm on the right track.<br />
><br />
>Is this scenario possible using postGIS?<br />
><br />
>i have many items (hundreds of thousands) with each item containing a <br />
>lat / lng and a radius (each item could have a different radius). The <br />
>circle created by the lat / lng and radius for each item would in all <br />
>likelihood overlap with other items.<br />
><br />
>Given a random point, is it possible to retrieve all of the rows where <br />
>the random point is inside the circle created from the lat / lng and <br />
>radius?<br />
><br />
>I would assume I should create the circle for each item first and <br />
>store that as geometry.<br />
><br />
>Could someone please verify that this scenario is appropriate for <br />
>postgis.<br />
><br />
>Regards,<br />
>Tony<br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
><br />
></div>
</body>
</html>