postgis函数功能记录

1.通用功能

• 添加postgis扩展
  CREATE EXTENSION postgis;
• 查看当前所在库
  SELECT current_database();
• 查看当前数据库下的所有 schema
  SELECT schema_name
  FROM information_schema.schemata;
• 查看某个schema中所有表
  SELECT table_name
  FROM information_schema.tables
  WHERE table_schema = ‘ch09’;
• 查询数据库的编码
  SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = ‘postgres’;

2.特定功能的SQL

• 转为完整的json，前端调用
• 用json_build_object、jsonb_agg等函数，处理mass_test表

select json_build_object('type','FetureCollection','features',jsonb_agg(st_asgeojson(mt.*)::json))
from mass_test mt

• 获取图形边界范围的坐标

select st_extent(geom) from mass_test mt

• 获取几何图形的中心

select st_centroid(geom) from mass_test mt

• 合并多边形

select st_union(geom) from mass_test mt

3.项目实用功能SQL

3.1修改坐标系统SRID以及将所有线要素长度存入新列

1.转换坐标系,将EPSG从4326转换为102039
ALTER TABLE polylines
ALTER COLUMN geom
TYPE geometry(LINESTRINGZ, 4326)
USING ST_SetSRID(geom,4326);
1.1验证是否执行成功
SELECT srid
FROM geometry_columns
WHERE f_table_name = 'polylines' AND f_geometry_column = 'geom';
2.Postgres里在表中新建或删除列
2.1在表中加一新列line_len
ALTER TABLE polylines 
ADD COLUMN len_in_m double precision;
2.2删除某一不想要的列
ALTER TABLE polylines 
DROP COLUMN line_len;
3.PostGIS里计算几何要素长度，并存入某列
UPDATE polylines  
SET len_in_m = ST_LengthSpheroid(geom, 'SPHEROID["WGS 84",6378137,298.257223563]');

3.2创建设备点的矢量数据

CREATE TABLE device_points AS
SELECT
  row_number() OVER () AS id,
  (ST_DumpPoints(ST_GeneratePoints(
    ST_Buffer(geom::geography, 2500)::geometry, 1))).geom AS geom
FROM tower;

3.3从数据库查询语句，将mass_test替换为对应表名, 加上查询语句

select json_build_object('type','FetureCollection','features',jsonb_agg(st_asgeojson(mt.*)::json))
from "ZYYZT_JCGL_XHXT_CYXHGJ" mt where "编号" = 'N-A-B-GS-4'

3.4根据起始经纬度和终止经纬度生成线要素，起始经纬度和终止经纬度在同一行

SELECT ST_MakeLine(
    ST_SetSRID(ST_MakePoint(起始经度, 起始纬度), 4326),
    ST_SetSRID(ST_MakePoint(终止经度, 终止纬度), 4326)
) AS line
FROM 防火阻隔系统调查表 ;

将上面生成的线要素，插入到原表中

	-- 1.在原表中添加新字段
	ALTER TABLE 防火阻隔系统调查表
	ADD COLUMN line_geometry GEOMETRY;
	-- 2.使用 UPDATE 语句填充新字段
	UPDATE 防火阻隔系统调查表
	SET line_geometry = ST_MakeLine(
    ST_SetSRID(ST_MakePoint(起始经度, 起始纬度), 4326),
    ST_SetSRID(ST_MakePoint(终止经度, 终止纬度), 4326)
	);

3.5添加/更新Geometry 字段

- 添加 Geometry 字段
ALTER TABLE tower  ADD COLUMN geometry geometry(Point, 4326);
- 更新 Geometry 字段
UPDATE tower
SET geometry = ST_SetSRID(ST_MakePoint(CAST(jd AS double precision), CAST(wd AS double precision)), 4326);

3.6将这些地理坐标点转换为线矢量数据

数据库表名为xh_rw_road_log,字段road_log中放所有点的经纬度。如下格式：
经度 纬度,经度 纬度… 逗号用英文的

116.543174 39.913452,116.543175 39.913449,116.543171 39.913452,116.64317 39.91345,116.543171 39.513448

1、修改现有表，在其后添加一个几何类型字段
ALTER TABLE xh_rw_road_log ADD COLUMN road_geom GEOMETRY(LineString, 4326);

2、使用ST_MakeLine将地理坐标点字符串转换为线矢量数据，并更新到新添加的字段中
UPDATE xh_rw_road_log
SET road_geom = (
    SELECT ST_MakeLine(geom ORDER BY path) AS geom
    FROM (
        SELECT 
            ST_SetSRID(ST_MakePoint(lon::DOUBLE PRECISION, lat::DOUBLE PRECISION), 4326) AS geom,
            ROW_NUMBER() OVER () AS path
        FROM (
            SELECT 
                CAST(SPLIT_PART(coord, ' ', 1) AS NUMERIC) AS lon,
                CAST(SPLIT_PART(coord, ' ', 2) AS NUMERIC) AS lat
            FROM (
                SELECT UNNEST(string_to_array(road_log, ',')) AS coord
                FROM xh_rw_road_log
            ) AS points
        ) AS coordinates
        WHERE lon IS NOT NULL AND lat IS NOT NULL
    ) AS line
);

3、确认数据已经成功更新
SELECT * FROM xh_rw_road_log;