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

[Guido Lemoine]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140709/16f3bedc/attachment.html>