-- https://postgis.us/presentations/Foss4G2021_SolvingProblemsWithPostGIS.html
-- default creation to foss4g2021 if not schema specified
SET search_path=foss4g2021,public,postgis,contrib,topology,tiger;

-- PRESENTATION STARTS HERE --
/**= Loading Data  =**/
/**== Loading Data Using ogr_fdw extension ==**/

/**=== Install ogr_fdw extension == **/
-- useful for linking to foreign data
-- part of postgis windows bundle, available on pgdg for apt / yum for supported PostgreSQL
-- source at: https://github.com/pramsey/pgsql-ogr-fdw 
-- can also use shp2pgsql packaged with PostGIS, but ogr_fdw supports all same formats as GDAL (ogr2ogr) and like ogr2ogr can use network paths and urls and read data from compressed files
CREATE EXTENSION IF NOT EXISTS ogr_fdw SCHEMA postgis;
SELECT ogr_fdw_version();
-- get a list of supported drivers
SELECT driver
FROM unnest(ogr_fdw_drivers()) AS driver
ORDER BY driver;
/**=== Load a web file using ogr_fdw === 
Caution: https doesn't work if you don't have the CURL_CA_BUNDLE environment variable set (should point at the ca-bundle.crt or some such file) and cert bundle is not available in system paths
Also doesn't work if your GDAL not compiled with curl
https://gdal.org/user/virtual_file_systems.html
**/
SELECT ogr_fdw_version();
-- more examples here - https://blog.crunchydata.com/blog/querying-spatial-data-with-postgis-and-ogr_fdw
DROP SERVER IF EXISTS fds_geojson CASCADE;
CREATE SERVER fds_geojson 
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS(datasource '/vsicurl/https://raw.githubusercontent.com/johan/world.geo.json/master/countries.geo.json', format 'GeoJSON');
IMPORT FOREIGN SCHEMA ogr_all 
FROM SERVER fds_geojson INTO staging;
-- visualize
SELECT * FROM staging.countries_geo;

-- make into a physical table
DROP TABLE IF EXISTS countries;
SELECT id, name, geom::geography AS geog
INTO countries
FROM staging.countries_geo;
-- visualize
SELECT * FROM countries;


-- both zipped web file
DROP SERVER IF EXISTS fds_ne CASCADE;
CREATE SERVER fds_ne 
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS(datasource '/vsizip//vsicurl/http://postgis.us/downloads/ne_10m_admin_1_states_provinces.zip', format 'ESRI Shapefile');

IMPORT FOREIGN SCHEMA ogr_all FROM SERVER fds_ne INTO staging;

DROP TABLE IF EXISTS states_provinces;
-- 4595 rows, 1 secs 824 msec
CREATE TABLE states_provinces AS 
SELECT * FROM staging.ne_10m_admin_1_states_provinces;

/**=== Convert geometry to geography types === **/
-- 1 secs 198 msec.
ALTER TABLE states_provinces 
ALTER COLUMN geom TYPE geography(MULTIPOLYGON,4326) USING ST_Multi(geom)::geography(MULTIPOLYGON,4326);

ALTER TABLE states_provinces RENAME geom TO geog;

-- spot check 
SELECT * FROM states_provinces WHERE name ILIKE 'Buenos A%';

/**=== Load a zipped file of ESRI Shapefiles using ogr_fdw === **/
-- create server - data from https://download.geofabrik.de/south-america/argentina-latest-free.shp.zip
DROP SERVER IF EXISTS fds_osm CASCADE;
-- yes gdal can read zipped files and extract
-- to be more pedantic (or if file doesn't end in .zip) can use /vsizip//fdw_data/argentina-latest-free.shp.zip
-- windows you should put drive letter or network path
CREATE SERVER fds_osm 
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS(datasource 'c:/fdw_data/argentina-latest-free.shp.zip', format 'ESRI Shapefile');

IMPORT FOREIGN SCHEMA ogr_all FROM SERVER fds_osm INTO staging;

-- see list of tables and geometry type (doesn't tell you they are foreign but tells you they are geometry and srid, type ogr_fdw takes the first type and assumes all are that so be careful shapefiles are mixed (single/multi so POLYGON might be POLYGON/MULTIPOLYGON))
SELECT f_table_name, f_geometry_column, coord_dimension, srid, type 
FROM geometry_columns 
WHERE f_table_schema = 'staging';

-- Tells you what kind of table
SELECT table_name, table_type , is_insertable_into 
FROM information_schema.tables 
WHERE table_schema = 'staging';

DROP TABLE IF EXISTS buildings;
-- 720043 rows, 10 secs 528 msec.
CREATE TABLE buildings AS
SELECT osm_id::bigint, code, fclass, name, type, ST_Multi(geom)::geography(MULTIPOLYGON,4326) AS geog
FROM staging.gis_osm_buildings_a_free_1;

ALTER TABLE buildings ADD CONSTRAINT pk_buildings PRIMARY KEY(osm_id);
-- 4 secs 984 msec.
CREATE INDEX ix_buildings_geog_spgist ON buildings USING spgist(geog);

analyze buildings;

-- 4 secs 984 msec.
CREATE INDEX ix_buildings_geog_gist ON buildings USING gist(geog);
DROP TABLE IF EXISTS pois;
-- 137314 rows, 926 msec.
CREATE TABLE pois AS
SELECT osm_id::bigint, code, 
    fclass, name, geom::geography(POINT,4326) AS geog
FROM staging.gis_osm_pois_free_1;
-- 134 ms
CREATE INDEX pk_pois ON pois (osm_id);
-- 637 msec.
CREATE INDEX ix_pois_geog_gist ON pois USING gist(geog);
-- 812 ms
analyze pois;

DROP TABLE IF EXISTS landuse;
-- 255109 rows, 10 secs 714 msec.
-- don't want places duped and there are unique 255109 osm_ids our of 255532 records, so just take the biggest
CREATE TABLE landuse AS
SELECT DISTINCT ON(osm_id) osm_id::bigint, code, 
    fclass, name, ST_Multi(geom)::geography(MULTIPOLYGON,4326) AS geog
FROM staging.gis_osm_landuse_a_free_1
ORDER BY osm_id, COALESCE(name,'') DESC;

-- 373 msec
ALTER TABLE landuse ADD CONSTRAINT pk_landuse PRIMARY KEY (osm_id);
-- 2 secs 343 msec.
CREATE INDEX ix_landuse_geog_gist ON landuse USING gist(geog);
-- 307 msec.
analyze landuse; 

-- load roads
-- 1150920, 19 secs 197 ms 
CREATE TABLE roads AS
SELECT osm_id::bigint, code, 
    fclass, name, ref, oneway, maxspeed, layer, bridge, tunnel, geom::geography(LINESTRING,4326) AS geog
FROM staging.gis_osm_roads_free_1;
-- 984 msec.
ALTER TABLE roads ADD CONSTRAINT pk_roads PRIMARY KEY (osm_id);
-- 6 secs 414 msec.
CREATE INDEX ix_roads_geog_gist ON roads USING gist(geog);
analyze roads;

/** ==========================================
Take measurements, find largest named buildings
==========================================**/
-- 1 secs 549 msec. 10 rows
SELECT name AS building, 
    ST_Area(geog) AS geog_area_sqm, geog, 
    ST_Perimeter(geog) AS per_m, 
    RANK() OVER(ORDER BY   ST_Perimeter(geog)) AS rank_per
FROM buildings WHERE name > ''
ORDER BY geog_area_sqm DESC LIMIT 10;

--= Lighten the weight of geometries
/**===========================================
 Reduce Precision of geometries/geographies
 ST_ReducePrecision: 
 Introduced in PostGIS 3.1, requires GEOS 3.9+
 -- gridSize is in srid units so in this case wgs 84 degrees. 
--==========================================**/
SELECT geog As before, ST_NPoints(geog::geometry) AS before_npoints, after_geom, ST_NPoints(after_geom) AS after_npoints, ST_IsValid(after_geom)
FROM states_provinces, ST_ReducePrecision(geog::geometry, 0.5) AS after_geom
WHERE iso_a2 = 'AR';
--==========================================**/
-- ST_SnapToGrid
SELECT geog As before, ST_NPoints(geog::geometry) AS before_npoints, after_geom, ST_NPoints(after_geom) AS after_npoints, ST_IsValid(after_geom)
FROM states_provinces, ST_SnapToGrid(geog::geometry, 0.5) AS after_geom
WHERE iso_a2 = 'AR';

-- ST_SimplifyPreserveTopology
SELECT geog As before, ST_NPoints(geog::geometry) AS before_npoints, after_geom, ST_NPoints(after_geom) AS after_npoints, ST_IsValid(after_geom)
FROM states_provinces, ST_SimplifyPreserveTopology(geog::geometry, 0.5) AS after_geom
WHERE iso_a2 = 'AR';

/**===========================================
 Other ways (let's compare)
 Big different with ST_ReducePrecision is that it overlays on a grid (so edges that are connected are more likely to stay connected)
--==========================================**/
SELECT name, geog As before, ST_Area(geog)::bigint AS area_m_before,
    --pg_column_size(geog::geometry) AS weight_before,    
    ST_NPoints(geog::geometry) AS before_npoints, 
    f.type, after_geom, 
    ST_NPoints(after_geom) AS after_npoints,
    -- pg_column_size(after_geom) AS weight_after,
    ST_IsValid(after_geom) AS is_valid_after,
    ST_Area(after_geom::geography)::bigint AS area_m_after
FROM states_provinces, (SELECT 0.5) AS prec(prec) CROSS JOIN
    LATERAL 
  (  VALUES
    ('ST_Simplify', ST_Simplify(geog::geometry, prec) ) 
  ,  ('ST_SimplifyPreserveTopology', ST_SimplifyPreserveTopology(geog::geometry,prec) ) 
  ,  ('ST_ReducePrecision', ST_ReducePrecision(geog::geometry, prec) ) 
     ,  ('ST_SnapToGrid', ST_SnapToGrid(geog::geometry, prec) ) 
  ) AS f(type, after_geom)
WHERE iso_a2 = 'AR'
ORDER BY geog,f.type;

--= Reprojecting data to measure preserving planar spatial reference system

/**===========================================
 Determine Best srid for region of interest
 top result is srid 6932 NSIDC EASE-Grid 2.0 South
--==========================================**/
WITH s AS (SELECT * FROM spatial_ref_sys 
WHERE (srtext ILIKE '%argentina%' or proj4text ILIKE '%lat_0=-90%') AND proj4text ILIKE '%units=m%'
          AND NOT srtext ILIKE '%deprecated%')
, sp AS (SELECT s.srid, s.auth_name, s.srtext, s.proj4text, ST_Area(geog)::bigint AS a_m_geog, ST_Area(ST_Transform(geog::geometry,s.srid))::bigint AS a_m_after, ST_Perimeter(geog) AS p_m_geog, ST_Perimeter(ST_Transform(geog::geometry,s.srid)) AS p_m_after
FROM states_provinces, s
WHERE iso_a2 = 'AR' AND name = 'Buenos Aires')
SELECT * FROM sp
ORDER BY abs(a_m_geog - a_m_after), abs(p_m_geog - p_m_after), auth_name
LIMIT 5;

/**===========================================
 Create a new geometry column from geography
 top result is srid 6932 NSIDC EASE-Grid 2.0 South
--==========================================**/
-- 24 rows, 67 ms
DROP TABLE IF EXISTS argentina;
CREATE TABLE argentina AS 
SELECT *, ST_Transform(geog::geometry,6932)::geometry(MULTIPOLYGON,6932) AS geom
FROM states_provinces
WHERE iso_a2 = 'AR';

-- spot check worst
SELECT name, abs(ST_Area(geog) - ST_Area(geom))::bigint AS diff_area_m, geom
FROM argentina
ORDER BY diff_area_m DESC;

ALTER TABLE buildings 
ADD COLUMN geom geometry(MULTIPOLYGON,6932);
-- 720043 rows, 28-41 secs
UPDATE buildings SET geom = ST_Transform(geog::geometry, 6932);
DROP INDEX IF EXISTS ix_buildings_geom_gist;
-- 1 secs 263msec
CREATE INDEX ix_buildings_geom_gist ON buildings USING gist(geom);
analyze buildings;
-- spot check worst
SELECT name, abs(ST_Area(geog) - ST_Area(geom))::bigint AS diff_area_m
FROM buildings
ORDER BY diff_area_m DESC
limit 10;

ALTER TABLE roads ADD COLUMN geom geometry(LINESTRING,6932);
-- 1150920 38 secs 761 msec.
UPDATE roads SET geom = ST_Transform(geog::geometry, 6932);

CREATE INDEX ix_roads_geom_gist ON roads USING gist(geom);
analyze roads;

ALTER TABLE pois ADD COLUMN geom geometry(POINT,6932);
-- 137314 2 secs 893 msec.
UPDATE pois SET geom = ST_Transform(geog::geometry, 6932);
-- 1 secs 984 msec.
CREATE INDEX ix_pois_geom_gist ON pois USING gist(geom);
analyze pois;
--= Finding things
--==========================================**/
/**===========================================
    Find pois within 200 meters of a place
    geometry/geography performance for point <-> point is about the same
--==========================================**/
-- Geography 72 rows, 43 msec.
SELECT name, geog, pt <->geog AS sphere_m_dist, ST_Distance(pt,geog) AS spheroid_m_dist 
FROM pois , 
    geography(ST_Point(-58.382, -34.603)) AS pt
WHERE ST_DWithin(geog,pt, 200)
ORDER BY sphere_m_dist;

-- Geometry 68 rows affected. 42 msec.
-- using new feature in PostGIS 3.2 ST_Point(lon,lat,srid).  If using pre 3.2 do ST_SetSRID(ST_Point(lon,lat),srid
SELECT osm_id, name, geog, geom <-> pt AS dist 
FROM pois , ST_Transform(ST_Point(-58.382, -34.603,4326),6932) AS pt
WHERE ST_DWithin(geom, pt, 200)
ORDER BY dist;

-- the two answers are not the same, how can we make them closer
-- Geometry ST_Intersects 72 rows, 72 msec.
SELECT osm_id, name, geog, geom <-> ST_Centroid(pt) AS dist 
FROM pois , ST_Transform(ST_Buffer(ST_Point(-58.382, -34.603)::geography, 200)::geometry,6932) AS pt
WHERE ST_Intersects(geom, pt)
ORDER BY dist;
/**===========================================
    Find buildings completely in Buenos Aires
    geography performance for polygon/polygon 
    varies on complexity, can be super slow compared to geometry.
--==========================================**/
-- 645 msec. (with index) 151508 buildings (without index on geom 2.5 secs)
WITH a AS (SELECT  geom FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT  count(*) 
FROM buildings AS b INNER JOIN a ON ST_Covers(a.geom, b.geom) ;

-- repeat same exercise with geography
-- 151324 rows, 12 min 7 secs. ST_Covers for geography Super slow
WITH a AS (SELECT  geog FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT  count(*) 
FROM buildings AS b INNER JOIN a ON ST_Covers(a.geog, b.geog) ;

/**===========================================
    Find buildings that intersect Buenos Aires
    geography performance for polygon/polygon 
    varies on complexity (but much faster than ST_Covers)
    -- USING ST_Intersects compare with geometry/geography
--==========================================**/
-- ST_Intersects for geometry
-- 151602 rows, 528 msec.
WITH a AS (SELECT  geom FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT  count(*) 
FROM buildings AS b INNER JOIN a ON ST_Intersects(a.geom, b.geom) ;

-- ST_Intersects for geography
-- 151602 rows, 56 secs 622 msec.
-- much better than ST_Covers but still slower than geometry
WITH a AS (SELECT  geog FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT  count(*) 
FROM buildings AS b INNER JOIN a ON ST_Intersects(a.geog, b.geog) ;

/**===========================================
    Breaking Geometries apart using 
    ST_Subdivide, ST_HexagonGrid, ST_SquareGrid
    Why: Better intersects performance, 
        For dividing for statistical analysis
--==========================================**/
/** ST_Subdivide (based on max points) 
* http://postgis.net/docs/ST_Subdivide.html
    * with 100 max points  13 rows, 59 msec.
    * with 10 max points 195 rows, 52ms **/
WITH a AS (SELECT  geog FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT ST_Subdivide(geog::geometry, 10)::geography
FROM a;

/** -- PostGIS >= 3.1 + GEOS >= 3.9
-- ST_Subdivide (based on max points and gridsize) in WGS 84 spatial ref, gridsize is measured in units of spatial ref sys.  Remember geography -> geometry units are degrees (NOT meters)
 * with 100 max points  12 rows, 41 msec.
   slightly faster than without grid, first lays geometries on grid and then does the subdivide **/
WITH a AS (SELECT  geog FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT ST_Subdivide(geog::geometry, 100, 0.01)::geography
FROM a;

/** -- PostGIS >= 3.1 
-- ST_HexagonGrid(float8 size, geometry bounds)
http://postgis.net/docs/ST_HexagonGrid.html
bounds is geometry in same srid as your input.

Remember geography -> geometry units are degrees (NOT meters)
 * with 100 max points  12 rows, 41 msec.
   slightly faster than without grid, first lays geometries on grid and then does the subdivide **/
   -- hexagon grid always turns the input geom to a bounding box of the input geom, so you get this:
-- 361 rows 40 msec.
WITH a AS (SELECT  geog FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT f.i, f.j, geom::geography AS geog
FROM a CROSS JOIN LATERAL 
    ST_HexagonGrid(0.25, geog::geometry) AS f;
    
-- if you just want the hexagons that cover Argentina, need to do this:
-- 230 rows 338 msec.
WITH a AS (SELECT  geog FROM argentina 
           WHERE name = 'Buenos Aires'
          )
SELECT f.i, f.j, f.geom::geography AS geog
FROM a INNER JOIN LATERAL 
    ST_HexagonGrid(0.25, geog::geometry) AS f ON ST_Intersects(a.geog, f.geom::geography);
    
-- ST_HexagonGrid guarantees unique set of grids for any given srid and size, which means you can do this
-- the darker cells are the boundary hexagons between 2 regions
-- 2 secs 911 msec., 2310 rows affected.
SELECT a.name, f.i, f.j, f.geom::geography AS geog
FROM argentina AS a INNER JOIN LATERAL 
    ST_HexagonGrid(0.25, geog::geometry) AS f ON ST_Intersects(a.geog, f.geom::geography);
    
-- ST_SquareGrid if you prefer squares
--  5 secs 758 msec. 5455 rows affected.
SELECT a.name, f.i, f.j, f.geom::geography AS geog
FROM argentina AS a INNER JOIN LATERAL 
    ST_SquareGrid(0.25, geog::geometry) AS f ON ST_Intersects(a.geog, f.geom::geography);

/**===========================================
    Breaking Line Strings apart using 
    ST_Segmentize and ST_MakeLine
    Why: Better performance, show curvature of geography on flat map
--==========================================**/

/** ST_Segmentize(geometry/geography, float max_segment_length) (exists for both geometry and geography)
https://postgis.net/docs/ST_Segmentize.html **/

WITH a AS (SELECT ST_MakeLine(ST_Point(-58.382, -34.603), ST_Point(40.382, 42.603  ))::geography AS geog
)
SELECT ST_Segmentize(geog, 1000)
FROM a;

-- Lets load some GPS traces using ogr_fdw
-- https://www.openstreetmap.org/trace/3848902/data
DROP SERVER IF EXISTS fds_gpx CASCADE;
CREATE SERVER fds_gpx 
    FOREIGN DATA WRAPPER ogr_fdw
    OPTIONS(datasource '/fdw_data/3848902.gpx', format 'GPX');
    
IMPORT FOREIGN SCHEMA ogr_all 
FROM SERVER fds_gpx INTO staging;
-- 68 ms, 4 rows - aggregate points into lines
SELECT date_trunc('minute', time) AS time_slot, ST_MakeLine(geom ORDER BY time) AS geom
FROM staging.track_points
GROUP BY date_trunc('minute', time);

/**===========================================
    Using PostGIS rasters
--==========================================**/

CREATE EXTENSION IF NOT EXISTS postgis_raster;

-- new in upcoming PostGIS 3.2 lots of cool new raster features like secure cloud raster access - more details - https://blog.crunchydata.com/blog/waiting-for-postgis-3.2-secure-cloud-raster-access, not going to cover it

ALTER DATABASE gisdb SET postgis.enable_outdb_rasters = true;
ALTER DATABASE gisdb SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';
-- need to reconnect or set your local session
SET postgis.enable_outdb_rasters = true;
SET postgis.gdal_enabled_drivers TO 'ENABLE_ALL';

-- What kind of rasters can you query/output?
SELECT idx, short_name, can_read, can_write, create_options
FROM ST_GdalDrivers()
ORDER BY short_name;

-- PostGIS 3.2 
-- Several raster functions ST_InterpolateRaster, ST_Contour, ST_SetM, ST_SetZ, enhanced ST_Value

-- Build a raster using ST_InterpolateRaster
DROP TABLE IF EXISTS building_density_raster;
CREATE TABLE building_density_raster AS 
WITH a AS (SELECT  geom, ST_SRID(geom) AS srid FROM argentina 
           WHERE name = 'Buenos Aires'
          )
, grid AS (SELECT f.i, f.j, f.geom
FROM a INNER JOIN LATERAL 
    ST_HexagonGrid(10000, geom) AS f ON ST_Intersects(a.geom, f.geom)
    )
, p AS (
    -- theme points buildings
SELECT grid.geom, 
        COUNT(*) AS num_buildings
     FROM buildings AS b INNER JOIN grid ON ST_Intersects(b.geom, grid.geom) 
    GROUP BY grid.geom
),
--SELECT * FROM p
ext AS (
    -- extent of our raster
    SELECT ST_SetSRID(ST_Extent(geom),a.srid) AS geom, a.srid
    FROM a
    GROUP BY srid
)
SELECT -- https://postgis.net/docs/RT_ST_InterpolateRaster.html
      ST_InterpolateRaster(
        -- collect the points and fees as the z-value
        ST_Collect( 
          ST_Force3D(
              p.geom ,
              num_buildings
              )
        ),
   'linear',
    ST_AddBand( 
        ST_MakeEmptyRaster(width => ((ST_XMax(ext.geom) - ST_XMin(ext.geom))/1000)::integer,
                           height => ((ST_YMax(ext.geom) - ST_YMin(ext.geom))/1000)::integer, 
                           upperleftx => ST_XMin(ext.geom),
                           upperlefty => ST_YMax(ext.geom),
                           scalex => 1000,
                           scaley => - 1000,
                           skewx => 0, skewy =>0, 
                           srid => ext.srid)
        , '16BSI' )
  ) AS rast
FROM p, ext
GROUP BY ext.geom, ext.srid;

-- spot check if covers the right area
SELECT ST_Transform(rast::geometry,4326)
FROM building_density_raster;

-- 2111 buildings in this area
SELECT ST_Value(rast, ST_Transform(ST_Point(-58.382, -34.603,4326),6932)  )
FROM building_density_raster;


-- Another cool new raster feature in 3.2 building contours
-- https://blog.crunchydata.com/blog/waiting-for-postgis-3.2-st_contour-and-st_setz

-- Exporting data into various formats
-- Can take a whole row as of 3.1
SELECT ST_AsGeoJSON(a)::json
FROM (SELECT name, geom FROM argentina) AS a ;

-- PostGIS 3.1 aggregate into a single feature collection
SELECT json_build_object('type', 'FeatureCollection', 'features', 
    json_agg(ST_AsGeoJSON(r.*)::json) )
FROM (SELECT name, geom FROM argentina) AS r;

-- Lots of other output functions, ST_AsFlatGeoBuf new in PostGIS 3.2
/** ST_AsEncodedPolyline — Returns an Encoded Polyline from a LineString geometry.
ST_AsFlatGeobuf — Return a FlatGeobuf representation of a set of rows.
ST_AsGeobuf — Return a Geobuf representation of a set of rows.
ST_AsGeoJSON — Return a geometry as a GeoJSON element.
ST_AsGML — Return the geometry as a GML version 2 or 3 element.
ST_AsKML — Return the geometry as a KML element. Several variants. Default version=2, default maxdecimaldigits=15
ST_AsLatLonText — Return the Degrees, Minutes, Seconds representation of the given point.
ST_AsMVTGeom — Transform a geometry into the coordinate space of a Mapbox Vector Tile.
ST_AsMVT — Aggregate function returning a Mapbox Vector Tile representation of a set of rows.
ST_AsSVG — Returns SVG path data for a geometry.
ST_AsTWKB — Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
ST_AsX3D — Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
ST_GeoHash — Return a GeoHash representation of the geometry.
**/



