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

[James G Wilkinson]

All,

I am new to PostGIS but not new to GIS in general having been an
ARC/Info user for sometime.  I am trying to create a new geometry from the
intersection of two other MULTIPOLYGON geometries:

Coverage #1 (us_states)  -- United States state boundaries
Coverage #2 (grid) -- a regularized grid of cells where each grid cell is
12,000 by 12,000 meters in size.  There are 168 grid cells in the east-west
direction and 177 grid cell in the north-south direction.

Each coverage has the proper (and same) PROJ4 map projection parameters
defined in the PostGIS table 'spatial_ref_sys.'  There are also appropriate
entries in the PostGIS table 'geometry_columns.'  I have GiST indexes on the
geometries in each coverage, but I do not believe that they are used for
the SQL command that I am about to present.

I am pretty sure that the coverages are stored correctly in the PostgreSQL
data base since I can export both coverages to shape files using
'psql2shp' and import/display them into ARC/Info.

I am using PostgreSQL 7.4.1, PostGIS 0.9.0, and GEOS 2.1.0.

The SQL command that I use in my attempt to create a third geometry is as
follows:

beta=# CREATE TABLE test_int_gis AS SELECT INTERSECTION(t1.the_geom,
t2.the_geom) AS the_geom FROM grid t1, us_states t2;

After 24 hours on my Sun UltraSparc 160 (yeah, I know it is slow), the 
SQL command
is still running.  I can do a similar operation in ARC/Info in about 20 
minutes.

Any assistance will be greatly appreciated.

Regards,

terrakit