/**
 * Eigene Funktionen in PostgreSQL erstellen:
 * Geometrien als OpenLayers-JavaScript-Deklarationen ausgeben. 
 * 
 * @author Marc Jansen <jansen(at)terrestris(dot)de>
 * @author Till Adams <adams(at)terrestris(dot)de>
 * @link http://openlayers-buch.de
 */

/**
 * Generische Funktion, die je nach Geometrytyp
 * andere Funktionen (s.u.) aufruft. 
 */
CREATE OR REPLACE FUNCTION
  ST_AsOpenLayersGeometry( in geo geometry )
RETURNS text AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    olgeo text := null;
  BEGIN
    -- die Variable 'geo' enthält $1
    IF (geotype = 'ST_Point') THEN
      olgeo = ST_AsOpenLayersPointGeometry( geo );
    END IF;
    IF (geotype = 'ST_MultiPoint') THEN
      olgeo = ST_AsOpenLayersMultiPointGeometry( geo );
    END IF;
    IF (geotype = 'ST_LineString') THEN
      olgeo = ST_AsOpenLayersLineStringGeometry( geo );
    END IF;
    IF (geotype = 'ST_MultiLineString') THEN
      olgeo = ST_AsOpenLayersMultiLineStringGeometry( geo );
    END IF;
    IF (geotype = 'ST_Polygon') THEN
      olgeo = ST_AsOpenLayersPolygonGeometry( geo );
    END IF;
    IF (geotype = 'ST_MultiPolygon') THEN
      olgeo = ST_AsOpenLayersMultiPolygonGeometry( geo );
    END IF;
    IF (geotype = 'ST_Geometry') THEN
      olgeo = ST_AsOpenLayersGeometryCollectionGeometry( geo );
    END IF;
    RETURN olgeo;
  END;
$BODY$ 
LANGUAGE 'plpgsql';

/**
 * Exportiert Punktgeometrien in JavaScript zur Erzeugung von 
 * OpenLayers-Geometrien
 */
CREATE OR REPLACE FUNCTION 
  ST_AsOpenLayersPointGeometry( in geo geometry )
RETURNS text AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    olgeo text := '';
  BEGIN
    IF (geotype = 'ST_Point') THEN
      olgeo = 'new OpenLayers.Geometry.Point(' 
      olgeo = olgeo || ST_X(geo) || ',' || ST_Y(geo) 
      olgeo = olgeo || ')';
    END IF;
    RETURN olgeo;
  END;
$BODY$ 
LANGUAGE 'plpgsql';

/**
 * Exportiert Multi-Punktgeometrien in JavaScript zur Erzeugung von 
 * OpenLayers-Geometrien
 */
CREATE OR REPLACE FUNCTION 
  ST_AsOpenLayersMultiPointGeometry( in geo geometry )
RETURNS TEXT AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    olgeo text := '';
    rec record;
  BEGIN
    IF (geotype = 'ST_MultiPoint') THEN
      olgeo = 'new OpenLayers.Geometry.MultiPoint([';
      FOR rec IN SELECT ST_GeometryN(geo, num_geom) AS geo_n
                   FROM generate_series(1, ST_NumGeometries( geo ))
                     AS num_geom LOOP
        olgeo = olgeo || ST_AsOpenLayersPointGeometry(rec.geo_n)
        olgeo = olgeo || ',';
      END LOOP;
      olgeo = substr(olgeo, 0, length(olgeo));
      olgeo = olgeo || '])';
    END IF;
    RETURN olgeo;
  END;
$BODY$ LANGUAGE 'plpgsql';

/**
 * Exportiert Liniengeometrien in JavaScript zur Erzeugung von 
 * OpenLayers-Geometrien
 */
CREATE OR REPLACE FUNCTION
  ST_AsOpenLayersLineStringGeometry( in geo geometry )
RETURNS TEXT AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    olgeo text := '';
    rec record;
  BEGIN
    IF (geotype = 'ST_LineString') THEN
      olgeo = 'new OpenLayers.Geometry.LineString([';
      FOR rec IN SELECT ST_PointN(geo, num_geom) as geo_n
                   FROM generate_series( 1, ST_NumPoints(geo) )
                     AS num_geom LOOP
	olgeo = olgeo || ST_AsOpenLayersPointGeometry(rec.geo_n) || ',';
      END LOOP;
      olgeo = substr(olgeo, 0, length(olgeo));
      olgeo = olgeo || '])';
    END IF;
    RETURN olgeo;
  END;
$BODY$ LANGUAGE 'plpgsql';

/**
 * Exportiert Multi-Liniengeometrien in JavaScript zur Erzeugung von 
 * OpenLayers-Geometrien
 */
CREATE OR REPLACE FUNCTION
  ST_AsOpenLayersMultiLineStringGeometry( in geo geometry )
RETURNS TEXT AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    olgeo text := '';
    rec record;
  BEGIN
    IF (geotype = 'ST_MultiLineString') THEN
      olgeo = 'new OpenLayers.Geometry.MultiLineString([';
      FOR rec IN SELECT ST_GeometryN(geo, num_geom) as geo_n
                   FROM generate_series( 1, ST_NumGeometries(geo) )
                     AS num_geom LOOP
	olgeo = olgeo || ST_AsOpenLayersLineStringGeometry(rec.geo_n) || ',';
      END LOOP;
      olgeo = substr(olgeo, 0, length(olgeo));
      olgeo = olgeo || '])';
    END IF;
    RETURN olgeo;
  END;
$BODY$ LANGUAGE 'plpgsql';

/**
 * Exportiert Polygongeometrien in JavaScript zur Erzeugung von 
 * OpenLayers-Geometrien
 */
CREATE OR REPLACE FUNCTION
  ST_AsOpenLayersPolygonGeometry( in geo geometry )
RETURNS TEXT AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    olgeo text := '';
    rec record;
    rec2 record;
    rec3 record;
    ext_ring geometry;
    num_int_rings integer;
  BEGIN
    IF (geotype = 'ST_Polygon') THEN
      ext_ring = ST_ExteriorRing(geo);
      num_int_rings = ST_NumInteriorRings(geo);
      IF ( ST_IsRing(ext_ring) ) THEN
        olgeo = 'new OpenLayers.Geometry.Polygon([';
	olgeo = olgeo || 'new OpenLayers.Geometry.LinearRing([';
        FOR rec IN SELECT ST_PointN(ext_ring, num_geom) as geo_n
                     FROM generate_series( 1, ST_NumPoints(ext_ring) )
                       AS num_geom LOOP
	  olgeo = olgeo || ST_AsOpenLayersPointGeometry(rec.geo_n) || ',';
        END LOOP;
        olgeo = substr(olgeo, 0, length(olgeo));
        olgeo = olgeo || '])';
        IF ( num_int_rings > 0) THEN
          olgeo = olgeo || ',';
          FOR rec2 IN SELECT ST_InteriorRingN(geo, int_ring_n) AS geo_int_ring_n
                        FROM generate_series( 1, num_int_rings )
                          AS int_ring_n LOOP
            olgeo = olgeo || 'new OpenLayers.Geometry.LinearRing([';
	        FOR rec3 IN SELECT ST_PointN(rec2.geo_int_ring_n, num_point) as geo_n
                          FROM generate_series( 1, ST_NumPoints(rec2.geo_int_ring_n) )
                            AS num_point LOOP
	          olgeo = olgeo || ST_AsOpenLayersPointGeometry(rec3.geo_n) || ',';
            END LOOP;
            olgeo = substr(olgeo, 0, length(olgeo));
            olgeo = olgeo || ']),';
          END LOOP;
          olgeo = substr(olgeo, 0, length(olgeo));
        END IF;
        olgeo = olgeo || '])';
      END IF;
    END IF;
    RETURN olgeo;
  END;
$BODY$ LANGUAGE 'plpgsql';

/**
 * Exportiert Multi-Polygongeometrien in JavaScript zur Erzeugung von 
 * OpenLayers-Geometrien
 */
CREATE OR REPLACE FUNCTION
  ST_AsOpenLayersMultiPolygonGeometry( in geo geometry )
RETURNS TEXT AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    olgeo text := '';
    rec record;
  BEGIN
    IF (geotype = 'ST_MultiPolygon') THEN
      olgeo = 'new OpenLayers.Geometry.MultiPolygon([';
      FOR rec IN SELECT ST_GeometryN(geo, num_geom) as geo_n
                   FROM generate_series( 1, ST_NumGeometries(geo) )
                     AS num_geom LOOP
	olgeo = olgeo || ST_AsOpenLayersPolygonGeometry(rec.geo_n) || ',';
      END LOOP;
      olgeo = substr(olgeo, 0, length(olgeo));
      olgeo = olgeo || '])';
    END IF;
    RETURN olgeo;
  END;
$BODY$ LANGUAGE 'plpgsql';

/**
 * Exportiert Geometrie-Collections in JavaScript zur Erzeugung von 
 * OpenLayers-Geometrien
 */
CREATE OR REPLACE FUNCTION
  ST_AsOpenLayersGeometryCollectionGeometry( in geo geometry )
RETURNS TEXT AS
$BODY$
  DECLARE
    geotype text := ST_GeometryType(geo);
    geotype_elem text;
    olgeo text := '';
    rec record;
  BEGIN
    IF (geotype = 'ST_Geometry') THEN
      olgeo = 'new OpenLayers.Geometry.Collection([';
      FOR rec IN SELECT ST_GeometryN(geo, num_geom) as geo_n
                   FROM generate_series( 1, ST_NumGeometries(geo) )
                     AS num_geom LOOP
        olgeo = olgeo || ST_AsOpenLayersGeometry(rec.geo_n) || ',';
      END LOOP;
      olgeo = substr(olgeo, 0, length(olgeo));
      olgeo = olgeo || '])';
    END IF;
    RETURN olgeo;
  END;
$BODY$ LANGUAGE 'plpgsql';