[postgis-users] Lines to Polygons

[Serban Constantinescu]

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.