[postgis-users] Very slow intersection

[Paul Ramsey]

select st_npoints(the_geom) as npoints from uscoast order by npoints
desc limit 100;

On Thu, Dec 2, 2010 at 11:15 AM, Paul Ramsey <pramsey at opengeo.org> wrote:
> 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
>>
>>
>