[postgis-users] Very slow intersection

[Paul Ramsey]

Is uscoast one big polygon? (or one big polygon and a bunch of islands?)

On Thu, Dec 2, 2010 at 9:53 AM, Ted Rosenbaum <rosenbat at gmail.com> wrote:
> 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 list
>> postgis-users at postgis.refractions.net
>> http://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
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>