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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
