Subject: request/index problem

From: Christian Millour <[email-masked]>
Date: Fri, 26 Mar 93 06:32:51 -0800

Date: Fri, 26 Mar 93 14:43:59 +0100
   From: [email-masked] (Tom Vijlbrief)

   >
   >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.
   >

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

Are the those last significantly different from the postgres built-in ?

   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)

I want to compute this for all rectangles and all points.
Actually I'd create two relations:

create rects(r=box)
create locations(p=point)

now the request 

retrieve into pairs (r=rect.r, p=location.p) 
from rect in rects and location in locations 
where location.p ---> rect.r

scares me stiff. Maybe it shouldn't, I did not try it out. Part of my
uncertainty comes from my poor knowledge of how exactly R-trees work. I
sort of thought they were at their best when dealing with a somewhat
``hierarchical'' set of rectangles, which is not the case for the problem
at hand (similar sized rectangles), and do not know at all how they
perform with point data.

   C'est tout !

   Tom

Thanks for your time.

- 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.