Spatial Joins

[Brent Wood]

--- Eric Shuman <erics at AMERI-TITLE.COM> wrote:

> Hi list,
> 
> What options are there for spatially joining the centroid of one polygon
> layer to another polygon layer?
> 
> I have been trying to preprocess some data by running a join using postGIS,
> but after 2+ hours I give up on it.  A similar join using the same data in
> ArcView only takes 3 minutes.


The postgis list might be a better place for this, but here's my 02c :-)


If I understand your question correctly, what you want to do is along the lines
of:

select * from table1 t1, table2 t2
where t1.the_geom && t2.the_geom
  and centroid(t1.the_geom) && t2.the_geom
  and contains(centroid(t1.the_geom), t2.the_geom);

where t1.the_geom & t2.the_geom are spatially indexed polygon geometries.

So, the three where clauses filter out any polygons whose bounding boxes don't
overlap using the spatial index on both tables, then filter any centroids
whoich don't lie within the polygon bounding box (indexed on one side only, the
other fixed) then check the remainder that the centroid point lies within the
actual polygon.


One way I can see which might speed this up would be to create a column of
point geometries in t1, update that to equal the centroid of the t1 polygons &
create a spatial index on it. Then select on the two spatially indexed
attributes, instead of calculating the centroid each time.  


Cheers,
   
  Brent Wood


 
____________________________________________________________________________________
The all-new Yahoo! Mail beta
Fire up a more powerful email and get things done faster. 
http://new.mail.yahoo.com