/*
Prereq:

Relational database experience, preferably PostgreSQL
SQL, SQL, SQL: If no have, focus on the functions

What is a spatial extension to a database?

What to take away: (Tools vs HD analogy)
1. Capability of PostGIS
2. Application to your own projects

Following along
*/

-- CREATE Database

/*
DROP DATABASE IF EXISTS chicago;
CREATE DATABASE chicago WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' CONNECTION LIMIT = -1;

-- CREATE new Schemas [Explain use of schema]

CREATE SCHEMA postgis AUTHORIZATION postgres;
CREATE SCHEMA contrib AUTHORIZATION postgres;

-- Set Search Path

ALTER DATABASE chicago SET search_path = public, contrib, postgis;
CREATE EXTENSION postgis SCHEMA postgis;
--[Install hstore via PGAdmin]

CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
    
-- You'll see these in PostGIS 2.2
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION pointcloud;
CREATE EXTENSION pointcloud_postgis;

DROP TABLE IF EXISTS worlds;

CREATE TABLE public.worlds (id serial) WITH (OIDS = FALSE);
ALTER TABLE public.worlds ADD CONSTRAINT pk_worlds PRIMARY KEY (id);
ALTER TABLE public.worlds OWNER TO postgres;

-- PostGIS introduces new data types

ALTER TABLE worlds ADD COLUMN name varchar(20) NOT NULL;
ALTER TABLE worlds ADD COLUMN points geometry(point);
ALTER TABLE worlds ADD COLUMN linestrings geometry(linestring);
ALTER TABLE worlds ADD COLUMN polygons geometry(polygon);
*?
-- Modeling (Terrestrial, Political, Zoom levels

TRUNCATE TABLE worlds;

INSERT INTO worlds (name, points) 
    VALUES 
        ('City Hall', ST_GeomFromText('POINT(0 0)')),
        ('Fire Station', ST_GeomFromText('POINT(2 4)')),
        ('Tower', ST_GeomFromText('POINT(10 0)'));
    
INSERT INTO worlds (name, linestrings) VALUES ('Main St', ST_GeomFromText('LINESTRING(-12 0,0 0,12 12)'));
INSERT INTO worlds (name, polygons) VALUES ('Centerville', ST_GeomFromText('POLYGON((-10 -10,-10 10,10 10,10 -10,-10 -10))')); --[Note: parens]

-- Show table entries (point out how you can't read geom)

-- OJ [Explain, not something we're teaching]

-- Functions: Need to know only a few functions, use intuition on which function works on which geometry
-- Different versions of same function

/*
SELECT ST_Length(linestrings) FROM worlds;
SELECT ST_Length(points) FROM worlds;
SELECT ST_Length(polygons) FROM worlds;

SELECT ST_Area(polygons) FROM worlds;
SELECT ST_Perimeter(polygons) FROM worlds;

SELECT ST_NPoints(polygons) FROM worlds; -- ST_NumPoints
SELECT ST_NPoints(linestrings) FROM worlds;

SELECT ST_AsText(ST_Centroid(polygons)) FROM worlds;
SELECT ST_AsText(ST_Centroid(linestrings)) FROM worlds;

--SELECT ST_X(linestrings) FROM worlds;
SELECT ST_XMax(linestrings) FROM worlds;
SELECT ST_XMin(linestrings) FROM worlds;

*/

-- Two Geometries (Yes and No)

--SELECT id, name, ST_AsText(points), ST_AsText(linestrings), ST_AsText(polygons) FROM worlds

-- ST_Intersects

INSERT INTO worlds (name, points) VALUES ('School', ST_GeomFromText('POINT(0 2)'));
INSERT INTO worlds (name, linestrings) VALUES ('School St', ST_GeomFromText('LINESTRING(-0 -12,0 12)'));

INSERT INTO worlds (name, linestrings) VALUES ('Fire Station Dr', 
    ST_MakeLine(
        (SELECT points FROM worlds WHERE name = 'Fire Station'),
        ST_ClosestPoint(
            (SELECT linestrings FROM worlds WHERE name = 'Main St'),
            (SELECT points FROM worlds WHERE name = 'Fire Station')
        )            
    )
);
/*
--Point and LineString
WITH 
    A AS (SELECT name, points As geom FROM worlds WHERE points IS NOT NULL),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE linestrings IS NOT NULL)
SELECT A.name, B.name, ST_Intersects(A.geom,B.geom) FROM A CROSS JOIN B;

--Point and Polygon
WITH 
    A AS (SELECT name, points As geom FROM worlds WHERE points IS NOT NULL),
    B AS (SELECT name, polygons As geom FROM worlds WHERE polygons IS NOT NULL)
SELECT A.name, B.name, ST_Intersects(A.geom,B.geom), ST_Within(A.geom,B.geom);

--LineString and Polygon

WITH 
    A AS (SELECT name, linestrings As geom FROM worlds WHERE linestrings IS NOT NULL),
    B AS (SELECT name, polygons As geom FROM worlds WHERE polygons IS NOT NULL)
SELECT A.name, B.name, ST_Intersects(A.geom,B.geom) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT name, polygons As geom FROM worlds WHERE name = 'Centerville'),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Main St')
SELECT A.name, B.name, ST_Contains(A.geom,B.geom), ST_Contains(B.geom,A.geom), ST_Within(A.geom,B.geom), ST_Within(B.geom,A.geom) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT name, polygons As geom FROM worlds WHERE name = 'Centerville'),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Fire Station Dr')
SELECT A.name, B.name, ST_Contains(A.geom,B.geom), ST_Contains(B.geom,A.geom), ST_Within(A.geom,B.geom), ST_Within(B.geom,A.geom) FROM A CROSS JOIN B;

--LineString and LineString

WITH 
    A AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Fire Station Dr'),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Main St')
SELECT A.name, B.name, ST_Intersects(A.geom,B.geom), ST_Touches(A.geom,B.geom), ST_Crosses(A.geom,B.geom) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'School St'),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Main St')
SELECT A.name, B.name, ST_Intersects(A.geom,B.geom), ST_Touches(A.geom,B.geom), ST_Crosses(A.geom,B.geom) FROM A CROSS JOIN B;
*/

DELETE FROM worlds WHERE name IN ('NETown','Resevoir');
INSERT INTO worlds (name, polygons) VALUES ('NETown', ST_GeomFromText('POLYGON((10 8,10 14,18 18,14 8,10 8))'));
INSERT INTO worlds (name, polygons) VALUES ('Resevoir', ST_GeomFromText('POLYGON((-14 -10,-10 -6,-6 -10,-10 -14,-14 -10))'));

/*
WITH 
    A AS (SELECT name, polygons As geom FROM worlds WHERE name = 'Centerville'),
    B AS (SELECT name, polygons As geom FROM worlds WHERE name = 'NETown')
SELECT A.name, B.name, ST_Intersects(A.geom,B.geom), ST_Overlaps(A.geom,B.geom) FROM A CROSS JOIN B;
*/
--Intersections

/*
WITH 
    A AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'School St'),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Main St')
SELECT A.name, B.name, ST_AsText(ST_Intersection(A.geom,B.geom)) FROM A CROSS JOIN B;
 
WITH 
    A AS (SELECT name, polygons As geom FROM worlds WHERE name = 'Centerville'),
    B AS (SELECT name, polygons As geom FROM worlds WHERE name = 'NETown')
SELECT A.name, B.name, ST_AsText(ST_Intersection(A.geom,B.geom)) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT name, polygons As geom FROM worlds WHERE name = 'Centerville'),
    B AS (SELECT name, polygons As geom FROM worlds WHERE name = 'Resevoir')
SELECT A.name, B.name, ST_AsText(ST_Intersection(A.geom,B.geom)) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT name, polygons As geom FROM worlds WHERE name = 'Centerville'),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Main St')
SELECT A.name, B.name, ST_AsText(ST_Intersection(A.geom,B.geom)) FROM A CROSS JOIN B;

*/


DELETE FROM worlds WHERE name LIKE ('Home%');

WITH 
    X AS (SELECT name, generate_series(ST_XMin(polygons)::integer,ST_XMax(polygons)::integer,2) AS x FROM worlds WHERE name IN ('Centerville','NETown')),
    Y AS (SELECT name, generate_series(ST_YMin(polygons)::integer,ST_YMax(polygons)::integer,2) AS y FROM worlds WHERE name IN ('Centerville','NETown'))
INSERT INTO worlds (name, points)
SELECT 'H', ST_GeomFromText('POINT (' || x || ' ' || y || ')') FROM X INNER JOIN Y ON X.name = Y.name;

DELETE FROM worlds WHERE name = 'H';

WITH 
    X AS (SELECT name, generate_series(ST_XMin(polygons)::integer,ST_XMax(polygons)::integer,2) AS x FROM worlds WHERE name IN ('Centerville','NETown')),
    Y AS (SELECT name, generate_series(ST_YMin(polygons)::integer,ST_YMax(polygons)::integer,2) AS y FROM worlds WHERE name IN ('Centerville','NETown'))
INSERT INTO worlds (name, points)
SELECT 'H', ST_GeomFromText('POINT (' || x || ' ' || y || ')') FROM X INNER JOIN Y ON X.name = Y.name
EXCEPT
SELECT 'H', points FROM worlds WHERE points IS NOT NULL;

WITH X AS (SELECT linestrings FROM worlds WHERE linestrings IS NOT NULL)
DELETE FROM worlds W 
USING X
WHERE name = 'H' AND ST_Intersects(W.points,X.linestrings);

WITH X AS (SELECT polygons FROM worlds WHERE name = 'Resevoir')
DELETE FROM worlds W 
USING X
WHERE name = 'H' AND ST_Within(W.points,X.polygons)

--Buffer

DELETE FROM worlds WHERE name like 'Tower Buffer Zone%';
INSERT INTO worlds (name, polygons) 
SELECT 'Tower Buffer Zone 1', ST_AsText(ST_Buffer(points,1)) FROM worlds WHERE name = 'Tower'
UNION ALL
SELECT 'Tower Buffer Zone 2', ST_AsText(ST_Buffer(points,2)) FROM worlds WHERE name = 'Tower'
UNION ALL
SELECT 'Tower Buffer Zone 3', ST_AsText(ST_Buffer(points,3)) FROM worlds WHERE name = 'Tower'

DELETE FROM worlds WHERE name like 'Main St Buffer Zone%';
INSERT INTO worlds (name, polygons) 
SELECT 'Main St Buffer Zone', ST_Buffer(linestrings,2) FROM worlds WHERE name = 'Main St'


--Equality

DELETE FROM worlds WHERE name LIKE 'Solidus%';

INSERT INTO worlds (name, linestrings) 
SELECT 'Solidus Back', ST_GeomFromText('LINESTRING(3 -3,6 -6)') FROM worlds WHERE name = 'Fire Station'
UNION ALL
SELECT 'Solidus Fwrd', ST_GeomFromText('LINESTRING(6 -3,3 -6)') FROM worlds WHERE name = 'Tower';

WITH 
    F AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Solidus Fwrd'),
    B AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Solidus Back')
--SELECT F.geom = B.geom FROM F CROSS JOIN B
SELECT ST_Equals(F.geom,B.geom) FROM F CROSS JOIN B;

SELECT Box2D(linestrings) FROM worlds WHERE name LIKE 'Solidus%' --[BOX is not a geometry!]

DELETE FROM worlds WHERE name LIKE 'Solidus%';

INSERT INTO worlds (name, linestrings) 
SELECT 'Solidus 1', ST_GeomFromText('LINESTRING(2 -2,6 -6)') FROM worlds WHERE name = 'Fire Station'
UNION ALL
SELECT 'Solidus 2', ST_GeomFromText('LINESTRING(6 -6,2 -2)') FROM worlds WHERE name = 'Tower';

WITH 
    S1 AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Solidus 1'),
    S2 AS (SELECT name, linestrings As geom FROM worlds WHERE name = 'Solidus 2')
SELECT F.geom = B.geom FROM F CROSS JOIN B
--SELECT ST_Equals(S1.geom,S2.geom) FROM S1 CROSS JOIN S2;
--SELECT ST_OrderingEquals(S1.geom,S2.geom) FROM S1 CROSS JOIN S2;

SELECT Box2D(linestrings) FROM worlds WHERE name LIKE 'Solidus%' --[BOX is not a geometry!]



--Distance
WITH 
    X As (SELECT name, points AS geom FROM worlds WHERE points IS NOT NULL AND name <> 'H')
SELECT L.name, R.name, ST_Distance(L.geom,R.geom) 
FROM X L INNER JOIN X R ON L.name <> R.name

WITH 
    X As (SELECT name, points AS geom FROM worlds WHERE points IS NOT NULL AND name <> 'H')
SELECT L.name, R.name, ST_Distance(L.geom,R.geom) 
FROM X L INNER JOIN X R ON NOT ST_Equals(L.geom,R.geom)

WITH 
    X As (SELECT name, linestrings AS geom FROM worlds WHERE linestrings IS NOT NULL)
SELECT L.name, R.name, ST_Distance(L.geom,R.geom) 
FROM X L INNER JOIN X R ON NOT ST_Equals(L.geom,R.geom)

WITH 
    X As (SELECT name, polygons AS geom FROM worlds WHERE polygons IS NOT NULL)
SELECT L.name, R.name, ST_Distance(L.geom,R.geom) 
FROM X L INNER JOIN X R ON NOT ST_Equals(L.geom,R.geom)


WITH 
    X As (
        SELECT name, points AS geom FROM worlds WHERE points IS NOT NULL AND name <> 'H' UNION ALL 
        SELECT name, linestrings AS geom FROM worlds WHERE linestrings IS NOT NULL UNION ALL 
        SELECT name, polygons AS geom FROM worlds WHERE polygons IS NOT NULL
    )
SELECT L.name, R.name, ST_Distance(L.geom,R.geom) 
FROM X L INNER JOIN X R ON NOT ST_Equals(L.geom,R.geom);


--Within 

WITH 
    X As (
        SELECT name, points AS geom FROM worlds WHERE points IS NOT NULL AND name <> 'H' UNION ALL 
        SELECT name, linestrings AS geom FROM worlds WHERE linestrings IS NOT NULL UNION ALL 
        SELECT name, polygons AS geom FROM worlds WHERE polygons IS NOT NULL
    )
SELECT name FROM X WHERE ST_Distance(ST_GeomFromText('POINT(0 0 )'),geom) <= 7.5;

WITH 
    X As (
        SELECT name, points AS geom FROM worlds WHERE points IS NOT NULL AND name <> 'H' UNION ALL 
        SELECT name, linestrings AS geom FROM worlds WHERE linestrings IS NOT NULL UNION ALL 
        SELECT name, polygons AS geom FROM worlds WHERE polygons IS NOT NULL
    )
SELECT name FROM X WHERE ST_Intersects(ST_Buffer(ST_GeomFromText('POINT(0 0 )'),7.5),geom);

WITH 
    X As (
        SELECT name, points AS geom FROM worlds WHERE points IS NOT NULL AND name <> 'H' UNION ALL 
        SELECT name, linestrings AS geom FROM worlds WHERE linestrings IS NOT NULL UNION ALL 
        SELECT name, polygons AS geom FROM worlds WHERE polygons IS NOT NULL
    )
SELECT name FROM X WHERE ST_DWithin(ST_GeomFromText('POINT(0 0 )'),geom,7.5);


--MULTI

SELECT ST_AsText(ST_Collect(points)) FROM worlds WHERE name <> 'H';
SELECT ST_AsText(ST_Collect(linstrsings)) FROM worlds;
SELECT ST_AsText(ST_Collect(polygons)) FROM worlds;
SELECT ST_AsText(polygons) FROM worlds WHERE polygons IS NOT NULL;
SELECT ST_AsText(ST_Multi(polygons)) FROM worlds WHERE polygons IS NOT NULL;

WITH 
    X As (
        SELECT name, points AS geom FROM worlds WHERE points IS NOT NULL AND name <> 'H' UNION ALL 
        SELECT name, linestrings AS geom FROM worlds WHERE linestrings IS NOT NULL UNION ALL 
        SELECT name, polygons AS geom FROM worlds WHERE polygons IS NOT NULL
    )
SELECT ST_AsText(ST_Collect(geom)) FROM X;

--SRID {Slides]
/*
GeoID, Datum, Projection
*/

SELECT srid, srtext::varchar(50) 
FROM spatial_ref_sys 
WHERE srtext ILIKE '%83%' AND srtext ILIKE '%Illinois%' AND srtext ILIKE '% East%' AND proj4text ILIKE '%ft%';

DROP TABLE IF EXISTS ni; 
SELECT * INTO ni FROM worlds ORDER BY id;

ALTER TABLE ni ALTER COLUMN points TYPE geometry(point,4326) USING ST_SetSRID(points,4326);
ALTER TABLE ni ALTER COLUMN linestrings TYPE geometry(linestring,4326) USING ST_SetSRID(linestrings,4326);
ALTER TABLE ni ALTER COLUMN polygons TYPE geometry(polygon,4326) USING ST_SetSRID(polygons,4326);

--NULL Island

--http://www.naturalearthdata.com/downloads/50m-cultural-vectors/

WITH 
    C AS (SELECT name, geom FROM countries WHERE Box2D(geom) && ('BOX(-10 -10, 10 10)')::Box2D),
    X As (
        SELECT name, points AS geom FROM ni WHERE points IS NOT NULL AND name <> 'H' UNION ALL 
        SELECT name, linestrings AS geom FROM ni WHERE linestrings IS NOT NULL UNION ALL 
        SELECT name, polygons AS geom FROM ni WHERE polygons IS NOT NULL
    )
SELECT X.name, C.name, ST_Distance(X.geom,C.geom) 
FROM X INNER JOIN C ON NOT ST_Equals(X.geom,C.geom);

ALTER TABLE ni ADD COLUMN points_geog geography;
ALTER TABLE ni ADD COLUMN linestrings_geog geography;
ALTER TABLE ni ADD COLUMN polygons_geog geography;

UPDATE ni SET points_geog = points::geography, linestrings_geog = linestrings::geography, polygons_geog = polygons::geography;

ALTER TABLE countries ADD COLUMN geog geography;
UPDATE countries SET geog = geom::geography;

WITH 
    A AS (SELECT geom FROM countries WHERE name = 'Japan'),
    B AS (SELECT points As geom FROM ni WHERE name = 'City Hall')
SELECT ROUND(ST_Distance(A.geom,B.geom)) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT geom FROM countries WHERE name = 'Japan'),
    B AS (SELECT points As geom FROM ni WHERE name = 'City Hall')
SELECT ROUND(ST_Distance_Sphere(A.geom,B.geom)) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT geog FROM countries WHERE name = 'Japan'),
    B AS (SELECT points_geog As geog FROM ni WHERE name = 'City Hall')
SELECT ROUND(ST_Distance(A.geog,B.geog)) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT geom FROM countries),
    B AS (SELECT points As geom FROM ni WHERE name <> 'H')
SELECT ROUND(ST_Distance_Sphere(A.geom,B.geom)) FROM A CROSS JOIN B;

WITH 
    A AS (SELECT geog FROM countries),
    B AS (SELECT points_geog As geog FROM ni WHERE name <> 'H')
SELECT ROUND(ST_Distance(A.geog,B.geog)) FROM A CROSS JOIN B;

--[Load cities with GUI]

WITH 
    A AS (SELECT name, geog::geometry(POINT,4326) As geom FROM cities WHERE name = 'Houston'),
    B As (SELECT name, geog::geometry(POINT,4326) As geom FROM cities WHERE name = 'Mumbai')
SELECT ST_Segmentize(ST_MakeLine(A.geom,B.geom)::geography, 100)::geometry FROM A CROSS JOIN B;

WITH 
    A AS (SELECT name, geog::geometry(POINT,4326) As geom FROM cities WHERE name = 'Houston'),
    B As (SELECT name, geog::geometry(POINT,4326) As geom FROM cities WHERE name = 'Mumbai')
SELECT ST_MakeLine(A.geom,B.geom) FROM A CROSS JOIN B;

SELECT ROUND(ST_Area(geog)/1000000) FROM countries WHERE name = 'United States'

--ftp://ftp2.census.gov/geo/tiger/TIGER2014/STATE

--[Load US in PC, NA, WM] Demo in OJ

--https://data.cityofchicago.org/Transportation/CTA-L-Rail-Stations-Shapefile/53r7-y88m
--https://data.cityofchicago.org/Transportation/CTA-L-Rail-Lines-Shapefile/53r7-y88m

SELECT * FROM rails;
SELECT lines, ST_Length(geom) FROM rails;
SELECT lines, SUM(ST_Length(geom)) FROM rails GROUP BY lines ORDER BY lines;

SELECT ST_AsEWKT(ST_GeomFromText('POINT(1146615.6192745704 1942608.0430093298)',3435));


SELECT lines, ST_Distance(ST_GeomFromEWKT('SRID=3435;POINT(1146615.61927457 1942608.04300933)'),geom)
FROM rails
ORDER BY 2
LIMIT 3

--Station within 1500 FT
WITH X As (SELECT ST_Transform(ST_GeomFromText('POINT(-87.6294 41.8885)',4326),3435) As geom)
SELECT longname, address, ST_Distance(S.geom, X.geom)::integer As dist_ft 
FROM stations S INNER JOIN X
ON ST_DWithin(S.geom, X.geom, 1500)


--Three closest train stops using bounding box
WITH X As (SELECT ST_Transform(ST_GeomFromText('POINT(-87.6294 41.8885)',4326),3435) As geom)
SELECT 
    longname, address, 
    ST_Distance(geom, (SELECT geom FROM X))::integer As dist_ft, 
    (S.geom <-> (SELECT geom FROM X))::integer As dist_knn 
FROM stations S
ORDER BY S.geom <-> (SELECT geom FROM X) LIMIT 3;


--Simplification
SELECT COUNT(*) FROM rails;
SELECT lines, ST_AsText(geom) FROM rails WHERE lines  = 'Yellow Line';
SELECT lines, ST_AsText(ST_LineMerge(ST_Union(geom))) FROM rails GROUP BY lines;
SELECT lines, ST_AsText(ST_Simplify((ST_LineMerge(ST_Union(geom))),1000)) FROM rails GROUP BY lines;
SELECT lines, ST_AsText(ST_Simplify((ST_LineMerge(ST_Union(geom))),10000)) FROM rails GROUP BY lines;
SELECT lines, ST_AsText(ST_Simplify((ST_LineMerge(ST_Union(geom))),100000)) FROM rails GROUP BY lines;


--OSM
--https://mapzen.com/metro-extracts

--3D linestring
SELECT 
    '<shape><appearance><material ambientintensity="0.200" containerfield="material" shininess="0.25" emissivecolor="1 .25 .25"></material></appearance>' || 

    ST_AsX3D('LINESTRING Z(100 200 0, 200 200 0, 200 300 0, 200 350 0, 200 350 10, 200 350 50)'::geometry) || 

    '</shape>' As x3d;
                       
--Closed PolyhedralSurface with X3D
SELECT 
    '<Shape><Appearance><Material ambientIntensity="0.200" containerField="material" shininess="0.200" diffuseColor="1 0 0"/></Appearance>' || 
    ST_AsX3D(
        'POLYHEDRALSURFACE Z( 
            ((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))
        )'::geometry
    ) || 
    '</Shape>';
                    
--Closed TIN with X3D
SELECT 
    '<Shape><Appearance><Material ambientIntensity="0.200" containerField="material" shininess="0.200" diffuseColor="0.8 0 0.6"/></Appearance>' || 
    replace(
        ST_AsX3D(
            'TIN(
                ((0 0 0, 1 0 0, 0 1 0, 0 0 0)),
                ((0 0 0, 1 0 0, 0 0 1, 0 0 0)),
                ((0 0 0, 0 0 1, 0 1 0, 0 0 0)),
                ((0 0 1, 1 0 0, 0 1 0, 0 0 1))
            )'::geometry),  
        '&lt;IndexedTriangleSet',
        '&lt;IndexedTriangleSet solid="false" ') || 
    '</Shape>';     

--Road network in X3D
SELECT string_agg('<shape><appearance><material ambientintensity="0.200" containerfield="material" shininess="0.25" emissivecolor="' || (rn*1.0)/cnt || ' 0.1 0"></material></appearance>' || ST_AsX3D(ST_Force_3D(geom)) || '</shape>', '') As x3d
FROM (
    SELECT row_number() over(ORDER BY ST_Length(way) DESC) As rn, name, count(*) OVER() As cnt, way As geom
    FROM po.planet_osm_line 
    WHERE ST_Intersects(ST_Transform(ST_Buffer(ST_Point(-87.627,41.8819)::geography,100)::geometry,900913), way)) As f;


--SFCGAL ST_Extrude convert 2D Buildings to 3D Buildings near Trump
SELECT string_agg('<shape><appearance><material ambientintensity="0.200" containerfield="material" shininess="0.25" diffusecolor="' || (rn*1.0)/cnt || ' 0.1 0"></material></appearance>' || ST_AsX3D(ST_Extrude(geom,0,0,50)) || '</shape>', '') As x3d
FROM (
    SELECT 
        row_number() OVER (ORDER BY ST_Area(p.way) DESC) As rn, 
        name, 
        count(*) OVER() As cnt, 
        p.way As geom
    FROM 
        po.planet_osm_polygon AS p INNER JOIN 
        (SELECT way FROM po.planet_osm_polygon WHERE name ILIKE 'Trump%') As r 
    ON ST_DWithin(r.way, p.way, 150)
    WHERE p.building > ''
) As f;


--Tiger
SELECT address, streetname, streettypeabbrev, zip FROM normalize_address('1 Devonshire Place, Boston, MA 02109');

SELECT address, streetname, streettypeabbrev, zip FROM pagc_normalize_address('1 Devonshire Place, Boston, MA 02109');

SELECT 'Non-PAGC', address, streetname, streettypeabbrev, zip FROM normalize_address('ONE Devonshire Place, Boston, MA 02109')
UNION ALL
SELECT 'PAGC', address, streetname, streettypeabbrev, zip FROM pagc_normalize_address('ONE Devonshire Place, Boston, MA 02109');













































































