[postgis-users] polygonize line strings

[Michael Fuhr]

On Fri, Mar 03, 2006 at 03:55:00AM -0700, Michael Fuhr wrote:
> On Fri, Mar 03, 2006 at 09:58:30AM +0100, Bart van den Eijnden (OSGIS) wrote:
> > Can this data be polygonized with PostGIS? If so how can this be done?
> 
> Is the following close to what you're looking for?  If not then
> maybe I've misunderstood.
> 
> SELECT perceel, polygonize_garray(accum(geom)) AS geom
> FROM (
>   SELECT perceel_li AS perceel, geom FROM foo
>   UNION ALL
>   SELECT perceel_re AS perceel, geom FROM foo
> ) AS s
> GROUP BY perceel;

Or perhaps

SELECT perceel, polygonize(geom) AS geom FROM ....

The main idea, if I understand what you're after, is the union.
Below is a complete example using the following geometries (best
viewed with a fixed-width font):

15 +-------+
   |       |
10 |   A   +-------+
   |       |       |
 5 +-------+   C   |
   |   B   |       |
 0 +-------+-------+
   0       10      20

CREATE TABLE foo (
    id    serial PRIMARY KEY,
    p_li  char,
    p_re  char
);

SELECT AddGeometryColumn('foo', 'geom', -1, 'LINESTRING', 2);

INSERT INTO foo (p_li, p_re, geom) VALUES (NULL, 'A', 'LINESTRING(0 15,10 15)');
INSERT INTO foo (p_li, p_re, geom) VALUES (NULL, 'A', 'LINESTRING(10 15,10 10)');
INSERT INTO foo (p_li, p_re, geom) VALUES ('C', 'A', 'LINESTRING(10 10,10 5)');
INSERT INTO foo (p_li, p_re, geom) VALUES ('B', 'A', 'LINESTRING(10 5,0 5)');
INSERT INTO foo (p_li, p_re, geom) VALUES ('A', NULL, 'LINESTRING(0 15,0 5)');
INSERT INTO foo (p_li, p_re, geom) VALUES ('C', 'B', 'LINESTRING(10 5,10 0)');
INSERT INTO foo (p_li, p_re, geom) VALUES (NULL, 'B', 'LINESTRING(10 0,0 0)');
INSERT INTO foo (p_li, p_re, geom) VALUES ('B', NULL, 'LINESTRING(0 5,0 0)');
INSERT INTO foo (p_li, p_re, geom) VALUES ('C', NULL, 'LINESTRING(20 10,10 10)');
INSERT INTO foo (p_li, p_re, geom) VALUES (NULL, 'C', 'LINESTRING(20 10,20 0)');
INSERT INTO foo (p_li, p_re, geom) VALUES (NULL, 'C', 'LINESTRING(20 0,10 0)');

SELECT p, AsText(polygonize(geom)) AS geom
FROM (
  SELECT p_li AS p, geom FROM foo WHERE p_li IS NOT NULL
  UNION
  SELECT p_re AS p, geom FROM foo WHERE p_re IS NOT NULL
) AS s
GROUP BY p
ORDER BY p;

 p |                              geom                              
---+----------------------------------------------------------------
 A | GEOMETRYCOLLECTION(POLYGON((0 5,0 15,10 15,10 10,10 5,0 5)))
 B | GEOMETRYCOLLECTION(POLYGON((0 0,0 5,10 5,10 0,0 0)))
 C | GEOMETRYCOLLECTION(POLYGON((10 0,10 5,10 10,20 10,20 0,10 0)))
(3 rows)

You could use GeometryN() to get just the polygon.

Is this anything like what you're trying to do?

-- 
Michael Fuhr