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

[Rémi Cura]

Hey,
please _don't_ use st_buffer
, but ST_DWithin (http://postgis.net/docs/ST_DWithin.html).

This querry should be around 30 ms.

You need to add an index to the geometry :
CREATE INDEX parcels_geom_gist_index ON sdgis.parcels4326 USING GIST(geom) ;

Now if you want to use the field "id", you also need an index on it (unless
it is already a primary key):
CREATE INDEX parcels_id_btree_index ON sdgis.parcels4326 (id) ;

Depending on your use case you may need to use the GEOGRAPHY type instead
of the geometry.

Your querry could look like this (there could be more efficicient way, at
the price of readability)

WITH my_input_polygon AS (
SELECT *
FROM sdgis.parcels4326 AS parcels
WHERE id = #ID_OF_TARGET_POLYGON#
)
SELECT p.*
FROM my_input_polygon AS mip
INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);

It sholdn't take more than few dozen milliseconds.

Cheers,
Rémi-C




2014-07-09 9:25 GMT+02:00 Guido Lemoine <guido.lemoine at jrc.ec.europa.eu>:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140709/ab30650e/attachment.html>