[postgis-users] Very slow intersection

[Birgit Laggner]

  Hi Ted,

I am not sure if this would help very much, but I always make an inner 
join on the bounding boxes of the geometries (as a sort of filter), like 
this:

create table countyShp as select cty.gid, 
st_intersection(cty.the_geom,cst.the_geom) as the_geom from 
countyShpWideBound as cty inner join uscoast as cst on cty.the_geom && 
cst.the_geom where st_intersects(cty.the_geom,cst.the_geom);

Are you sure, you have a gist-index on your geometries?

But, with tables with more than 500000 rows, I get comparable run times 
like you.

Regards,

Birgit.


On 01.12.2010 15:30, Ted Rosenbaum wrote:
> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101202/768cf633/attachment.html>