[postgis-users] Lines to Polygons

[Paragon Corporation]

Serban,
Have you tried ST_Union and skip ST_BuildArea?

Leo 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Serban
Constantinescu
Sent: Saturday, June 06, 2009 4:09 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Lines to Polygons

I have 2 tables for maintaining and editing boundaries:

TABLE 1: counties_ln (LINE)
gid (serial, pk)
countyid_left (integer)
county_id_right (integer)
county_name_left
county_name_right
...other fields...
geom (geometry)


TABLE 2: counties_py (POLYGON)
gid (serial, pk)
county_id
county_name
...other fields...
geom (geometry)


and a 3rd one which maintains polygon attributes

TABLE 3: counties_pt (POINT)
gid
county_id
county_name
...other fields...
geom (geometry)

I do all my edits in the line layer (counties_ln), using Qgis connected to
the Postgis, such that I can maintain correct topology, but once in a while
I need to recompose the polygon layer
(counties_py) from the LINE layer.

I tried to use ST_BuildArea and ST_Collect but I end up with one single
county that is made of 2 simple polygons, which messes up my results.

The SQL is as follows:

-- clear all records
DELETE FROM counties_py WHERE gid > 0;

INSERT INTO counties_py (county_id, county_name, geom)

SELECT county_id, county_name, ST_BuildArea(ST_Collect(counties_ln.geom))
FROM counties_pt, counties_ln
WHERE (county_id_left = county_id OR county_id_right = county_id) GROUP BY
counties_pt.county_id, counties_pt.county_name;

If I do this, I end up with everything OK, except for 1 row, which contains
a null geometry, because the corresponding county is comprised of 2
polygons, so the resulting geometry should be a multupolygon feature.

I tried using ST_Dump as sugested in other posts to generate 2 polygons (2
rows with simple geometries), but after many trials, I can't exactly figure
out where and how to add the ST_Dump function to get the desired result.

Is my logic wrong or can somebody point me in the right direction, please.

If it's not clear from the above, I'll try to add further details.

I am new to postgis and sql, so it's hard in the beginning.

Thanks in advance for your help.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users