PostGIS - GIS objects

-- Reference: https://postgis.net/docs/manual-2.0/using_postgis_dbmanagement.html


-- OpenGIS objects  

SELECT ST_GeomFromText('POINT(0 0)',0);

SELECT ST_GeomFromText('LINESTRING(0 0,1 1,1 2)',0);

SELECT ST_GeomFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))',0);

SELECT ST_GeomFromText('MULTIPOINT(0 0,1 2)',0);

SELECT ST_GeomFromText('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))',0);

SELECT ST_GeomFromText('MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))',0);

SELECT ST_GeomFromText('GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4))',0);


-- Well-Known Text (WKT) and Well-Known Binary (WKB) form

SELECT ST_AsText(ST_GeomFromText('POINT(0 0)',0));

SELECT ST_AsBinary(ST_GeomFromText('POINT(0 0)',0));


create table gis (

city text,

lon numeric,

lat numeric,

wkt text,

wkb bytea

);


insert into gis (city,lon,lat) values ('Hong Kong',114.109497,22.396427);


alter table gis add column geom geometry(POINT,4326);

update gis set geom = ST_SETSRID(ST_POINT(lon,lat),4326);

select geom from gis;


update gis set wkt = ST_AsText(geom);

update gis set wkb = ST_AsBinary(geom);


update gis set geom = ST_GeomFromWKB(wkb,4326);

select geom from gis;

update gis set geom = ST_GeomFromText(wkt,4326);

select geom from gis;

update gis set geom = ST_GeometryFromText(wkt,4326);

select geom from gis;


drop table gis;


-- PostGIS EWKT and EWKB

SELECT 'POINT(0 0 0)'::geometry;

SELECT 'SRID=32632;POINT(0 0)'::geometry;

SELECT 'POINTM(0 0 0)'::geometry;

SELECT 'POINT(0 0 0 0)'::geometry;

SELECT 'SRID=4326;MULTIPOINTM(0 0 0,1 2 1)'::geometry;

SELECT 'MULTILINESTRING((0 0 0,1 1 0,1 2 1),(2 3 1,3 2 1,5 4 1))'::geometry;

SELECT 'POLYGON((0 0 0,4 0 0,4 4 0,0 4 0,0 0 0),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 0))'::geometry;

SELECT 'MULTIPOLYGON(((0 0 0,4 0 0,4 4 0,0 4 0,0 0 0),(1 1 0,2 1 0,2 2 0,1 2 0,1 1 0)),((-1 -1 0,-1 -2 0,-2 -2 0,-2 -1 0,-1 -1 0)))'::geometry;

SELECT 'GEOMETRYCOLLECTIONM( POINTM(2 3 9), LINESTRINGM(2 3 4, 3 4 5) )'::geometry;

SELECT 'MULTICURVE( (0 0, 5 5), CIRCULARSTRING(4 0, 4 4, 8 4) )'::geometry;

SELECT '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)) )'::geometry;

SELECT 'TRIANGLE ((0 0, 0 9, 9 0, 0 0))'::geometry;

SELECT 'TIN( ((0 0 0, 0 0 1, 0 1 0, 0 0 0)), ((0 0 0, 0 1 0, 1 1 0, 0 0 0)) )'::geometry;


select ST_AsEWKT(ST_GeomFromEWKB(ST_AsEWKB(ST_GeomFromEWKT('POINT(1 2 3)'))));