[postgis-users] ST_Difference lots of lines with lots of polygons

[Nicklas Avén]

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)then use st_dump on the resulting cutted lines and after that delete all lines that is within polygons. /Nicklas

2009-12-19 Christy Nieman wrote:

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