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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
