[postgis-users] intersects (topogeom, topogeom) is too slow

[Jose Carlos Martinez Llario]

On 17/04/2012 23:42, Andy Colson wrote:
> On 4/17/2012 4:36 PM, Andy Colson wrote:
>> On 4/17/2012 4:00 PM, Jose Carlos Martinez Llario wrote:
>>> Hi,
>>> first I wanted to say thanks because Im asking a lot of questions 
>>> lately
>>> and you guys always answer me in a fast and kind way. Sandro you
>>> specially must be tired of me. sorry!! :)
>>>
>>> now another more question.
>>>
>>> I found the function intersects (topogeom, topogeom) quite interesting
>>> even it is not documented, because I was expecting it to show me the
>>> persistent topology performance.
>>>
>>> I have a table called suelos (10000 polygons) with a normal geometry
>>> column (geom) and a topology column (topogeom) where I populated the
>>> geometries.
>>>
>>> This query takes 25 secs.
>>> select count(*) from suelos s1, suelos s2 where st_intersects (s1.geom,
>>> s2.geom);
>>>
>>> I was expecting this query to take 1 or less secs:
>>> select count(*) from suelos s1, suelos s2 where intersects 
>>> (s1.topogeom,
>>> s2.topogeom);
>>>
>>> but after taking more than 10 mins I cancelled it.
>>>
>>> Then I noticed that the topogeom column is not using any spatial index
>>> then I added a column:
>>>
>>> s1=# alter table suelos add column box geometry (polygon, 23030);
>>> s1=# update suelos set box = st_envelope (topogeom::geometry);
>>> s1=# create index gist_suelos_topogeom using gist (box);
>>>
>>> and the following sentence took 60 secs (still much more that I was
>>> expecting)
>>> select count(*) from suelos s1, suelos s2 where s1.box && s2.box and
>>> intersects (s1.topogeom, s2.topogeom);
>>>
>>>
>>> Then my questions?
>>>
>>> - How can we use spatial index with topogeometry layers, making a new
>>> box column and keep it synchronized? Why topogeom composite type does
>>> not include a box that we can index?
>>> - The topogeom composite type is using indexes with its fields?
>>> - Why its taking more time that geometry types if it should have
>>> opposite behavior?
>>>
>>> Regards and thanks,
>>> Jose
>>>
>>>
>>>
>>
>>
>> Have you tried something like:
>>
>> select count(*) from suelos s1, suelos s2
>> where intersects (s1.topogeom, st_envelope(s2.topogeom));
>>
>>
>> > - How can we use spatial index with topogeometry layers, making a new
>> > box column and keep it synchronized?
>>
>>
>> You can put an update trigger on the table to reset the box.
>>
>>
>> -Andy
>
>
>
>
> > select count(*) from suelos s1, suelos s2
> > where intersects (s1.topogeom, st_envelope(s2.topogeom));
>
>
> Oops.  Forget this part, it makes no sense at all, sorry.
>
>
>
> >> and the following sentence took 60 secs (still much more that I was
> >> expecting)
> >> select count(*) from suelos s1, suelos s2 where s1.box && s2.box and
> >> intersects (s1.topogeom, s2.topogeom);
>
>
> You are comparing every row of one table to every row of the other. 
> That is probably not a real world query, is it?  Wont you have some 
> other conditions you can put on?  A bounding box to draw?  A click 
> point?  How real world is this query?
Its not related with that. it makes sense. You can check 
self-intersections, self-overlaping in one layer or any other issue. 
With two different layers the same problem remains.
>
> -Andy
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users