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

[Rémi Cura]

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/6b206a5c/attachment.html>