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

[Rémi Cura]

Oups, sent it to early.
If you trust the planner you can simply try :

WITH my_input_polygon AS (
 SELECT geom
FROM sdgis.parcels4326 AS parcels
 WHERE apn = 3500600300
)
SELECT mip.*
FROM my_input_polygon AS mip
INNER JOIN sdgis.parcels4326 AS p ON
(
(ST_DWITHIN(mip.geom,p.geom,2*100)=TRUE)
AND (ST_DWITHIN(mip.geom::geography,p.geom::geography,
100)=TRUE)
)

Cheers,
Rémi-C


2014-07-10 18:33 GMT+02:00 Rémi Cura <remi.cura at gmail.com>:

> A single statement
> , the 2 step is only metaphorical (or can be enforced if you use CTE).
> Somehting liek this (untested)
>
> WITH my_input_polygon AS (
>  SELECT geom
> FROM sdgis.parcels4326 AS parcels
>  WHERE apn = 3500600300
> )
> ,filtering_with_geometry AS (
> SELECT p.*
> FROM my_input_polygon AS mip
> INNER JOIN sdgis.parcels4326 AS p ON
> (ST_DWITHIN(mip.geom,p.geom,2*100)=TRUE)
> )
> SELECT fw.*
> FROM filtering_with_geometry AS fw
> INNER JOIN sdgis.parcels4326 AS p ON
> (ST_DWITHIN(mip.geom::geography,p.geom::geography,
> 100)=TRUE)
>
> Cheers,
> Rémi-C
>
>
> 2014-07-10 18:01 GMT+02:00 Alexander W. Rolek <a.rolek at gmail.com>:
>
> The 2 step query is an interesting idea. Would I need to do that in 2
>> queries, or can that be accomplished in a single statement?
>>
>>
>>
>> On Thu, Jul 10, 2014 at 8:58 AM, Rémi Cura <remi.cura at gmail.com> wrote:
>>
>>> Hey,
>>> I'm glad you found out.
>>> I never tried but you could probably try to index on the result of the
>>> cast :
>>> CREATE INDEX ON ... USING GIST ((CAST geom AS GEOGRAPHY))
>>> .
>>>
>>> However if your distance are not that big (compaired to earth curve),
>>> stick to geometry.
>>> If your distances are big, go geography.
>>> You could also simply do it in 2 steps, a first step with geometry and
>>> maybe 2 times the distance to be sure on all the table, a second step more
>>> precise with geography on only the result of the previous filter.
>>>
>>> Cheers,
>>> Rémi-C
>>>
>>>
>>>
>>> 2014-07-10 17:47 GMT+02:00 Alexander W. Rolek <a.rolek at gmail.com>:
>>>
>>> Remi -
>>>>
>>>> Thanks again for the quick response. I dug into this a bit more last
>>>> night, and apparently the issue was the distance I was using with the
>>>> ST_DWithin call. When I had a projection under 2230, the unit of measure
>>>> was feet. With 4326 it appears to be degrees. I kept the distance of 100 on
>>>> both tests, so the 2230 project returned quick, but a distance of 100 on a
>>>> 4326 projection is very large so it was returning my whole table. When I
>>>> changed the distance to something small (0.0009) my query is now fast. ;-)
>>>>
>>>> My next challenge will be figuring out the best way to convert between
>>>> degrees & meters. I understand the geography type is better for queries
>>>> like this, but I'm already using the geometry type for other queries. Is it
>>>> common to have both geometry and geography for a record in a table? I know
>>>> I can cast using ::geography but the queries are dramatically slower.
>>>>
>>>> Alex
>>>>
>>>>
>>>> On Thu, Jul 10, 2014 at 1:11 AM, Rémi Cura <remi.cura at gmail.com> wrote:
>>>>
>>>>> 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
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Alexander W. Rolek
>>>> 303-829-9989
>>>>
>>>
>>>
>>
>>
>> --
>> Alexander W. Rolek
>> 303-829-9989
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140710/9b36d55c/attachment.html>