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

[Rémi Cura]

Hey,
If I take into account your last mail,
you probably have forget to transform your data.

If you have a table in srid 4326
, you can't use a transform in your querry if you want it to uses indexes.
OR you have to buil and index like this :
CREATE INDEX ON sdgis.parcels4326 USING GIST (ST_Transform(geom, 4326));

So can you confirm thatl your geom have the correct srid in the correct
table?

SELECT DISTINCT ST_SRID(geom)
FROM sdgis.parcels ;

and
SELECT DISTINCT ST_SRID(geom)
FROM sdgis.parcels4326 ;

You uses pgadmin , so instead of running your querry
, run it with "explain analyse" (Select the text of your querry, then press
shift+F7).
You should see a graphic explaining what it happening. You should look for
sequential scans.

You can also try the same querry without CTE :

SELECT p.*
FROM (
SELECT geom
FROM sdgis.parcels4326 AS parcels
 WHERE apn = '3500600300
) AS mip
INNER JOIN sdgis.parcels4326 AS p ON (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);


Of course vacuum analyze both table before running the querry.
It would be difficult to help you more without detailed information (tables
declarations, querry used, result of explain analyse), because slowness can
come from several reasons .

Here is a link of requirements for people having slow querry on postgres
mailing list (https://wiki.postgresql.org/wiki/Slow_Query_Questions).
I'm not guru so I couldn't use all this informations but that give you an
idea of how many reasons of slow query they can be

Cheers,
Rémi-C




""
I tried the query that Remi suggested (thank you!) and it works and doesn't
work. I have the exact same table in two different projections (2230,
4326). When I run the ST_Dwithin query against the 2230 table, I get
results in under 100 ms. When I run the same query against the 4326
projection it still takes around 270 seconds! I have confirmed that I have
the gist index on the 4326 table. Any ideas why the 4326 projection would
be dramatically slower?

""


2014-07-09 18:38 GMT+02:00 Alexander W. Rolek <a.rolek at gmail.com>:

> Remi -
>
> Thanks for the quick response. Sorry to respond to you directly, but my
> message settings are set on digest so I can't respond to the thread yet.
>
> I went through your steps, and have built out my query, but it's still
> taking around 270 seconds to run the query, and it's returning ever record
> in my table. Here's my query:
>
> WITH my_input_polygon AS (
> SELECT geom
> FROM sdgis.parcels4326 AS parcels
>  WHERE apn = '3500600300'
> )
> SELECT p.*
> FROM my_input_polygon AS mip
> INNER JOIN sdgis.parcels4326 AS p ON
> (ST_DWITHIN(mip.geom,p.geom,100)=TRUE);
>
> I also have indexes on the apn and the geom columns (see attached screen
> shots)
>
> Any ideas what I'm missing here?
>
> Thanks again for the help
>
> --
> Alexander W. Rolek
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140710/d3dd9589/attachment.html>