<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Ted,<br>
<br>
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:<br>
<br>
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);<br>
<br>
Are you sure, you have a gist-index on your geometries?<br>
<br>
But, with tables with more than 500000 rows, I get comparable run
times like you.<br>
<br>
Regards,<br>
<br>
Birgit.<br>
<br>
<br>
On 01.12.2010 15:30, Ted Rosenbaum wrote:
<blockquote
cite="mid:AANLkTim=2erm6OuxdwgGSEyTHkFteHRhAahnZFP18uum@mail.gmail.com"
type="cite">Hello,<br>
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:<br>
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)<br>
<br>
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). <br>
<br>
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.<br>
<br>
Thanks.<br>
<br>
-----------------------------------------<br>
Ted Rosenbaum<br>
Graduate Student<br>
Department of Economics<br>
Yale University<br>
<br>
<br>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>