Hi,<br>I am not sure if this is related to the previous issue I posted or not, but when I run the query:<br>select st_union(the_geom) from stateshp<br>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.<br>
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).<br>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.<br>
I welcome any insights.<br>Thanks,<br>Ted<br><br><div class="gmail_quote">On Thu, Dec 2, 2010 at 12:53 PM, Ted Rosenbaum <span dir="ltr"><<a href="mailto:rosenbat@gmail.com">rosenbat@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im"><span style="font-family: arial,sans-serif; font-size: 13px; border-collapse: collapse;">Thanks everyone.<div>
In the end I did this in ArcMap and it took a couple of minutes. I wonder why the major difference in computational time?</div>
<div></div></span><br></div><div class="gmail_quote"><div class="im">On Thu, Dec 2, 2010 at 9:05 AM, Birgit Laggner <span dir="ltr"><<a href="mailto:birgit.laggner@vti.bund.de" target="_blank">birgit.laggner@vti.bund.de</a>></span> wrote:<br>
</div><div><div></div><div class="h5"><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div 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.<div><div></div><div><br>
<br>
<br>
On 01.12.2010 15:30, Ted Rosenbaum wrote:
</div></div><blockquote type="cite"><div><div></div><div>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>
</div></div><pre><fieldset></fieldset>
_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div></div></div><br>
</blockquote></div><br>