[postgis-users] Very slow intersection

[Ted Rosenbaum]

Hello,
I am looking to take the standard Tiger/Line Shapefile of US counties, which
includes major waterways in the border of counties (especially an issue
around the Great Lakes) and truncate the county polygons to exclude the area
beyond the coastline.  I tried creating a new table based on two tables from
two shapefiles -- one of the county polygons (from tiger/line) and one of
the US coastline-- using the following code:
create table countyShp as select cty.gid,
st_intersection(cty.the_geom,cst.the_geom) as the_geom from
countyShpWideBound as cty, uscoast as cst where
st_intersects(cty.the_geom,cst.the_geom)

I have indexes on the geometries in both tables, but this is taking hours to
run (I had a power failure after about 6 hours and it was not finished
running).

This seems like it should be a very simple and common issue, so I wanted to
see if people could let me know either what I am doing wrong in my SQL
statement or of alternative approaches to excluding areas of the coast from
US county boundaries.

Thanks.

-----------------------------------------
Ted Rosenbaum
Graduate Student
Department of Economics
Yale University
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101201/8aab2fa7/attachment.html>