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

[Alexander W. Rolek]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140710/188781d4/attachment.html>