-- Section: Geometry and Geography Standard Output Formats
-- examples of output functions --
-- <start id="code_output_geom" /> --
SELECT ST_AsGML(geom,5) as GML
	, ST_AsKML(geom,5) As KML
	, ST_AsGeoJSON(geom,5) As GeoJSON
	, ST_AsSVG(geom,0,5) As SVG_Absolute
	, ST_AsSVG(geom,1,5) As SVG_Relative
	, ST_AsX3D(geom,6) As X3D
FROM (SELECT 
 ST_GeomFromText('LINESTRING(2 48, 0 51)', 4326) 
  As geom) foo;
-- <end id="code_output_geom" /> --

-- example of geohash as increase size --
-- <start id="code_geohash" /> --
SELECT i As rad_meters
 , ST_GeoHash(geog::geometry) as ghash
FROM (
 SELECT i,
  ST_Buffer(
   ST_GeogFromText('POINT(2 48)'), i) As geog 
 FROM unnest(
  ARRAY[0.02,1,1000,10000,50000,150000]) AS i) As foo;
-- <end id="code_geohash" /> --

-- Creating geometries from Standard Text and Binary formats
-- bulk table insert using ST_GeomFromtText
-- Example: Bag O' Geometries
-- <start id="code_bag_geometries" /> --
SELECT * INTO unconstrained_geoms
FROM ( VALUES 
 ( ST_GeomFromText('POINT(-100 28 1)', 4326) )
 , ( ST_GeomFromText('LINESTRING(-80 28 1, -90 29 1)', 4326) )
 , ( ST_GeomFromText('POLYGONZ((10 28 1, 9 29 1
         , 7 30 1, 10 28 1))' ) )
 , ( ST_GeomFromText('POLYHEDRALSURFACE( 
   ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),  
   ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), 
   ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), 
   ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),  
   ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),  
   ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) 
  )' ) )	
    ) As foo(geom);
-- <end id="code_bag_geometries" /> --

-- Example: Bulk insert enforcing geometry type and spatial reference system
-- <start id="code_typed_geometries" /> --
SELECT geom::geometry(LineString,4326) INTO constrained_geoms
FROM ( VALUES 
 ( ST_GeomFromText('LINESTRING(-80 28, -90 29)', 4326) )
  , ( ST_GeomFromText('LINESTRING(10 28, 9 29, 7 30)', 4326 ) )
) As foo(geom);
-- <end id="code_typed_geometries" /> --

-- constrain to require there is a z coordinate --
-- <start id="code_constraining_z" /> --
ALTER TABLE unconstrained_geoms 
 ALTER COLUMN geom TYPE geometry(GeometryZ);
-- <end id="code_constraining_z" /> --

-- transforming kml --
-- <start id="code_transforming_kml" /> --
SELECT 
 ST_GeomFromKML(
  '<LineString>
    <coordinates>10,28 9,29 7,30</coordinates>
  </LineString>');
-- <end id="code_transforming_kml" /> --

-- Bulk table insert using ST_GeomFromEWKT    
SELECT * INTO table2 
FROM ( VALUES 
    (ST_GeomFromEWKT('SRID=4326;POINT(-100 28)')),
    (ST_GeomFromEWKT('SRID=4326;LINESTRING(-80 28,-90 29)')),
    (ST_GeomFromEWKT('SRID=4326;POLYGON((10 28, 9 29, 7 30, 10 28))' ) ) ) As foo(geom);

    
-- using WKB
-- <start id="code_geom_from_wkb" /> --
SELECT ST_GeomFromWKB(E'\\001\\001\\000\\000\\000
 \\321\\256B\\312O\\304Q\\300
 \\347\\030\\220\\275\\336%E@',4326);
-- <end id="code_geom_from_wkb" /> --

-- <start id="code_geom_to_wkb" /> --
SELECT
ST_AsBinary(ST_GeomFromWKB(E'\\001\\001\\000\\000\\000\\321\\256B
\\312O\\304Q\\300\\347\\030\\220\\275\\336%E@',4326));
-- <end id="code_geom_to_wkb" /> --

-- using standard conforming sql strings
-- <start id="code_geom_from_wkb_scs_on" /> --
set standard_conforming_strings = on;
SELECT ST_GeomFromWKB('\001\001\000\000\000\321\256B\312O\304Q
 \300\347\030\220\275\336%E@');
-- <end id="code_geom_from_wkb_scs_on" /> --

--<start id="code_canonical_geom_form_1" /> --
SELECT CAST('0101000020E61000008048BF7D1D2059
C017B7D100DEB23C40'
 As geometry);
--<end id="code_canonical_geom_form_1" /> --

-- PostgreSQL short-cast notation --
-- <start id="code_canonical_geom_form_short_cast" /> --
SELECT '0101000020E61000008048BF7D1D2059C017B
7D100DEB23C40'::geometry;
-- <end id="code_canonical_geom_form_short_cast" /> --

-- seciotn exmaples: Autocasting
-- <start id="code_autocast_1" /> --
SELECT ST_Centroid('LINESTRING(1 2,3 4)');
-- <end id="code_autocast_1" /> --

-- <start id="code_autocast_2" /> --
SELECT ST_AsText(ST_Centroid('LINESTRING(1 2,3 4)'));
-- <end id="code_autocast_2" /> --

-- <start id="code_autocast_3" /> --
SELECT ST_Centroid(ST_GeomFromText('LINESTRING(1 2,3 4)'));
-- <end id="code_autocast_3" /> --

-- Get function function st_perimeter(unknown) is not unique -- 
-- <start id="code_autocast_4" /> --
SELECT ST_Perimeter('POLYGON((145.007 13.581,144.765 13.21
  ,144.602 13.2,144.589 13.494
  ,144.845 13.705,145.007 13.581))');
-- <end id="code_autocast_4" /> --
  
SELECT ST_Box3D('BOX(1 2, 3 4)');

SELECT ST_XMin('LINESTRING(1 2, 3 4)');

SELECT ST_XMin('LINESTRING(1 2, 3 4)'::geometry::box3d);

-- Section: Getting and setting spatial reference system 
-- Example use of ST_SRID

-- <start id="code_st_srid_geom" /> --
SELECT ST_SRID(      -- <co id="co_code_st_srid_geom_1" /> --
  ST_GeomFromText('POLYGON((1 1, 2 2, 2 0, 1 1))', 4326)
  );

SELECT ST_SRID(geom) As srid  -- <co id="co_code_st_srid_geom_2" /> --
 , COUNT(*) As number_of_geoms 
FROM sometable 
GROUP BY ST_SRID(geom);


SELECT ST_SRID(geom) As srid
  , ST_SRID(ST_SetSRID(geom,4326)) as srid_new -- <co id="co_code_st_srid_geom_3" /> --
FROM ( VALUES (
 ST_GeomFromText('POLYGON((70 20, 71 21, 71 19, 70 20))', 4269)
  )
  , (ST_Point(1,2))
  ) As foo (geom);
-- <end id="code_st_srid_geom" /> --
--[1] Simple use of ST_SRID
--[2] Counts number of distinct SRIDs
--[3] Using ST_SetSRID to change SRID

-- Transform to a different spatial reference
-- <start id="code_st_transform_geom" /> --
SELECT ST_AsEWKT(
  ST_Transform('SRID=4326;LINESTRING(-73 41, -72 42)'::geometry, 32618)
 );
-- <end id="code_st_transform_geom" /> --

-- Using ST_Transform with geography --
-- <start id="code_geog_transform" /> --
SELECT ST_Transform( -- <co id="co_code_geog_transform_4" /> --
 ST_ClosestPoint( -- <co id="co_code_geog_transform_3" /> --
  ST_Transform( -- <co id="co_code_geog_transform_2" /> --
  	geog::geometry -- <co id="co_code_geog_transform_1" /> --
	,32618), 
   ST_Transform('SRID=4326;LINESTRING(-73 41, -72 42)'::geometry,32618)
 ),4326)::geography; -- <co id="co_code_geog_transform_5" /> --
-- <end id="code_geog_transform" /> --
-- #1 cast geography to geometry
-- #2 transform to a good planar coordinate for the area of interest
-- #3 find closest point in planar coordinates
-- #4 transform back to WGS 84
-- #5 cast back to geography

-- Section: Geometry Type
-- Listing: Differences between ST_GeometryType and GeometryType
-- <start id="code_geom_geometrytype" /> --
SELECT ST_GeometryType(geom) As new_name
 , GeometryType(geom) As old_name
 FROM (VALUES 
  (ST_GeomFromText('POLYGON((0 0, 1 1, 0 1, 0 0))')),
  (ST_Point(1, 2)),
  (ST_MakeLine(ST_Point(1, 2), ST_Point(1, 2))),
  (ST_Collect(ST_Point(1, 2), ST_Buffer(ST_Point(1, 2),3))),
  (ST_LineToCurve(ST_Buffer(ST_Point(1, 2), 3))),
  (ST_LineToCurve(ST_Boundary(ST_Buffer(ST_Point(1, 2), 3)))),
  (ST_Multi(ST_LineToCurve(ST_Boundary(ST_Buffer(ST_Point(1, 2),3)))))
) As foo (geom);
-- <end id="code_geom_geometrytype" /> --

-- utilizing type to infer function to use --
-- <start id="code_geom_conditional_type" /> --
SELECT CASE 
 WHEN GeometryType(geom) = 'POLYGON' THEN ST_Area(geom) 
 WHEN GeometryType(geom) = 'LINESTRING' 
  THEN ST_Length(geom) ELSE NULL END As measure 
FROM sometable;
-- <end id="code_geom_conditional_type" /> --

-- Listing: Coordinate and geometry dimensions of various geometries --
-- <start id="code_geom_coord_dims" /> --
SELECT ST_GeometryType(geom) As type, 
 ST_Dimension(geom) As gdim, ST_CoordDim(geom) as cdim
FROM unconstrained_geoms;
-- <end id="code_geom_coord_dims" /> -- 

-- Section: Number of Points that defines a geometry --
-- Listing: Example of ST_NPoints and ST_NumPoints --
-- <start id="code_npoints_numpoints" /> --
SELECT type, ST_NPoints(geom) As npoints, 
  ST_NumPoints(geom) As numpoints
FROM (VALUES ('LinestringM' , 
  ST_GeomFromEWKT('LINESTRINGM(1 2 3, 3 4 5, 5 8 7, 6 10 11)')),
 ('Circularstring', 
  ST_GeomFromText('CIRCULARSTRING(2.5 2.5, 4.5 2.5, 4.5 4.5)')),
('Polygon (Triangle)', 
  ST_GeomFromText('POLYGON((0 1,1 -1,-1 -1,0 1))')),
('Multilinestring', 
  ST_GeomFromText('MULTILINESTRING ((1 2, 3 4, 5 6),
   (10 20, 30 40))')),
('Collection', ST_Collect(
  ST_GeomFromText('POLYGON((0 1,1 -1,-1 -1,0 1))'),
   ST_Point(1,3)))
) As foo(type, geom);
-- <end id="code_npoints_numpoints" /> --

-- Section: Measurement --
-- Length2d vs 3D length --
-- <start id="code_length_3dlength" /> --
SELECT ST_Length(geom) As length_2d
 , ST_3DLength(geom) As length_3d
FROM (VALUES ( ST_GeomFromText('LINESTRING(1 2 3, 4 5 6)') )
 , (ST_GeomFromText('LINESTRING(1 2, 4 5)'))) 
  As foo(geom);
-- <end id="code_length_3dlength" /> --

-- Section: Decomposition
-- Sub: Bounding Box of Geometries
-- Listing: Box2D, Box3D of various geoms
-- <start id="code_box2d_box3d" /> --
SELECT name, Box2D(geom) As box2d 
   , Box3D(geom) As box3d
FROM (
VALUES 
   ('2D line'
     , ST_GeomFromText('LINESTRING(121.63 25.03, 3.03 6.58
      , -71.06 42.36)'
     , 4326))
 , ('3D line', ST_GeomFromText('LINESTRING(1 2 3, 3 4 1000.34567)'))
 , ('Vert 2D line', ST_GeomFromText('LINESTRING(1 2, 1 4)')) 
 , ('Point', ST_GeomFromText('POINT(1 2)')) 
 , ('Polygon', ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'))
 , ('Cube',  ST_GeomFromText('POLYHEDRALSURFACE( 
   ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),  
   ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), 
   ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), 
   ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),  
   ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),  
   ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) 
  )' ) )
 )
AS foo(name, geom);
-- <end id="code_box2d_box3d" /> --

-- Sect Sub: Boundaries 
-- Listing: Examples of ST_Boundary
-- <start id="code_st_boundary" /> --
SELECT name, ST_AsText(ST_Boundary(geom)) As WKT
FROM (VALUES 
('Simple linestring', 
             ST_GeomFromText('LINESTRING(-14 21,0 0,35 26)')),
('Non-simple linestring', 
            ST_GeomFromText('LINESTRING(2 0,0 0,1 1,1 -1)')),
('Closed linestring', 
          ST_GeomFromText('LINESTRING(52 218, 139 82, 
          262 207, 245 261, 207 267, 153 207, 
          125 235, 90 270, 55 244, 51 219, 52 218)')),
('Polygon', 
          ST_GeomFromText('POLYGON((52 218, 139 82, 262 207, 
       245 261, 207 267, 153 207, 125 235, 90 270, 
        55 244, 51 219, 52 218))')),
 ('Polygon with holes', 
          ST_GeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25,
       2.5 -1.25,-0.25 -1.25),(2.25 0,1.25 1,1.25 -1,2.25 0),
         (1 -1,1 1,0 0,1 -1))'))
)
AS foo(name, geom);
-- <end id="code_st_boundary" /> --

-- Section Point marker for a geometry: centroid, point on surface, and nth point
-- Listing: Examples of ST_Centroid/ST_PointOnSurface
-- <start id="code_centroid_pointonsurface" /> --
SELECT name
 , ST_AsEWKT(ST_Centroid(geom)) As centroid
 , ST_AsEWKT(ST_PointOnSurface(geom)) As point_on_surface
FROM ( VALUES 
 , ('Multipoint', ST_GeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)'))
 , ('Multipoin 3D'
     , ST_GeomFromText('MULTIPOINT(-1 1 1, 0 0 2, 2 3 1)'))
 , ('Multilinestring'
   , ST_GeomFromText('MULTILINESTRING((0 0,0 1,1 1),
      (-1 1,-1 -1))'))
 , ('Polygon', 
   ST_GeomFromEWKT('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25
    , 2.5 -1.25,-0.25 -1.25), (2.25 0,1.25 1,1.25 -1,2.25 0)
    , (1 -1,1 1,0 0,1 -1))'))
   )
As foo(name, geom);
-- <end id="code_centroid_pointonsurface" /> --

-- Section: point n
-- inline example
-- <start id="code_pointn" /> --
SELECT ST_AsText(
 ST_PointN(
  ST_GeomFromText('LINESTRING(1 2, 3 4, 5 8)'), 2)
);
-- <end id="code_pointn" /> --

-- Section: Multigeom to set --

-- <start id="code_st_dump" /> --
WITH 
 foo(gid, geom) As -- <co id="code_st_dump_1" /> --
 (VALUES 
  (1, 
   ST_GeomFromText('POLYHEDRALSURFACE( 
   ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),  
   ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), 
   ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), 
   ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),  
   ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),  
   ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) 
  )' )),
  (2, ST_GeomFromText('GEOMETRYCOLLECTION(
   MULTIPOLYGON(((2.25 0,1.25 1,1.25 -1,2.25 0)),
     ((1 -1,1 1,0 0,1 -1)) ), 
   MULTIPOINT(1 2, 3 4), LINESTRING(5 6, 7 8), 
     MULTICURVE(CIRCULARSTRING(1 2, 0 4, 2 8), (1 2, 5 6)))'))
 )
SELECT gid
 , (gdump).path As pos -- <co id="code_st_dump_3" /> --
 , ST_AsText((gdump).geom) As exploded_geometry 
FROM (SELECT gid, ST_Dump(geom) As gdump -- <co id="code_st_dump_2" /> ---
   FROM foo) As foofoo;
-- <end id="code_st_dump" /> --

-- Listing: compare simplifypreservetopology, simplify
-- <start id="code_compare_simplify_preserve" /> -- 
SELECT pow(2, n) as tolerance, 
  ST_AsText(ST_Simplify(geom, pow(2, n))) As ST_Simplify, 
  ST_AsText(
    ST_SimplifyPreserveTopology(geom, pow(2, n))) 
    As ST_SimplifyPreserveTopology
FROM (SELECT 
  ST_GeomFromText('POLYGON((10 0, 20 0, 30 10, 30 20, 
  20 30, 10 30, 0 20, 0 10, 10 0))') As geom
) As foo CROSS JOIN generate_series(2,4) As n;
-- <end id="code_compare_simplify_preserve" /> -- 

-- Section making points --
-- Listing:  Point constructor functions --
-- <start id="code_whale_spots" /> -- 
SELECT whale, ST_AsEWKT(spot) As spot
FROM 
(VALUES 
('Mr. Whale', ST_SetSRID(ST_Point(-100.499, 28.7015), 4326)), 
 ('Mr. Whale with M as time',                                    -- <co id="code_whale_spots_1a" /> --
  ST_SetSRID(ST_MakePointM(-100.499, 28.7015, 5), 4326)),        -- <co id="code_whale_spots_1b" /> --
 ('Mr. Whale with Z as depth',                                   -- <co id="code_whale_spots_2a" /> --
  ST_SetSRID(ST_MakePoint(-100.499, 28.7015, 0.5), 4326)),       -- <co id="code_whale_spots_2b" /> --
 ('Mr. Whale with M and Z',                                      -- <co id="code_whale_spots_3a" /> --
  ST_SetSRID(ST_MakePoint(-100.499, 28.7015, 0.5, 5), 4326))     -- <co id="code_whale_spots_3b" /> --
) As foo(whale, spot);
-- <end id="code_whale_spots" /> -- 
-- #1 Whale with time
-- #2 Whale with depth
-- #3 Whale with time and depth

