postgis 常用 sql 20210707

drop table if exists public."position";

CREATE TABLE public."position" (
	id serial NOT NULL,
	"name" varchar(255) NOT NULL DEFAULT 'unnamed'::character varying,
	geom geometry NOT NULL,
	CONSTRAINT position_pk PRIMARY KEY (id)
);

insert into public."position"(name, geom) values('p1', ST_GeomFromText('POINT(120.0 30.0)', 4326));
insert into public."position"(name, geom) values('p2', ST_GeomFromText('POINT(114.0 30.0)', 4326));

-- 加 gist 索引
CREATE INDEX "xzqh_raw_info_20210426_the_geom_idx" ON "public"."xzqh_raw_info_20210426" USING GIST ("the_geom");

-- 求球面距离 select ST_Distance_Sphere(ST_GeomFromText('POINT(120 30)',4326), ST_GeomFromText('POINT(120.0001 30)',4326));
SELECT id, ST_AsText(geom), ST_AsGeoJson(geom), ST_AsEwkt(geom), ST_X(geom), ST_Y(geom) FROM "position" limit 100;
        
-- 判断是否包含
SELECT ST_Contains(ST_MakePolygon(ST_GeomFromText('LINESTRING ( 121.312350 30.971457 , 121.156783 31.092221 , 121.353250 31.278195 , 121.509125 31.157431 , 121.312350 30.971457 )', 4326)), ST_GeomFromText('POINT(121.332378 31.07106)', 4326));

-- 判断是否在缓冲范围内，区别于ST_Buffer，不建立缓冲区对象
select st_dwithin(st_geomfromtext('POINT(118.62987950798799 29.10533395408949)', 4326), st_geomfromtext('POINT(119.61907950728799 29.10533395402942)', 4326), 0.1); -- 单位是度
select st_dwithin(st_geogfromtext('SRID=4326;POINT(118.62987950798799 29.10533395408949)'), st_geogfromtext('SRID=4326;POINT(119.61907950728799 29.10533395402942)'), 10); -- 单位是米

-- 搜索“双塔街道”面状区域内包含的社区点
select v.* from public."STP_VILLAGE" as v, public."STR_PL_JDXZQY" as jd where jd.tsmc = '双塔街道' and st_contains(jd.the_geom, v.the_geom) = true;

-- 搜索给定的多边形内的数据
select tsmc, the_geom from temp_addr_spatial_info where ST_Contains(st_geomfromtext('SRID=4326;POLYGON((119 29 , 122 29 , 122 32 , 119 32 , 119 29))'), the_geom);

-- 删除表字段
alter table public.placecommon drop column the_geom;

-- 只指定srid，不指定几何形状的几何字段
CREATE TABLE public.geom_test (
	id serial NOT NULL,
	"name" varchar null,
	geom geometry(geometry, 4490) NULL, -- 几何字段，只指定srid，不指定几何形状
	CONSTRAINT geom_test_pk PRIMARY KEY (id)
);

-- 新增几何字段，只指定srid，不指定几何形状，对应insert必须st_geomfromtext('POINT(0 0)', 4326)制定srid
alter table public.placecommon add column the_geom geometry;  -- geometry(point) 可限定几何类型
select updategeometrysrid('public', 'placecommon', 'the_geom', 4326);
-- 查看几何字段SRID
select st_srid(the_geom) from temp_addr_spatial_info;
-- 更改几何字段SRID（不是转换）
select UpdateGeometrySRID('temp_addr_spatial_info', 'the_geom', 4490);

-- 地理坐标转投影坐标，st_transform 中的 from_proj, to_proj 要在 spatial_ref_sys 表中查询
select st_transform(
	st_point(120, 30), 
	'+proj=longlat +ellps=GRS80 +no_defs',  -- EPSG:4490
	'+proj=tmerc +lat_0=0 +lon_0=120 +k=1 +x_0=500000 +y_0=0 +ellps=GRS80 +units=m +no_defs'  -- EPSG:4549
);

-- point 转 multipoint
select ST_Multi(ST_Union(the_geom));

-- 获取 envelope
select st_envelope(the_geom);

-- 面转线
select st_exteriorring(st_geomfromewkt('POLYGON((0 0, 2 0, 1 1, 2 2, 0 2, 0 0))')) as linestring; -- 仅限 polygon
select ST_Boundary(st_geomfromewkt('MULTIPOLYGON(((0 0, 2 0, 1 1, 2 2, 0 2, 0 0)))'));

-- 获取表注释
select relname as tabname,
cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c 
where   relname ='chengzhenjumindian';

-- 球面距离
select 
	st_distancesphere(st_pointfromtext('POINT(120 30)'), st_pointfromtext('POINT(120.1 30.1)')) as "srid=0", 
	st_distancesphere(st_pointfromtext('POINT(120 30)', 4326), st_pointfromtext('POINT(120.1 30.1)', 4326)) as "srid=4326",
	st_distancesphere(st_pointfromtext('POINT(120 30)', 4490), st_pointfromtext('POINT(120.1 30.1)', 4490)) as "srid=4490";

-- 大地椭球体距离，单位为米。 ST_DistanceSpheroid() ，第三个参数（椭球体参数）在 spatial_ref_sys 表中
select ST_DistanceSpheroid(st_geomfromewkt('SRID=4490;POINT(120 30)'), st_geomfromewkt('SRID=4490;POINT(114 30)'), 'SPHEROID["CGCS2000",6378137,298.257222101,AUTHORITY["EPSG","1024"]]');

-- 连表 update ，吴兴区点位点表叠加在吴兴区村社区面表上，把对应社区名添加到社区内的点表字段中
UPDATE public."吴兴区点位" as t1
SET "村社区名称" = (select t2."comname" from public."吴兴区村社区" as t2 where ST_Contains(t2.geom, t1.geom));
-- 下面这句更规范 update set from where
update public."吴兴区点位" as t1
set "村社区名称" = t2."comname"
from public."吴兴区村社区" as t2
where ST_Contains(t2.geom, t1.geom);

-- 包含和完全包含
select 'POLYGON((0 0, 5 0, 0 5, 0 0))'::geometry as "outer", 'POLYGON((0 0, 2 0, 0 2, 0 0))'::geometry as "inner", 
	ST_Contains('POLYGON((0 0, 5 0, 0 5, 0 0))'::geometry, 'POLYGON((0 0, 2 0, 0 2, 0 0))'::geometry) as "含边界的包含",
	ST_ContainsProperly('POLYGON((0 0, 5 0, 0 5, 0 0))'::geometry, 'POLYGON((0 0, 2 0, 0 2, 0 0))'::geometry) as "不含边界的包含（彻底包含）";

-- 连表 update
update public."xzjd" as xzjd
set code = xzqh_tree."xzqh"
from public."xzqh_tree" as xzqh_tree
where length(xzqh_tree."xzqh") = 9 and xzjd."fname" = xzqh_tree."label";

-- 临时建个表
select * from (values (1, 'wang'), (2, 'tang')) as t(id, "name");

-- 创建物化视图，浙江政务服务网行政区划信息表部分 wfs 发布物化视图 20210507
drop materialized view if exists public.xzqh_from_zjzwfw_materialized_view;
CREATE materialized view public.xzqh_from_zjzwfw_materialized_view
AS select 
	id, 
	"name",
	code,
	-- fullname, 
	raw->>'SECTIONTYPE' as sectiontype, 
	(select 
		t."level" 
	from (values ('一级行政区域（省）', 0), ('二级行政区域（市）', 1), ('三级行政区域（县）', 2), ('四级行政区域（乡）', 3), ('五级行政区域（行政村）', 4)) as t("sectiontype", "level")
	where raw->>'SECTIONTYPE' = t."sectiontype" limit 1) as "level",
	geom
from xzqh_from_zjzwfw 
where raw->>'SECTIONTYPE' is not null and geometrytype(geom) != 'POINT' 
limit 100000
with data;  -- This clause specifies whether or not the materialized view should be populated at creation time. 
-- If not, the materialized view will be flagged as unscannable and cannot be queried until REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] [ WITH [ NO ] DATA ] is used.
-- 注意上 unique 索引才能 refresh MATERIALIZED VIEW concurrently public.xzqh_from_zjzwfw_materialized_view
-- CREATE UNIQUE INDEX xzqh_from_zjzwfw_materialized_view_id_idx ON public.xzqh_from_zjzwfw_materialized_view (id);


-- 通过 select 批量插入 insert into ... select * from ...
insert into public.xzqh_tree ("label", "fullname", "xzqh", "sectiontype", "the_geom") select "raw_data"::jsonb->>'NAME', "raw_data"::jsonb->>'FULLNAME', "raw_data"::jsonb->>'CODE', "raw_data"::jsonb->>'SECTIONTYPE', "the_geom" from public.xzqh_raw_info;


-- st_difference 图形做差，逆时针为外多边形，顺时针为内多边形
-- POLYGON ((-1 -1, -1 2, 2 2, 2 -1, -1 -1), (0 0, 1 0, 1 1, 0 1, 0 0))
select 
	st_geomfromtext('POLYGON((-1 -1, -1 2, 2 2, 2 -1, -1 -1))') as a, 
	st_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))') as b,
	st_difference(st_geomfromtext('POLYGON((-1 -1, -1 2, 2 2, 2 -1, -1 -1))'), st_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')) as "a-b";


-- 行转 json
select row_to_json(file_view) from file_view limit 3;
select row_to_json(t) from (select id, "name", children from file_view limit 3) t;  -- 部分字段

-- 行转 json array
select json_agg(row_to_json(fv)) from file_view fv limit 2;

-- jsonb update 新增成员，这两者等价，json 不能
select '{"age": 20}'::jsonb||'{"id": 2, "name": "wang"}'::jsonb;
select '{"age": 20}'||'{"id": 2, "name": "wang"}'::jsonb;

-- json 数组
select '[1, 2, 3]'::jsonb->0, jsonb_array_length('[1, 2]'::jsonb);

-- jsonb_set 替换值
select jsonb_set('{"ui_edit_by": 1}'::jsonb, '{ui_edit_by}', '2'::jsonb);

-- geojson 仅几何
select 'polygon((0 0, 1 0, 1 1, 0 1, 0 0))'::geometry as geom, st_asgeojson('polygon((0 0, 1 0, 1 1, 0 1, 0 0))'::geometry, 9, 8) as geojson; 

-- jsonb 的带属性 geojson ，还有一步到位更好的方法？
select 
	gid, ST_AsGeoJSON(the_geom, 9, 8)::jsonb ||
	jsonb_build_object('properties', jsonb_build_object('gid', gid, 'tsmc', tsmc)) as geojson 
from "STR_LZM" 
limit 2;

-- 转义
select 
	'{"id": 13, "edit_by": "admin\n2"}'::jsonb->>'edit_by',  -- 自动转为换行符
	'abc\ndef',  -- 不转为换行符，直接显示 \n
	e'abc\ndef';  -- 转为换行符


-- st_isvalid 检测几何合法性
-- 关于 Polygon 内外环顺时针逆时针同逆向都属于合法，自相交非法，环在外非法
-- 注意非法 Polygon 也可以存储，前端（leaflet, openlayers）一般也能渲染
-- 前端 openlayers Polygon 用 appendLinearRing 方法增绘内环，内环坐标串必须首位相同闭合！否则虽然能绘制出，但是转 wkt 是错误的
-- ST_IsPolygonCCW — Tests if Polygons have exterior rings oriented counter-clockwise and interior rings oriented clockwise.
-- ST_IsPolygonCW — Tests if Polygons have exterior rings oriented clockwise and interior rings oriented counter-clockwise.
select 
	'POLYGON((0 0,0 5,5 5,5 0,0 0),(-1 1,-1 2,1 2,1 1,-1 1))'::geometry,
	st_isvalid('POLYGON((0 0,0 5,5 5,5 0,0 0),(-1 1,-1 2,1 2,1 1,-1 1))'::geometry) as "是否合法", 
	st_isvalidreason('POLYGON((0 0,0 5,5 5,5 0,0 0),(-1 1,-1 2,1 2,1 1,-1 1))'::geometry) as "原因", 
	st_isvaliddetail('POLYGON((0 0,0 5,5 5,5 0,0 0),(-1 1,-1 2,1 2,1 1,-1 1))'::geometry) as "详情";


-- 字符串 split 方法，注意从 1 开始而非 0
select split_part('3133沪浙线-1', '-', 1), split_part('3133沪浙线-1', '-', 2);


-- array  https://www.postgresql.org/docs/11/functions-array.html
-- 连接
select 
	array[1, 2]||array[3, 4] as a, 
	array_append(array[1, 2, 3], 4) as a2,  -- array_prepend 在头部添加
	array_cat(array[1, 2], array[3, 4]) as a3;

-- 多维长度
select 
	array_length(array[[1, 2, 3], [4, 5, 6]], 1),  -- 2 第一个维度长度是 2
	array_length(array[[1, 2, 3], [4, 5, 6]], 2);  -- 3 第二个维度长度是 3

-- 字符串快速构造
select '{}'::
select
	'{{1, 2}, {3, 4}}'::int[] as a1,
	array_ndims('{{1, 2}, {3, 4}}'::int[]) as a1_dims,  -- 2
	'{{1, 2}, {3, 4}}'::int[][] as a2,  -- int[][] 同上
	array_ndims('{{1, 2}, {3, 4}}'::int[][]) as a2_dims;  -- 2

-- 字符串数组，无需加引号
select 
	array_remove('{dickhead, apple, banana, dickhead, dickhead, it''s \"nasty\"}'::varchar[], 'dickhead'),
	array_remove('{1, 2, 1}'::int[], 1);

-- 时间，是保留小数的
select '2016-05-10 06:02:13.083'::timestamp::varchar, now()::varchar;


--	case 表达式，类似于 mysql 中的 IF(expr,v1,v2)
--	CASE 表达式是一种通用的条件表达式，类似于其它语言中的 if/else 语句。
--	
--	CASE WHEN condition THEN result
--	     [WHEN ...]
--	     [ELSE result]
--	end
--	
--	CASE 子句可以用于任何表达式可以存在的地方。
--	condition 是一个返回 boolean 的表达式。
--	如果结果为真，那么 CASE 表达式的结果就是符合条件的 result 。
--	如果结果为假，那么以相同方式搜寻任何随后的 WHEN 子句。
--	如果没有 WHEN condition 为真，那么表达式的结果就是在 ELSE 子句里的 result 。
--	如果省略了 ELSE 子句且没有匹配的条件，结果为 NULL 。
-- 
--	SELECT a,
--	       CASE WHEN a=1 THEN 'one'
--	            WHEN a=2 THEN 'two'
--	            ELSE 'other'
--	       END
--	    FROM test;
--	
--	 a | case
--	---+-------
--	 1 | one
--	 2 | two
--	 3 | other
select 
	case 1 > 0 when true then '对' else '错' end,
	case 4 when 1 then '一' when 2 then '二' when 3 then '三' else '大于三' end;


-- 使用 uuid
-- 创建 uuid-ossp 扩展
create extension "uuid-ossp";  -- https://www.postgresql.org/docs/11/uuid-ossp.html

drop table if exists public.sample;
CREATE TABLE public.sample (
	id varchar NOT NULL DEFAULT uuid_generate_v4(),
	"name" varchar NULL,
	CONSTRAINT sample_pk PRIMARY KEY (id)
);

INSERT INTO public.sample
("name")
VALUES('ydkvs') returning "id";

select * from public.sample limit 200;