PostGis -基础、Springboot 整合、电子围栏处理

目的： 为什么要用PostgreSQL?

• 因为有时候我们需要存储 空间数据，如：存储一个 多边形 到数据。
• PostGis中 geometry、geography ：基本空间数据类型，用于表达点线面等空间要素，具体类型涵盖了OGC的简单对象模型
• 

说明：PostGIS是PostgreSQL对象关系数据库的空间数据库扩展。它增加了对地理对象的支持，允许在SQL中运行位置查询。

特点：

• PostGIS是在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力的开源空间数据库，空间数据库像存储和操作数据库中其他任何对象一样去存储和操作空间对象。
• 它是PostgreSQL的扩展插件，在PostgreSQL的core（存储、检索、事务等）层之上实现GIS层的功能，它不能独立于PostgreSQL运行。

常见名词：

• WKT(Well-known text) 是开放地理空间联盟OGC（Open GIS Consortium ）制定的一种文本标记语言，用于表示矢量几何对象、空间参照系统及空间参照系统之间的转换
  • POINT(1 2)
  • 常见描述： Point 点 、LineString 线、 Polygon 多边形 …> - WKB(well-known binary) 是WKT的二进制表示形式，解决了WKT表达方式冗余的问题，便于传输和在数据库中存储相同的信息> - "01020000800200000097E5880801845C404D064F3AF4AE36400000000000000000290A915F01845C40DC90B1A051AE36400000000000000000"> - pg库中 geometry 类型字段，默认存储的就是wkb
• GeoJson ：{[“type": “Point", “coordinates”: [30, 10]}

常见安装：

官网下载地址：
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

扩展插件，空间数据处理 postgis安装
https://www.postgresql.org/ftp/postgis/
http://download.osgeo.org/postgis/windows/pg10/

docker安装

version: "3.1"
services:postgresql:image: postgres:12-alpinecontainer_name: postgresqlenvironment:POSTGRES_DB: postgresPOSTGRES_USER: "root"POSTGRES_PASSWORD: "root"ports:- 5432:5432volumes:- ./data:/var/lib/postgresql/data

常见函数

-- 创建扩展函数
CREATE EXTENSION postgis;--  读取wkt字符串为geometry
ST_GeomFromText（ST_GeometryFromText）--  转换为wkt
ST_AsText
--  转换为geojson
ST_AsGeoJSON

转换函数

• 运行这个sql https://gitcode.net/mirrors/geocompass/pg-coordtransform/-/blob/master/geoc-pg-coordtransform.sql

-- 坐标系转换
-- WGS84坐标系：地球坐标系，国际通用坐标系
-- GCJ02坐标系：火星坐标系，WGS84坐标系加密后的坐标系；Google国内地图、高德、QQ地图 使用
-- BD09坐标系：    百度坐标系，GCJ02坐标系加密后的坐标系
-- CGCS坐标系：国家2000大地坐标系-- WGS84转GCJ02
select ST_AsText(geoc_wgs84togcj02(geom)) from md_geom_space-- GCJ02转WGS84
select geoc_gcj02towgs84(geom) from md_geom_space-- WGS84转BD09
select geoc_wgs84tobd09(geom) from md_geom_space-- BD09转WGS84
select geoc_bd09towgs84(geom) from md_geom_space-- CGCS2000转GCJ02
select geoc_cgcs2000togcj02(geom) from md_geom_space-- GCJ02转CGCS2000
select geoc_gcj02tocgcs2000(geom) from md_geom_space-- CGCS2000转BD09
select geoc_cgcs2000tobd09(geom) from md_geom_space-- BD09转CGCS2000
select geoc_bd09tocgcs2000(geom) from md_geom_space-- GCJ02转BD09
select geoc_gcj02tobd09(geom) from md_geom_space-- BD09转GCJ02
select geoc_bd09togcj02(geom) from md_geom_space

代码整合

pom

		<dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>42.3.8</version></dependency><!-- GeoTools --><dependency><groupId>org.geolatte</groupId><artifactId>geolatte-geom</artifactId><version>1.6.0</version></dependency><!-- https://mvnrepository.com/artifact/org.geolatte/geolatte-geojson --><dependency><groupId>org.geolatte</groupId><artifactId>geolatte-geojson</artifactId><version>1.6.0</version></dependency><dependency><groupId>net.postgis</groupId><artifactId>postgis-jdbc</artifactId><version>2.5.0</version></dependency><dependency><groupId>org.geotools</groupId><artifactId>gt-main</artifactId><version>28-SNAPSHOT</version></dependency><dependency><groupId>org.geotools</groupId><artifactId>gt-geojson</artifactId><version>28-SNAPSHOT</version></dependency><!-- geotools的远程库 --><repositories><repository><id>osgeo</id><name>OSGeo Release Repository</name><url>https://repo.osgeo.org/repository/release/</url><snapshots><enabled>false</enabled></snapshots><releases><enabled>true</enabled></releases></repository><repository><id>osgeo-snapshot</id><name>OSGeo Snapshot Repository</name><url>https://repo.osgeo.org/repository/snapshot/</url><snapshots><enabled>true</enabled></snapshots><releases><enabled>false</enabled></releases></repository></repositories>

mybatisplus 自定义类型处理

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgis.Geometry;
import org.postgis.PGgeometry;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;@MappedTypes({String.class})
public class MyGeometryTypeHandler extends BaseTypeHandler<String> {@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {PGgeometry pGgeometry = new PGgeometry(parameter);Geometry geometry = pGgeometry.getGeometry();//坐标系geometry.setSrid(4326);ps.setObject(i, pGgeometry);}@Overridepublic String getNullableResult(ResultSet rs, String columnName) throws SQLException {PGgeometry pGgeometry = new PGgeometry(rs.getString(columnName));return pGgeometry.getValue().replace("SRID=4326;","");}@Overridepublic String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {PGgeometry pGgeometry = new PGgeometry(rs.getString(columnIndex));return pGgeometry.toString();}@Overridepublic String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {PGgeometry pGgeometry = new PGgeometry(cs.getString(columnIndex));return pGgeometry.toString();}
}

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.typeHandler.MyGeometryTypeHandler;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;
import java.io.Serializable;/*** xxx** @author heyonghao* @date 2023/7/17*/
@TableName(autoResultMap = true)
@ApiModel(value = "电子围栏")
@Accessors(chain = true)
@Data
public class GeomSpace implements Serializable {@ApiModelProperty(value = "主键")private Long id;//对应数据库 geometry 类型@ApiModelProperty(value = "围栏geo参数- 返回wkt")@TableField(typeHandler = MyGeometryTypeHandler.class)private String geom;@ApiModelProperty(value = "电子围栏名称")private String title;@ApiModelProperty(value = "其它组成参数 eg: 圆 半径 {\\\"radius\\\":0.00030345730927194836,\\\"radiusTrue\\\":29.068128024579657}")private String other;}

新增空间类型数据

@ApiModel(value = "围栏点位参数")
@Accessors(chain = true)
@Data
public class GeoJsonVo implements Serializable {@ApiModelProperty(value = "类型： POINT、MULTIPOINT、LINESTRING、MULTILINESTRING、POLYGON、MULTIPOLYGON")private String type;@ApiModelProperty(value = "点位 eg: [ [\"106.29384466232433\",\"31.2648657606391\"] ]")private List<List<String>> coordinates;public String getGeomStr(){String coordinate="SRID=4326;%s(%s)";String collect = this.coordinates.stream().map(s -> s.stream().map(String::valueOf).collect(Collectors.joining(" "))).collect(Collectors.joining(","));coordinate=String.format(coordinate,this.getType(),collect);return coordinate;}}

@ApiModel(value = "围栏新增VO")
@Data
public class MdGeomSpaceSaveVo implements Serializable {@ApiModelProperty(value = "围栏信息")private GeomSpace mdGeomSpace;@ApiModelProperty(value = "围栏点位信息")private GeoJsonVo geoJsonVo;}

@ApiOperation(value = "添加围栏")
@PostMapping("/save")
public R<Boolean> save(@RequestBody MdGeomSpaceSaveVo geomSpaceSaveVo){GeoJsonVo geoJsonVo = geomSpaceSaveVo.getGeoJsonVo();String geomStr = geoJsonVo.getGeomStr();GeomSpace mdGeomSpace = geomSpaceSaveVo.getMdGeomSpace();mdGeomSpace.setGeom(geomStr);mdGeomSpace.setId(IdUtil.getSnowflakeNextId());boolean save = mdGeomSpaceService.save(mdGeomSpace);return R.ok(save);
}

电子围栏：

• 用一个虚拟的栅栏围出一个虚拟地理边界；
• 在地图上 圈出一个圆形范围、或多边形范围；绑定特点规则，如 超速、滞留、偏离、聚集等，做出相应告警
• 传入空间数据，存入PG库

表：

-- ----------------------------
DROP TABLE IF EXISTS "public"."geom_space";
CREATE TABLE "public"."geom_space" ("id" int8 NOT NULL DEFAULT nextval('testgeomobj_id_seq'::regclass),"geom" geometry(GEOMETRY) NOT NULL,"title" varchar(255) COLLATE "pg_catalog"."default","other" varchar(255) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."geom_space"."id" IS '主键';
COMMENT ON COLUMN "public"."geom_space"."geom" IS '围栏geo参数';
COMMENT ON COLUMN "public"."geom_space"."title" IS '电子围栏名称';
COMMENT ON COLUMN "public"."geom_space"."other" IS '其它组成参数 eg: 圆 半径';-- ----------------------------
-- Primary Key structure for table geom_space
-- ----------------------------
ALTER TABLE "public"."geom_space" ADD CONSTRAINT "md_geom_space_pkey" PRIMARY KEY ("id");-- ----------------------------
DROP TABLE IF EXISTS "public"."geom_space_rule";
CREATE TABLE "public"."geom_space_rule" ("id" int8 NOT NULL,"name" varchar(255) COLLATE "pg_catalog"."default","type" int2,"rule" varchar(255) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."geom_space_rule"."id" IS '主键';
COMMENT ON COLUMN "public"."geom_space_rule"."name" IS '规则名称';
COMMENT ON COLUMN "public"."geom_space_rule"."type" IS '规则类型 1-超速 2-聚焦 3-规矩偏离 4-滞留';
COMMENT ON COLUMN "public"."geom_space_rule"."rule" IS '规则JSON';-- ----------------------------
-- Records of geom_space_rule
-- ----------------------------
INSERT INTO "public"."geom_space_rule" VALUES (1685206843057606656, '超速', 1, '{"speed":60,"duration":10}');-- ----------------------------
-- Primary Key structure for table geom_space_rule
-- ----------------------------
ALTER TABLE "public"."geom_space_rule" ADD CONSTRAINT "geom_space_rule_pkey" PRIMARY KEY ("id");-- ----------------------------
DROP TABLE IF EXISTS "public"."geom_space_bind";
CREATE TABLE "public"."geom_space_bind" ("id" int8 NOT NULL,"space_id" int8,"rule_id" int8
)
;
COMMENT ON COLUMN "public"."geom_space_bind"."id" IS '主键';
COMMENT ON COLUMN "public"."geom_space_bind"."space_id" IS '围栏id';
COMMENT ON COLUMN "public"."geom_space_bind"."rule_id" IS '规则id';-- ----------------------------
-- Records of geom_space_bind
-- ----------------------------
INSERT INTO "public"."geom_space_bind" VALUES (1685206981247340544, 1685205995128717312, 1685206843057606656);-- ----------------------------
-- Primary Key structure for table geom_space_bind
-- ----------------------------
ALTER TABLE "public"."geom_space_bind" ADD CONSTRAINT "geom_space_bind_pkey" PRIMARY KEY ("id");