Subject: request/index problem

From: Christian Millour <[email-masked]>
Date: Fri, 26 Mar 93 02:55:15 -0800

Hi knowledgeable netters,

I have this little problem:

given 3000+ rectangles, similar size, irregular distribution, possibly
overlapping, relatively tiny with respect to the universe (let's say the
universe edge size is about 600 times that of one rectangle), and 5000+ 2D
point locations, also irregularly distributed, I'd like to compute which
rectangle(s) (if any) each point falls in, and which and how many points
are covered by each rectangle.

1) What is the Right Thing to do ?
2) What is the Right Thing to do with Postgres ?

I figure this will boil down to an index/access method issue, however I
lack experience in this domain. Would R-Trees help ? How ? 

Thanks in advance for any advice and pointers.

- Christian Millour         ETCA/CTME/GIS/CEPIA 
  [email-masked]             16bis Ave Prieur de la Cote d'Or
  [33] (1) 42 31 96 45      94114 Arcueil Cedex -- France
All opinions expressed are my own. Standard disclaimers apply.

---

From: [email-masked] (Tom Vijlbrief)
Date: Fri, 26 Mar 93 05:44:01 -0800

>Thanks in advance for any advice and pointers.

This is relatively easy, you need geometric datatypes
(e.g. the standard Postgres point and box) or the datatypes
from the geo release.

You create a relation:

create rects(r=box)

where r contains the rectangle

The query:

retrieve (rects.all) where "(23,45.4)"::point ---> rects.r

If you define an index then this will speed thing up:

define index ri on rects using rtree (r bigbox_ops)

C'est tout !

Tom