<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class='hmmessage'>Hello,<br><br><font style="font-family: Geneva,Arial,Sans-serif;" size="3">Is POSTGIS, ASKML command support extrude, tesselate, altitudemode now.<br><br>thank you <br><br>ihab hijazi<span style="font-size: 120%; font-family: Tahoma,Helvetica,Sans-Serif;"></span><span style="font-family: Tahoma,Helvetica,Sans-Serif;"></span></font><br><br><br><hr id="stopSpelling">> From: postgis-users-request@postgis.refractions.net<br>> Subject: postgis-users Digest, Vol 69, Issue 16<br>> To: postgis-users@postgis.refractions.net<br>> Date: Wed, 16 Jul 2008 12:01:04 -0700<br>> <br>> Send postgis-users mailing list submissions to<br>> postgis-users@postgis.refractions.net<br>> <br>> To subscribe or unsubscribe via the World Wide Web, visit<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> or, via email, send a message with subject or body 'help' to<br>> postgis-users-request@postgis.refractions.net<br>> <br>> You can reach the person managing the list at<br>> postgis-users-owner@postgis.refractions.net<br>> <br>> When replying, please edit your Subject line so it is more specific<br>> than "Re: Contents of postgis-users digest..."<br>> <br>> <br>> Today's Topics:<br>> <br>> 1. RE: newbie question: PostGIS and .NET? (Rob Tester)<br>> 2. Re: Query that locks up system memory/disk usage (Kevin Neufeld)<br>> 3. Re: Raster elevation x/y lookup (was: Splitting/merging<br>> linework into equal interval linestrings) (Dane Springmeyer)<br>> 4. match line with road segment (searchelite)<br>> 5. Need help with a query (Pedro Doria Meunier)<br>> 6. Re: newbie question: PostGIS and .NET? (Saka Royban)<br>> 7. contains syntax (jj.wag@gmx.de)<br>> 8. RE: newbie question: PostGIS and .NET? (Obe, Regina)<br>> 9. RE: contains syntax (Obe, Regina)<br>> 10. Re: newbie question: PostGIS and .NET? (Saka Royban)<br>> 11. Re: Windows Crash (Chris Hermansen)<br>> 12. Re: match line with road segment (Stephen Woodbridge)<br>> 13. Re: Transform overlapping polygons to non-overlapping?<br>> (Brent Fraser)<br>> 14. RE: Transform overlapping polygons to non-overlapping?<br>> (Paragon Corporation)<br>> 15. RE: Transform overlapping polygons to non-overlapping?<br>> (Paragon Corporation)<br>> 16. Re: Transform overlapping polygons to non-overlapping?<br>> (Brent Fraser)<br>> 17. Re: Transform overlapping polygons to non-overlapping?<br>> (Dylan Beaudette)<br>> 18. Re: Transform overlapping polygons to non-overlapping?<br>> (Brent Fraser)<br>> 19. Re: Transform overlapping polygons to non-overlapping?<br>> (Kevin Neufeld)<br>> 20. RE: Transform overlapping polygons to non-overlapping?<br>> (Obe, Regina)<br>> <br>> <br>> ----------------------------------------------------------------------<br>> <br>> Message: 1<br>> Date: Tue, 15 Jul 2008 12:33:44 -0700<br>> From: "Rob Tester" <robtester@gmail.com><br>> Subject: RE: [postgis-users] newbie question: PostGIS and .NET?<br>> To: "'PostGIS Users Discussion'"<br>> <postgis-users@postgis.refractions.net><br>> Message-ID: <018501c8e6b1$b2f13050$18d390f0$@com><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> Not 100% sure what you are looking for, but if you want access to<br>> PostgreSQL with the PostGIS extensions from .NET use NPGSQL:<br>> <br>> <br>> <br>> <br>> <br>> http://npgsql.projects.postgresql.org<br>> <br>> <br>> <br>> <br>> <br>> If you want more than that, we commonly use GDAL tools from here: <br>> <br>> <br>> <br>> http://www.gdal.org/<br>> <br>> <br>> <br>> Rob<br>> <br>> <br>> <br>> From: postgis-users-bounces@postgis.refractions.net<br>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Saka<br>> Royban<br>> Sent: Tuesday, July 15, 2008 11:03 AM<br>> To: PostGIS<br>> Subject: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> <br>> Hi all.<br>> Sorry for elementary question. but i didn't find anything to support PostGIS<br>> in .NET environment. <br>> just some driver for Java. <br>> I mainly wanna make a desktop GIS applications and it sounds PostGIS helpful<br>> to be used as a back-end to manage large number of shapefiles. So, is there<br>> a way to use this spatial database in .NET? <br>> Queries support by PostGIS are excellent but i need to have export of SQL<br>> commands in shapefile.<br>> Thanks<br>> <br>> saka<br>> <br>> <br>> <br>> -------------- next part --------------<br>> An HTML attachment was scrubbed...<br>> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20080715/06d14228/attachment-0001.html<br>> <br>> ------------------------------<br>> <br>> Message: 2<br>> Date: Mon, 14 Jul 2008 17:18:50 -0700<br>> From: Kevin Neufeld <kneufeld@refractions.net><br>> Subject: Re: [postgis-users] Query that locks up system memory/disk<br>> usage<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <487BECEA.3000402@refractions.net><br>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>> <br>> Paul, while "kill -9" will undoubtedly stop the current running query, <br>> it will also crash the entire database cluster since the shared memory <br>> will become corrupt. <br>> <br>> I recommend using a "kill -2" instead which is the same thing as issuing <br>> a ctrl^c while in the terminal program. It may take longer since it has <br>> to rollback the transaction, but it will do so gracefully.<br>> <br>> -- Kevin<br>> <br>> Paul Ramsey wrote:<br>> > Break yourself of the subquery habit:<br>> ><br>> > select a.* from a join b on (st_dwithin(a.the_geom,b.the_geom,50000))<br>> > where b.gid = 10;<br>> ><br>> ><br>> > On your process:<br>> ><br>> > ps ax | grep postgres<br>> ><br>> > Find the process id that is using all the CPU and just kill -9 it. The<br>> > glory of running a proper ACID database like PgSQL is that if you<br>> > don't like what it's doing, you can rip the power cord out of the<br>> > wall, and it'll still start up clean. (Do not try this with MySQL.)<br>> ><br>> > P.<br>> ><br>> > buffer((select b.the_geom<br>> > <br>> >> where gid = 10),50000));<br>> >> <br>> ><br>> > On Mon, Jul 14, 2008 at 4:33 PM, Mike Leahy <mgleahy@alumni.uwaterloo.ca> wrote:<br>> > <br>> >> Hello list,<br>> >><br>> >> I've run into some situations where running certain queries end up locking<br>> >> up all of my system's ram memory, with constant disk access. I can't cancel<br>> >> the query by hitting ctrl+c in the psql terminal, by restarting the service,<br>> >> or even killing the postmaster. I'm running on a fairly high end system, so<br>> >> it's not an issue with CPU power or available ram. Here's an example of<br>> >> what I did today that caused this:<br>> >><br>> >> Table A is a table I imported from a tile index shapefile.<br>> >><br>> >> Table B has several fairly large irregular polygons of different study<br>> >> areas.<br>> >><br>> >> To get all of the polygons in Table A within a certain distance (50km) of<br>> >> one of the polygons in Table B, without giving it much thought I did the<br>> >> following:<br>> >><br>> >> select * from a where st_intersects(a.the_geom,buffer((select b.the_geom<br>> >> where gid = 10),50000));<br>> >><br>> >> I realize how wrong that is, as calculates the buffer for every tile it<br>> >> compares to...I should have done something like:<br>> >><br>> >> select * from a where st_intersects(a.the_geom,(select b.the_geom where gid<br>> >> = 10)) or st_distance(a.the_geom,(select b.the_geom where gid = 10))<=50000;<br>> >><br>> >> The problem is...I'm still waiting for the first query to either finish, or<br>> >> cancel, or something. In the meantime, postmaster is still using 99% of my<br>> >> memory, and the disk is still thrashing away (though CPU usage pretty much<br>> >> at 0). What's the best strategy to kill the previous query without having<br>> >> to shut down the entire server?<br>> >><br>> >> Keep in mind that is just an example of how this can happen for me - I've<br>> >> had it happen in other more complex situations where it was less obvious<br>> >> what I was doing wrong in the logic of the query. I'm just wondering how I<br>> >> can recover from these sorts of mistakes without potentially damaging the<br>> >> database.<br>> >><br>> >> Regards,<br>> >> Mike<br>> >><br>> >><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >> <br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > postgis-users@postgis.refractions.net<br>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 3<br>> Date: Tue, 15 Jul 2008 18:20:19 -0700<br>> From: Dane Springmeyer <blake@hailmail.net><br>> Subject: Re: [postgis-users] Raster elevation x/y lookup (was:<br>> Splitting/merging linework into equal interval linestrings)<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <59CE83AC-6EB1-461D-8F49-A3DD0B6717AE@hailmail.net><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> For anyone interested I've worked up Regina's great python snippet <br>> into a full script.<br>> <br>> The script pulls records of x/y values from a river hydrography <br>> network from both upstream and downstream nodes of each equi-length <br>> linestring. It then iterates through each record and updates the table <br>> with the elevation values for the low and highpoint of each river <br>> segment. Stream gradient is then easy to calculate back inside the <br>> database.<br>> <br>> I'm sure there are ways to improve or optimize this script, so if <br>> anyone has any pointers don't hesitate to send them along.<br>> <br>> Thanks again to Regina for the great push forward.<br>> <br>> Cheers,<br>> Dane<br>> <br>> <br>> ###########<br>> <br>> from osgeo import gdal<br>> import math<br>> import struct<br>> <br>> import psycopg2<br>> import psycopg2.extras<br>> <br>> gdal.SetCacheMax(15)<br>> <br>> connection = psycopg2.connect("dbname='beaver' user='postgres' <br>> host='localhost'");<br>> sql = connection.cursor(cursor_factory=psycopg2.extras.DictCursor)<br>> <br>> dataset = gdal.Open('/Volumes/Wren/Users/spring/projects/beaver/data/ <br>> elevation/uw/tiff/methow-tiled.tif', gdal.GA_ReadOnly)<br>> geotransform = dataset.GetGeoTransform()<br>> min_x = geotransform[0]<br>> max_y = geotransform[3]<br>> res_x = abs(geotransform[1])<br>> res_y = abs(geotransform[5])<br>> <br>> sql.execute("""SELECT oid, ST_X(ST_StartPoint(geometry)) as down_x, <br>> ST_Y(ST_StartPoint(geometry)) as down_y,ST_X(ST_EndPoint(geometry)) as <br>> up_x, ST_Y(ST_EndPoint(geometry)) as up_y FROM sshiap_splits ORDER by <br>> OID;""")<br>> <br>> query_records = float(sql.rowcount) - 1.0<br>> <br>> print 'Beginning lookup of %s features...' % query_records<br>> <br>> for row in sql.fetchall():<br>> oid = row['oid']<br>> down_x = row['down_x']<br>> down_y = row['down_y']<br>> up_x = row['up_x']<br>> up_y = row['up_y']<br>> down_row = int(math.ceil((max_y - down_y)/res_y))<br>> down_col = int(math.ceil((down_x - min_x)/res_x))<br>> up_row = int(math.ceil((max_y - up_y)/res_y))<br>> up_col = int(math.ceil((up_x - min_x)/res_x))<br>> if down_row > 0 and down_col > 0 and up_row > 0 and up_col > 0 <br>> and dataset.RasterYSize > down_row and dataset.RasterXSize > down_col <br>> and dataset.RasterYSize > up_row and dataset.RasterXSize > up_col:<br>> down_raster_scan = dataset.ReadRaster(down_col - 1, down_row <br>> - 1, 1, 1)<br>> down_raster_area = struct.unpack('f' * 1, down_raster_scan)<br>> lowpoint = int(down_raster_area[0])<br>> up_raster_scan = dataset.ReadRaster(up_col - 1, up_row - 1, <br>> 1, 1)<br>> up_raster_area = struct.unpack('f' * 1, up_raster_scan)<br>> highpoint = int(up_raster_area[0])<br>> sql.execute("""UPDATE sshiap_splits SET lowpoint = %s, <br>> highpoint = %s WHERE oid = %s""" % (lowpoint, highpoint, oid))<br>> if oid/query_records in [.1,.2,.5,.10,.15,.20, . <br>> 25,.30,.35,.40,.45,.50,.55,.60,.65,.70,.75,.80,.85,.90,.95,1.0]:<br>> print '%s%s completed' % (oid/query_records*100, '%')<br>> else:<br>> print 'error <br>> ---------------------------------------------------------------------------------'<br>> <br>> print 'Complete'<br>> connection.commit()<br>> <br>> <br>> <br>> <br>> On Jul 10, 2008, at 11:38 AM, Dane Springmeyer wrote:<br>> <br>> > Regina and Brent:<br>> ><br>> > Wow. Thanks so much for the pointers.<br>> ><br>> > I've now got a single query that is working excellently so far. <br>> > Without indexes it runs on the entire dataset in about 5 minutes <br>> > when splitting lines to ~ 150 m, which is just fine.<br>> ><br>> > I was able to use the ST_Reverse function to make sure that any <br>> > fragments at the end of the linestrings would occur at the upstream <br>> > end, which I think handles my minimum requirement (for now).<br>> ><br>> > Below is what just a slight reworking of Regina's second email <br>> > example looks like.<br>> ><br>> > Thanks!<br>> ><br>> > Dane<br>> ><br>> ><br>> > ------<br>> ><br>> > drop table if exists test2;<br>> > create table test2 (oid serial,strahler varchar, geometry geometry);<br>> > INSERT<br>> > INTO test2 (strahler, geometry)<br>> > SELECT strahler,<br>> > -- take a substring if the length is greater than 152.4 meters <br>> > otherwise take the remainder<br>> > ST_Line_Substring(geometry, 152.4*n/length,<br>> > CASE<br>> > WHEN 152.4*(n+1) < length THEN 152.4*(n+1)/length<br>> > ELSE 1<br>> > END) As geometry<br>> > FROM<br>> > (SELECT strahler,<br>> > -- reverse the vertex order so that the upstream end of each <br>> > linestring will be the remainder<br>> > ST_LineMerge(ST_Reverse(ts.geometry)) AS geometry,<br>> > ST_Length(ts.geometry) As length<br>> > FROM sshiap_lines ts<br>> > ) t<br>> > CROSS JOIN generate_series(0,10000) n<br>> > WHERE n*152.4/length < 1;<br>> ><br>> ><br>> ><br>> > On Jul 9, 2008, at 10:21 PM, Paragon Corporation wrote:<br>> ><br>> >> Dane,<br>> >><br>> >> I recall doing something along 2 and 3 before and I did it in python<br>> >> (although I was interested in getting each measure at x distance <br>> >> along the<br>> >> line so I was collecting<br>> >> Points every x distance meters along the way rather than breaking <br>> >> up the<br>> >> lines. So my translation of what I did to what you want may have a <br>> >> lot of<br>> >> logical errors)<br>> >><br>> >> For 2 - I think your logic would look something like<br>> >><br>> >> 2) SELECT gid, length*frac As dist, <br>> >> ST_Line_Substring(the_geom,startfrac,<br>> >> endfrac) As the_geom<br>> >> FROM (SELECT gid, 500*n/length As startfrac, CASE WHEN 500*(n+1) < <br>> >> length<br>> >> THEN 500*(n+1)/length ELSE 1 END As endfrac, length, the_geom<br>> >> FROM (SELECT ts.gid, ST_LineMerge(ts.the_geom) As the_geom,<br>> >> ST_Length(ts.the_geom) As length FROM line_segments ts<br>> >> ) t CROSS JOIN generate_series(0,10000) n<br>> >> WHERE n*500/length < 1) As segments_500<br>> >><br>> >><br>> >><br>> >> The above basically assumes your data is in some meter projection <br>> >> and you<br>> >> have no line segment that has got more than 10000 (500 ft segments)<br>> >> If you know for sure there is no way you have a line segment <br>> >> greater than<br>> >> 500*10000 then you can safely reduce that generate_series number <br>> >> and get<br>> >> better performance.<br>> >><br>> >> This I did do in python by the way - and my logic is much more <br>> >> complicated<br>> >> than above but hopefully I cut out enough for you to get the basic <br>> >> idea<br>> >><br>> >> 3) For this I used python and the gdal and psycopg libraries. <br>> >> Basic logic<br>> >> looks something like Below<br>> >> open up a tile (my above query (maybe taking the centroid of each <br>> >> point I<br>> >> actually limited to only those linesegments that intersected the <br>> >> tile I was<br>> >> loading so I processed one tile at a time) - so my above query had <br>> >> an extra<br>> >> join with the tile extent<br>> >><br>> >> Looks something like below where pt is an array of the centroid <br>> >> points of<br>> >> the above query<br>> >><br>> >> dataset =<br>> >> osgeo.gdal.Open('%(atilefolder)s/%(atilefile)s'% <br>> >> {'atilefolder':atilefolder,'<br>> >> atilefile':atilefile}, GA_ReadOnly)<br>> >> geot = dataset.GetGeoTransform()<br>> >> #0 0 row col is at top left corner of the tif so we<br>> >> need min_x, max_y that corresponds to pos (0,0)<br>> >> min_x = geot[0]<br>> >> max_y = geot[3]<br>> >> res_x = abs(geot[1])<br>> >> res_y = abs(geot[5])<br>> >> print 'Origin of tile ', atilefile , '= (',min_x,<br>> >> ',',max_y,')'<br>> >> print 'Pixel Size = (',res_x, ',',res_y,')'<br>> >> print 'Size is<br>> >> ',dataset.RasterXSize,'x',dataset.RasterYSize, <br>> >> 'x',dataset.RasterCount<br>> >> for pt in r:<br>> >> #for each point figure out the row col<br>> >> position in the tiff<br>> >> gid = pt[0]<br>> >> x = pt[2]<br>> >> y = pt[3]<br>> >> #print max_y - y<br>> >> row = int(math.ceil((max_y - y)/res_y))<br>> >> col = int(math.ceil((x - min_x)/res_x))<br>> >> <br>> >> #If point falls in grid proceed to determine<br>> >> elevation from tiff<br>> >> #Some line segments may have measures that<br>> >> span multiple tiles so we need to skip the measurement points that <br>> >> don't<br>> >> fall in the tile<br>> >> if row > 0 and col > 0 and<br>> >> dataset.RasterYSize > row and dataset.RasterXSize > col:<br>> >> numpoints = numpoints + 1<br>> >> pt_dist = pt[1]<br>> >> <br>> >> #grab 1 pixel at row col position<br>> >> in tif and return the band1 - which is the elevation<br>> >> rd_scan = dataset.ReadRaster(col -<br>> >> 1, row - 1, 1, 1)<br>> >> rd_area = struct.unpack('f' * 1,<br>> >> rd_scan)<br>> >> elev = int(rd_area[0])<br>> >> #your update statement goes here<br>> >> <br>> >><br>> >> Hope that helps,<br>> >> Regina<br>> >><br>> >><br>> >><br>> >> -----Original Message-----<br>> >> From: postgis-users-bounces@postgis.refractions.net<br>> >> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of <br>> >> Dane<br>> >> Blakely Springmeyer<br>> >> Sent: Wednesday, July 09, 2008 11:25 PM<br>> >> To: postgis-users@postgis.refractions.net<br>> >> Subject: [postgis-users] Splitting/merging linework into equal<br>> >> intervallinestrings<br>> >><br>> >> PostGIS users,<br>> >><br>> >> I have 1:24k hydrographic linework that I need process in several <br>> >> successive<br>> >> steps using a postgis workflow.<br>> >><br>> >> I'm stuck at step 2 of this overall workflow:<br>> >><br>> >> 1) separate all linestrings into distinct Strahler Order groups <br>> >> (done)<br>> >> 2) split all linework into linestrings of 500 ft (while avoiding any<br>> >> linestring fragments smaller than 500 ft)<br>> >> 3) perform an elevation lookup to raster data to calculate the <br>> >> gradient of<br>> >> each 500 ft segment and..<br>> >> 4) combine all adjacent linestrings which fall into similar gradient<br>> >> classes.<br>> >><br>> >> For step 2 I'm investigating using ST_Segmentize() and/or<br>> >> ST_Line_substring(), but I'd really appreciate some help with how <br>> >> to best<br>> >> approach the problem.<br>> >><br>> >> ST_Segmentize seems to only insert more nodes/points into already <br>> >> very high<br>> >> resolution linework (ie nodes already exist at intervals much more <br>> >> frequent<br>> >> than 500ft), thus extracting linestrings from the result of <br>> >> ST_Segmentize<br>> >> clearly isn't as simple as using MakeLine() between each segment.<br>> >><br>> >> ST_Line_Substring works on percentages which is smart, but I have <br>> >> yet to<br>> >> wrap my mind around how to measure each individual line such that I <br>> >> can<br>> >> translate percentage distance along a linestring into equal distance<br>> >> intervals.<br>> >><br>> >> Anyone have examples or guidance?<br>> >><br>> >> Thanks,<br>> >><br>> >> Dane<br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >><br>> >><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> ><br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > postgis-users@postgis.refractions.net<br>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> -------------- next part --------------<br>> An HTML attachment was scrubbed...<br>> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20080715/65087217/attachment-0001.html<br>> <br>> ------------------------------<br>> <br>> Message: 4<br>> Date: Tue, 15 Jul 2008 21:27:26 -0700 (PDT)<br>> From: searchelite <searchelite@gmail.com><br>> Subject: [postgis-users] match line with road segment<br>> To: postgis-users@postgis.refractions.net<br>> Message-ID: <18479996.post@talk.nabble.com><br>> Content-Type: text/plain; charset=us-ascii<br>> <br>> <br>> hi all..<br>> i have a line table consists of gps points, i'm using<br>> ST_makeline(gps_points) to create the line..my question is, how to match the<br>> gps line into road segment in postgis? <br>> <br>> thank you<br>> -- <br>> View this message in context: http://www.nabble.com/match-line-with-road-segment-tp18479996p18479996.html<br>> Sent from the PostGIS - User mailing list archive at Nabble.com.<br>> <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 5<br>> Date: Wed, 16 Jul 2008 09:54:08 +0100<br>> From: Pedro Doria Meunier <pdoria@netmadeira.com><br>> Subject: [postgis-users] Need help with a query<br>> To: postgis-users@postgis.refractions.net<br>> Message-ID:<br>> <1216198449.3915.20.camel@213-190-214-90-cmts01.netmadeira.com><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> Hi all,<br>> <br>> I need help with an unusual query (at least for me :] )<br>> <br>> The objective is to find road segments with a type defined to tunnel<br>> within a given distance.<br>> This is needed because of gps signal spikes (speed-wise) at tunnel<br>> exits...<br>> <br>> Now given the following scenario of road segments:<br>> <br>> <br>> =====*-----------------*==========<br>> ====V===*========================*<br>> *======<br>> <br>> =====*-----------------*==========<br>> |<br>> |<br>> |<br>> |<br>> |<br>> |<br>> |<br>> |<br>> |<br>> |<br>> ==========-----------------------=======================<br>> <br>> = | road<br>> - tunnel<br>> * nodes<br>> V vehicle<br>> <br>> Now when I execute a distance-based query (for road segments of tunnel<br>> type) I get the lower tunnel, which is closer.<br>> But I want the upper one which is actually the one on the top.<br>> <br>> the actual query is as follows (tunnels (road type=67) within a 150<br>> metres radius) -- the road map is in WGS84:<br>> SELECT name, road_type FROM $road_net WHERE <br>> transform(geometry,$srid) &&<br>> setsrid(expand(transform(geomfromtext('POINT($lon $lat)',4326),<br>> $srid),150),$srid) <br>> AND distance(transform(geomfromtext('POINT($lon $lat)',4326),$srid),<br>> transform(geometry,$srid)) <=150 AND road_type=67;<br>> <br>> Already thankful for any tips,<br>> <br>> -- <br>> Pedro Doria Meunier <pdoria@netmadeira.com><br>> -------------- next part --------------<br>> An HTML attachment was scrubbed...<br>> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20080716/79ab6bec/attachment-0001.html<br>> <br>> ------------------------------<br>> <br>> Message: 6<br>> Date: Wed, 16 Jul 2008 03:16:24 -0700 (PDT)<br>> From: Saka Royban <srph124@yahoo.com><br>> Subject: Re: [postgis-users] newbie question: PostGIS and .NET?<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <875184.66925.qm@web58203.mail.re3.yahoo.com><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> It means using SharpMap i can load shapfiles to PostGIS, perform the query and finally have the result in shapefile format? Am i right?<br>> Thanks in advance<br>> <br>> <br>> <br>> ----- Original Message ----<br>> From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>; postgis-users@postgis.refractions.net<br>> Sent: Tuesday, July 15, 2008 10:10:39 PM<br>> Subject: RE: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> Re: [postgis-users] newbie question: PostGIS and .NET? <br>> David,<br>> <br>> Have him take a look at SharpMap. Its got .NET drivers for PostGIS and other layer types.<br>> <br>> http://www.codeplex.com/SharpMap<br>> <br>> It works for both ASP.NET and windows .net apps.<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> <br>> <br>> <br>> ________________________________<br>> From: postgis-users-bounces@postgis.refractions.net on behalf of Jean David TECHER<br>> Sent: Tue 7/15/2008 2:32 PM<br>> To: postgis-users@postgis.refractions.net<br>> Subject: Re: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> <br>> <br>> Quoting Saka Royban <srph124@yahoo.com>:<br>> <br>> > Hi all.<br>> > Sorry for elementary question. but i didn't find anything to support <br>> > PostGIS in .NET environment.<br>> > just some driver for Java.<br>> > I mainly wanna make a desktop GIS applications and it sounds PostGIS <br>> > helpful to be used as a back-end to manage large number of <br>> > shapefiles. So, is there a way to use this spatial database in .NET?<br>> For PostgreSQL you have Npgsql that support PostgreSQL queries<br>> <br>> <br>> > Queries support by PostGIS are excellent but i need to have export <br>> > of SQL commands in shapefile.<br>> <br>> You have pgsql2shp that could do it! Perhaps need to use API to .net?<br>> <br>> > Thanks<br>> ><br>> > saka<br>> ><br>> ><br>> ><br>> ><br>> <br>> <br>> <br>> ===================<br>> Jean David TECHER<br>> 06 60 46 85 05<br>> 04 99 77 17 87<br>> ===================<br>> <br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> ________________________________<br>> <br>> The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. <br>> ________________________________<br>> <br>> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. <br>> <br>> <br>> <br>> -------------- next part --------------<br>> An HTML attachment was scrubbed...<br>> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20080716/e36ff65f/attachment-0001.html<br>> <br>> ------------------------------<br>> <br>> Message: 7<br>> Date: Wed, 16 Jul 2008 12:18:09 +0200<br>> From: <jj.wag@gmx.de><br>> Subject: [postgis-users] contains syntax<br>> To: <postgis-users@postgis.refractions.net><br>> Message-ID: <004101c8e72d$3fb35100$08b2a8c0@SVEN><br>> Content-Type: text/plain; format=flowed; charset="iso-8859-1";<br>> reply-type=original<br>> <br>> Hi all,<br>> <br>> I have 2 tables:<br>> <br>> 1. table "landkreise" (polygon) with column "lk" (names of administrative <br>> borders (polygons))<br>> 2. standorte (point) with column "lk" (empty, I want to write the name of <br>> the administrative borders from the polygon-feature that contains the point)<br>> <br>> When I use following select-statement it seems to work:<br>> <br>> SELECT l.lk FROM landkreise as l, standorte as s WHERE <br>> contains(l.the_geom,s.the_geom) = TRUE;<br>> <br>> When I want to update the column "lk" in table standorte allways the same <br>> name is written:<br>> <br>> UPDATE standorte SET lk = l.lk FROM landkreise as l, standorte AS s WHERE <br>> contains(l.the_geom,s.the_geom) = TRUE;<br>> <br>> What is wrong with my syntax?<br>> <br>> Thanks<br>> Jo<br>> <br>> <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 8<br>> Date: Wed, 16 Jul 2008 06:38:46 -0400<br>> From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>> Subject: RE: [postgis-users] newbie question: PostGIS and .NET?<br>> To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>> Message-ID:<br>> <53F9CF533E1AA14EA1F8C5C08ABC08D204623CF6@ZDND.DND.boston.cob><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> Well you could load shape files with SharpMap directly into PostGIS<br>> since it supports both types but you would have to roll your own a bit,<br>> although you can encode the password info etc which you can't with<br>> shp2pgsql. <br>> <br>> SharpMap is more designed for if you already have data in PostGIS and<br>> you want to query it from PostGIS and display it without having to dump<br>> to shape file format first. <br>> <br>> If you just want a shape file to PostGIS loader - may be easier to just<br>> call shp2pgsql or ogr2ogr using the .NET Process class, which is<br>> described here<br>> http://msdn.microsoft.com/en-us/library/system.diagnostics.process.aspx<br>> . <br>> <br>> I haven't had a need to do that for loading shape files, but I use the<br>> process class for doing xcopy all the time (from one server to another)<br>> <br>> .NET Process class would look something like this I imagine -- you may<br>> need to fiddle with security, pemissions etc. I have never tested this<br>> so not sure what issues would arise or if you can even do a | using<br>> process. You may need to dump to sql and then load sql file first.<br>> <br>> Process.Start("path/to/shp2pgsql", " -s 4269 -I someshapefile<br>> somepgtable | psql -h someserver -d gisdb -U somepguser")<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> <br>> <br>> <br>> <br>> <br>> ________________________________<br>> <br>> From: postgis-users-bounces@postgis.refractions.net<br>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Saka<br>> Royban<br>> Sent: Wednesday, July 16, 2008 6:16 AM<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> It means using SharpMap i can load shapfiles to PostGIS, perform the<br>> query and finally have the result in shapefile format? Am i right?<br>> Thanks in advance<br>> <br>> <br>> ----- Original Message ----<br>> From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>;<br>> postgis-users@postgis.refractions.net<br>> Sent: Tuesday, July 15, 2008 10:10:39 PM<br>> Subject: RE: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> David,<br>> <br>> Have him take a look at SharpMap. Its got .NET drivers for PostGIS and<br>> other layer types.<br>> <br>> http://www.codeplex.com/SharpMap<br>> <br>> It works for both ASP.NET and windows .net apps.<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> <br>> <br>> <br>> ________________________________<br>> <br>> From: postgis-users-bounces@postgis.refractions.net on behalf of Jean<br>> David TECHER<br>> Sent: Tue 7/15/2008 2:32 PM<br>> To: postgis-users@postgis.refractions.net<br>> Subject: Re: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> <br>> <br>> Quoting Saka Royban <srph124@yahoo.com>:<br>> <br>> > Hi all.<br>> > Sorry for elementary question. but i didn't find anything to support <br>> > PostGIS in .NET environment.<br>> > just some driver for Java.<br>> > I mainly wanna make a desktop GIS applications and it sounds PostGIS <br>> > helpful to be used as a back-end to manage large number of <br>> > shapefiles. So, is there a way to use this spatial database in .NET?<br>> For PostgreSQL you have Npgsql that support PostgreSQL queries<br>> <br>> <br>> > Queries support by PostGIS are excellent but i need to have export <br>> > of SQL commands in shapefile.<br>> <br>> You have pgsql2shp that could do it! Perhaps need to use API to .net?<br>> <br>> > Thanks<br>> ><br>> > saka<br>> ><br>> ><br>> ><br>> ><br>> <br>> <br>> <br>> ===================<br>> Jean David TECHER<br>> 06 60 46 85 05<br>> 04 99 77 17 87<br>> ===================<br>> <br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> ________________________________<br>> <br>> The substance of this message, including any attachments, may be<br>> confidential, legally privileged and/or exempt from disclosure pursuant<br>> to Massachusetts law. It is intended solely for the addressee. If you<br>> received this in error, please contact the sender and delete the<br>> material from any computer. <br>> <br>> ________________________________<br>> <br>> Help make the earth a greener place. If at all possible resist printing<br>> this email and join us in saving paper. <br>> <br>> <br>> -----------------------------------------<br>> The substance of this message, including any attachments, may be<br>> confidential, legally privileged and/or exempt from disclosure<br>> pursuant to Massachusetts law. It is intended<br>> solely for the addressee. If you received this in error, please<br>> contact the sender and delete the material from any computer.<br>> <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 9<br>> Date: Wed, 16 Jul 2008 07:07:09 -0400<br>> From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>> Subject: RE: [postgis-users] contains syntax<br>> To: "PostGIS Users Discussion" <postgis-users@postgis.refractions.net><br>> Message-ID:<br>> <53F9CF533E1AA14EA1F8C5C08ABC08D204623CFC@ZDND.DND.boston.cob><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> Jo,<br>> Your update syntax should be<br>> <br>> UPDATE standorte <br>> SET lk = l.lk <br>> FROM landkreise as l<br>> WHERE <br>> ST_Contains(l.the_geom,standorte.the_geom) = TRUE;<br>> <br>> Note - your below query is not using indexes. If you are using Postgis<br>> 1.2.1 or above, use the new<br>> <br>> ST_Contains instead (that will use indexes since it automagically adds<br>> the && operator)<br>> <br>> If you are using older PostGIS, then use<br>> <br>> l.the_geom && s.the_geom AND Contains(l.the_geom, s.the_geom)<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> -----Original Message-----<br>> From: postgis-users-bounces@postgis.refractions.net<br>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of<br>> jj.wag@gmx.de<br>> Sent: Wednesday, July 16, 2008 6:18 AM<br>> To: postgis-users@postgis.refractions.net<br>> Subject: [postgis-users] contains syntax<br>> <br>> Hi all,<br>> <br>> I have 2 tables:<br>> <br>> 1. table "landkreise" (polygon) with column "lk" (names of<br>> administrative <br>> borders (polygons))<br>> 2. standorte (point) with column "lk" (empty, I want to write the name<br>> of <br>> the administrative borders from the polygon-feature that contains the<br>> point)<br>> <br>> When I use following select-statement it seems to work:<br>> <br>> SELECT l.lk FROM landkreise as l, standorte as s WHERE <br>> contains(l.the_geom,s.the_geom) = TRUE;<br>> <br>> When I want to update the column "lk" in table standorte allways the<br>> same <br>> name is written:<br>> <br>> UPDATE standorte SET lk = l.lk FROM landkreise as l, standorte AS s<br>> WHERE <br>> contains(l.the_geom,s.the_geom) = TRUE;<br>> <br>> What is wrong with my syntax?<br>> <br>> Thanks<br>> Jo<br>> <br>> <br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> -----------------------------------------<br>> The substance of this message, including any attachments, may be<br>> confidential, legally privileged and/or exempt from disclosure<br>> pursuant to Massachusetts law. It is intended<br>> solely for the addressee. If you received this in error, please<br>> contact the sender and delete the material from any computer.<br>> <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 10<br>> Date: Wed, 16 Jul 2008 06:00:38 -0700 (PDT)<br>> From: Saka Royban <srph124@yahoo.com><br>> Subject: Re: [postgis-users] newbie question: PostGIS and .NET?<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <285528.51138.qm@web58214.mail.re3.yahoo.com><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> Very helpful<br>> Thanks<br>> <br>> <br>> <br>> ----- Original Message ----<br>> From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Sent: Wednesday, July 16, 2008 2:08:46 PM<br>> Subject: RE: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> Well you could load shape files with SharpMap directly into PostGIS<br>> since it supports both types but you would have to roll your own a bit,<br>> although you can encode the password info etc which you can't with<br>> shp2pgsql. <br>> <br>> SharpMap is more designed for if you already have data in PostGIS and<br>> you want to query it from PostGIS and display it without having to dump<br>> to shape file format first. <br>> <br>> If you just want a shape file to PostGIS loader - may be easier to just<br>> call shp2pgsql or ogr2ogr using the .NET Process class, which is<br>> described here<br>> http://msdn.microsoft.com/en-us/library/system.diagnostics.process.aspx <br>> . <br>> <br>> I haven't had a need to do that for loading shape files, but I use the<br>> process class for doing xcopy all the time (from one server to another)<br>> <br>> .NET Process class would look something like this I imagine -- you may<br>> need to fiddle with security, pemissions etc. I have never tested this<br>> so not sure what issues would arise or if you can even do a | using<br>> process. You may need to dump to sql and then load sql file first.<br>> <br>> Process.Start("path/to/shp2pgsql", " -s 4269 -I someshapefile<br>> somepgtable | psql -h someserver -d gisdb -U somepguser")<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> <br>> <br>> <br>> <br>> <br>> ________________________________<br>> <br>> From: postgis-users-bounces@postgis.refractions.net<br>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Saka<br>> Royban<br>> Sent: Wednesday, July 16, 2008 6:16 AM<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> It means using SharpMap i can load shapfiles to PostGIS, perform the<br>> query and finally have the result in shapefile format? Am i right?<br>> Thanks in advance<br>> <br>> <br>> ----- Original Message ----<br>> From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net>;<br>> postgis-users@postgis.refractions.net<br>> Sent: Tuesday, July 15, 2008 10:10:39 PM<br>> Subject: RE: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> David,<br>> <br>> Have him take a look at SharpMap. Its got .NET drivers for PostGIS and<br>> other layer types.<br>> <br>> http://www.codeplex.com/SharpMap <br>> <br>> It works for both ASP.NET and windows .net apps.<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> <br>> <br>> <br>> ________________________________<br>> <br>> From: postgis-users-bounces@postgis.refractions.net on behalf of Jean<br>> David TECHER<br>> Sent: Tue 7/15/2008 2:32 PM<br>> To: postgis-users@postgis.refractions.net<br>> Subject: Re: [postgis-users] newbie question: PostGIS and .NET?<br>> <br>> <br>> <br>> <br>> Quoting Saka Royban <srph124@yahoo.com>:<br>> <br>> > Hi all.<br>> > Sorry for elementary question. but i didn't find anything to support <br>> > PostGIS in .NET environment.<br>> > just some driver for Java.<br>> > I mainly wanna make a desktop GIS applications and it sounds PostGIS <br>> > helpful to be used as a back-end to manage large number of <br>> > shapefiles. So, is there a way to use this spatial database in .NET?<br>> For PostgreSQL you have Npgsql that support PostgreSQL queries<br>> <br>> <br>> > Queries support by PostGIS are excellent but i need to have export <br>> > of SQL commands in shapefile.<br>> <br>> You have pgsql2shp that could do it! Perhaps need to use API to .net?<br>> <br>> > Thanks<br>> ><br>> > saka<br>> ><br>> ><br>> ><br>> ><br>> <br>> <br>> <br>> ===================<br>> Jean David TECHER<br>> 06 60 46 85 05<br>> 04 99 77 17 87<br>> ===================<br>> <br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users <br>> <br>> <br>> ________________________________<br>> <br>> The substance of this message, including any attachments, may be<br>> confidential, legally privileged and/or exempt from disclosure pursuant<br>> to Massachusetts law. It is intended solely for the addressee. If you<br>> received this in error, please contact the sender and delete the<br>> material from any computer. <br>> <br>> ________________________________<br>> <br>> Help make the earth a greener place. If at all possible resist printing<br>> this email and join us in saving paper. <br>> <br>> <br>> -----------------------------------------<br>> The substance of this message, including any attachments, may be<br>> confidential, legally privileged and/or exempt from disclosure<br>> pursuant to Massachusetts law. It is intended<br>> solely for the addressee. If you received this in error, please<br>> contact the sender and delete the material from any computer.<br>> <br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> <br>> <br>> -------------- next part --------------<br>> An HTML attachment was scrubbed...<br>> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20080716/6f35a38f/attachment-0001.html<br>> <br>> ------------------------------<br>> <br>> Message: 11<br>> Date: Wed, 16 Jul 2008 06:11:18 -0700<br>> From: Chris Hermansen <chris.hermansen@timberline.ca><br>> Subject: Re: [postgis-users] Windows Crash<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <487DF376.20308@timberline.ca><br>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>> <br>> Heck, Regina, you certainly didn't offend me. I just didn't find any <br>> useful information on that site. It reminded me of the talking heads <br>> seen on news channels. The discussion of the 4.0 release of KDE for <br>> example - on and on and on and on about how stupid it was to release KDE <br>> 4.0 in that state. OK, we get the point, next?<br>> <br>> I guess anyone with enough starch in their boxers could write something <br>> equally negative about pretty much anything - Windows, OS/X, gardening, <br>> string theory, waste management. Maybe even about PostGIS! :-) :-) I <br>> just don't personally see a whole lot of point in being so <br>> single-mindedly provocative.<br>> <br>> Really, I guess this holds especially true in relation to open source. <br>> Why spend a LOT of energy criticizing it, when it's possible to spend <br>> the same amount of time improving it? Instead of carping, file bug <br>> reports or enhancement reports or whatever. Write code. Write <br>> documentation. Write tutorials. Help people out on mailing lists!<br>> <br>> If you believe that Windows is better than Linux because various Linux <br>> distros offer too many choices when it comes to package managers or <br>> whatever, that's a good reason for you to use Windows. For me, it's a <br>> good reason to pick a good Linux distro and stay with it for awhile. <br>> Both valid points of view, I think.<br>> <br>> You're right about this being off-topic, but to try to bring it back a <br>> bit - you help people out a HUGE amount on this list. You are a <br>> significant contributor to the success of us all in our day to day use <br>> of PostGIS. I think it's fair to say that PostGIS, and many other <br>> things like PostGIS, would not exist if it were not for you and other <br>> similar-minded people.<br>> <br>> So thanks! Thanks for all the individual and specific bits of help you <br>> give to people, and thanks even more for seeing value - to you and to <br>> others - in the activity of supporting us all on this list.<br>> <br>> Obe, Regina wrote:<br>> > Chris,<br>> ><br>> > I apologize if I offended anyone here. I guess this really was very<br>> > off-topic and probably was inappropriate for this list.<br>> ><br>> > >From a monetary stand-point humor sales and if you amass enough audience<br>> > you can get a lot of advertising revenue.<br>> ><br>> > I think its a matter of opininion and not taking yourself too seriously<br>> > also. Most people take themselves too seriously and get offended easily<br>> > when you suggest something they are doing is not peachy. <br>> ><br>> > I personally found the site educational. I think there are silly things<br>> > that all sides of the fence do and to pretend these silly things don't<br>> > exist is not doing anyone any good. People who complain about things get<br>> > results. As they say the squeaky wheel gets the grease.<br>> ><br>> > There are things in Linux I absolutely love, but it does frustrate me a<br>> > bit that each Linux distro is very different. I open up OpenSuse -<br>> > there's yahtz or whatever, Redhat has YUM and the place they decide to<br>> > install things is slightly different between Fedora and EL, and Ubuntu<br>> > (well I haven't really used that enough to complain about it - except it<br>> > has its apt-get and its apt-get crashed my last pc when I tried to<br>> > upgrade my open office). But that is all of minor consequence - I'm sure<br>> > if I dug deeper I would see a reason why no one can decide where to put<br>> > things.<br>> ><br>> > Similar things can be said about Windows and MacOSX I am sure. Although<br>> > I guess I've been drinking the Windows cool-aid for so long that I can't<br>> > think of anything bad to say about it except its command-line sucks<br>> > compared to Linux - but its new Windows Powershell interface is changing<br>> > all of that (so now I can run rm and ls and some of those other great<br>> > commands I learned in my Linux world in windows). Makes me wonder hey -<br>> > is Microsoft secretly injecting some unix juice in their system. I<br>> > expect their new upcoming OS to be "Hey guys we are Unix too".<br>> ><br>> > Thanks,<br>> > Regina<br>> ><br>> ><br>> > -----Original Message-----<br>> > From: postgis-users-bounces@postgis.refractions.net<br>> > [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of<br>> > Chris Hermansen<br>> > Sent: Sunday, July 13, 2008 1:47 PM<br>> > To: PostGIS Users Discussion<br>> > Subject: Re: [postgis-users] Windows Crash<br>> ><br>> > Regina, all;<br>> ><br>> > I read the top four articles on this blog and I guess I just don't <br>> > understand why someone would waste their valuable (?) time writing such <br>> > pointless material, whether the topic be Windows or Linux or OS/X or <br>> > whatever.<br>> ><br>> > Obe, Regina wrote:<br>> > <br>> >> Slightly off topic. Has anyone seen this Linux Haters Blog. All <br>> >> Linux users should read it to understand how the mind of a windows <br>> >> user works so that we can interoperate.<br>> >> <br>> >> http://linuxhaters.blogspot.com/<br>> >> <br>> >> Similarly I would like to see a windows haters blog so I can form a <br>> >> fully unbiased opinion of the various factions. Is there such a<br>> >> <br>> > thing?<br>> > <br>> >> <br>> > ------------------------------------------------------------------------<br>> > <br>> >> *From:* postgis-users-bounces@postgis.refractions.net <br>> >> [mailto:postgis-users-bounces@postgis.refractions.net] *On Behalf Of <br>> >> *Bruce Rindahl<br>> >> *Sent:* Thursday, June 26, 2008 3:53 PM<br>> >> *To:* PostGIS Users Discussion<br>> >> *Subject:* Re: [postgis-users] Windows Crash<br>> >><br>> >> Also on a long shot - do you have any hardware conflicts?<br>> >> Start->Control Panel->System->Hardware->Device Manager<br>> >> Are there any little yellow info symbols?<br>> >> Bruce<br>> >><br>> >> Paul Ramsey wrote:<br>> >> <br>> >>> This is in no way a PostGIS problem, certainly nothing we can ever<br>> >>> fix, just something that the particular combination of PgAdmin, the<br>> >>> geometry we are feeding it, and the way the Windows rendering system<br>> >>> are interacting.<br>> >>><br>> >>> My experience has been that these days the thing that causes a modern<br>> >>> operating system (Windows, Linux, whatever) to crash is bad hardware,<br>> >>> usually bad memory, but sometimes other things. Basically, the<br>> >>> operating system itself is usually rock solid until you shake the<br>> >>> foundations it sits on, the hardware.<br>> >>><br>> >>> So basically, all I can offer is the doctor's advice to the patient<br>> >>> who said "doctor, doctor, it hurts when I raise my arm over my<br>> >>> head!"... "don't raise your arm over your head".<br>> >>><br>> >>> In the meantime, if you want to fiddle with things, swapping the<br>> >>> <br>> > order<br>> > <br>> >>> of your memory in the sockets, changing out the video card, even<br>> >>> changing your screen resolution, might change the interaction such<br>> >>> that you can exercise this particular use case without dropping your<br>> >>> OS to the floor.<br>> >>><br>> >>> P.<br>> >>><br>> >>> On Thu, Jun 26, 2008 at 10:31 AM, Obe, Regina<br>> >>> <br>> > <robe.dnd@cityofboston.gov> wrote:<br>> > <br>> >>> <br>> >>> <br>> >>>> I second Bruce's comment about unhelpful comments.<br>> >>>><br>> >>>> It may actually have nothing to do with Windows in fact.<br>> >>>><br>> >>>> Now getting back to the crash issue. Bob if you are still having<br>> >>>> <br>> > the same<br>> > <br>> >>>> issue with that file, maybe you can post that (or provide a link to<br>> >>>> <br>> > the<br>> > <br>> >>>> shape file you loaded). It could be something to do with the<br>> >>>> <br>> > shapefile.<br>> > <br>> >>>> I recall having this same issue with one particular geometry file<br>> >>>> <br>> > way back<br>> > <br>> >>>> in PgAdmin 1.6 and it was when I scrolled to a specific record it<br>> >>>> <br>> > would<br>> > <br>> >>>> crash, but then later additions (I either maybe never selected the<br>> >>>> <br>> > geometry<br>> > <br>> >>>> field again) or it was fixed in later versions. Doing an AsText<br>> >>>> always seemed to work where as displaying the raw binary was where I<br>> >>>> <br>> > ran<br>> > <br>> >>>> into issues.<br>> >>>><br>> >>>> The other possibility is that it could be a defective graphics card.<br>> >>>> <br>> > I<br>> > <br>> >>>> forget which video cards I had bad runs with, but I remember having<br>> >>>> <br>> > this<br>> > <br>> >>>> same exact issue with my favorite programming editor JEdit. I had 2<br>> >>>> identical computers with same drivers etc and for some reason I<br>> >>>> <br>> > would get<br>> > <br>> >>>> intermittent crashes on one of them. I finally just got fed up and<br>> >>>> <br>> > replaced<br>> > <br>> >>>> the graphics card and then it worked fine.<br>> >>>><br>> >>>> Just some thoughts.<br>> >>>><br>> >>>> Thanks,<br>> >>>> Regina<br>> >>>><br>> >>>><br>> >>>><br>> >>>> ________________________________<br>> >>>><br>> >>>> The substance of this message, including any attachments, may be<br>> >>>> confidential, legally privileged and/or exempt from disclosure<br>> >>>> <br>> > pursuant to<br>> > <br>> >>>> Massachusetts law. It is intended solely for the addressee. If you<br>> >>>> <br>> > received<br>> > <br>> >>>> this in error, please contact the sender and delete the material<br>> >>>> <br>> > from any<br>> > <br>> >>>> computer.<br>> >>>><br>> >>>> ________________________________<br>> >>>><br>> >>>> Help make the earth a greener place. If at all possible resist<br>> >>>> <br>> > printing this<br>> > <br>> >>>> email and join us in saving paper.<br>> >>>><br>> >>>> _______________________________________________<br>> >>>> postgis-users mailing list<br>> >>>> postgis-users@postgis.refractions.net<br>> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>>><br>> >>>><br>> >>>> <br>> >>>> <br>> >>> _______________________________________________<br>> >>> postgis-users mailing list<br>> >>> postgis-users@postgis.refractions.net<br>> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>><br>> >>><br>> >>> <br>> >>> <br>> >> <br>> > ------------------------------------------------------------------------<br>> > <br>> >> *The substance of this message, including any attachments, may be <br>> >> confidential, legally privileged and/or exempt from disclosure <br>> >> pursuant to Massachusetts law. It is intended solely for the <br>> >> addressee. If you received this in error, please contact the sender <br>> >> and delete the material from any computer. *<br>> >><br>> >><br>> >> <br>> > ------------------------------------------------------------------------<br>> > <br>> >> * Help make the earth a greener place. If at all possible resist <br>> >> printing this email and join us in saving paper. *<br>> >><br>> >> * *<br>> >><br>> >> * *<br>> >><br>> >><br>> >> <br>> > ------------------------------------------------------------------------<br>> > <br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >> <br>> >> <br>> ><br>> ><br>> > <br>> <br>> <br>> -- <br>> Regards,<br>> <br>> Chris Hermansen mailto:chris.hermansen@timberline.ca<br>> tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644<br>> Timberline Natural Resource Group · http://www.timberline.ca<br>> 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5<br>> <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 12<br>> Date: Wed, 16 Jul 2008 10:24:25 -0500<br>> From: Stephen Woodbridge <woodbri@swoodbridge.com><br>> Subject: Re: [postgis-users] match line with road segment<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <487E12A9.2070705@swoodbridge.com><br>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>> <br>> searchelite wrote:<br>> > hi all..<br>> > i have a line table consists of gps points, i'm using<br>> > ST_makeline(gps_points) to create the line..my question is, how to match the<br>> > gps line into road segment in postgis? <br>> > <br>> > thank you<br>> <br>> You can buffer the line and see which segments fall inside the buffer.<br>> <br>> select * from roads<br>> where ST_Contains(the_geom, ST_makeline(gps_points));<br>> <br>> -Steve<br>> <br>> <br>> ------------------------------<br>> <br>> Message: 13<br>> Date: Wed, 16 Jul 2008 10:13:19 -0600<br>> From: Brent Fraser <bfraser@geoanalytic.com><br>> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <487E1E1F.9070409@geoanalytic.com><br>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>> <br>> To all,<br>> <br>> My quest for non-overlapping polygons continues:<br>> <br>> I started with a table (temp_polys) of 3253 polygons (with some overlap) with a "class" attribute.<br>> <br>> To get rid of overlapping polys with the same class value:<br>> CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from temp_polys GROUP BY class;<br>> <br>> This created a table of 32 multi-polygons (grouped by class). I still have to remove the overlap between polygons with different class values, so my plan is to convert to linestrings, node the linestrings, polygonize, and (re)assign the class value using StarSpan. So first:<br>> <br>> Convert to linestrings:<br>> INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing( ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys;<br>> <br>> This produced 1768 linestring records. Attempting to node the linestrings:<br>> INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom) AS the_geom FROM temp3_lines;<br>> <br>> Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz Windows XP, Postgres 8.3.3, PostGIS 1.3.3).<br>> <br>> I dumped the temp3_lines table into a shapefile and asked OpenJump to node AND polygonize. That took 24 seconds.<br>> <br>> Since the above data is a small sub-set of my 1.2 million polygons, OpenJump is not really a solution for cleaning the data all at once. Looks like some scripting is in order...<br>> <br>> Brent<br>> <br>> <br>> Brent Fraser wrote:<br>> > Regina,<br>> > <br>> > The "SELECT MAX..." query didn't work on my sub-set of 12800 polygons. <br>> > It created 12643 polygons some of which overlap (I expected more, not <br>> > less, than the original).<br>> > <br>> > I may try converting to linestrings, creating one "minimum bounding <br>> > rectangle" for the entire dataset, then doing an intersect of the lines <br>> > with the MBR. In my case this would be ok as there are not attributes <br>> > on the polygons yet.<br>> > <br>> > Thanks!<br>> > Brent<br>> > <br>> > Obe, Regina wrote:<br>> >> Brent,<br>> >> <br>> >> I guess it really depends on how exactly you want to achieve <br>> >> non-overlapping.<br>> >> If for example you are basing it on some sort of attribute and all <br>> >> your overlapping polygons are valid<br>> >> <br>> >> Then a simple<br>> >> <br>> >> SELECT ST_Union(the_geom) As newgeom, field1<br>> >> FROM sometable<br>> >> GROUP BY field1<br>> >> <br>> >> I think will guarantee non-overlapping polygons because as part of the <br>> >> process of ST_Union - it would irradicate the overlapping regions to <br>> >> just create one. That is part of the reason why its so much slower <br>> >> than ST_Collect for example.<br>> >> <br>> >> For your exact case below - you would union all the overlapping <br>> >> polygons together which could be really slow depending on how many <br>> >> overlap. The query I would write to achieve that would be something <br>> >> like this<br>> >> <br>> >> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom<br>> >> FROM poly a<br>> >> GROUP BY (SELECT MAX(r.gid) FROM poly r<br>> >> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));<br>> >> <br>> >> Hope that helps,<br>> >> Regina<br>> >><br>> >> ------------------------------------------------------------------------<br>> >> *From:* postgis-users-bounces@postgis.refractions.net on behalf of <br>> >> Brent Fraser<br>> >> *Sent:* Fri 7/11/2008 12:14 PM<br>> >> *To:* PostGIS Users Discussion<br>> >> *Subject:* Re: [postgis-users] Transform overlapping polygons to <br>> >> non-overlapping?<br>> >><br>> >> To All,<br>> >><br>> >> There doesn't seem to be an obvious answer to the problem given <br>> >> below (aka cleaning polygons, creating planar polygons, etc). I did <br>> >> see a note on the PostGIS wiki wishlist to "Add a geometry cleaner". <br>> >> There is also a suggestion to convert to linestrings, node, then <br>> >> polygonize (while that may work for a small set of polygons, I've got <br>> >> 1.1 million to clean). JTS, Geos, etc will likely fail due to the <br>> >> large number of polygons so I'll need a different approach.<br>> >><br>> >> I'm considering writing some code to iterate through my table of <br>> >> polygons, cleaning a small subset at a time. I think using PostGIS <br>> >> for the geometry storage and spatial query/selection makes sense. Any <br>> >> suggestions on which API to use?<br>> >> GDAL's OGR<br>> >> PostgreSQL's libpq<br>> >> other?<br>> >><br>> >> Thanks!<br>> >> Brent Fraser<br>> >><br>> >> Brent Fraser wrote:<br>> >>> PostGIS'ers,<br>> >>><br>> >>> I've got a table of overlapping polygons. How can I make it a table of<br>> >>> non-overlapping polygons?<br>> >>><br>> >>> For example, if table "polys2" contains two polygons A1 and B1 which<br>> >>> overlap. I'd like to create table "polys3" with polygons A2, B2, C2,<br>> >>> where C2 is the overlap region of A1 and B1, and A2 = A1 - C2, and B2 =<br>> >>> B1 - C2.<br>> >>><br>> >>> Looking at the overlay operations in the JTS doc it looks like doing an<br>> >>> Intersection (to get only the overlapping area) then adding the<br>> >>> Symmetric Difference (to get the non-overlapping areas) might work.<br>> >>><br>> >>> Am I on the right track or is there an easier way (since all the<br>> >>> polygons are in one table)?<br>> >>><br>> >>> Thanks!<br>> >>> Brent Fraser<br>> >>> _______________________________________________<br>> >>> postgis-users mailing list<br>> >>> postgis-users@postgis.refractions.net<br>> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >> ------------------------------------------------------------------------<br>> >><br>> >> * The substance of this message, including any attachments, may be <br>> >> confidential, legally privileged and/or exempt from disclosure <br>> >> pursuant to Massachusetts law. It is intended solely for the <br>> >> addressee. If you received this in error, please contact the sender <br>> >> and delete the material from any computer. *<br>> >><br>> >> ------------------------------------------------------------------------<br>> >><br>> >> * Help make the earth a greener place. If at all possible resist <br>> >> printing this email and join us in saving paper. *<br>> >><br>> >> * *<br>> >><br>> >> * *<br>> >><br>> >><br>> >> ------------------------------------------------------------------------<br>> >><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>> <br>> <br>> ------------------------------<br>> <br>> Message: 14<br>> Date: Wed, 16 Jul 2008 12:40:49 -0400<br>> From: "Paragon Corporation" <lr@pcorp.us><br>> Subject: RE: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: "'PostGIS Users Discussion'"<br>> <postgis-users@postgis.refractions.net><br>> Message-ID: <9ECA0A60357B4AF2AFA91EECE971CA8F@H><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> Brent,<br>> <br>> INSERT INTO temp3_lines (the_geom) <br>> SELECT ST_ExteriorRing( ST_GeometryN(the_geom, generate_series(1,<br>> ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys;<br>> <br>> Can be done more efficiently using ST_Dump<br>> <br>> INSERT INTO temp3_lines (the_geom) <br>> SELECT ST_ExteriorRing((ST_Dump(the_geom)).geom) AS the_geom FROM<br>> temp2_polys;<br>> <br>> <br>> Regarding the unioning I mentioned - I thought about more why what I<br>> proposed had still overlapping polygons and I realized its because the<br>> grouping I proposed needs to be <br>> A recursive query (which means you'd need to wrap it in an sql or plpgsql<br>> function) since as it stands it would only find the first root overlaps and<br>> not the A overlap B overlap C (e.g. c would not be in the same grouping and<br>> A,B if it doesn't also overlap with A) .<br>> <br>> So two ways<br>> 1) Write recursive query (using a plpgsql or sql helper function) - which I<br>> haven't given much thought to the most efficient way of doing that<br>> <br>> Or<br>> <br>> 2) Repeat the union thing I mentioned over and over again until you have a<br>> set that has no more overlapping polygons.<br>> <br>> Then you do a ST_Dump to get back individual polygons.<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> -----Original Message-----<br>> From: postgis-users-bounces@postgis.refractions.net<br>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Brent<br>> Fraser<br>> Sent: Wednesday, July 16, 2008 12:13 PM<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> <br>> To all,<br>> <br>> My quest for non-overlapping polygons continues:<br>> <br>> I started with a table (temp_polys) of 3253 polygons (with some overlap)<br>> with a "class" attribute.<br>> <br>> To get rid of overlapping polys with the same class value:<br>> CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from<br>> temp_polys GROUP BY class;<br>> <br>> This created a table of 32 multi-polygons (grouped by class). I still have<br>> to remove the overlap between polygons with different class values, so my<br>> plan is to convert to linestrings, node the linestrings, polygonize, and<br>> (re)assign the class value using StarSpan. So first:<br>> <br>> Convert to linestrings:<br>> INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing(<br>> ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))) AS<br>> the_geom FROM temp2_polys;<br>> <br>> This produced 1768 linestring records. Attempting to node the linestrings:<br>> INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom) AS<br>> the_geom FROM temp3_lines;<br>> <br>> Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz Windows<br>> XP, Postgres 8.3.3, PostGIS 1.3.3).<br>> <br>> I dumped the temp3_lines table into a shapefile and asked OpenJump to node<br>> AND polygonize. That took 24 seconds.<br>> <br>> Since the above data is a small sub-set of my 1.2 million polygons, OpenJump<br>> is not really a solution for cleaning the data all at once. Looks like some<br>> scripting is in order...<br>> <br>> Brent<br>> <br>> <br>> Brent Fraser wrote:<br>> > Regina,<br>> > <br>> > The "SELECT MAX..." query didn't work on my sub-set of 12800 polygons. <br>> > It created 12643 polygons some of which overlap (I expected more, not <br>> > less, than the original).<br>> > <br>> > I may try converting to linestrings, creating one "minimum bounding <br>> > rectangle" for the entire dataset, then doing an intersect of the <br>> > lines with the MBR. In my case this would be ok as there are not <br>> > attributes on the polygons yet.<br>> > <br>> > Thanks!<br>> > Brent<br>> > <br>> > Obe, Regina wrote:<br>> >> Brent,<br>> >> <br>> >> I guess it really depends on how exactly you want to achieve <br>> >> non-overlapping.<br>> >> If for example you are basing it on some sort of attribute and all <br>> >> your overlapping polygons are valid<br>> >> <br>> >> Then a simple<br>> >> <br>> >> SELECT ST_Union(the_geom) As newgeom, field1 FROM sometable GROUP BY <br>> >> field1<br>> >> <br>> >> I think will guarantee non-overlapping polygons because as part of <br>> >> the process of ST_Union - it would irradicate the overlapping regions <br>> >> to just create one. That is part of the reason why its so much <br>> >> slower than ST_Collect for example.<br>> >> <br>> >> For your exact case below - you would union all the overlapping <br>> >> polygons together which could be really slow depending on how many <br>> >> overlap. The query I would write to achieve that would be something <br>> >> like this<br>> >> <br>> >> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom FROM <br>> >> poly a GROUP BY (SELECT MAX(r.gid) FROM poly r<br>> >> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));<br>> >> <br>> >> Hope that helps,<br>> >> Regina<br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >> *From:* postgis-users-bounces@postgis.refractions.net on behalf of <br>> >> Brent Fraser<br>> >> *Sent:* Fri 7/11/2008 12:14 PM<br>> >> *To:* PostGIS Users Discussion<br>> >> *Subject:* Re: [postgis-users] Transform overlapping polygons to <br>> >> non-overlapping?<br>> >><br>> >> To All,<br>> >><br>> >> There doesn't seem to be an obvious answer to the problem given <br>> >> below (aka cleaning polygons, creating planar polygons, etc). I did <br>> >> see a note on the PostGIS wiki wishlist to "Add a geometry cleaner".<br>> >> There is also a suggestion to convert to linestrings, node, then <br>> >> polygonize (while that may work for a small set of polygons, I've got<br>> >> 1.1 million to clean). JTS, Geos, etc will likely fail due to the <br>> >> large number of polygons so I'll need a different approach.<br>> >><br>> >> I'm considering writing some code to iterate through my table of <br>> >> polygons, cleaning a small subset at a time. I think using PostGIS <br>> >> for the geometry storage and spatial query/selection makes sense. <br>> >> Any suggestions on which API to use?<br>> >> GDAL's OGR<br>> >> PostgreSQL's libpq<br>> >> other?<br>> >><br>> >> Thanks!<br>> >> Brent Fraser<br>> >><br>> >> Brent Fraser wrote:<br>> >>> PostGIS'ers,<br>> >>><br>> >>> I've got a table of overlapping polygons. How can I make it a <br>> >>> table of non-overlapping polygons?<br>> >>><br>> >>> For example, if table "polys2" contains two polygons A1 and B1 <br>> >>> which overlap. I'd like to create table "polys3" with polygons A2, <br>> >>> B2, C2, where C2 is the overlap region of A1 and B1, and A2 = A1 - <br>> >>> C2, and B2 =<br>> >>> B1 - C2.<br>> >>><br>> >>> Looking at the overlay operations in the JTS doc it looks like <br>> >>> doing an Intersection (to get only the overlapping area) then <br>> >>> adding the Symmetric Difference (to get the non-overlapping areas)<br>> might work.<br>> >>><br>> >>> Am I on the right track or is there an easier way (since all the <br>> >>> polygons are in one table)?<br>> >>><br>> >>> Thanks!<br>> >>> Brent Fraser<br>> >>> _______________________________________________<br>> >>> postgis-users mailing list<br>> >>> postgis-users@postgis.refractions.net<br>> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >><br>> >> * The substance of this message, including any attachments, may be <br>> >> confidential, legally privileged and/or exempt from disclosure <br>> >> pursuant to Massachusetts law. It is intended solely for the <br>> >> addressee. If you received this in error, please contact the sender <br>> >> and delete the material from any computer. *<br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >><br>> >> * Help make the earth a greener place. If at all possible resist <br>> >> printing this email and join us in saving paper. *<br>> >><br>> >> * *<br>> >><br>> >> * *<br>> >><br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >><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>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 15<br>> Date: Wed, 16 Jul 2008 13:05:59 -0400<br>> From: "Paragon Corporation" <lr@pcorp.us><br>> Subject: RE: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: "'PostGIS Users Discussion'"<br>> <postgis-users@postgis.refractions.net><br>> Message-ID: <2A0B206E275A48A791393C6AEE6CFFF0@H><br>> Content-Type: text/plain; charset="us-ascii"<br>> <br>> One more question - you sure you want ST_Overlaps and not ST_Intersects.<br>> If one geometry sits completely inside another, it is not considered to<br>> overlap, but they do intersect. <br>> <br>> -----Original Message-----<br>> From: postgis-users-bounces@postgis.refractions.net<br>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Paragon<br>> Corporation<br>> Sent: Wednesday, July 16, 2008 12:41 PM<br>> To: 'PostGIS Users Discussion'<br>> Subject: RE: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> <br>> Brent,<br>> <br>> INSERT INTO temp3_lines (the_geom) <br>> SELECT ST_ExteriorRing( ST_GeometryN(the_geom, generate_series(1,<br>> ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys;<br>> <br>> Can be done more efficiently using ST_Dump<br>> <br>> INSERT INTO temp3_lines (the_geom) <br>> SELECT ST_ExteriorRing((ST_Dump(the_geom)).geom) AS the_geom FROM<br>> temp2_polys;<br>> <br>> <br>> Regarding the unioning I mentioned - I thought about more why what I<br>> proposed had still overlapping polygons and I realized its because the<br>> grouping I proposed needs to be A recursive query (which means you'd need to<br>> wrap it in an sql or plpgsql<br>> function) since as it stands it would only find the first root overlaps and<br>> not the A overlap B overlap C (e.g. c would not be in the same grouping and<br>> A,B if it doesn't also overlap with A) .<br>> <br>> So two ways<br>> 1) Write recursive query (using a plpgsql or sql helper function) - which I<br>> haven't given much thought to the most efficient way of doing that<br>> <br>> Or<br>> <br>> 2) Repeat the union thing I mentioned over and over again until you have a<br>> set that has no more overlapping polygons.<br>> <br>> Then you do a ST_Dump to get back individual polygons.<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> <br>> -----Original Message-----<br>> From: postgis-users-bounces@postgis.refractions.net<br>> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Brent<br>> Fraser<br>> Sent: Wednesday, July 16, 2008 12:13 PM<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> <br>> To all,<br>> <br>> My quest for non-overlapping polygons continues:<br>> <br>> I started with a table (temp_polys) of 3253 polygons (with some overlap)<br>> with a "class" attribute.<br>> <br>> To get rid of overlapping polys with the same class value:<br>> CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from<br>> temp_polys GROUP BY class;<br>> <br>> This created a table of 32 multi-polygons (grouped by class). I still have<br>> to remove the overlap between polygons with different class values, so my<br>> plan is to convert to linestrings, node the linestrings, polygonize, and<br>> (re)assign the class value using StarSpan. So first:<br>> <br>> Convert to linestrings:<br>> INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing(<br>> ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))) AS<br>> the_geom FROM temp2_polys;<br>> <br>> This produced 1768 linestring records. Attempting to node the linestrings:<br>> INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom) AS<br>> the_geom FROM temp3_lines;<br>> <br>> Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz Windows<br>> XP, Postgres 8.3.3, PostGIS 1.3.3).<br>> <br>> I dumped the temp3_lines table into a shapefile and asked OpenJump to node<br>> AND polygonize. That took 24 seconds.<br>> <br>> Since the above data is a small sub-set of my 1.2 million polygons, OpenJump<br>> is not really a solution for cleaning the data all at once. Looks like some<br>> scripting is in order...<br>> <br>> Brent<br>> <br>> <br>> Brent Fraser wrote:<br>> > Regina,<br>> > <br>> > The "SELECT MAX..." query didn't work on my sub-set of 12800 polygons. <br>> > It created 12643 polygons some of which overlap (I expected more, not <br>> > less, than the original).<br>> > <br>> > I may try converting to linestrings, creating one "minimum bounding <br>> > rectangle" for the entire dataset, then doing an intersect of the <br>> > lines with the MBR. In my case this would be ok as there are not <br>> > attributes on the polygons yet.<br>> > <br>> > Thanks!<br>> > Brent<br>> > <br>> > Obe, Regina wrote:<br>> >> Brent,<br>> >> <br>> >> I guess it really depends on how exactly you want to achieve <br>> >> non-overlapping.<br>> >> If for example you are basing it on some sort of attribute and all <br>> >> your overlapping polygons are valid<br>> >> <br>> >> Then a simple<br>> >> <br>> >> SELECT ST_Union(the_geom) As newgeom, field1 FROM sometable GROUP BY<br>> >> field1<br>> >> <br>> >> I think will guarantee non-overlapping polygons because as part of <br>> >> the process of ST_Union - it would irradicate the overlapping regions <br>> >> to just create one. That is part of the reason why its so much <br>> >> slower than ST_Collect for example.<br>> >> <br>> >> For your exact case below - you would union all the overlapping <br>> >> polygons together which could be really slow depending on how many <br>> >> overlap. The query I would write to achieve that would be something <br>> >> like this<br>> >> <br>> >> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom FROM <br>> >> poly a GROUP BY (SELECT MAX(r.gid) FROM poly r<br>> >> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));<br>> >> <br>> >> Hope that helps,<br>> >> Regina<br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >> *From:* postgis-users-bounces@postgis.refractions.net on behalf of <br>> >> Brent Fraser<br>> >> *Sent:* Fri 7/11/2008 12:14 PM<br>> >> *To:* PostGIS Users Discussion<br>> >> *Subject:* Re: [postgis-users] Transform overlapping polygons to <br>> >> non-overlapping?<br>> >><br>> >> To All,<br>> >><br>> >> There doesn't seem to be an obvious answer to the problem given <br>> >> below (aka cleaning polygons, creating planar polygons, etc). I did <br>> >> see a note on the PostGIS wiki wishlist to "Add a geometry cleaner".<br>> >> There is also a suggestion to convert to linestrings, node, then <br>> >> polygonize (while that may work for a small set of polygons, I've got<br>> >> 1.1 million to clean). JTS, Geos, etc will likely fail due to the <br>> >> large number of polygons so I'll need a different approach.<br>> >><br>> >> I'm considering writing some code to iterate through my table of <br>> >> polygons, cleaning a small subset at a time. I think using PostGIS <br>> >> for the geometry storage and spatial query/selection makes sense.<br>> >> Any suggestions on which API to use?<br>> >> GDAL's OGR<br>> >> PostgreSQL's libpq<br>> >> other?<br>> >><br>> >> Thanks!<br>> >> Brent Fraser<br>> >><br>> >> Brent Fraser wrote:<br>> >>> PostGIS'ers,<br>> >>><br>> >>> I've got a table of overlapping polygons. How can I make it a <br>> >>> table of non-overlapping polygons?<br>> >>><br>> >>> For example, if table "polys2" contains two polygons A1 and B1 <br>> >>> which overlap. I'd like to create table "polys3" with polygons A2, <br>> >>> B2, C2, where C2 is the overlap region of A1 and B1, and A2 = A1 - <br>> >>> C2, and B2 =<br>> >>> B1 - C2.<br>> >>><br>> >>> Looking at the overlay operations in the JTS doc it looks like <br>> >>> doing an Intersection (to get only the overlapping area) then <br>> >>> adding the Symmetric Difference (to get the non-overlapping areas)<br>> might work.<br>> >>><br>> >>> Am I on the right track or is there an easier way (since all the <br>> >>> polygons are in one table)?<br>> >>><br>> >>> Thanks!<br>> >>> Brent Fraser<br>> >>> _______________________________________________<br>> >>> postgis-users mailing list<br>> >>> postgis-users@postgis.refractions.net<br>> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >><br>> >> * The substance of this message, including any attachments, may be <br>> >> confidential, legally privileged and/or exempt from disclosure <br>> >> pursuant to Massachusetts law. It is intended solely for the <br>> >> addressee. If you received this in error, please contact the sender <br>> >> and delete the material from any computer. *<br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >><br>> >> * Help make the earth a greener place. If at all possible resist <br>> >> printing this email and join us in saving paper. *<br>> >><br>> >> * *<br>> >><br>> >> * *<br>> >><br>> >><br>> >> ---------------------------------------------------------------------<br>> >> ---<br>> >><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>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 16<br>> Date: Wed, 16 Jul 2008 11:58:07 -0600<br>> From: Brent Fraser <bfraser@geoanalytic.com><br>> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <487E36AF.2020801@geoanalytic.com><br>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>> <br>> Regina,<br>> <br>> I'm not convinced ST_Union is the way to go (using ST_Overlaps OR ST_Intersects as a condition). Basically I want to iterate over the collection (recursively?) clipping one polygon to another until I'm left with no overlapping (or intersecting) polygons (planar topology). This is sightly more complicated than the way I originally posed the problem (I wanted to created slivers from the overlapping areas to get planar topology).<br>> <br>> A little background:<br>> <br>> My polygons represent a classification of vegetation of a large area of interest. In theory any point in the area of interest must fall in one and only one polygon. Due to an artifact of my image segmentation process, my polygons currently have slight overlap which I need to "dissolve" (and I don't care which polygon the overlap sliver gets dissolved into).<br>> <br>> Thanks!<br>> Brent. <br>> <br>> Paragon Corporation wrote:<br>> > One more question - you sure you want ST_Overlaps and not ST_Intersects.<br>> > If one geometry sits completely inside another, it is not considered to<br>> > overlap, but they do intersect. <br>> > <br>> > -----Original Message-----<br>> > From: postgis-users-bounces@postgis.refractions.net<br>> > [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Paragon<br>> > Corporation<br>> > Sent: Wednesday, July 16, 2008 12:41 PM<br>> > To: 'PostGIS Users Discussion'<br>> > Subject: RE: [postgis-users] Transform overlapping polygons to<br>> > non-overlapping?<br>> > <br>> > Brent,<br>> > <br>> > INSERT INTO temp3_lines (the_geom) <br>> > SELECT ST_ExteriorRing( ST_GeometryN(the_geom, generate_series(1,<br>> > ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys;<br>> > <br>> > Can be done more efficiently using ST_Dump<br>> > <br>> > INSERT INTO temp3_lines (the_geom) <br>> > SELECT ST_ExteriorRing((ST_Dump(the_geom)).geom) AS the_geom FROM<br>> > temp2_polys;<br>> > <br>> > <br>> > Regarding the unioning I mentioned - I thought about more why what I<br>> > proposed had still overlapping polygons and I realized its because the<br>> > grouping I proposed needs to be A recursive query (which means you'd need to<br>> > wrap it in an sql or plpgsql<br>> > function) since as it stands it would only find the first root overlaps and<br>> > not the A overlap B overlap C (e.g. c would not be in the same grouping and<br>> > A,B if it doesn't also overlap with A) .<br>> > <br>> > So two ways<br>> > 1) Write recursive query (using a plpgsql or sql helper function) - which I<br>> > haven't given much thought to the most efficient way of doing that<br>> > <br>> > Or<br>> > <br>> > 2) Repeat the union thing I mentioned over and over again until you have a<br>> > set that has no more overlapping polygons.<br>> > <br>> > Then you do a ST_Dump to get back individual polygons.<br>> > <br>> > Hope that helps,<br>> > Regina<br>> > <br>> > <br>> > -----Original Message-----<br>> > From: postgis-users-bounces@postgis.refractions.net<br>> > [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Brent<br>> > Fraser<br>> > Sent: Wednesday, July 16, 2008 12:13 PM<br>> > To: PostGIS Users Discussion<br>> > Subject: Re: [postgis-users] Transform overlapping polygons to<br>> > non-overlapping?<br>> > <br>> > To all,<br>> > <br>> > My quest for non-overlapping polygons continues:<br>> > <br>> > I started with a table (temp_polys) of 3253 polygons (with some overlap)<br>> > with a "class" attribute.<br>> > <br>> > To get rid of overlapping polys with the same class value:<br>> > CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from<br>> > temp_polys GROUP BY class;<br>> > <br>> > This created a table of 32 multi-polygons (grouped by class). I still have<br>> > to remove the overlap between polygons with different class values, so my<br>> > plan is to convert to linestrings, node the linestrings, polygonize, and<br>> > (re)assign the class value using StarSpan. So first:<br>> > <br>> > Convert to linestrings:<br>> > INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing(<br>> > ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))) AS<br>> > the_geom FROM temp2_polys;<br>> > <br>> > This produced 1768 linestring records. Attempting to node the linestrings:<br>> > INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom) AS<br>> > the_geom FROM temp3_lines;<br>> > <br>> > Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz Windows<br>> > XP, Postgres 8.3.3, PostGIS 1.3.3).<br>> > <br>> > I dumped the temp3_lines table into a shapefile and asked OpenJump to node<br>> > AND polygonize. That took 24 seconds.<br>> > <br>> > Since the above data is a small sub-set of my 1.2 million polygons, OpenJump<br>> > is not really a solution for cleaning the data all at once. Looks like some<br>> > scripting is in order...<br>> > <br>> > Brent<br>> > <br>> > <br>> > Brent Fraser wrote:<br>> >> Regina,<br>> >><br>> >> The "SELECT MAX..." query didn't work on my sub-set of 12800 polygons. <br>> >> It created 12643 polygons some of which overlap (I expected more, not <br>> >> less, than the original).<br>> >><br>> >> I may try converting to linestrings, creating one "minimum bounding <br>> >> rectangle" for the entire dataset, then doing an intersect of the <br>> >> lines with the MBR. In my case this would be ok as there are not <br>> >> attributes on the polygons yet.<br>> >><br>> >> Thanks!<br>> >> Brent<br>> >><br>> >> Obe, Regina wrote:<br>> >>> Brent,<br>> >>> <br>> >>> I guess it really depends on how exactly you want to achieve <br>> >>> non-overlapping.<br>> >>> If for example you are basing it on some sort of attribute and all <br>> >>> your overlapping polygons are valid<br>> >>> <br>> >>> Then a simple<br>> >>> <br>> >>> SELECT ST_Union(the_geom) As newgeom, field1 FROM sometable GROUP BY<br>> >>> field1<br>> >>> <br>> >>> I think will guarantee non-overlapping polygons because as part of <br>> >>> the process of ST_Union - it would irradicate the overlapping regions <br>> >>> to just create one. That is part of the reason why its so much <br>> >>> slower than ST_Collect for example.<br>> >>> <br>> >>> For your exact case below - you would union all the overlapping <br>> >>> polygons together which could be really slow depending on how many <br>> >>> overlap. The query I would write to achieve that would be something <br>> >>> like this<br>> >>> <br>> >>> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom FROM <br>> >>> poly a GROUP BY (SELECT MAX(r.gid) FROM poly r<br>> >>> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));<br>> >>> <br>> >>> Hope that helps,<br>> >>> Regina<br>> >>><br>> >>> ---------------------------------------------------------------------<br>> >>> ---<br>> >>> *From:* postgis-users-bounces@postgis.refractions.net on behalf of <br>> >>> Brent Fraser<br>> >>> *Sent:* Fri 7/11/2008 12:14 PM<br>> >>> *To:* PostGIS Users Discussion<br>> >>> *Subject:* Re: [postgis-users] Transform overlapping polygons to <br>> >>> non-overlapping?<br>> >>><br>> >>> To All,<br>> >>><br>> >>> There doesn't seem to be an obvious answer to the problem given <br>> >>> below (aka cleaning polygons, creating planar polygons, etc). I did <br>> >>> see a note on the PostGIS wiki wishlist to "Add a geometry cleaner".<br>> >>> There is also a suggestion to convert to linestrings, node, then <br>> >>> polygonize (while that may work for a small set of polygons, I've got<br>> >>> 1.1 million to clean). JTS, Geos, etc will likely fail due to the <br>> >>> large number of polygons so I'll need a different approach.<br>> >>><br>> >>> I'm considering writing some code to iterate through my table of <br>> >>> polygons, cleaning a small subset at a time. I think using PostGIS <br>> >>> for the geometry storage and spatial query/selection makes sense.<br>> >>> Any suggestions on which API to use?<br>> >>> GDAL's OGR<br>> >>> PostgreSQL's libpq<br>> >>> other?<br>> >>><br>> >>> Thanks!<br>> >>> Brent Fraser<br>> >>><br>> >>> Brent Fraser wrote:<br>> >>>> PostGIS'ers,<br>> >>>><br>> >>>> I've got a table of overlapping polygons. How can I make it a <br>> >>>> table of non-overlapping polygons?<br>> >>>><br>> >>>> For example, if table "polys2" contains two polygons A1 and B1 <br>> >>>> which overlap. I'd like to create table "polys3" with polygons A2, <br>> >>>> B2, C2, where C2 is the overlap region of A1 and B1, and A2 = A1 - <br>> >>>> C2, and B2 =<br>> >>>> B1 - C2.<br>> >>>><br>> >>>> Looking at the overlay operations in the JTS doc it looks like <br>> >>>> doing an Intersection (to get only the overlapping area) then <br>> >>>> adding the Symmetric Difference (to get the non-overlapping areas)<br>> > might work.<br>> >>>> Am I on the right track or is there an easier way (since all the <br>> >>>> polygons are in one table)?<br>> >>>><br>> >>>> Thanks!<br>> >>>> Brent Fraser<br>> >>>> _______________________________________________<br>> >>>> postgis-users mailing list<br>> >>>> postgis-users@postgis.refractions.net<br>> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>>><br>> >>> _______________________________________________<br>> >>> postgis-users mailing list<br>> >>> postgis-users@postgis.refractions.net<br>> >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>><br>> >>> ---------------------------------------------------------------------<br>> >>> ---<br>> >>><br>> >>> * The substance of this message, including any attachments, may be <br>> >>> confidential, legally privileged and/or exempt from disclosure <br>> >>> pursuant to Massachusetts law. It is intended solely for the <br>> >>> addressee. If you received this in error, please contact the sender <br>> >>> and delete the material from any computer. *<br>> >>><br>> >>> ---------------------------------------------------------------------<br>> >>> ---<br>> >>><br>> >>> * Help make the earth a greener place. If at all possible resist <br>> >>> printing this email and join us in saving paper. *<br>> >>><br>> >>> * *<br>> >>><br>> >>> * *<br>> >>><br>> >>><br>> >>> ---------------------------------------------------------------------<br>> >>> ---<br>> >>><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>> > _______________________________________________<br>> > postgis-users mailing list<br>> > postgis-users@postgis.refractions.net<br>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > <br>> > <br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > postgis-users@postgis.refractions.net<br>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > <br>> > <br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > postgis-users@postgis.refractions.net<br>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > <br>> <br>> <br>> ------------------------------<br>> <br>> Message: 17<br>> Date: Wed, 16 Jul 2008 11:07:09 -0700<br>> From: Dylan Beaudette <dylan.beaudette@gmail.com><br>> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: postgis-users@postgis.refractions.net<br>> Message-ID: <200807161107.09588.dylan.beaudette@gmail.com><br>> Content-Type: text/plain; charset="iso-8859-1"<br>> <br>> On Wednesday 16 July 2008, Brent Fraser wrote:<br>> > Regina,<br>> ><br>> > I'm not convinced ST_Union is the way to go (using ST_Overlaps OR<br>> > ST_Intersects as a condition). Basically I want to iterate over the<br>> > collection (recursively?) clipping one polygon to another until I'm left<br>> > with no overlapping (or intersecting) polygons (planar topology). This is<br>> > sightly more complicated than the way I originally posed the problem (I<br>> > wanted to created slivers from the overlapping areas to get planar<br>> > topology).<br>> ><br>> > A little background:<br>> ><br>> > My polygons represent a classification of vegetation of a large area of<br>> > interest. In theory any point in the area of interest must fall in one and<br>> > only one polygon. Due to an artifact of my image segmentation process, my<br>> > polygons currently have slight overlap which I need to "dissolve" (and I<br>> > don't care which polygon the overlap sliver gets dissolved into).<br>> ><br>> > Thanks!<br>> > Brent.<br>> <br>> It really sounds like you need to perform some "cleaning" operations in a <br>> topologically-aware GIS. Check out the v.clean module in GRASS.<br>> <br>> Cheers,<br>> <br>> Dylan<br>> <br>> <br>> > Paragon Corporation wrote:<br>> > > One more question - you sure you want ST_Overlaps and not ST_Intersects.<br>> > > If one geometry sits completely inside another, it is not considered to<br>> > > overlap, but they do intersect.<br>> > ><br>> > > -----Original Message-----<br>> > > From: postgis-users-bounces@postgis.refractions.net<br>> > > [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of<br>> > > Paragon Corporation<br>> > > Sent: Wednesday, July 16, 2008 12:41 PM<br>> > > To: 'PostGIS Users Discussion'<br>> > > Subject: RE: [postgis-users] Transform overlapping polygons to<br>> > > non-overlapping?<br>> > ><br>> > > Brent,<br>> > ><br>> > > INSERT INTO temp3_lines (the_geom)<br>> > > SELECT ST_ExteriorRing( ST_GeometryN(the_geom, generate_series(1,<br>> > > ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys;<br>> > ><br>> > > Can be done more efficiently using ST_Dump<br>> > ><br>> > > INSERT INTO temp3_lines (the_geom)<br>> > > SELECT ST_ExteriorRing((ST_Dump(the_geom)).geom) AS the_geom FROM<br>> > > temp2_polys;<br>> > ><br>> > ><br>> > > Regarding the unioning I mentioned - I thought about more why what I<br>> > > proposed had still overlapping polygons and I realized its because the<br>> > > grouping I proposed needs to be A recursive query (which means you'd need<br>> > > to wrap it in an sql or plpgsql<br>> > > function) since as it stands it would only find the first root overlaps<br>> > > and not the A overlap B overlap C (e.g. c would not be in the same<br>> > > grouping and A,B if it doesn't also overlap with A) .<br>> > ><br>> > > So two ways<br>> > > 1) Write recursive query (using a plpgsql or sql helper function) - which<br>> > > I haven't given much thought to the most efficient way of doing that<br>> > ><br>> > > Or<br>> > ><br>> > > 2) Repeat the union thing I mentioned over and over again until you have<br>> > > a set that has no more overlapping polygons.<br>> > ><br>> > > Then you do a ST_Dump to get back individual polygons.<br>> > ><br>> > > Hope that helps,<br>> > > Regina<br>> > ><br>> > ><br>> > > -----Original Message-----<br>> > > From: postgis-users-bounces@postgis.refractions.net<br>> > > [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Brent<br>> > > Fraser<br>> > > Sent: Wednesday, July 16, 2008 12:13 PM<br>> > > To: PostGIS Users Discussion<br>> > > Subject: Re: [postgis-users] Transform overlapping polygons to<br>> > > non-overlapping?<br>> > ><br>> > > To all,<br>> > ><br>> > > My quest for non-overlapping polygons continues:<br>> > ><br>> > > I started with a table (temp_polys) of 3253 polygons (with some overlap)<br>> > > with a "class" attribute.<br>> > ><br>> > > To get rid of overlapping polys with the same class value:<br>> > > CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from<br>> > > temp_polys GROUP BY class;<br>> > ><br>> > > This created a table of 32 multi-polygons (grouped by class). I still<br>> > > have to remove the overlap between polygons with different class values,<br>> > > so my plan is to convert to linestrings, node the linestrings,<br>> > > polygonize, and (re)assign the class value using StarSpan. So first:<br>> > ><br>> > > Convert to linestrings:<br>> > > INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing(<br>> > > ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom))))<br>> > > AS the_geom FROM temp2_polys;<br>> > ><br>> > > This produced 1768 linestring records. Attempting to node the<br>> > > linestrings: INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom)<br>> > > AS the_geom FROM temp3_lines;<br>> > ><br>> > > Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz<br>> > > Windows XP, Postgres 8.3.3, PostGIS 1.3.3).<br>> > ><br>> > > I dumped the temp3_lines table into a shapefile and asked OpenJump to<br>> > > node AND polygonize. That took 24 seconds.<br>> > ><br>> > > Since the above data is a small sub-set of my 1.2 million polygons,<br>> > > OpenJump is not really a solution for cleaning the data all at once. <br>> > > Looks like some scripting is in order...<br>> > ><br>> > > Brent<br>> > ><br>> > > Brent Fraser wrote:<br>> > >> Regina,<br>> > >><br>> > >> The "SELECT MAX..." query didn't work on my sub-set of 12800 polygons.<br>> > >> It created 12643 polygons some of which overlap (I expected more, not<br>> > >> less, than the original).<br>> > >><br>> > >> I may try converting to linestrings, creating one "minimum bounding<br>> > >> rectangle" for the entire dataset, then doing an intersect of the<br>> > >> lines with the MBR. In my case this would be ok as there are not<br>> > >> attributes on the polygons yet.<br>> > >><br>> > >> Thanks!<br>> > >> Brent<br>> > >><br>> > >> Obe, Regina wrote:<br>> > >>> Brent,<br>> > >>><br>> > >>> I guess it really depends on how exactly you want to achieve<br>> > >>> non-overlapping.<br>> > >>> If for example you are basing it on some sort of attribute and all<br>> > >>> your overlapping polygons are valid<br>> > >>><br>> > >>> Then a simple<br>> > >>><br>> > >>> SELECT ST_Union(the_geom) As newgeom, field1 FROM sometable GROUP BY<br>> > >>> field1<br>> > >>><br>> > >>> I think will guarantee non-overlapping polygons because as part of<br>> > >>> the process of ST_Union - it would irradicate the overlapping regions<br>> > >>> to just create one. That is part of the reason why its so much<br>> > >>> slower than ST_Collect for example.<br>> > >>><br>> > >>> For your exact case below - you would union all the overlapping<br>> > >>> polygons together which could be really slow depending on how many<br>> > >>> overlap. The query I would write to achieve that would be something<br>> > >>> like this<br>> > >>><br>> > >>> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom FROM<br>> > >>> poly a GROUP BY (SELECT MAX(r.gid) FROM poly r<br>> > >>> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));<br>> > >>><br>> > >>> Hope that helps,<br>> > >>> Regina<br>> > >>><br>> > >>> ---------------------------------------------------------------------<br>> > >>> ---<br>> > >>> *From:* postgis-users-bounces@postgis.refractions.net on behalf of<br>> > >>> Brent Fraser<br>> > >>> *Sent:* Fri 7/11/2008 12:14 PM<br>> > >>> *To:* PostGIS Users Discussion<br>> > >>> *Subject:* Re: [postgis-users] Transform overlapping polygons to<br>> > >>> non-overlapping?<br>> > >>><br>> > >>> To All,<br>> > >>><br>> > >>> There doesn't seem to be an obvious answer to the problem given<br>> > >>> below (aka cleaning polygons, creating planar polygons, etc). I did<br>> > >>> see a note on the PostGIS wiki wishlist to "Add a geometry cleaner".<br>> > >>> There is also a suggestion to convert to linestrings, node, then<br>> > >>> polygonize (while that may work for a small set of polygons, I've got<br>> > >>> 1.1 million to clean). JTS, Geos, etc will likely fail due to the<br>> > >>> large number of polygons so I'll need a different approach.<br>> > >>><br>> > >>> I'm considering writing some code to iterate through my table of<br>> > >>> polygons, cleaning a small subset at a time. I think using PostGIS<br>> > >>> for the geometry storage and spatial query/selection makes sense.<br>> > >>> Any suggestions on which API to use?<br>> > >>> GDAL's OGR<br>> > >>> PostgreSQL's libpq<br>> > >>> other?<br>> > >>><br>> > >>> Thanks!<br>> > >>> Brent Fraser<br>> > >>><br>> > >>> Brent Fraser wrote:<br>> > >>>> PostGIS'ers,<br>> > >>>><br>> > >>>> I've got a table of overlapping polygons. How can I make it a<br>> > >>>> table of non-overlapping polygons?<br>> > >>>><br>> > >>>> For example, if table "polys2" contains two polygons A1 and B1<br>> > >>>> which overlap. I'd like to create table "polys3" with polygons A2,<br>> > >>>> B2, C2, where C2 is the overlap region of A1 and B1, and A2 = A1 -<br>> > >>>> C2, and B2 =<br>> > >>>> B1 - C2.<br>> > >>>><br>> > >>>> Looking at the overlay operations in the JTS doc it looks like<br>> > >>>> doing an Intersection (to get only the overlapping area) then<br>> > >>>> adding the Symmetric Difference (to get the non-overlapping areas)<br>> > ><br>> > > might work.<br>> > ><br>> > >>>> Am I on the right track or is there an easier way (since all the<br>> > >>>> polygons are in one table)?<br>> > >>>><br>> > >>>> Thanks!<br>> > >>>> Brent Fraser<br>> > >>>> _______________________________________________<br>> > >>>> postgis-users mailing list<br>> > >>>> postgis-users@postgis.refractions.net<br>> > >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > >>><br>> > >>> _______________________________________________<br>> > >>> postgis-users mailing list<br>> > >>> postgis-users@postgis.refractions.net<br>> > >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > >>><br>> > >>> ---------------------------------------------------------------------<br>> > >>> ---<br>> > >>><br>> > >>> * The substance of this message, including any attachments, may be<br>> > >>> confidential, legally privileged and/or exempt from disclosure<br>> > >>> pursuant to Massachusetts law. It is intended solely for the<br>> > >>> addressee. If you received this in error, please contact the sender<br>> > >>> and delete the material from any computer. *<br>> > >>><br>> > >>> ---------------------------------------------------------------------<br>> > >>> ---<br>> > >>><br>> > >>> * Help make the earth a greener place. If at all possible resist<br>> > >>> printing this email and join us in saving paper. *<br>> > >>><br>> > >>> * *<br>> > >>><br>> > >>> * *<br>> > >>><br>> > >>><br>> > >>> ---------------------------------------------------------------------<br>> > >>> ---<br>> > >>><br>> > >>> _______________________________________________<br>> > >>> postgis-users mailing list<br>> > >>> postgis-users@postgis.refractions.net<br>> > >>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > >><br>> > >> _______________________________________________<br>> > >> postgis-users mailing list<br>> > >> postgis-users@postgis.refractions.net<br>> > >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > ><br>> > > _______________________________________________<br>> > > postgis-users mailing list<br>> > > postgis-users@postgis.refractions.net<br>> > > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > ><br>> > ><br>> > > _______________________________________________<br>> > > postgis-users mailing list<br>> > > postgis-users@postgis.refractions.net<br>> > > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> > ><br>> > ><br>> > > _______________________________________________<br>> > > postgis-users mailing list<br>> > > postgis-users@postgis.refractions.net<br>> > > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> ><br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > postgis-users@postgis.refractions.net<br>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> <br>> -- <br>> Dylan Beaudette<br>> Soil Resource Laboratory<br>> http://casoilresource.lawr.ucdavis.edu/<br>> University of California at Davis<br>> 530.754.7341<br>> <br>> <br>> ------------------------------<br>> <br>> Message: 18<br>> Date: Wed, 16 Jul 2008 12:20:23 -0600<br>> From: Brent Fraser <bfraser@geoanalytic.com><br>> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: dylan.beaudette@gmail.com<br>> Cc: postgis-users@postgis.refractions.net<br>> Message-ID: <487E3BE7.4030308@geoanalytic.com><br>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>> <br>> Dylan,<br>> <br>> Yes! Clean polygons! I gravitated towards PostGIS due to my large number of polygons (1.2 million), and I'd like a solution that is scalable beyond that number. GRASS may be the/an answer; I'll give it a try.<br>> <br>> Thanks!<br>> Brent<br>> <br>> Dylan Beaudette wrote:<br>> > On Wednesday 16 July 2008, Brent Fraser wrote:<br>> >> Regina,<br>> >><br>> >> I'm not convinced ST_Union is the way to go (using ST_Overlaps OR<br>> >> ST_Intersects as a condition). Basically I want to iterate over the<br>> >> collection (recursively?) clipping one polygon to another until I'm left<br>> >> with no overlapping (or intersecting) polygons (planar topology). This is<br>> >> sightly more complicated than the way I originally posed the problem (I<br>> >> wanted to created slivers from the overlapping areas to get planar<br>> >> topology).<br>> >><br>> >> A little background:<br>> >><br>> >> My polygons represent a classification of vegetation of a large area of<br>> >> interest. In theory any point in the area of interest must fall in one and<br>> >> only one polygon. Due to an artifact of my image segmentation process, my<br>> >> polygons currently have slight overlap which I need to "dissolve" (and I<br>> >> don't care which polygon the overlap sliver gets dissolved into).<br>> >><br>> >> Thanks!<br>> >> Brent.<br>> > <br>> > It really sounds like you need to perform some "cleaning" operations in a <br>> > topologically-aware GIS. Check out the v.clean module in GRASS.<br>> > <br>> > Cheers,<br>> > <br>> > Dylan<br>> <br>> <br>> ------------------------------<br>> <br>> Message: 19<br>> Date: Wed, 16 Jul 2008 11:24:38 -0700<br>> From: Kevin Neufeld <kneufeld@refractions.net><br>> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: PostGIS Users Discussion <postgis-users@postgis.refractions.net><br>> Message-ID: <487E3CE6.4040505@refractions.net><br>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed<br>> <br>> Ah, another reason to have topology finished in PostGIS. If only, eh?<br>> <br>> Brent, what if you created a plpgsql script that simply iterated through <br>> a table of source polygons and slowly inserted them one at a time into a <br>> target table by:<br>> <br>> foreach poly in source table<br>> <br>> 1. move all bounding box overlaps in target table with current poly <br>> into a temp table.<br>> 2. take all polygons from your temp table and union the current poly <br>> using the techniques described on the wiki and what Regina suggested.<br>> 3. insert the individual polygons from the overlay back into the <br>> target table using ST_Dump.<br>> <br>> end foreach.<br>> <br>> It might take a while, but in the end, you would have a single <br>> topologically correct (non-overlapping) polygonal table.<br>> <br>> You may want to insert the geometries ordered by some x,y grid so that <br>> your working area will more likely be cached in memory.<br>> <br>> Also, you may want to perform vacuum once in while on your target table. <br>> It could bloat really quickly. So don't iterate through all the <br>> source polygons all at once.<br>> <br>> Cheers,<br>> Kevin<br>> <br>> <br>> Brent Fraser wrote:<br>> > Regina,<br>> > <br>> > I'm not convinced ST_Union is the way to go (using ST_Overlaps OR <br>> > ST_Intersects as a condition). Basically I want to iterate over the <br>> > collection (recursively?) clipping one polygon to another until I'm left <br>> > with no overlapping (or intersecting) polygons (planar topology). This <br>> > is sightly more complicated than the way I originally posed the problem <br>> > (I wanted to created slivers from the overlapping areas to get planar <br>> > topology).<br>> > <br>> > A little background:<br>> > <br>> > My polygons represent a classification of vegetation of a large area <br>> > of interest. In theory any point in the area of interest must fall in <br>> > one and only one polygon. Due to an artifact of my image segmentation <br>> > process, my polygons currently have slight overlap which I need to <br>> > "dissolve" (and I don't care which polygon the overlap sliver gets <br>> > dissolved into).<br>> > <br>> > Thanks!<br>> > Brent.<br>> > Paragon Corporation wrote:<br>> >> One more question - you sure you want ST_Overlaps and not ST_Intersects.<br>> >> If one geometry sits completely inside another, it is not considered to<br>> >> overlap, but they do intersect.<br>> >> -----Original Message-----<br>> >> From: postgis-users-bounces@postgis.refractions.net<br>> >> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of <br>> >> Paragon<br>> >> Corporation<br>> >> Sent: Wednesday, July 16, 2008 12:41 PM<br>> >> To: 'PostGIS Users Discussion'<br>> >> Subject: RE: [postgis-users] Transform overlapping polygons to<br>> >> non-overlapping?<br>> >><br>> >> Brent,<br>> >><br>> >> INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing( <br>> >> ST_GeometryN(the_geom, generate_series(1,<br>> >> ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys;<br>> >><br>> >> Can be done more efficiently using ST_Dump<br>> >><br>> >> INSERT INTO temp3_lines (the_geom) SELECT <br>> >> ST_ExteriorRing((ST_Dump(the_geom)).geom) AS the_geom FROM<br>> >> temp2_polys;<br>> >><br>> >><br>> >> Regarding the unioning I mentioned - I thought about more why what I<br>> >> proposed had still overlapping polygons and I realized its because the<br>> >> grouping I proposed needs to be A recursive query (which means you'd <br>> >> need to<br>> >> wrap it in an sql or plpgsql<br>> >> function) since as it stands it would only find the first root <br>> >> overlaps and<br>> >> not the A overlap B overlap C (e.g. c would not be in the same <br>> >> grouping and<br>> >> A,B if it doesn't also overlap with A) .<br>> >><br>> >> So two ways<br>> >> 1) Write recursive query (using a plpgsql or sql helper function) - <br>> >> which I<br>> >> haven't given much thought to the most efficient way of doing that<br>> >><br>> >> Or<br>> >><br>> >> 2) Repeat the union thing I mentioned over and over again until you <br>> >> have a<br>> >> set that has no more overlapping polygons.<br>> >><br>> >> Then you do a ST_Dump to get back individual polygons.<br>> >><br>> >> Hope that helps,<br>> >> Regina<br>> >><br>> >><br>> >> -----Original Message-----<br>> >> From: postgis-users-bounces@postgis.refractions.net<br>> >> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Brent<br>> >> Fraser<br>> >> Sent: Wednesday, July 16, 2008 12:13 PM<br>> >> To: PostGIS Users Discussion<br>> >> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> >> non-overlapping?<br>> >><br>> >> To all,<br>> >><br>> >> My quest for non-overlapping polygons continues:<br>> >><br>> >> I started with a table (temp_polys) of 3253 polygons (with some overlap)<br>> >> with a "class" attribute.<br>> >><br>> >> To get rid of overlapping polys with the same class value:<br>> >> CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from<br>> >> temp_polys GROUP BY class;<br>> >><br>> >> This created a table of 32 multi-polygons (grouped by class). I still <br>> >> have<br>> >> to remove the overlap between polygons with different class values, so my<br>> >> plan is to convert to linestrings, node the linestrings, polygonize, and<br>> >> (re)assign the class value using StarSpan. So first:<br>> >><br>> >> Convert to linestrings:<br>> >> INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing(<br>> >> ST_GeometryN(the_geom, generate_series(1, <br>> >> ST_NumGeometries(the_geom)))) AS<br>> >> the_geom FROM temp2_polys;<br>> >><br>> >> This produced 1768 linestring records. Attempting to node the <br>> >> linestrings:<br>> >> INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom) AS<br>> >> the_geom FROM temp3_lines;<br>> >><br>> >> Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz <br>> >> Windows<br>> >> XP, Postgres 8.3.3, PostGIS 1.3.3).<br>> >><br>> >> I dumped the temp3_lines table into a shapefile and asked OpenJump to <br>> >> node<br>> >> AND polygonize. That took 24 seconds.<br>> >><br>> >> Since the above data is a small sub-set of my 1.2 million polygons, <br>> >> OpenJump<br>> >> is not really a solution for cleaning the data all at once. Looks <br>> >> like some<br>> >> scripting is in order...<br>> >><br>> >> Brent<br>> >><br>> >><br>> >> Brent Fraser wrote:<br>> >>> Regina,<br>> >>><br>> >>> The "SELECT MAX..." query didn't work on my sub-set of 12800 <br>> >>> polygons. It created 12643 polygons some of which overlap (I <br>> >>> expected more, not less, than the original).<br>> >>><br>> >>> I may try converting to linestrings, creating one "minimum bounding <br>> >>> rectangle" for the entire dataset, then doing an intersect of the <br>> >>> lines with the MBR. In my case this would be ok as there are not <br>> >>> attributes on the polygons yet.<br>> >>><br>> >>> Thanks!<br>> >>> Brent<br>> >>><br>> >>> Obe, Regina wrote:<br>> >>>> Brent,<br>> >>>> <br>> >>>> I guess it really depends on how exactly you want to achieve <br>> >>>> non-overlapping.<br>> >>>> If for example you are basing it on some sort of attribute and all <br>> >>>> your overlapping polygons are valid<br>> >>>> <br>> >>>> Then a simple<br>> >>>> <br>> >>>> SELECT ST_Union(the_geom) As newgeom, field1 FROM sometable GROUP BY<br>> >>>> field1<br>> >>>> <br>> >>>> I think will guarantee non-overlapping polygons because as part of <br>> >>>> the process of ST_Union - it would irradicate the overlapping <br>> >>>> regions to just create one. That is part of the reason why its so <br>> >>>> much slower than ST_Collect for example.<br>> >>>> <br>> >>>> For your exact case below - you would union all the overlapping <br>> >>>> polygons together which could be really slow depending on how many <br>> >>>> overlap. The query I would write to achieve that would be something <br>> >>>> like this<br>> >>>> <br>> >>>> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom FROM <br>> >>>> poly a GROUP BY (SELECT MAX(r.gid) FROM poly r<br>> >>>> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));<br>> >>>> <br>> >>>> Hope that helps,<br>> >>>> Regina<br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>> *From:* postgis-users-bounces@postgis.refractions.net on behalf of <br>> >>>> Brent Fraser<br>> >>>> *Sent:* Fri 7/11/2008 12:14 PM<br>> >>>> *To:* PostGIS Users Discussion<br>> >>>> *Subject:* Re: [postgis-users] Transform overlapping polygons to <br>> >>>> non-overlapping?<br>> >>>><br>> >>>> To All,<br>> >>>><br>> >>>> There doesn't seem to be an obvious answer to the problem given <br>> >>>> below (aka cleaning polygons, creating planar polygons, etc). I did <br>> >>>> see a note on the PostGIS wiki wishlist to "Add a geometry cleaner".<br>> >>>> There is also a suggestion to convert to linestrings, node, then <br>> >>>> polygonize (while that may work for a small set of polygons, I've got<br>> >>>> 1.1 million to clean). JTS, Geos, etc will likely fail due to the <br>> >>>> large number of polygons so I'll need a different approach.<br>> >>>><br>> >>>> I'm considering writing some code to iterate through my table of <br>> >>>> polygons, cleaning a small subset at a time. I think using PostGIS <br>> >>>> for the geometry storage and spatial query/selection makes sense.<br>> >>>> Any suggestions on which API to use?<br>> >>>> GDAL's OGR<br>> >>>> PostgreSQL's libpq<br>> >>>> other?<br>> >>>><br>> >>>> Thanks!<br>> >>>> Brent Fraser<br>> >>>><br>> >>>> Brent Fraser wrote:<br>> >>>>> PostGIS'ers,<br>> >>>>><br>> >>>>> I've got a table of overlapping polygons. How can I make it a <br>> >>>>> table of non-overlapping polygons?<br>> >>>>><br>> >>>>> For example, if table "polys2" contains two polygons A1 and B1 <br>> >>>>> which overlap. I'd like to create table "polys3" with polygons <br>> >>>>> A2, B2, C2, where C2 is the overlap region of A1 and B1, and A2 = <br>> >>>>> A1 - C2, and B2 =<br>> >>>>> B1 - C2.<br>> >>>>><br>> >>>>> Looking at the overlay operations in the JTS doc it looks like <br>> >>>>> doing an Intersection (to get only the overlapping area) then <br>> >>>>> adding the Symmetric Difference (to get the non-overlapping areas)<br>> >> might work.<br>> >>>>> Am I on the right track or is there an easier way (since all the <br>> >>>>> polygons are in one table)?<br>> >>>>><br>> >>>>> Thanks!<br>> >>>>> Brent Fraser<br>> >>>>> _______________________________________________<br>> >>>>> postgis-users mailing list<br>> >>>>> postgis-users@postgis.refractions.net<br>> >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>>>><br>> >>>> _______________________________________________<br>> >>>> postgis-users mailing list<br>> >>>> postgis-users@postgis.refractions.net<br>> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>><br>> >>>> * The substance of this message, including any attachments, may be <br>> >>>> confidential, legally privileged and/or exempt from disclosure <br>> >>>> pursuant to Massachusetts law. It is intended solely for the <br>> >>>> addressee. If you received this in error, please contact the sender <br>> >>>> and delete the material from any computer. *<br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>><br>> >>>> * Help make the earth a greener place. If at all possible resist <br>> >>>> printing this email and join us in saving paper. *<br>> >>>><br>> >>>> * *<br>> >>>><br>> >>>> * *<br>> >>>><br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>><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>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> > _______________________________________________<br>> > postgis-users mailing list<br>> > postgis-users@postgis.refractions.net<br>> > http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> ------------------------------<br>> <br>> Message: 20<br>> Date: Wed, 16 Jul 2008 14:57:15 -0400<br>> From: "Obe, Regina" <robe.dnd@cityofboston.gov><br>> Subject: RE: [postgis-users] Transform overlapping polygons to<br>> non-overlapping?<br>> To: "PostGIS Users Discussion"<br>> <postgis-users@postgis.refractions.net>, "PostGIS Users Discussion"<br>> <postgis-users@postgis.refractions.net><br>> Message-ID:<br>> <53F9CF533E1AA14EA1F8C5C08ABC08D20197A130@ZDND.DND.boston.cob><br>> Content-Type: text/plain; charset="iso-8859-1"<br>> <br>> Brent,<br>> <br>> This might help reduce your problem set a bit. You don't care about the ones that don't overlap anything right. Hopefully there are a lot of those. So use the old LEFT JOIN trick<br>> <br>> Create new table<br>> <br>> INSERT INTO newtable(gid,the_geom)<br>> SELECT a.gid, a.the_geom<br>> FROM oldtable a LEFT JOIN oldtable b ON (a.gid <> b.gid AND ST_Overlaps(a.the_geom, b.the_geom))<br>> WHERE b.gid IS NULL<br>> <br>> Next step create unique index on gid on new table<br>> <br>> DELETE FROM oldtable<br>> WHERE gid IN(SELECT gid FROM newtable)<br>> <br>> Now all you are left to work with are your polygons that overlap each other.<br>> <br>> With those follow Kevin's lead by creating an intermediary workspace table<br>> <br>> Hope that helps,<br>> Regina<br>> <br>> ________________________________<br>> <br>> From: postgis-users-bounces@postgis.refractions.net on behalf of Kevin Neufeld<br>> Sent: Wed 7/16/2008 2:24 PM<br>> To: PostGIS Users Discussion<br>> Subject: Re: [postgis-users] Transform overlapping polygons to non-overlapping?<br>> <br>> <br>> <br>> Ah, another reason to have topology finished in PostGIS. If only, eh?<br>> <br>> Brent, what if you created a plpgsql script that simply iterated through<br>> a table of source polygons and slowly inserted them one at a time into a<br>> target table by:<br>> <br>> foreach poly in source table<br>> <br>> 1. move all bounding box overlaps in target table with current poly<br>> into a temp table.<br>> 2. take all polygons from your temp table and union the current poly<br>> using the techniques described on the wiki and what Regina suggested.<br>> 3. insert the individual polygons from the overlay back into the<br>> target table using ST_Dump.<br>> <br>> end foreach.<br>> <br>> It might take a while, but in the end, you would have a single<br>> topologically correct (non-overlapping) polygonal table.<br>> <br>> You may want to insert the geometries ordered by some x,y grid so that<br>> your working area will more likely be cached in memory.<br>> <br>> Also, you may want to perform vacuum once in while on your target table.<br>> It could bloat really quickly. So don't iterate through all the<br>> source polygons all at once.<br>> <br>> Cheers,<br>> Kevin<br>> <br>> <br>> Brent Fraser wrote:<br>> > Regina,<br>> ><br>> > I'm not convinced ST_Union is the way to go (using ST_Overlaps OR<br>> > ST_Intersects as a condition). Basically I want to iterate over the<br>> > collection (recursively?) clipping one polygon to another until I'm left<br>> > with no overlapping (or intersecting) polygons (planar topology). This<br>> > is sightly more complicated than the way I originally posed the problem<br>> > (I wanted to created slivers from the overlapping areas to get planar<br>> > topology).<br>> ><br>> > A little background:<br>> ><br>> > My polygons represent a classification of vegetation of a large area<br>> > of interest. In theory any point in the area of interest must fall in<br>> > one and only one polygon. Due to an artifact of my image segmentation<br>> > process, my polygons currently have slight overlap which I need to<br>> > "dissolve" (and I don't care which polygon the overlap sliver gets<br>> > dissolved into).<br>> ><br>> > Thanks!<br>> > Brent.<br>> > Paragon Corporation wrote:<br>> >> One more question - you sure you want ST_Overlaps and not ST_Intersects.<br>> >> If one geometry sits completely inside another, it is not considered to<br>> >> overlap, but they do intersect.<br>> >> -----Original Message-----<br>> >> From: postgis-users-bounces@postgis.refractions.net<br>> >> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of<br>> >> Paragon<br>> >> Corporation<br>> >> Sent: Wednesday, July 16, 2008 12:41 PM<br>> >> To: 'PostGIS Users Discussion'<br>> >> Subject: RE: [postgis-users] Transform overlapping polygons to<br>> >> non-overlapping?<br>> >><br>> >> Brent,<br>> >><br>> >> INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing(<br>> >> ST_GeometryN(the_geom, generate_series(1,<br>> >> ST_NumGeometries(the_geom)))) AS the_geom FROM temp2_polys;<br>> >><br>> >> Can be done more efficiently using ST_Dump<br>> >><br>> >> INSERT INTO temp3_lines (the_geom) SELECT<br>> >> ST_ExteriorRing((ST_Dump(the_geom)).geom) AS the_geom FROM<br>> >> temp2_polys;<br>> >><br>> >><br>> >> Regarding the unioning I mentioned - I thought about more why what I<br>> >> proposed had still overlapping polygons and I realized its because the<br>> >> grouping I proposed needs to be A recursive query (which means you'd<br>> >> need to<br>> >> wrap it in an sql or plpgsql<br>> >> function) since as it stands it would only find the first root<br>> >> overlaps and<br>> >> not the A overlap B overlap C (e.g. c would not be in the same<br>> >> grouping and<br>> >> A,B if it doesn't also overlap with A) .<br>> >><br>> >> So two ways<br>> >> 1) Write recursive query (using a plpgsql or sql helper function) -<br>> >> which I<br>> >> haven't given much thought to the most efficient way of doing that<br>> >><br>> >> Or<br>> >><br>> >> 2) Repeat the union thing I mentioned over and over again until you<br>> >> have a<br>> >> set that has no more overlapping polygons.<br>> >><br>> >> Then you do a ST_Dump to get back individual polygons.<br>> >><br>> >> Hope that helps,<br>> >> Regina<br>> >><br>> >><br>> >> -----Original Message-----<br>> >> From: postgis-users-bounces@postgis.refractions.net<br>> >> [mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Brent<br>> >> Fraser<br>> >> Sent: Wednesday, July 16, 2008 12:13 PM<br>> >> To: PostGIS Users Discussion<br>> >> Subject: Re: [postgis-users] Transform overlapping polygons to<br>> >> non-overlapping?<br>> >><br>> >> To all,<br>> >><br>> >> My quest for non-overlapping polygons continues:<br>> >><br>> >> I started with a table (temp_polys) of 3253 polygons (with some overlap)<br>> >> with a "class" attribute.<br>> >><br>> >> To get rid of overlapping polys with the same class value:<br>> >> CREATE TABLE temp2_polys as SELECT class, ST_UNION(the_geom) from<br>> >> temp_polys GROUP BY class;<br>> >><br>> >> This created a table of 32 multi-polygons (grouped by class). I still<br>> >> have<br>> >> to remove the overlap between polygons with different class values, so my<br>> >> plan is to convert to linestrings, node the linestrings, polygonize, and<br>> >> (re)assign the class value using StarSpan. So first:<br>> >><br>> >> Convert to linestrings:<br>> >> INSERT INTO temp3_lines (the_geom) SELECT ST_ExteriorRing(<br>> >> ST_GeometryN(the_geom, generate_series(1,<br>> >> ST_NumGeometries(the_geom)))) AS<br>> >> the_geom FROM temp2_polys;<br>> >><br>> >> This produced 1768 linestring records. Attempting to node the<br>> >> linestrings:<br>> >> INSERT INTO temp4_lines (the_geom) SELECT St_Union(the_geom) AS<br>> >> the_geom FROM temp3_lines;<br>> >><br>> >> Yikes! This query ran for 4.5 hours and crashed Postgres (1.8 gHz<br>> >> Windows<br>> >> XP, Postgres 8.3.3, PostGIS 1.3.3).<br>> >><br>> >> I dumped the temp3_lines table into a shapefile and asked OpenJump to<br>> >> node<br>> >> AND polygonize. That took 24 seconds.<br>> >><br>> >> Since the above data is a small sub-set of my 1.2 million polygons,<br>> >> OpenJump<br>> >> is not really a solution for cleaning the data all at once. Looks<br>> >> like some<br>> >> scripting is in order...<br>> >><br>> >> Brent<br>> >><br>> >><br>> >> Brent Fraser wrote:<br>> >>> Regina,<br>> >>><br>> >>> The "SELECT MAX..." query didn't work on my sub-set of 12800<br>> >>> polygons. It created 12643 polygons some of which overlap (I<br>> >>> expected more, not less, than the original).<br>> >>><br>> >>> I may try converting to linestrings, creating one "minimum bounding<br>> >>> rectangle" for the entire dataset, then doing an intersect of the<br>> >>> lines with the MBR. In my case this would be ok as there are not<br>> >>> attributes on the polygons yet.<br>> >>><br>> >>> Thanks!<br>> >>> Brent<br>> >>><br>> >>> Obe, Regina wrote:<br>> >>>> Brent,<br>> >>>> <br>> >>>> I guess it really depends on how exactly you want to achieve<br>> >>>> non-overlapping.<br>> >>>> If for example you are basing it on some sort of attribute and all<br>> >>>> your overlapping polygons are valid<br>> >>>> <br>> >>>> Then a simple<br>> >>>> <br>> >>>> SELECT ST_Union(the_geom) As newgeom, field1 FROM sometable GROUP BY<br>> >>>> field1<br>> >>>> <br>> >>>> I think will guarantee non-overlapping polygons because as part of<br>> >>>> the process of ST_Union - it would irradicate the overlapping<br>> >>>> regions to just create one. That is part of the reason why its so<br>> >>>> much slower than ST_Collect for example.<br>> >>>> <br>> >>>> For your exact case below - you would union all the overlapping<br>> >>>> polygons together which could be really slow depending on how many<br>> >>>> overlap. The query I would write to achieve that would be something<br>> >>>> like this<br>> >>>> <br>> >>>> SELECT MAX(a.gid) As newgid, ST_Union(a.the_geom) As the_geom FROM<br>> >>>> poly a GROUP BY (SELECT MAX(r.gid) FROM poly r<br>> >>>> WHERE (a.gid = r.gid OR ST_Overlaps(r.the_geom, a.the_geom)));<br>> >>>> <br>> >>>> Hope that helps,<br>> >>>> Regina<br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>> *From:* postgis-users-bounces@postgis.refractions.net on behalf of<br>> >>>> Brent Fraser<br>> >>>> *Sent:* Fri 7/11/2008 12:14 PM<br>> >>>> *To:* PostGIS Users Discussion<br>> >>>> *Subject:* Re: [postgis-users] Transform overlapping polygons to<br>> >>>> non-overlapping?<br>> >>>><br>> >>>> To All,<br>> >>>><br>> >>>> There doesn't seem to be an obvious answer to the problem given<br>> >>>> below (aka cleaning polygons, creating planar polygons, etc). I did<br>> >>>> see a note on the PostGIS wiki wishlist to "Add a geometry cleaner".<br>> >>>> There is also a suggestion to convert to linestrings, node, then<br>> >>>> polygonize (while that may work for a small set of polygons, I've got<br>> >>>> 1.1 million to clean). JTS, Geos, etc will likely fail due to the<br>> >>>> large number of polygons so I'll need a different approach.<br>> >>>><br>> >>>> I'm considering writing some code to iterate through my table of<br>> >>>> polygons, cleaning a small subset at a time. I think using PostGIS<br>> >>>> for the geometry storage and spatial query/selection makes sense.<br>> >>>> Any suggestions on which API to use?<br>> >>>> GDAL's OGR<br>> >>>> PostgreSQL's libpq<br>> >>>> other?<br>> >>>><br>> >>>> Thanks!<br>> >>>> Brent Fraser<br>> >>>><br>> >>>> Brent Fraser wrote:<br>> >>>>> PostGIS'ers,<br>> >>>>><br>> >>>>> I've got a table of overlapping polygons. How can I make it a<br>> >>>>> table of non-overlapping polygons?<br>> >>>>><br>> >>>>> For example, if table "polys2" contains two polygons A1 and B1<br>> >>>>> which overlap. I'd like to create table "polys3" with polygons<br>> >>>>> A2, B2, C2, where C2 is the overlap region of A1 and B1, and A2 =<br>> >>>>> A1 - C2, and B2 =<br>> >>>>> B1 - C2.<br>> >>>>><br>> >>>>> Looking at the overlay operations in the JTS doc it looks like<br>> >>>>> doing an Intersection (to get only the overlapping area) then<br>> >>>>> adding the Symmetric Difference (to get the non-overlapping areas)<br>> >> might work.<br>> >>>>> Am I on the right track or is there an easier way (since all the<br>> >>>>> polygons are in one table)?<br>> >>>>><br>> >>>>> Thanks!<br>> >>>>> Brent Fraser<br>> >>>>> _______________________________________________<br>> >>>>> postgis-users mailing list<br>> >>>>> postgis-users@postgis.refractions.net<br>> >>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>>>><br>> >>>> _______________________________________________<br>> >>>> postgis-users mailing list<br>> >>>> postgis-users@postgis.refractions.net<br>> >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>><br>> >>>> * The substance of this message, including any attachments, may be<br>> >>>> confidential, legally privileged and/or exempt from disclosure<br>> >>>> pursuant to Massachusetts law. It is intended solely for the<br>> >>>> addressee. If you received this in error, please contact the sender<br>> >>>> and delete the material from any computer. *<br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>><br>> >>>> * Help make the earth a greener place. If at all possible resist<br>> >>>> printing this email and join us in saving paper. *<br>> >>>><br>> >>>> * *<br>> >>>><br>> >>>> * *<br>> >>>><br>> >>>><br>> >>>> ---------------------------------------------------------------------<br>> >>>> ---<br>> >>>><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>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><br>> >><br>> >> _______________________________________________<br>> >> postgis-users mailing list<br>> >> postgis-users@postgis.refractions.net<br>> >> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> >><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>> <br>> -------------- next part --------------<br>> An HTML attachment was scrubbed...<br>> URL: http://lists.refractions.net/pipermail/postgis-users/attachments/20080716/a94b9d50/attachment-0001.html<br>> <br>> ------------------------------<br>> <br>> _______________________________________________<br>> postgis-users mailing list<br>> postgis-users@postgis.refractions.net<br>> http://postgis.refractions.net/mailman/listinfo/postgis-users<br>> <br>> <br>> End of postgis-users Digest, Vol 69, Issue 16<br>> *********************************************<br><br /><hr />Explore the seven wonders of the world <a href='http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE' target='_new'>Learn more!</a></body>
</html>