GLE-Postgis-Brach-/Entwicklungsflächen

Tabelle brach_entwicklungsflaechen

CREATE TABLE geodaten_gemeinden.brach_entwicklungsflaechen
(
 id serial NOT NULL,
 art character varying NOT NULL,
 flaeche numeric,
 bemerkungen text,
 bearbeiter character varying,
 letzte_akt date,
 stelle character varying
)
WITH (
 OIDS=TRUE
);

SELECT AddGeometryColumn('geodaten_gemeinden', 'brach_entwicklungsflaechen','the_geom',25833,'MULTIPOLYGON', 2);
ALTER TABLE geodaten_gemeinden.brach_entwicklungsflaechen ALTER COLUMN the_geom SET NOT NULL;

ALTER TABLE geodaten_gemeinden.brach_entwicklungsflaechen ADD CONSTRAINT brach_entwflaechen_pkey PRIMARY KEY(id);

CREATE INDEX gist_geom
 ON geodaten_gemeinden.brach_entwicklungsflaechen
 USING gist
 (the_geom);

View brach_entwicklungsflaechen

CREATE OR REPLACE VIEW geodaten_gemeinden.brach_entwicklungsflaechen_view AS 
SELECT row_number() OVER () AS id,
 round(st_length(st_makeline(segments.sp, segments.ep))::numeric, 1) AS label,
 st_makeline(segments.sp, segments.ep) AS the_geom, 
 segments.stelle
FROM (SELECT st_pointn(linestrings.the_geom, generate_series(1, st_npoints(linestrings.the_geom) - 1)) AS sp,
      st_pointn(linestrings.the_geom, generate_series(2, st_npoints(linestrings.the_geom))) AS ep,
      linestrings.stelle
      FROM ( SELECT (st_dump(st_boundary(b.the_geom))).geom AS the_geom,
             b.stelle
             FROM geodaten_gemeinden.brach_entwicklungsflaechen b
           ) linestrings
     ) segments
GROUP BY st_makeline(segments.sp, segments.ep), segments.stelle;