postgresql postgis常用sql 记录

json查询---------------------------------------------------------------------------------------------------------------------------
SELECT
jsonb_build_object(‘type’,‘FeatureCollection’,‘features’,jsonb_agg(feature))
FROM
(
SELECT
jsonb_build_object(‘type’,‘Feature’,‘properties’,to_jsonb(row)) AS feature
FROM
(
select
A.origin_xq,strength_grade,B.geom
from
(SELECT
origin_xq,sum(strength_grade) as strength_grade
from
mobile_phone_od
where
(destination_xq=‘2501’ or destination_xq=‘2502’) and hour=‘0’ and (origin_xq !=‘2501’ and origin_xq !=‘2502’)
GROUP BY
origin_xq
) A,xiaoqupoint B
where
A.origin_xq = B.xqcode
ORDER BY
strength_grade DESC)row
)features

数据库坐标系定义-------------------------------------------------------------------------------------------------------------------------------------
SELECT UpdateGeometrySRID(‘rail_station_2020’,‘geom’,4326);
更新数据库,将矢量数据列由二维变更为三维----
ALTER TABLE “GJLW_guihua”
ALTER COLUMN geom TYPE geometry(MULTILINESTRING)
USING ST_Force_2D(geom)

列转行------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE
xq_cnts_avgwd
AS
SELECT xqid,citycode
,sum(CASE WHEN hour=‘0’ THEN avg_ctns END) as t0
,sum(CASE WHEN hour=‘1’ THEN avg_ctns END) as t1
,sum(CASE WHEN hour=‘2’ THEN avg_ctns END) as t2
,sum(CASE WHEN hour=‘3’ THEN avg_ctns END) as t3
,sum(CASE WHEN hour=‘4’ THEN avg_ctns END) as t4
,sum(CASE WHEN hour=‘5’ THEN avg_ctns END) as t5
,sum(CASE WHEN hour=‘6’ THEN avg_ctns END) as t6
,sum(CASE WHEN hour=‘7’ THEN avg_ctns END) as t7
,sum(CASE WHEN hour=‘8’ THEN avg_ctns END) as t8
,sum(CASE WHEN hour=‘9’ THEN avg_ctns END) as t9
,sum(CASE WHEN hour=‘10’ THEN avg_ctns END) as t10
,sum(CASE WHEN hour=‘11’ THEN avg_ctns END) as t11
,sum(CASE WHEN hour=‘12’ THEN avg_ctns END) as t12
,sum(CASE WHEN hour=‘13’ THEN avg_ctns END) as t13
,sum(CASE WHEN hour=‘14’ THEN avg_ctns END) as t14
,sum(CASE WHEN hour=‘15’ THEN avg_ctns END) as t15
,sum(CASE WHEN hour=‘16’ THEN avg_ctns END) as t16
,sum(CASE WHEN hour=‘17’ THEN avg_ctns END) as t17
,sum(CASE WHEN hour=‘18’ THEN avg_ctns END) as t18
,sum(CASE WHEN hour=‘19’ THEN avg_ctns END) as t19
,sum(CASE WHEN hour=‘20’ THEN avg_ctns END) as t20
,sum(CASE WHEN hour=‘21’ THEN avg_ctns END) as t21
,sum(CASE WHEN hour=‘22’ THEN avg_ctns END) as t22
,sum(CASE WHEN hour=‘23’ THEN avg_ctns END) as t23

FROM
jyy_s_xq1_cnts_a_ageg1_avgwd
GROUP BY
xqid,citycode

postgrsql修改自增序列----------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE “config” (
“id” int4 NOT NULL DEFAULT nextval(‘config_id_seq’::regclass),
…
);
postgrsql 修改创建表之后的序列（添加自增序列）------------------------------------------------------------------------------------------------------------------

----删除前先解除 id 对该序列的依赖
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT null;
DROP SEQUENCE IF EXISTS sequence_name;
---- id_max 即 id 目前的最大值，可写为1，可通过 “SELECT MAX(id) FROM tablename” 得到
CREATE SEQUENCE sequence_name START WITH id_max;
ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval(‘sequence_name’::regclass);

postgrsql数据整库导入导出-----------------------------------------------------------------------------------------------------------------------------------------------
导出数据库
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)>/data/dum.sql

导入数据库
psql -U postgres pgtest < /dum.sql
postgres （用户名)
pgtest (数据库名称)

创建扩展
CREATE EXTENSION postgis;

CREATE EXTENSION postgis SCHEMA public VERSION “3.0.0”;

增加地理要素字段
SELECT AddGeometryColumn (‘test1’, ‘the_geom’, 4326, ‘POINT’, 2);

创建序列
DROP SEQUENCE if EXISTS “控规导则202001_smid_seq”;
CREATE SEQUENCE “public”.“控规导则202001_smid_seq”
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

计算两点间距离
SELECT st_distance(st_geographyfromtext((select st_astext(geom) from xq_center_gf where xq=‘1860’)), st_geographyfromtext((select st_astext(geom) from xq_center_gf where xq=‘5105’)))
update xq1_xq2
set “length”=st_distance(st_geographyfromtext((select st_astext(geom) from xq_center_gf where xq=xq1)), st_geographyfromtext((select st_astext(geom) from xq_center_gf where xq=xq2)))
计算线长
st_length(ST_GeographyFromText(st_astext(ST_intersection(t.geom,ST_MakeValid(ST_GeomFromGeoJSON(’""" + geom + “”"’))))),true)

计算面的面积
st_Area(st_geographyFromText(st_astext(ST_MakeValid(ST_GeomFromGeoJSON(’""" + geom + “”"’)))))

添加ID序列自增序列
ALTER TABLE “修改公交时间各比例” ADD COLUMN id SERIAL PRIMARY KEY

设置已有字段为自增长字段
CREATE SEQUENCE tablename_seq START WITH 1;
ALTER TABLE “tablename”
alter COLUMN id set DEFAULT nextval(‘tablename_seq’::regclass)

通过x、y坐标更新点
update “20210428_station”
set geom=st_pointfromtext(concat(‘point (’,x,’ ‘,y,’)’),4326)
where x is not null and y is not null

缓冲区生成
update rail_station_20210528
set buffer =st_geomfromtext(st_AsText(ST_Buffer(geom::geography, 800)::geometry),4326)

面合并
SELECT zqcode,st_union(geom) as geom into guangzhou_zq FROM “guangzhou_xq_zq_dq” group by zqcode