[postgis-users] Very slow intersection

[Ted Rosenbaum]

Thanks everyone.
In the end I did this in ArcMap and it took a couple of minutes.  I wonder
why the major difference in computational time?

On Thu, Dec 2, 2010 at 9:05 AM, Birgit Laggner
<birgit.laggner at vti.bund.de>wrote:

>  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 listpostgis-users at postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> 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/d1c3411b/attachment.html>