pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedgeometry poligons performance and index
4+ messages / 3 participants
[nested] [flat]
* geometry poligons performance and index
@ 2007-08-27 09:29 giuseppe.derossi@email.it
0 siblings, 0 replies; 4+ messages in thread
From: giuseppe.derossi@email.it @ 2007-08-27 09:29 UTC (permalink / raw)
To: pgsql-admin
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:
Hai bisogno di contanti per realizzare i tuoi desideri? Prometeo ti propone
prestiti da 1.500 a 31.000 Euro! Clicca qui per un preventivo immediato.
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6916&d=20070827
^ permalink raw reply [nested|flat] 4+ messages in thread
* geometry poligons performance and index
@ 2007-08-29 06:54 giuseppe.derossi@email.it
0 siblings, 2 replies; 4+ messages in thread
From: giuseppe.derossi@email.it @ 2007-08-29 06:54 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: geometry poligons performance and index
@ 2007-08-29 14:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: giuseppe.derossi@email.it
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2007-08-29 14:51 UTC (permalink / raw)
To: giuseppe.derossi@email.it; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: geometry poligons performance and index
@ 2007-09-05 02:58 Peter Koczan <pjkoczan@gmail.com>
parent: giuseppe.derossi@email.it
1 sibling, 0 replies; 4+ messages in thread
From: Peter Koczan @ 2007-09-05 02:58 UTC (permalink / raw)
To: giuseppe.derossi@email.it; +Cc: pgsql-admin
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-09-05 02:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-27 09:29 geometry poligons performance and index giuseppe.derossi@email.it
2007-08-29 06:54 geometry poligons performance and index giuseppe.derossi@email.it
2007-08-29 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-05 02:58 ` Peter Koczan <pjkoczan@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox