[postgis-users] Re: truly_inside

[David Blasby]

Juanse wrote:

> Is the distance() method too slow, i am runnig
>
> select count(*) t0,t1 where distance(t0.wkb_geomtry, t1.wkb_geometry)= 0 and
> (t0.nombre = 'Galvarino')
>
> and it takes for ages
>
> t0 have 31 records
> t1 have 1321 records

try an:
explain select count(*) t0,t1 where distance(t0.wkb_geomtry, t1.wkb_geometry)= 0
and
(t0.nombre = 'Galvarino')

you're probably doing a full join - thats 40,000 distance() calculations!

You can do this to use an index and speed things up considerably:

SELECT ... FROM t0,t1 WHERE t0.wkb_geometry && t1.wkb_geometry
            AND distance(t0.wkb_geomtry, t1.wkb_geometry)= 0

This will first ensure that the geometry's bounding boxes intersect (&&), then
do the expensive distance calculation.

Dont forget to make a GiST index on t0 and t1.

dave