postgis_practice20201216

postgis1216.sql

SELECT 
	id, ST_BUFFER(geom_centroid, 0.0083333) as geom_buff
FROM 
(SELECT 
   1 as id, 
   ST_SRID(ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326)) as srid,
   st_centroid(ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326)) as geom_centroid,
   ST_GeomFromText('POLYGON((1 1,2 2,2 0,1 1))', 4326) as geom)
AS s;
	      
-- PostGIS in Action ch6.5
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',
        ST_SetSRID(ST_MakePointM(-100.499,28.7015,5), 4326)
    ),
    ('Mr. Whale with Z as depth',
        ST_SetSRID(ST_MakePoint(-100.499,28.7015,0.5), 4326)
    ),
    ('Mr. Whale with M and Z',
        ST_SetSRID(ST_MakePoint(-100.499,28.7015,0.5,5), 4326)
    )
) As x(whale, spot);
     
-- make envelope
     
-- xmin,ymin,xmax,ymax,SRID 
SELECT 
	1 as id, 
	ST_MakeEnvelope(119,21,122.5,26,4326) as geom,
	st_transform(ST_MakeEnvelope(119,21,122.5,26,4326), 3826) as geom_twd97;
-- make polygon from linestring
SELECT 
	1 as id,
	st_setsrid(st_makepolygon(ST_GeomFromText('LINESTRING(121.5 23.5, 121.6 23.6, 121.7 23.8, 121.5 23.5)')), 4326) as geom