[postgis-users] Very slow intersection

[Ted Rosenbaum]

Hi,
I am not sure if this is related to the previous issue I posted or not, but
when I run the query:
select st_union(the_geom) from stateshp
the query takes a really long time, and i eventually need to kill the
process (after about 20 minutes or so of what should be a 2 second query).
Where stateshp is a table of state boundaries, and I want the US as one
polygon.
I tried increasing the memory allocated in my datatbase -- and that did not
fix the issue.  I also checked my version of geos and I am running version
3.2.2 (which is supposed to be the fast version).
I don't believe i had this issue in postgis 1.4 on postgresql 8.4, but in
1.5 and 9.0 this is now behaving this way.
I welcome any insights.
Thanks,
Ted

On Thu, Dec 2, 2010 at 12:53 PM, 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 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/20101203/38667ecb/attachment.html>