Thread: geometry poligons performance and index
Hi I need to set a query which performs a intersection check between two set of polygons which are stored into two tables. I wrote the where instruction so: ... where area(intersection(t1.the_geom,t2.the_geom))>0 ... The results are ok, but the query is too slow. I tried to applay a GIST and gtree index but performance did not improve. By explaining the query I see that the where condition gets the query slow. Is there a more simple way to check if two poligons have a not null intersection (they overlap)? Is there a kind of index I can use ? THanks Giu -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Vuoi un prestito fino a 30.000 Euro? Clicca qui per un preventivo immediato! Prometeo ti propone prestiti personali senza attese e senza spese. Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6915&d=20070829
giuseppe.derossi@email.it writes: > Is there a more simple way to check if two poligons have a not null > intersection (they overlap)? && operator? regards, tom lane
> The results are ok, but the query is too slow. I tried to applay a GIST and > gtree index but performance did not improve. > By explaining the query I see that the where condition gets the query slow. > Is there a more simple way to check if two poligons have a not null > intersection (they overlap)? > > Is there a kind of index I can use ? If you stored the polygon geometries in the same table, you could use a functional index (give the CREATE INDEX statement a function to evaluate rather than a column). Check the CREATE INDEX page for caveats. Beware that the index would grow with the square of the number of rows if you did it like this. Peter