---------------------------------------------------------------------
-- The SE_AsGML function converts spatial primitives into their
-- GML representation.
---------------------------------------------------------------------

----------------------------------------------------------------------
-- First create an entry in the spatial_references table which
-- is used by many of the examples.
-----------------------------------------------------------------------
INSERT INTO spatial_references VALUES
  (1000, NULL, 'AUTHNAME', 1234, -50000, -50000, 1000, -50000, 1000, -50000, 1000, 'UNKNOWN');


--------------------------------- Point -------------------------------
-- A table holding ST_Point geometries is created.
--
-- When ST_Point geometries are created by the ST_PointFromText
-- function they are assigned the SRID value of 1000. 
-- 
-- When SE_AsGML() UDR is used to retrieve the GML representation, the 
-- 'auth_name' and 'auth_srid' values for the SRID form the srsName
-- attribute to identify the spatial reference system. See below:
--
-- <gml:Point srsName="AUTHNAME:1234">
-- <gml:coord><gml:X>10.02</gml:X><gml:Y>20.01</gml:Y></gml:coord>
-- </gml:Point>
------------------------------------------------------------------------
CREATE TABLE point_t (id varchar(10),
                     p1 ST_Point);

INSERT INTO point_t VALUES( 
   'xy', 
   ST_PointFromText('point (10.02 20.01)', 1000) 
);

INSERT INTO point_t VALUES( 
   'z', 
   ST_PointFromText('point z (10.02 20.01 5.0)', 1000) 
);

INSERT INTO point_t VALUES( 
   'm', 
   ST_PointFromText('point m (10.02 20.01 7.0)', 1000) 
);

INSERT INTO point_t VALUES( 
   'zm', 
   ST_PointFromText('point zm (10.02 20.01 5.0 7.0)', 1000) 
);

INSERT INTO point_t VALUES( 
    'empty', ST_PointFromText('point empty',1000) 
);

SELECT id, SE_AsGML(p1) FROM point_t order by id asc;


--------------------------- LineString --------------------------------
-- Create a table holding ST_LineString geometries. Insert several
-- ST_LineString values, then call SE_AsGML() to retrieve their
-- GML representation.
------------------------------------------------------------------------
CREATE TABLE line_t (id varchar(10),
                     p1 ST_LineString);

INSERT INTO line_t VALUES( 
    'xy',
    ST_LineFromText('linestring (0.0 0.0,0.0 1.0,1.0 0.0,1.0 1.0)',1000) 
);

INSERT INTO line_t VALUES( 
    'z',
    ST_LineFromText('linestring z (0.0 0.0 0,0.0 1.0 1,1.0 0.0 1,1.0 1.0 1)',1000) 
);

INSERT INTO line_t VALUES( 
    'm',
    ST_LineFromText('linestring m (0.0 0.0 0,0.0 1.0 1,1.0 0.0 1,1.0 1.0 1)',1000) 
);

INSERT INTO line_t VALUES( 
    'zm',
    ST_LineFromText('linestring zm (0.0 0.0 0 0,0.0 1.0 1 1,1.0 0.0 1 1,1.0 1.0 1 1)',1000) 
);

INSERT INTO line_t VALUES( 
   'empty',
   ST_LineFromText('linestring empty',1000) 
);

SELECT id, SE_AsGML(p1) FROM line_t order by id asc;

--------------------------- Polygon -----------------------------------
-- Create a table holding ST_Polygon geometries. Insert several
-- ST_Polygon values, then call SE_AsGML() to retrieve their
-- GML representation.
------------------------------------------------------------------------

CREATE TABLE polygon_t (id varchar(20),
                        p1 ST_Polygon);

INSERT INTO polygon_t VALUES( 
    'xy-1ring',
    ST_PolyFromText('polygon ((0.0 0.0,0.0 1.0,1.0 0.0,0.0 0.0))',1000) 
);

INSERT INTO polygon_t VALUES( 
    'xy-2ring',
    ST_PolyFromText('polygon ((0.0 0.0,0.0 1.0,1.0 0.0,0.0 0.0),(0.25 0.25,0.25 0.5,0.5 0.25,0.25 0.250))',1000) 
);

INSERT INTO polygon_t VALUES( 
    'xy-3ring',
    ST_PolyFromText('polygon ((0.0 0.0,0.0 1.0,1.0 0.0,0.0 0.0),(0.25 0.25,0.25 0.5,0.5 0.25,0.25 0.250),(0.125 0.125,0.125 0.25,0.25 0.125,0.125 0.1250))',1000) 
);

INSERT INTO polygon_t VALUES( 
    'xyz-1ring',
    ST_PolyFromText('polygon z ((0.0 0.0 0.0,0.0 1.0 1.0,1.0 0.0 1.0,0.0 0.0 0.0))',1000) 
);

INSERT INTO polygon_t VALUES( 
    'xym-1ring',
    ST_PolyFromText('polygon m ((0.0 0.0 0.0,0.0 1.0 1.0,1.0 0.0 1.0,0.0 0.0 0.0))',1000) 
);

INSERT INTO polygon_t VALUES( 
    'xyzm-1ring',
    ST_PolyFromText('polygon zm ((0.0 0.0 0.0 0,0.0 1.0 1.0 -1,1.0 0.0 1.0 -1,0.0 0.0 0.0 0))',1000) 
);

INSERT INTO polygon_t VALUES( 
    'empty',
    ST_PolyFromText('polygon empty',1000) 
);

SELECT id, SE_AsGML(p1) FROM polygon_t order by id asc;


-------------------------- MultiPoint ---------------------------------
-- Create a table holding ST_MultiPoint geometries. Insert several
-- ST_MultiPoint values, then call SE_AsGML() to retrieve their
-- GML representation.
------------------------------------------------------------------------
CREATE TABLE multipoint_t (id varchar(10),
                     p1 ST_MultiPoint);

INSERT INTO multipoint_t VALUES( 
  'xy',
  ST_MPointFromText('multipoint (0 0,0 1,1 1)',1000) 
);

INSERT INTO multipoint_t VALUES( 
  'z',
  ST_MPointFromText('multipoint z (0 0 0,0 1 1,1 1 1)',1000)
);

INSERT INTO multipoint_t VALUES( 
  'm',
  ST_MPointFromText('multipoint m (0 0 0,0 1 1,1 1 1)',1000)
);

INSERT INTO multipoint_t VALUES( 
  'zm',
  ST_MPointFromText('multipoint zm (0 0 0 0,0 1 1 1,1 1 1 0)',1000)
);

INSERT INTO multipoint_t VALUES( 
  '1xy',
  ST_MPointFromText('multipoint (0 0)',1000) 
);

INSERT INTO multipoint_t VALUES( 
  '1zm',
  ST_MPointFromText('multipoint zm (0 0 0 0)',1000)
);

INSERT INTO multipoint_t VALUES( 
  'empty',
  ST_MPointFromText('multipoint empty',1000) 
);

SELECT id, SE_AsGML(p1) FROM multipoint_t order by id asc;


------------------------- MultiLineString -----------------------------
-- Create a table holding ST_MultiLineString geometries. Insert several
-- ST_MultiLineString values, then call SE_AsGML() to retrieve their
-- GML representation.
------------------------------------------------------------------------
CREATE TABLE multiline_t (id varchar(10),
                     p1 ST_MultiLineString);

INSERT INTO multiline_t VALUES( 
    'xy',
    ST_MLineFromText('multilinestring ((0 0,0 1,1 0),(0 0,0 1,1 0),(0 0,0 1,1 0))',1000) 
);

INSERT INTO multiline_t VALUES( 
    'z',
    ST_MLineFromText('multilinestring z ((0 0 0,0 1 0,1 0 0),(0 0 0,0 1 0,1 0 0))',1000) 
);

INSERT INTO multiline_t VALUES( 
    'm',
    ST_MLineFromText('multilinestring m ((0 0 0,0 1 0,1 0 0),(0 0 0,0 1 0,1 0 0))',1000) 
);

INSERT INTO multiline_t VALUES( 
    'zm',
    ST_MLineFromText('multilinestring zm  ((0 0 0 0,0 1 0 0,1 0 0 0),(0 0 0 0,0 1 0 0,1 0 0 0))',1000) 
);

INSERT INTO multiline_t VALUES( 
    '1xy',
    ST_MLineFromText('multilinestring ((0 0,0 1,1 0))',1000) 
);

INSERT INTO multiline_t VALUES( 
    '1zm',
    ST_MLineFromText('multilinestring zm ((0 0 0 0,0 1 0 0,1 0 0 0))',1000) 
);

INSERT INTO multiline_t VALUES( 
   'empty',
   ST_MLineFromText('multilinestring empty',1000) 
);

SELECT id, SE_AsGML(p1) FROM multiline_t order by id asc;


--------------------------- MultiPolygon ------------------------------
-- Create a table holding ST_MultiPolygon geometries. Insert several
-- ST_MultiPolygon values, then call SE_AsGML() to retrieve their
-- GML representation.
------------------------------------------------------------------------
CREATE TABLE mpolygon_t (id varchar(20),
                        p1 ST_MultiPolygon);

INSERT INTO mpolygon_t VALUES( 
    'xy-1poly-1ring',
    ST_MPolyFromText('multipolygon (((0 0,0 1,1 0,0 0)))',1000) 
);

INSERT INTO mpolygon_t VALUES( 
    'xyzm-1poly-2ring',
    ST_MPolyFromText('multipolygon (((0 0,0 1,1 0,0 0),(0.25 0.25,0.25 0.5,0.5 0.25,0.25 0.25)))',1000) 
);

INSERT INTO mpolygon_t VALUES( 
    'xyzm-2poly-1ring',
    ST_MPolyFromText('multipolygon (((0 0,0 1,1 0,0 0)),((1 1,1 2,2 1,1 1)))',1000) 
);

INSERT INTO mpolygon_t VALUES( 
    'xy-2poly-2ring',
    ST_MPolyFromText('multipolygon (((0 0,0 1,1 0,0 0),(0.25 0.25,0.25 0.5,0.5 0.25,0.25 0.25)),((1 1,1 2,2 1,1 1),(1.3 1.3,1.3 1.5,1.5 1.3,1.3 1.3)))',1000) 
);

INSERT INTO mpolygon_t VALUES( 
    'xyzm-1poly-1ring',
    ST_MPolyFromText('multipolygon zm (((0 0 0 0,0 1 0 0,1 0 0 0,0 0 0 0)))',1000) 
);

INSERT INTO mpolygon_t VALUES( 
    'xyzm-2poly-2ring',
    ST_MPolyFromText('multipolygon zm (((0 0 0 0,0 1 0 0,1 0 0 0,0 0 0 0),(0.25 0.25 0 0,0.25 0.5 0 0,0.5 0.25 0 0,0.25 0.25 0 0)),((1 1 0 0,1 2 0 0,2 1 0 0,1 1 0 0),(1.3 1.3 0 0,1.3 1.5 0 0,1.5 1.3 0 0,1.3 1.3 0 0)))',1000) 
);

INSERT INTO mpolygon_t VALUES( 
    'empty',
    ST_MPolyFromText('multipolygon empty',1000) 
);

SELECT id, SE_AsGML(p1) FROM mpolygon_t order by id asc;


----------------------- srsName ---------------------------------------
-- When the 'auth_name' or 'auth_srid' values of a Spatial Reference 
-- System (SRS) associated with a geometry are NULL, the Well-Known Text 
-- representation of the SRS stored in the sde.spatial_references table,
-- column 'srtext' is returned in the srsName attribute.
------------------------------------------------------------------------
INSERT INTO spatial_references
  VALUES
     (1001, NULL, NULL, NULL, -50000, -50000, 1000, -50000, 1000, -50000, 1000, 'UNKNOWN');

INSERT INTO point_t VALUES( 
   'unknown', 
   ST_PointFromText('point (0 0)', 1001) 
);

SELECT SE_AsGML(p1) FROM point_t WHERE id='unknown';