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

Guido Lemoine guido.lemoine at jrc.ec.europa.eu
Wed Jul 9 00:25:59 PDT 2014



You would want to check out ST_BUFFER, which is doing what you intend to do.





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:



            FROM sdgis.parcels4326 AS parcels

            WHERE ST_Distance_Sphere(



            ) < 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>

More information about the postgis-users mailing list