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

Thu Jul 10 12:16:42 PDT 2014

Ok thanks for the follow up. I will try this tonight and report the
results.

On Thu, Jul 10, 2014 at 10:31 AM, Rémi Cura <remi.cura at gmail.com> wrote:

> Damn I'm really tired.
> Second join is useless.
> So you end up like this :
>
> WITH my_input_polygon AS (
>  SELECT geom
> FROM sdgis.parcels4326 AS parcels
>  WHERE apn = 3500600300
> )
> ,filtering_with_geometry AS (
> SELECT mip.geom AS target,  p.geom
> FROM my_input_polygon AS mip
> INNER JOIN sdgis.parcels4326 AS p ON
> (ST_DWITHIN(mip.geom,p.geom,2*100)=TRUE)
> )
> SELECT fw.geom
> FROM filtering_with_geometry AS fw
> WHERE ST_DWITHIN(target::
> geography,fw.geom::geography,
> 100)=TRUE
>
>
>
> 2014-07-10 18:54 GMT+02:00 Alexander W. Rolek <a.rolek at gmail.com>:
>
> This looks great. I will give it a run tonight and let you know the
>> results. I really appreciate the suggestions.
>>
>>
>> On Thu, Jul 10, 2014 at 9:36 AM, Rémi Cura <remi.cura at gmail.com> wrote:
>>
>>> 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
>>>>>
>>>>
>>>>
>>>
>>
>>
>> --
>> 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/14db2288/attachment.html>