[postgis-users] postgis-users Digest, Vol 203, Issue 11

[David M. Kaplan]

Hi,

I tried adding a gist index to geom::geography and oddly enough it made 
the query a lot slower... Not really sure why, but the best option in my 
case still seems to be to first doing a bounding box reduction in 
lon-lat space and then apply ST_DWithin after casting to geography...

Cheers,
David


On 15/01/2019 21:00, postgis-users-request at lists.osgeo.org wrote:
> From: Devdatta Tengshe <devdatta at tengshe.in> If you are running a 
> query on the Geography, you should create the index on Geography, 
> using something like this: Create Index g_geog_idx on mySchema.myTable 
> using GIST(geom::geography); Using this increased the speed of my 
> query 1000 times. Source: 
> https://twitter.com/postgis/status/675001071505383424 Regards, 
> Devdatta On Tue, Jan 15, 2019 at 4:07 PM David M. Kaplan 
> <david.kaplan at ird.fr> wrote:
>> Hi,
>>
>> For a while it has not been clear in my head if and when GIST indexes are
>> used when doing geography based calculations. If the data is stored in a
>> table with a geometry column that has a GIST index on it, will that index
>> be used if one does something like
>> ST_DWithin(a.geom::geography,b.geom,1000)? If not, then if the data was
>> stored in geography format instead of geometry, would this make the index
>> more useful?
>>
>> To give a specific context, I have a table of point geometries with SRID
>> 4326 and a GIST index, and I want to find the number of points in that
>> table that are within a certain distance of each other point in that table,
>> but I am not sure what is the most efficient way to do so. To do this, I
>> have a query of the form:
>>
>> SELECT a.gid, a.geom, count(*) AS num_points
>>
>> FROM mytable a
>>
>> JOIN mytable b
>>
>>       ON a.gid<>b.gid
>>       AND ST_DWithin(a.geom::geography,b.geom,1000,FALSE)
>>
>> GROUP BY a.gid,a.geom
>>
>> ;
>>
>>
>> This is quite slow, so I presume the GIST index is not being used (and
>> EXPLAIN didn't show anything that made it clear that it was being used
>> though bounding box comparisons are included in the join filter). Also
>> adding a lonlat bounding box comparison does speed the calculation up
>> significantly:
>>
>> SELECT a.gid, a.geom, count(*) AS num_points
>>
>> FROM mytable a
>>
>> JOIN mytable b
>>
>>       ON a.gid<>b.gid
>>       AND a.geom <#> b.geom < 0.02 -- in region where 0.02 degrees is > 1000 m
>>       AND ST_DWithin(a.geom::geography,b.geom,1000,FALSE)
>>
>> GROUP BY a.gid,a.geom
>>
>> ;
>>
>>
>> Is this the best approach or am I missing something? Would using a LATERAL
>> join improve things?
>>
>> Thanks,
>> David

-- 
**********************************
David M. Kaplan
Charge de Recherche 1

Institut de Recherche pour le Developpement (IRD)
UMR MARBEC (IRD/Ifremer/CNRS/UMII)
av. Jean Monnet
CS 30171
34203 Sete cedex
France

Email: david.kaplan at ird.fr
Phone: +33 (0)4 99 57 32 25
Fax: +33 (0)4 99 57 32 95

http://www.umr-marbec.fr/kaplan-david.html
http://www.davidmkaplan.fr/
**********************************

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190117/ea6d70bc/attachment.html>