[postgis-users] Finding records withing XX distance from a geometry in the same table

[tommaso]

You could use DWithin:  
http://blog.cleverelephant.ca/2010_08_01_archive.html
Because your geometries are in 4326, you can cast the them and the point  
to geography (without ST_transform) and then use DWithin:

WHERE ST_DWithin(parcels.geom::geography, yourpoint::geography, 100)

Regards Tommaso



On Wed, 09 Jul 2014 09:25:59 +0200, Guido Lemoine  
<guido.lemoine at jrc.ec.europa.eu> wrote:

>
> Alexander,
>
>
> You would want to check out ST_BUFFER, which is doing what you intend to  
> do.
>
>
> Guido
>
>
>
> From: postgis-users-bounces at lists.osgeo.org  
> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Alexander W.  
> Rolek
> Sent: 09 July 2014 05:36
> To: postgis-users at lists.osgeo.org; Jeffrey Johnson
> Subject: [postgis-users] Finding records withing XX distance from a  
> geometry in the same table
>
>
> I'm trying to query all the records within XX distance from a geometry.  
> I would like to be able to reference the subject >geometry via it's id,  
> and then find every geometry that is either:
>
>
> A) within XX distance from a x/y coordinate of the geometry
>
> B) within XX distance from the edge of the geometry on all sides. (ideal)
>
>
> What is the best way to accomplish this? My current query:
>
>
> SELECT *
>
>            FROM sdgis.parcels4326 AS parcels
>
>            WHERE ST_Distance_Sphere(
>
>                        parcels.geom,
>
>                        ST_Transform(ST_SetSRID(ST_MakePoint('6255896.314288','1898596.929109'),2230),4326)
>
>            ) < 100.0;
>
>
> This takes a few minutes to run on a table with just over 1 million  
> records. I would like to swap out the ST_MakePoint, >with a multipolygon  
> from a record that is looked up via it's id.
>
> Any suggestions are greatly appreciated.
>
>
> --Alexander W. Rolek



-- 
Using Opera's mail client: http://www.opera.com/mail/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140709/ec0ea3bb/attachment.html>