[postgis-users] UNION
Obe, Regina
robe.dnd at cityofboston.gov
Wed Sep 19 05:17:55 PDT 2007
Kevin,
When was st_polygonize introduced? The docs say it was introduced in
1.0.0 RC1? I see it in my newere 1.3 databases. I don't see it in my
1.2.1 corrupted upgrade to 1.3. I suspect this is because of the long
standing issue of the bug in upgrading aggregates with soft upgrade and
I have had a db that has existed pre 1.0 and I forget the last time I
did a complete upgrade on it.
Thanks,
Regina
________________________________
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Kevin Neufeld
Sent: Tuesday, September 18, 2007 3:43 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] UNION
It sounds like you are after performing an overlay operation on two
polygonal datasets.
I would do something like:
- extract all lines from your polygons
- node the linework
- re-polygonize the noded lines
- transfer attributes from the original dataset to the newly formed
polygons.
http://postgis.refractions.net/support/wiki/index.php?ExamplesOverlayTab
les
Hope this helps,
Kevin
-------------
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7
Phone: (250) 383-3022
Email: kneufeld at refractions.net
RAVI KUMAR wrote:
Hi,
a and b, are multiple tables derived from -2- shape files saved
as POST-GIS data.
Slide 1:
'a' this data as in the slide show has attributes, This is a
square with polygons.
'b' as in the slide show is made up of 2 polygons with
attributes which overlap 'a'.
Slide 2: a and b are in UNION with the syntax below. But this
is not the desired result. There are polygons overlapping other
polygons, one below the other.
Slide 3:
'abc' is a union of a and b, with desired result
My further analysis with post GIS awaits HELP.
Cheers
Ravi Kumar
"Obe, Regina" <robe.dnd at cityofboston.gov>
<mailto:robe.dnd at cityofboston.gov> wrote:
Ravi,
Is the graphic one record or multiple records? If
multiple records - I'm afraid you may be asking 2 conflicting questions
depending on your dataset so I'm not sure there is anything that can
remedy that aside from treating them as two separate questions.
I'm also afraid you are getting into territory I'm very
weak. You may want to investigate use of st_boundary (which will give
you a multilinestring of a polygon/multipolygon boundary and then apply
buildarea to that).
The below should get rid of some redundant overlapping
polygons, but probably won't completely satisfy what you want to do.
I'm also thinking you may want to be using intersection instead of
geomunion for the inner part. But again depends what you are trying to
answer.
INSERT INTO abc(code, info, the_geom)
SELECT newtb.code, newtb.info <http://newtb.info/> ,
multi(buffer(geomunion(distinct newtb.cgeom), 0.0)) as thenewgeom
FROM
( SELECT a.code, b.info <http://b.info/> ,
intersection(a.geometry, b.geometry) as cgeom
FROM a
INNER JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
UNION ALL
SELECT a.code, null As info, a.geometry as cgeom
FROM a
LEFT JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
WHERE b.geometry IS NULL
UNION ALL
SELECT null as code, b.info <http://b.info/> ,
b.geometry as cgeom
FROM b LEFT JOIN a ON a.geometry &&
b.geometry AND intersects(a.geometry, b.geometry)
WHERE a.geometry IS NULL
) AS newtb
GROUP BY newtb.code, newtb.info <http://newtb.info/>
Hope that helps,
Regina
________________________________
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of RAVI
KUMAR
Sent: Monday, September 17, 2007 9:04 AM
To: postgis
Subject: [postgis-users] UNION
Hi Regina,
giving the link to show actual how the UNION of polygons
is not working as per expectation.
Please see the clips in the link.
CREATE TABLE abc(code smallint, info smallint) with
oids;
SELECT AddGeometryColumn('public', 'abc', 'the_geom',
4326, 'MULTIPOLYGON', 2);
--The insert
INSERT INTO abc(code, info, the_geom)
SELECT newtb.code, newtb.info <http://newtb.info/> ,
geomunion(newtb.cgeom) as thenewgeom
FROM
( SELECT a.code, b.info <http://b.info/> ,
geomunion(a.geometry, b.geometry) as cgeom
FROM a
INNER JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
UNION ALL
SELECT a.code, null As info, a.geometry as cgeom
FROM a
LEFT JOIN b ON a.geometry && b.geometry AND
intersects(a.geometry, b.geometry)
WHERE b.geometry IS NULL
UNION ALL
SELECT null as code, b.info <http://b.info/> ,
b.geometry as cgeom
FROM b LEFT JOIN a ON a.geometry &&
b.geometry AND intersects(a.geometry, b.geometry)
WHERE a.geometry IS NULL
) AS newtb
GROUP BY newtb.code, newtb.info <http://newtb.info/>
May be I should be using different syntax for such a
result.
http://www.kodakgallery.com/I.jsp?c=cjns10h3.8ovw4dsn&x=0&y=w3kbw0
<http://www.kodakgallery.com/I.jsp?c=cjns10h3.8ovw4dsn&x=0&y=w3kbw0>
Ravi Kumar
________________________________
Catch up on fall's hot new shows
<http://us.rd.yahoo.com/tv/mail/tagline/falltv/evt=47093/*http://tv.yaho
o.com/collections/3658%20> on Yahoo! TV. Watch previews, get listings,
and more!
________________________________
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.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
________________________________
Need a vacation? Get great deals to amazing places
<http://us.rd.yahoo.com/evt=48256/*http://travel.yahoo.com/;_ylc=X3oDMTF
hN2hucjlpBF9TAzk3NDA3NTg5BHBvcwM1BHNlYwNncm91cHMEc2xrA2VtYWlsLW5jbQ-->
on Yahoo! Travel.
________________________________
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070919/df466a59/attachment.html>
More information about the postgis-users
mailing list