A-Zero

# 开源许可

  • BSD许可：可以将修改后的代码作为开源或者专有软件再发布
  • GPL许可：不允许修改后的代码做为闭源的商业软件发布和销售

# 安装和使用

下载地址 (opens new window)

  • 端口号默认：5432
  • 超级管理员：postgres
  • 在安装PostGIS前首先必须安装PostgreSQL，然后再安装好的Stack Builder中选择安装PostGIS组件
  • 如果找不到postGIS或下载失败，需要单独下载 (opens new window)与postgresql匹配的postGIS
  • 最好选择"Create spatial database"，以便在创建数据库时可以以此作为模板
  • PostGIS安装目录需要和postgresql的安装目录一致，版本对应关系 (opens new window)
  • 如果提示“找不到文件linintl-9.dll”，在postgresql/11/bin/postgisgui目录下，复制一份libintl-8.dll副本，改名为libintl-9.dll即可

# 远程访问（Windows）

  • 修改配置文件/pgsql/data/pg_hba.conf，在IPV4部分添加新的一行
host all all 0.0.0.0/0 scram-sha-256
  • 重启服务：postgresql-64-14

# 远程访问（Linux）

  • 修改配置文件postgresql.conf
vim /var/lib/pgsql/14/data/postgresql.conf

#listen_addresses='localhost'
#port = 5432
listen_addresses='*'
port = 5432
  • 修改配置文件/pgsql/data/pg_hba.conf，同上
  • 重启服务：postgresql-64-14

注意

Docker环境安装postgres+postgis扩展，直接docker安装postgis就可以

docker run --name postgis_postgres 
           --restart=always 
		   -e POSTGRES_USER=postgres 
		   -e POSTGRES_PASSWORD=123456 
		   -e POSTGRES_DBNAME=gis_db 
		   -p 5432:5432 
		   -v /data/postgres/postgis/data:/var/lib/postgis/data 
		   -v /data/postgres/postgresql/data:/var/lib/postgresql/data 
		   -d corpusops/postgis:10-2.4

# 修改时区

  • 编辑postgresql.conf文件，查找timezone关键字
  • 将timezone = 'Etc/UTC'修改成timezone = 'PRC'
  • 再查询下一个timeout，都改成PRC
  • 最后保存重启

# 修改数据库端口

# 在文件 postgres/postgresql.conf
port = 5878

# pgAdmin4的使用

  • PostgreSQL默认会创建一个二维数据库（postgres），一个空间数据库（postgis_33_sample）
  • 使用PostGIS Bundle 3 for PostgreSQL Shapefile and DBF Loader Exporter 导入导出 .shp文件

注意

  • 导入文件一定不要放在中文目录下
  • 一定要设置SRID值
  • Arcgis 中添加数据库连接时，数据库名称一定要设置为小写的，否则报错

# 数据库操作

数据类型 (opens new window)
常用函数 (opens new window)
日期/时间函数 (opens new window)

# 类型区别

  • character varying和character的区别
# character varying(n) / varchar(n)：变长，有长度限制
# character(n) / char(n)           ：定长，不足补空白
  • nvarchar和varchar的区别
# varchar是非Unicode可变长度类型，nvarchar是Unicode可变长度类型
# varchar的长度是1和8000之间，nvarchar的长度是1和4000之间
# 如果存储内容是英文字符建议使用varchar，含有汉字的使用nvarchar

# 模式（SCHEMA）

一个模式可以包含表、视图、索引、数据类型、函数和操作符等

  • 允许多个用户使用一个数据库并且不会互相干扰
  • 将数据库对象组织成逻辑组以便更容易管理
-- 接下来我们连接到 runoobdb 来创建模式 myschema：
runoobdb=# create schema myschema;
-- 输出结果 "CREATE SCHEMA" 就代表模式创建成功

-- 接下来我们再创建一个表格
runoobdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

# 三种自增列sequence，serial，identity区别

  • 字段类型SERIAL会自动创建序列:表名_字段名_seq，MAXVALUE=9223372036854775807，其它值为1
create table test(
	id serial primary key,
	age int
)
  • identity本质是为了兼容标准sql语法而新加的，修复了一些serial的缺陷，比如无法通过alter table的方式实现增加或者删除serial字段，是serial的"增强版"，更适合作为"自增列"使用
CREATE TABLE users (  
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  
    name VARCHAR(100)  
);
-- identity自增列的重置表或者更改
ALTER TABLE myschema.users ALTER COLUMN id RESTART WITH 100;
  • sequence在所有数据库中的性质都一样，它是跟具体的字段不是强绑定的，其特点是支持多个对象之间共享，无特殊需求的情况下，作为最次选
MAXVALUE：9223372036854775807
nextval('test_id_seq'::regclass)
  • 共同特点是都会创建序列，无法将自增值更新为表中的最大Id，一定要手动重置为表的最大Id
-- 查询 YourTable 表使用的序列
SELECT pg_get_serial_sequence('YourTable', 'ID');

-- 查询序列 YourTable_ID_seq 的最大值
SELECT last_value FROM YourTable_ID_seq;

# 变量赋值

注意：冒号和等号之间没空格

DECLARE
    my_variable1 INTEGER;  -- 声明变量
	my_variable2 TEXT;
BEGIN
    my_variable1 := 10;    -- 赋值
	my_variable2 := 'Hello, World!';
END

# 判断是否为 NULL

-- IS NULL / IS NOT NULL
IF my_order_num IS NULL THEN
	my_order_num = 1;
ELSE 
	my_order_num = my_order_num + 1;
END IF;

# 修改表字段

  • 修改表字段数据类型
-- table_name 为要修改的表名，column_name 为要修改的字段名，new_data_type 为新设定的数据类型
ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_data_type;
  • 修改表字段名称
-- table_name 为要修改的表名，old_name 为原始字段名，new_name 为新的字段名
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
  • 删除表字段
-- table_name 为要修改的表名，column_name 为要删除的字段名
ALTER TABLE table_name DROP COLUMN column_name;

# 拼接SQL语句

-- 使用字符串连接操作符 || 来拼接 SQL 语句
sql_query := 'SELECT column1, column2 FROM table_name WHERE ' || column_name;

# SELECT 赋值

  • 直接赋值到变量
-- 声明变量
DECLARE max_salary NUMERIC;

-- 将最大薪资赋给变量
SELECT MAX(salary) INTO max_salary FROM employees;
  • 将一个表中的列赋给一个记录变量
-- 声明记录变量
DECLARE max_value RECORD;

-- 使用 SELECT INTO 将结果集赋给变量
SELECT MAX(column_name) INTO max_value FROM table_name;

-- 使用
max_value.column_name
  • 两种表复制语句都可以用来复制表与表之间的数据
INSERT INTO Table2(field1,field2,…) SELECT value1,value2,… FROM Table1

SELECT vale1, value2 INTO Table2 FROM Table1

# 创建字段唯一约束

创建字段唯一约束

# 获取当前时间

-- 带时区带毫秒
SELECT now()
SELECT current_timestamp

-- 不带时区带毫秒
SELECT now()::timestamp

-- 不带时区不带毫秒
SELECT now()::timestamp(0)

# 时间格式化函数

  • to_char函数： to_char函数用于将时间戳转换为字符串
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS.US');
-- 输出格式：2021-07-22 10:54:15.20347
  • to_date函数： to_date函数用于将字符串转换为日期
SELECT to_date('2021-07-22', 'YYYY-MM-DD'); 
-- 输出格式：2021-07-22
  • to_timestamp函数： to_timestamp函数用于将字符串转换为时间戳
SELECT to_timestamp('2021-07-22 10:54:15', 'YYYY-MM-DD HH24:MI:SS'); 
-- 输出格式：2021-07-22 10:54:15+8
  • extract函数： Extract函数用于从时间戳中提取特定的日期或时间部分，例如小时、分钟或月份
SELECT extract(year FROM myTimestamp) FROM myTable

# 生成UUID

--将显示当前安装的扩展列表
SELECT * FROM pg_extension;
--安装 pgcrypto 扩展
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
--返回一个随机的 UUID
SELECT gen_random_uuid();

# 循环语句的使用

  • 使用 LOOP：LOOP 是一种无限循环，通常与 EXIT 语句结合使用来结束循环
DO $$
DECLARE
    i INT := 1;
BEGIN
    LOOP
        RAISE NOTICE 'Current value: %', i;
        IF i >= 10 THEN
            EXIT;  -- 退出循环
        END IF;
        i := i + 1;  -- 增加计数
    END LOOP;
END $$;
  • 使用 WHILE：WHILE 循环在条件为真时执行
DO $$
DECLARE
    i INT := 1;
BEGIN
    WHILE i <= 10 LOOP
        RAISE NOTICE 'Current value: %', i;
        i := i + 1;  -- 增加计数
    END LOOP;
END $$;
  • 使用 FOR 循环：FOR 循环可以遍历数值范围或集合
-- 数值范围的 FOR 循环
DO $$
BEGIN
    FOR i IN 1..10 LOOP
        RAISE NOTICE 'Current value: %', i;
    END LOOP;
END $$;

-- 遍历查询结果的 FOR 循环
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT id, name FROM your_table LOOP
        RAISE NOTICE 'ID: %, Name: %', rec.id, rec.name;
    END LOOP;
END $$;

# 自定义函数

-- 创建find_in_set函数
CREATE OR REPLACE FUNCTION find_in_set(value anyelement, string_list text)
    RETURNS bool
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
	IF string_list = '' THEN 
		RETURN false;
	ELSE
  		RETURN array_position(string_to_array(string_list, ','), value::TEXT)>0;
	END IF;
END;
$BODY$;

# 游标的使用

  • 静态游标（cursor）：不能修改里面的语句
CREATE OR REPLACE FUNCTION retrieve_employees() RETURNS VOID AS $$
DECLARE 
   my_cursor CURSOR FOR SELECT gid FROM public.table_pts;
   cur_gid BIGINT;
   cur_dept RECORD;
BEGIN
   OPEN my_cursor;
   
   LOOP
      FETCH NEXT FROM my_cursor INTO cur_gid;
      EXIT WHEN NOT FOUND;

      SELECT code INTO cur_dept FROM public.map_xiang WHERE 
	  ST_Contains(geom,(select geom::geometry from public.table_pts where gid=cur_gid));
	
      IF cur_dept.code IS NOT NULL THEN
	     UPDATE public.table_pts SET map_xiang=cur_dept.code WHERE gid=cur_gid;
	  END IF;
          
      -- 处理数据
      RAISE NOTICE 'code: %', cur_dept.code;
   END LOOP;

   CLOSE my_cursor;
END;
$$ LANGUAGE plpgsql;

注意

使用 fetch cur 和 fetch next from cur 一样，因为NEXT 是 FETCH 的默认选项

  • 动态游标（refcursor）：可以多次open和close，指定不同的SQL
CREATE OR REPLACE FUNCTION retrieve_employees() RETURNS VOID AS $$
DECLARE 
   my_cursor REFCURSOR;
   cur_gid BIGINT;
   cur_dept RECORD;
BEGIN
   OPEN my_cursor FOR SELECT * FROM public.table_pts;
   FETCH my_cursor INTO cur_dept;
 
   SELECT code INTO cur_dept FROM public.map_xiang WHERE 
   ST_Contains(geom,(select geom::geometry from public.table_pts where gid=cur_gid));

   IF cur_dept.code IS NOT NULL THEN
	 UPDATE public.table_pts SET map_xiang=cur_dept.code WHERE gid=cur_gid;
   END IF;
	  
   -- 处理数据
   RAISE NOTICE 'code: %', cur_dept.code;
   
   END LOOP;
   CLOSE my_cursor;
END;
$$ LANGUAGE plpgsql;
  • 隐式游标：DML和for操作，都在内部转换为了一个隐式游标在执行
CREATE OR REPLACE FUNCTION retrieve_employees() RETURNS VOID AS $$
DECLARE 
   cur_gid BIGINT;
   cur_dept RECORD;
BEGIN
   FOR cur_dept IN (SELECT * FROM public.table_pts) loop;
 
   SELECT code INTO cur_dept FROM public.map_xiang WHERE 
   ST_Contains(geom,(select geom::geometry from public.table_pts where gid=cur_gid));

   IF cur_dept.code IS NOT NULL THEN
	 UPDATE public.table_pts SET map_xiang=cur_dept.code WHERE gid=cur_gid;
   END IF;
	  
   -- 处理数据
   RAISE NOTICE 'code: %', cur_dept.code;
   
   END LOOP;
   CLOSE my_cursor;
END;
$$ LANGUAGE plpgsql;
  • 调用创建的函数
SELECT retrieve_employees();

注意

Definition中的Language一定要选择plpgsql

# 跨库操作

  • 在当前数据库和目标端数据库（需要跨库查询的数据库）分别创建dblink
create extension if not exists dblink;
  • 创建dblink成功后，进行操作测试
-- 查询
SELECT * FROM dblink ('host=localhost port=5432 dbname=test user=postgres password=123456',
            'select user_id,account from piedss_biz.sys_user' ) AS T ( ID TEXT, NAME TEXT);
	
-- 新增
SELECT dblink_exec ('host=localhost port=5432 dbname=test user=postgres password=123456',
              'INSERT INTO public.sys_user(user_id,account) VALUES(''12'',''ericfrq'')');

-- 将库A的数据查询出来后直接插入存库B
INSERT INTO userinfo (ID, true_name, username) SELECT * FROM dblink 
('host=localhost port=5432 dbname=test user=postgres password=123456',
'SELECT sys_user.user_id,sys_user.real_name,sys_user.account FROM piedss_biz.sys_user' ) 
AS T (ID TEXT, true_name TEXT, username TEXT);

-- 修改
SELECT dblink_exec ('host=localhost port=5432 dbname=test user=postgres password=123456',
'UPDATE public.sys_user SET account=''eric'',password=''1qazWSX'' WHERE  user_id=''12''');

-- 删除
SELECT dblink_exec ('host=localhost port=5432 dbname=test user=postgres password=123456',
'DELETE FROM public.sys_user WHERE user_id=''12'' AND create_user=''dms_datahub''' );
  • 如果不想每一次都写完整的dblink连接信息，可以先起别名
-- 起别名
select dblink_connect('bieming', 'host=localhost port=5432 dbname=piedssdb_sort user=postgres password=qQq314159@26');
-- 进行操作
SELECT dblink_exec ( 'bieming', 'INSERT INTO sys_user(user_id,account,password) VALUES(''1'',''ericfrq'',''1qazWSX'')');
-- 关闭连接
SELECT dblink_disconnect('bieming');

# 导出表结构信息

select 
a.attname AS "列名",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as "类型",
d.description AS "备注"
from pg_class c, pg_attribute a , pg_type t, pg_description d 
where  c.relname = 'dict' -- 这里是你的表名
and a.attnum>0 
and a.attrelid = c.oid 
and a.atttypid = t.oid 
and  d.objoid=a.attrelid
and d.objsubid=a.attnum
ORDER BY c.relname DESC,a.attnum ASC

# 在有进程连接到数据库时，对应的数据库是不允许被删除的

-- 先断开连接到这个数据库上的所有链接
SELECT pg_terminate_backend(pg_stat_activity.pid) 
FROM pg_stat_activity
WHERE datname='mydb' 
AND pid <> pg_backend_pid();

-- 再删除数据库
DROP DATABASE mydb;

参数说明：

pg_terminate_backend # 用来终止与数据库的连接的进程id的函数
pg_stat_activity # 是一个系统表，用于存储服务进程的属性和状态
pg_backend_pid() # 是一个系统函数，获取附加到当前会话的服务器进程的ID

# 触发器的使用

变量 描述
NEW 数据类型是record，在insert、update操作触发时存储新的数据行
OLD 数据类型是record，在update、delete操作触发时存储旧的数据行
TG_OP "INSERT"，"UPDATE"，"DELETE"，"TRUNCATE"
TG_TABLE_NAME 触发器所在表的表名称
TG_SCHEMA_NAME 触发器所在表的模式
-- 1、先创建触发器函数
CREATE OR REPLACE FUNCTION update_data()
RETURNS TRIGGER AS $$
DECLARE                     -- 在函数中同时定义多个变量，使用 DECLARE 语句
    count_var INTEGER;      -- 定义员工数量变量
    salary_var NUMERIC;     -- 定义平均工资变量
BEGIN
    NEW.some_column := NEW.some_column * 2; -- 假设我们要将some_column的值翻倍
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2、创建触发器
CREATE TRIGGER data_update_trigger
BEFORE INSERT OR UPDATE ON your_table_name
FOR EACH ROW
EXECUTE FUNCTION update_data();

-- 1、先创建触发器函数
create or replace function msg_file_record_fun() 
returns trigger as $$
	BEGIN
		IF TG_OP = 'INSERT' then
		INSERT INTO "public"."msg_file_record"("message_id", "file_url", "update_time") 
		VALUES (new.message_id, new.file_url, to_char(now(),'YYYYMMDD'));
		ELSIF TG_OP = 'UPDATE' then
		INSERT INTO "public"."msg_file_record"("message_id", "file_url", "update_time") 
		VALUES (new.message_id, new.file_url, to_char(now(),'YYYYMMDD'));
		ELSIF TG_OP = 'DELETE' then
		INSERT INTO "public"."msg_file_record"("message_id", "file_url","update_time") 
		VALUES (old.message_id, old.file_url, to_char(now(),'YYYYMMDD'));
		END IF;
		return new;
	END;
$$
LANGUAGE plpgsql;

-- 2、创建触发器
-- drop trigger msg_file_trigger on msg_file;
create trigger msg_file_trigger after insert or delete or update on msg_file for each row
execute function msg_file_record_fun();

# 查询结果转换为json数组

-- 所有数据转为 json
select array_to_json(array_agg(row_to_json(t))) from (SELECT * FROM test) t

-- 一行数据转为 json  tbl_res_disposal_report为表名
select row_to_json(tbl_res_disposal_report) from tbl_res_disposal_report

# 查看单个数据库的数据量

-- 示例 SQL，需要将 "your_database_name" 替换为实际的数据库名
SELECT pg_database_size('your_database_name');

-- 执行上述 SQL 语句，返回的结果单位是字节，将字节转换为 MB
SELECT pg_database_size('your_database_name') / 1024 / 1024 AS size_in_mb;

# PostGIS中的几何类型

  • PostGIS 是PostgreSQL的一个扩展，目的是使PostgreSQL支持空间数据的存储和使用，其本质类似于ArcSDE和Oracle Spatial Extension
  • PostGIS支持所有OGC规范的Simple Features类型，同时还扩展了对3DZ、3DM、4D坐标的支持

# OGC的WKB和WKT格式

OGC定义了两种描述几何对象的格式，分别是WKB（Well-Known Binary）和WKT（Well-Known Text）

//在SQL语句中，用以下的方式可以使用WKT格式定义几何对象：
POINT(0 0) //点
LINESTRING(0 0,1 1,1 2) //线
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) //面
MULTIPOINT(0 0,1 2) //多点
MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4)) //多线
MULTIPOLYGON(((0 0,4 0,4 4,0 4),(1 1,2 1,2 2,1 2)), ((-1 -1,-1 -2,-2 -2,-2 -1)) //多面
GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4))) //几何集合

//以下语句可以使用WKT格式插入一个点要素到一个表中
INSERT INTO table ( SHAPE, NAME )
VALUES ( GeomFromText('POINT(116.39 39.9)', 4326), '北京');

# EWKT、EWKB和Canonical格式

  • EWKT和EWKB相比OGC WKT和WKB格式主要的扩展有3DZ、3DM、4D坐标和内嵌空间参考支持
  • Canonical格式是16进制编码的几何对象，直接用SQL语句查询出来的就是这种格式
//以下以EWKT语句定义了一些几何对象：
POINT(0 0 0) //3D点
SRID=32632;POINT(0 0) //内嵌空间参考的点
POINTM(0 0 0) //带M值的点
POINT(0 0 0 0) //带M值的3D点
SRID=4326;MULTIPOINTM(0 0 0,1 2 1) //内嵌空间参考的带M值的多点

//以下语句可以使用EWKT格式插入一个点要素到一个表中
INSERT INTO table ( SHAPE, NAME )
VALUES ( GeomFromEWKT('SRID=4326;POINTM(116.39 39.9 10)'), '北京' )

# SQL-MM格式

SQL-MM格式定义了一些插值曲线，这些插值曲线和EWKT有点类似，也支持3DZ、3DM、4D坐标，但是不支持嵌入空间参考

//以下以SQL-MM语句定义了一些插值几何对象：
CIRCULARSTRING(0 0, 1 1, 1 0) //插值圆弧
COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1)) //插值复合曲线
CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1)) //曲线多边形
MULTICURVE((0 0, 5 5),CIRCULARSTRING(4 0, 4 4, 8 4)) //多曲线
MULTISURFACE(CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1)),((10 10, 14 12, 11 10, 10 10),(11 11, 11.5 11, 11 11.5, 11 11))) //多曲面

# PostGIS空间信息处理

# 元数据表

为了符合Simple Features for SQL（SFSQL (opens new window)）规范，PostGIS提供了两张表用于追踪和报告数据库中的几何图形（这两张表中的内容相当于元数据）：

  • patial_ref_sys表：定义了数据库已知的OGC规范空间参考
# srid存放的就是空间参考的Well-Known ID：4326
# srtext存放的是以字符串描述的空间参考
# proj4text存放的则是以字符串描述的PROJ.4 投影定义（PostGIS使用PROJ.4实现投影）
  • geometry_columns表（实际上是视图）：存放了当前数据库中所有几何字段的信息
# f_table_schema字段表示的是空间表所在的模式
# f_table_name字段表示的是空间表的表名
# f_geometry_column字段表示的是该空间表中几何字段的名称
# srid字段表示的是该空间表的空间参考

# 在PostGIS中创建一个空间数据库

  • 直接使用postgis_33_sample作为模板创建空间数据库或者添加postgis扩展
--将显示当前安装的扩展列表
select * from pg_extension;
--添加postgis扩展，使之成为支持空间类型的空间数据库
create extension postgis;

--当然可以继续添加其他的空间扩展
create extension postgis_topology;
create extension address_standardizer;
create extension fuzzystrmatch;
create extension postgis_tiger_geocoder;

--移除扩展
drop extension postgis;
  • 到这里我们的数据库已经支持空间数据下面我们创建表，并进行数据插入
--以下先在public模式下创建一个名为cities的一般表
create table public.cities (id int4, name varchar(20))
--再给cities添加一个名为shape的几何字段（二维点）
select AddGeometryColumn('public', 'cities', 'shape', 4326, 'POINT', 2)
--删除几何字段
SELECT DropGeometryColumn('public', 'cities', 'shape')

--直接创建
create table test(id int,geo geometry)
--插入数据
insert into test(id,geo) values (1,point(12.32232442,43.2324535)::geometry)
insert into test(id,geo) values (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326))
--查询数据
select st_astext(geo) from test

# PostGIS对几何信息的检查

PostGIS可以检查几何信息的正确性，这主要是通过ST_IsValid函数实现的

-- 以下语句分辨检查了2个几何对象的正确性
-- 显然(0, 0)点和(1,1)点可以构成一条线，但是(0, 0)点和(0, 0)点则不能构成
-- 这个语句执行以后的得出的结果是TRUE，FALSE

select ST_IsValid('LINESTRING(0 0, 1 1)'), ST_IsValid('LINESTRING(0 0,0 0)')

默认PostGIS并不会使用ST_IsValid函数检查用户插入的新数据，因为这会消耗较多的CPU资源（特别是复杂的几何对象）。当你需要使用这个功能的时候，你可以使用以下语句为表新建一个约束：

ALTER TABLE cities
ADD CONSTRAINT geometry_valid
CHECK (ST_IsValid(shape))

这时当我们往这个表试图插入一个错误的空间对象的时候，会得到一个错误：

INSERT INTO test.cities ( shape, name )
VALUES ( GeomFromText('LINESTRING(0 0,0 0)', 4326), '北京');
-- 错误信息
ERROR: new row for relation "cities" violates check constraint "geometry_valid"
SQL 状态: 23514

# PostGIS中的空间索引

  • 数据库对多维数据的存取有两种索引方案，BTree和GiST（Generalized Search Tree）
  • 在PostgreSQL中的GiST比BTree的健壮性更好，因此PostGIS对空间数据的索引一般采用GiST实现
  • PostGIS 扩展利用了 GIST 索引来优化空间查询的性能
-- 以下的语句给sde模式中的cities表添加了一个空间索引shape_index_cities
-- 在pgAdmin中也可以通过图形界面完成相同的功能：表名 -> indexes
CREATE INDEX shape_index_cities ON sde.cities USING gist (shape)

注意

空间索引只有在进行基于边界范围的查询时才起作用

# PostGIS中的常用函数

PostGIS 3.3.0dev 手册 (opens new window)
许多函数是以ST_[X]yyy形式命名的，事实上也可以通过xyyy的形式访问，这两种函数定义完全一样

# OGC标准函数

# 几何对象构造函数

# ST_GeometryType(text, srid) # 获取几何对象的类型
SELECT ST_GeometryType(ST_GeomFromText('POINT(1 1)')) # ST_Point
SELECT ST_GeometryType(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)')) # ST_LineString
SELECT ST_GeometryType(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))')) # ST_Polygon

# ST_GeomFromText(text, srid)

# ST_PointFromText(text, srid) 表示从输入文本创建一个 geometry 类型的几何对象
SELECT ST_AsText(ST_PointFromText('POINT(10 20)', 4326)) AS geom
SELECT point::geometry # 将point类型转化为geometry类型

# ST_GeomFromEWKB(bytea)
# ST_MakePoint(, , [], [])
SELECT ST_MakePoint(10, 20);

# ST_MakePointM(, , )
# ST_MakeBox2D(, )
# ST_MakeBox3D(, )
# ST_MakeLine(geometry, geometry)
SELECT ST_MakeLine(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POINT(1 1)')))

# ST_MakeLine(geometry[] geomArray)
SELECT ST_MakeLine(ARRAY[
    ST_GeomFromText('POINT(0 0)'),
    ST_GeomFromText('POINT(1 1)'),
    ST_GeomFromText('POINT(2 0)')
])

# ST_LineFromMultiPoint(multipoint)
# ST_MakePolygon(linestring, [linestring[]]) # 输入的线串必须是闭合的，且至少包含三个点
SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(0 0, 0 5, 5 5, 5 0, 0 0)'))

# ST_BuildArea(geometry)
# ST_Polygonize(geometry set)
# ST_Collect(geometry set)
# ST_Collect(geometry, geometry)
# ST_Dump(geometry)
# ST_DumpRings(geometry)

# ST_LineFromText(text, srid)
# ST_LinestringFromText(text, srid)
# ST_PolyFromText(text, srid)
# ST_PolygonFromText(text, srid)
SELECT ST_PolygonFromText('POLYGON((121.743 37.59,121.743 37.59,121.743 37.59))',4326)

# ST_MPointFromText(text, srid)
# ST_MLineFromText(text, srid)
# ST_MPolyFromText(text, srid)
# ST_GeomCollFromText(text, srid)
# ST_GeomFromWKB(bytea, srid)
# ST_GeometryFromWKB(bytea, srid)
# ST_PointFromWKB(bytea, srid)
# ST_LineFromWKB(bytea, srid)
# ST_LinestringFromWKB(bytea, srid)
# ST_PolyFromWKB(bytea, srid)
# ST_PolygonFromWKB(bytea, srid)
# ST_MPointFromWKB(bytea, srid)
# ST_MLineFromWKB(bytea, srid)
# ST_MPolyFromWKB(bytea, srid)
# ST_GeomCollFromWKB(bytea, srid)
# ST_BdPolyFromText(text WKT, integer srid)
# ST_BdMPolyFromText(text WKT, integer srid)

其中 :: 为postgre的强转符号，以下语句等价

SELECT CAST('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113)' AS geometry);
SELECT ('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113)'::geometry);
SELECT ST_GeomFromEWKT('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113)');

# 几何对象关系函数

# 获取两个几何对象间的距离：ST_Distance(geometry1, geometry2)
# 地理坐标系单位是度，需要乘111195（地球半径6371000*PI/180）是将值转化为米
SELECT ST_Distance(ST_GeomFromText('POINT(1 2)',4326), ST_GeomFromText('POINT(1 2)',4326)
SELECT ST_Distance(point(32.32,43.23)::geometry, point(42.32,53.23)::geometry)
# 根据经纬度点计算在地球曲面上的距离，单位是米
SELECT ST_DistanceSphere(point(32.32,43.23)::geometry, point(42.32,53.23)::geometry)

# 两个几何对象的距离是否在给定值范围内：ST_DWithin(geometry, geometry, float)
# 如下，检查点是否在指定半径内
CREATE INDEX idx_locations_geom ON locations USING GIST (geom) # 使用索引加速查询
SELECT * FROM locations WHERE ST_DWithin(geom, ST_GeomFromText('POINT(1 2)'), 10)

# 判断两个几何对象是否相等：ST_Equals(geometry, geometry)
# 查找与指定建筑物几何相同的其他建筑物
SELECT * FROM buildings WHERE ST_Equals(geom, (SELECT geom FROM buildings)) AND id <> 1


# 判断两个几何对象是否分离：ST_Disjoint(geometry, geometry)
# 判断两个几何对象是否相交：ST_Intersects(geometry, geometry)
SELECT * FROM places WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((0 0, 0 5, 5 5))'));

# 获取两个几何对象相交的部分：ST_Intersection(geometry, geometry)
# 获取两个几何对象不相交的部分（A、B可互换） ST_SymDifference(geometry A, geometry B)
# 判断两个几何对象的边缘是否接触：ST_Touches(geometry, geometry)
# 判断两个几何对象是否互相穿过：ST_Crosses(geometry, geometry)
# 判断A是否被B包含：ST_Within(geometry A, geometry B)
# 判断两个几何对象是否是重叠：ST_Overlaps(geometry, geometry)
# 判断A是否包含B：ST_Contains(geometry A, geometry B)
# 判断A是否覆盖B：ST_Covers(geometry A, geometry B)
# 判断A是否被B所覆盖：ST_CoveredBy(geometry A, geometry B)
# 从A去除和B相交的部分后返回：ST_Difference(geometry A, geometry B)
# 返回两个几何对象的合并结果：ST_Union(geometry, geometry)
# 返回一系列几何对象的合并结果：ST_Union(geometry set)

# 几何对象处理函数

# 获取几何对象的WKT描述：ST_AsText(geometry)
# 获取几何对象的WKB描述：ST_AsBinary(geometry)
# 获取几何对象的空间参考ID：ST_SRID(geometry) 
# 给几何对象设置空间参考：ST_SetSRID(geometry, integer)
UPDATE my_table SET geom = ST_SetSRID(geom, 4326);
# 转化到指定空间参考（转换坐标系）：ST_Transform(geometry,integer)
# 注意：ST_Transform和ST_SetSRID用法的不同
SELECT ST_Transform(geom,4326) from map_grid where gid=44401977
# 获取几何对象的维数：ST_Dimension(geometry)
# 获取几何对象的边界范围：ST_Envelope(geometry)

# 判断几何对象是否为空：ST_IsEmpty(geometry)
# 判断几何对象是否不包含特殊点（比如自相交）：ST_IsSimple(geometry)
# 判断几何对象是否闭合：ST_IsClosed(geometry)
# 判断曲线是否闭合并且不包含特殊点：ST_IsRing(geometry)

# 获取多几何对象中的对象个数：ST_NumGeometries(geometry)
# 获取多几何对象中第N个对象：ST_GeometryN(geometry,int)
# 获取几何对象中的点个数：ST_NumPoints(geometry)
# 获取几何对象的第N个点：ST_PointN(geometry,integer)

# 获取点的X坐标：ST_X(geometry)
# 获取点的Y坐标：ST_Y(geometry)
# 获取点的Z坐标：ST_Z(geometry)
# 获取点的M值：ST_M(geometry)
# 获取几何对象的中心：ST_Centroid(geometry)
SELECT ST_Centroid(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'))

# 获取线的终点：ST_EndPoint(geometry)
# 获取线的起始点：ST_StartPoint(geometry)
SELECT ST_StartPoint(ST_GeomFromText('LINESTRING(0 0, 10 10, 20 0)'))

# 返回线串的坐标数量：ST_NPoints(geometry)
# 长度量测：ST_Length(geometry)
# 2D对象长度：ST_length2d(geometry)
# 3D对象长度：ST_length3d(geometry)
# 线分段：ST_Segmentize(geometry, maxlength)
# 合并为线：ST_LineMerge(geometry)

# 面积量测：ST_Area(geometry)
# 返回多边形中环的数量（通常为1个，其他是孔）：ST_NRings(geometry)
# 以线串的形式返回多边形最外面的环：ST_ExteriorRing(geometry)
# 获取多边形内边界个数：ST_NumInteriorRings(geometry)
# 获取多边形顶点：ST_DumpPoints
SELECT (ST_DumpPoints(geom)).geom AS point_geom FROM geometries WHERE id = 1
# 获取多边形顶点的数组
SELECT id, array_agg(ST_AsText(geom)) AS vertex_array
FROM (
    SELECT id, (ST_DumpPoints(geom)).geom
    FROM geometries
    WHERE id = 1
) AS subquery
GROUP BY id
# 获取geojson每个coordinates的坐标点
SELECT fishnet.gid,x,y,
ST_X(vertex_array[1]) AS x1,ST_Y(vertex_array[1]) AS y1,
ST_X(vertex_array[2]) AS x2,ST_Y(vertex_array[2]) AS y2,
ST_X(vertex_array[3]) AS x3,ST_Y(vertex_array[3]) AS y3,
ST_X(vertex_array[4]) AS x4,ST_Y(vertex_array[4]) AS y4 
FROM pcs_fishnet_clip2 fishnet RIGHT JOIN (
    SELECT gid, array_agg(geom) AS vertex_array FROM ( 
	       SELECT gid, (ST_DumpPoints(geom)).geom FROM pcs_fishnet_clip2
    ) AS subquery GROUP BY gid
) point ON point.gid = fishnet.gid


# 以线串形式返回指定的内部环：ST_InteriorRingN(geometry,integer)
# 返回所有环的长度：ST_Perimeter(geometry)

# 返回曲面上的一个点：ST_PointOnSurface(geometry)
# 以确保获取到的点始终位于面内或边界上，避免了质心计算可能带来的问题
SELECT ST_PointOnSurface(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'))

# 获取边界：ST_Boundary(geometry)
# 获取缓冲后的几何对象：ST_Buffer(geometry, double, [integer])
# 获取包围几何对象所有点的最小凸多边形或凸多面体：ST_ConvexHull(geometry)

# 生成正方形格网数据：ST_SquareGrid(size,bounds) ，3857时size单位为米，4826时为经纬度
SELECT (ST_SquareGrid(1000, ST_Transform(a.geom, 3857))).* FROM country;
# 为地区生成1度栅格
WITH grid AS (SELECT (ST_SquareGrid(1, ST_Transform(geom,4326))).* FROM public.map_sheng)
SELECT ST_AsText(geom) FROM grid;
# 只生成接触到该地区的正方形
WITH grid AS (SELECT (ST_SquareGrid(1, ST_Transform(geom,4326))).* FROM public.map_sheng)
SELECT * FROM grid WHERE ST_Intersects(geom,(SELECT geom FROM public.map_sheng));

# 生成蜂窝格网数据：ST_HexagonGrid(size,bounds)，用法同ST_SquareGrid

# PostGIS扩展函数

# 管理函数

# Geos：GEOS库
# Jts：JTS库
# Proj：PROJ4库 postgis_version()

# postgis_lib_version()
# postgis_lib_build_date()
# postgis_script_build_date()
# postgis_scripts_installed()
# postgis_scripts_released()
# postgis_geos_version()
# postgis_jts_version()
# postgis_proj_version()
# postgis_uses_stats()
# postgis_full_version()

# 几何操作符

A范围=B范围 A = B
A范围覆盖B范围或A范围在B范围左侧 A &<> B
A范围在B范围左侧 A <<>> B
A范围覆盖B范围或A范围在B范围下方 A &<| B A范围覆盖B范围或A范围在B范围上方 A |&> B
A范围在B范围下方 A <<| B A范围在B范围上方 A |>> B
A=B A ~= B
A范围被B范围包含 A @ B
A范围包含B范围 A ~ B
A范围覆盖B范围 A && B

# 几何对象输出

参考语义：
NDR：Little Endian
XDR：big-endian
HEXEWKB：Canonical
SVG：SVG 格式
GML：GML 格式
KML：KML 格式
GeoJson：GeoJson 格式

# ST_AsBinary(geometry,{'NDR'|'XDR'})
# ST_AsEWKT(geometry)
# ST_AsEWKB(geometry, {'NDR'|'XDR'})
# ST_AsHEXEWKB(geometry, {'NDR'|'XDR'})
# ST_AsSVG(geometry, [rel], [precision])
# ST_AsGML([version], geometry, [precision])
# ST_AsKML([version], geometry, [precision])
# ST_AsGeoJson([version], geometry, [precision], [options])
SELECT id, ST_AsGeoJSON(geom) AS geojson FROM your_table;

# 几何对象编辑

# 给几何对象添加一个边界，会使查询速度加快：ST_AddBBOX(geometry)
# 删除几何对象的边界：ST_DropBBOX(geometry)

# 添加点：ST_AddPoint(linestring, point, [])
SELECT ST_AddPoint(ST_GeomFromText('LINESTRING(0 0,1 1,2 2)'),ST_GeomFromText('POINT(1 1.5)'))

# 删除点：ST_RemovePoint(linestring, offset)
# 设置点：ST_SetPoint(linestring, N, point)

# 几何对象类型转换
# ST_Force_collection(geometry)、ST_Force_2d(geometry)、ST_Force_3d(geometry)
# ST_Force_3dz(geometry)、ST_Force_3dm(geometry)、ST_Multi(geometry)

# 对几何对象作偏移：ST_Translate(geometry, float8, float8, float8)
# 对几何对象作缩放：ST_Scale(geometry, float8, float8, float8)
# 对3D几何对象作旋转：
# ST_RotateZ(geometry, float8)、ST_RotateX(geometry, float8)、ST_RotateY(geometry, float8)
# 对2D对象作偏移和缩放：ST_TransScale(geometry, float8, float8, float8, float8)
# 对2D对象作反转：ST_Reverse(geometry)

# 将几何对象的坐标网格化或者按照指定的网格大小进行调整：ST_SnapToGrid(geometry, size)
# 将点几何对象(100.4, 40.7) 按照网格大小0.5进行调整
# 其坐标将会被调整为 (100.5, 41.0)，因为 0.4 和 0.7 分别向下取整到最接近的 0.5 的倍数
SELECT ST_AsText(ST_SnapToGrid(ST_SetSRID(ST_MakePoint(100.4, 40.7), 4326), 0.5))
# 在 x 方向和 y 方向上使用不同的网格大小
SELECT ST_AsText(ST_SnapToGrid(ST_SetSRID(ST_MakePoint(100.4, 40.7), 4326),ARRAY[0.5, 0.2]))

← HighGoDB →