pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
geometry 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
  2007-08-29 14:51 ` Re: geometry poligons performance and index Tom Lane <tgl@sss.pgh.pa.us>
  2007-09-05 02:58 ` Re: geometry poligons performance and index Peter Koczan <pjkoczan@gmail.com>
  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 06:54 geometry poligons performance and index giuseppe.derossi@email.it
@ 2007-08-29 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
  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-08-29 06:54 geometry poligons performance and index giuseppe.derossi@email.it
@ 2007-09-05 02:58 ` Peter Koczan <pjkoczan@gmail.com>
  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