[postgis-users] Help: CREATE TABLE <y> AS SELECT INTERSECTION(..., ...) ...

[Tyler Mitchell]

Hi James,
You can get a lot more mileage if you use a couple strategic WHERE clauses at the end of your SQL.  

Here is one way to think of the query you made.  "Intersect every feature in t1 with every feature in t2."  Of course not every feature will overlap every other feature, will it?  ArcInfo assumes this and must accomodate it somehow internally when doing what appears to be the same operation.

I'm pretty sure that is you used a couple more functions, like this, you'd see some improvement:

...FROM grid t1, us_states t2 WHERE Distance(t1.the_geom, t2.the_geom) = 0;

That would used the GIST indexes (someone correct me if Intersect() uses indexes at all!!!) and would pass only features that touch each other to the Intersect function.

Taking it a step further, you might try also doing a rough cut where the 'bounding box' of the features are compared first.  This is much quicker.  So you could add to the above statement:

...the_geom) = 0 AND t1.the_geom && t2.the_geom;

That will create a rectangle for every feature and compare them to see if they overlap.  Odds are, if these bounding boxes overlap, the features will too.  Of course, that's not true in all cases, but it will certainly filter some out.

So, to summarize, give this a try:
#CREATE TABLE test_int_gis AS 
SELECT INTERSECTION(t1.the_geom, t2.the_geom) 
AS the_geom 
FROM grid t1, us_states t2
WHERE t1.the_geom && t2.the_geom
AND Distance(t1.the_geom,t2.the_geom) = 0;

Hope it helps.  Let us know how it goes!

Tyler