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

[Alexander W. Rolek]

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/1e47801e/attachment.html>