<html>
<head>
<title></title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.18854"></meta>
<meta name="SKYPE_FRAMEID" content="UKSDOXEVME"></meta>
<meta id="skype_v3_tb_marker_id" name="SKYPE_PARSING_HAS_FINISHED" content="metacontent"></meta>
</head>
<body>Another approach could be to cut the lines up with the linestrings from st_exteriorring of the polygons after using st_dump (if there is multipolygons) and st_dumprings (if there is "holes in the poygons)
<div align="left">then use st_dump on the resulting cutted lines and after that delete all lines that is within polygons.</div>
<div align="left"> </div>
<div align="left">/Nicklas<br />
<br />
2009-12-19 Christy Nieman wrote:<br />
<br />
Kevin,<br />
><br />
>Thanks for responding. I'll give it a shot Monday morning when I'm back <br />
>at work, and will report back on how it goes.<br />
><br />
>Christy<br />
><br />
>On 12/18/2009 11:52 PM, Kevin Neufeld wrote:<br />
>> Hmm. Another use case for having topology in PostGIS ... if only :)<br />
>><br />
>> Well, I think my approach would be to iterate through your polygons <br />
>> and do as you suggest, use ST_Difference, but use a collection of all <br />
>> the lines that overlap the polygon and the polygon as parameters.<br />
>><br />
>> 1. First identity all the lines that potentially overlap a polygon<br />
>> CREATE TABLE overlapping_contours AS<br />
>> SELECT a.id AS poly_id, b.id AS cont_id, b.geom<br />
>> FROM poly a, contours b<br />
>> WHERE a.geom && b.geom;<br />
>><br />
>> 2. Remove all original contour lines so you don't get duplicates later<br />
>> DELETE FROM contours WHERE id IN (SELECT cont_id FROM <br />
>> overlapping_contours);<br />
>><br />
>> 3. Create multilinestring of the contours around every polygon (to be <br />
>> used as input into ST_Difference)<br />
>> CREATE TABLE grouped_contours AS<br />
>> SELECT poly_id, ST_Collect(geom) AS geom<br />
>> FROM overlapping_contours<br />
>> GROUP BY poly_id<br />
>><br />
>> 4. Subtract the polys from the multilinestrings<br />
>> UPDATE grouped_contours a<br />
>> SET geom = ST_Difference(a.geom, b.geom)<br />
>> FROM polys b<br />
>> WHERE a.poly_id = b.id;<br />
>><br />
>> 5. Expand and insert the mlines back into the contours table<br />
>> INSERT INTO contours (geom)<br />
>> SELECT (ST_Dump(geom)).geom<br />
>> FROM grouped_contours;<br />
>><br />
>> None of this code is tested, and obviously you'll need to add indexes <br />
>> to your intermediate tables, but it might be a start. Note that <br />
>> PostgreSQL does not use more than one CPU at a time per query. I <br />
>> could see this taking quite a long time. To speed things up, you <br />
>> might try partitioning your polygons into 4 logical groups and run <br />
>> things concurrently. IE, in step one, add the filter AND a.id < <br />
>> 50000. Then, at the same time, run another query using AND a.id <br />
>> >=50000 AND a.id < 100000. Just a thought.<br />
>><br />
>> Cheers,<br />
>> Kevin<br />
>><br />
>> Christy Nieman wrote:<br />
>>> Hello,<br />
>>><br />
>>> I have been having difficulty figuring out how I might go about doing <br />
>>> a difference of many lines and polygons. For context, I have many (> <br />
>>> 3,000,000) contour lines that I want to difference based on many (> <br />
>>> 180,000) lakes. My goal is to remove the sections of the lines that <br />
>>> are within the polygons. I know that I can use ST_Difference(aLine, <br />
>>> aPolygon) to do this for one feature, but, what would the syntax look <br />
>>> like to go though all my line and all my polygons and return just the <br />
>>> lines/parts of lines that are outside of the polygons (and do this in <br />
>>> as quickly and resource-friendly way as possible)? The machine I am <br />
>>> working on is a relatively well-powered desktop (quad core, 6GB RAM).<br />
>>><br />
>>> Thanks for any insight/suggestions,<br />
>>><br />
>>> Christy<br />
>> _______________________________________________<br />
>> postgis-users mailing list<br />
>> postgis-users@postgis.refractions.net<br />
>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
>_______________________________________________<br />
>postgis-users mailing list<br />
>postgis-users@postgis.refractions.net<br />
>http://postgis.refractions.net/mailman/listinfo/postgis-users<br />
><br />
></div>
</body>
</html>