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

[Paul Ramsey]

Tyler has it right, your first query was trying every single 
combination, and there are a *lot* of combinations. The && operator is 
absolutely required for this kind of thing, to quickly reduce the 
selection set. (Hopefully you have GiST indexes defined on the geometries.)

Note that this will *still* be slower than an Arc/INFO overlay on the 
same data, because the simple features model has more overhead for mass 
intersections (GEOS basically builds a coverage for each intersection 
pair, while Arc/INFO already has one built that it uses for the whole 
operation).

PostGIS mostly has a convenience, modularity advantage over Arc/INFO, 
for things like web mapping, large data set management, stanardized 
query language, etc. Oh, and you don't have to store everything in 
coverages to get things done. A double edged sword, as you can see.

Paul

Tyler Mitchell wrote:

> 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
> 
> _______________________________________________ postgis-users mailing
> list postgis-users at postgis.refractions.net 
> http://postgis.refractions.net/mailman/listinfo/postgis-users