'Re: [postgis-users] ST_Difference lots of lines with lots'

[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] ST_Difference lots of lines with lots
From:       "Nicklas =?iso-8859-1?Q?Av=E9n?=" <nicklas.aven () jordogskog ! no>
Date:       2009-12-20 10:14:20
Message-ID: 200912201014.nBKAEKxD004600 () mail-core ! space2u ! com
[Download RAW message or body]

[Attachment #2 (multipart/alternative)]

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 &amp;&amp; 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 &lt; 
> > 50000. Then, at the same time, run another query using AND a.id 
> > > =50000 AND a.id &lt; 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@postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

[Attachment #5 (text/html)]

<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&nbsp;and after that \
delete all lines that is within polygons.</div>  
<div align="left">&nbsp;</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 &amp;&amp; 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 &lt; <br />
		>> 50000. Then, at the same time, run another query using AND a.id <br />
		>> >=50000 AND a.id &lt; 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>

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread] 