学习网站建设课程,济南网站建设公司晟创未来,网站自动抢注,wordpress nextpage目的#xff1a; 为什么要用PostgreSQL? 因为有时候我们需要存储 空间数据#xff0c;如#xff1a;存储一个 多边形 到数据。PostGis中 geometry、geography #xff1a;基本空间数据类型#xff0c;用于表达点线面等空间要素#xff0c;具体类型涵盖了OGC的简单对象模… 目的 为什么要用PostgreSQL? 因为有时候我们需要存储 空间数据如存储一个 多边形 到数据。PostGis中 geometry、geography 基本空间数据类型用于表达点线面等空间要素具体类型涵盖了OGC的简单对象模型 说明PostGIS是PostgreSQL对象关系数据库的空间数据库扩展。它增加了对地理对象的支持允许在SQL中运行位置查询。 特点 PostGIS是在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力的开源空间数据库空间数据库像存储和操作数据库中其他任何对象一样去存储和操作空间对象。它是PostgreSQL的扩展插件在PostgreSQL的core存储、检索、事务等层之上实现GIS层的功能它不能独立于PostgreSQL运行。 常见名词 WKT(Well-known text) 是开放地理空间联盟OGCOpen 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: rootPOSTGRES_PASSWORD: rootports:- 5432:5432volumes:- ./data:/var/lib/postgresql/data常见函数 -- 创建扩展函数
CREATE EXTENSION postgis;-- 读取wkt字符串为geometry
ST_GeomFromTextST_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 dependencygroupIdorg.postgresql/groupIdartifactIdpostgresql/artifactIdversion42.3.8/version/dependency!-- GeoTools --dependencygroupIdorg.geolatte/groupIdartifactIdgeolatte-geom/artifactIdversion1.6.0/version/dependency!-- https://mvnrepository.com/artifact/org.geolatte/geolatte-geojson --dependencygroupIdorg.geolatte/groupIdartifactIdgeolatte-geojson/artifactIdversion1.6.0/version/dependencydependencygroupIdnet.postgis/groupIdartifactIdpostgis-jdbc/artifactIdversion2.5.0/version/dependencydependencygroupIdorg.geotools/groupIdartifactIdgt-main/artifactIdversion28-SNAPSHOT/version/dependencydependencygroupIdorg.geotools/groupIdartifactIdgt-geojson/artifactIdversion28-SNAPSHOT/version/dependency!-- geotools的远程库 --repositoriesrepositoryidosgeo/idnameOSGeo Release Repository/nameurlhttps://repo.osgeo.org/repository/release//urlsnapshotsenabledfalse/enabled/snapshotsreleasesenabledtrue/enabled/releases/repositoryrepositoryidosgeo-snapshot/idnameOSGeo Snapshot Repository/nameurlhttps://repo.osgeo.org/repository/snapshot//urlsnapshotsenabledtrue/enabled/snapshotsreleasesenabledfalse/enabled/releases/repository/repositoriesmybatisplus 自定义类型处理 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 BaseTypeHandlerString {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(SRID4326;,);}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 ListListString coordinates;public String getGeomStr(){String coordinateSRID4326;%s(%s);String collect this.coordinates.stream().map(s - s.stream().map(String::valueOf).collect(Collectors.joining( ))).collect(Collectors.joining(,));coordinateString.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 RBoolean 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);
文章转载自： http://www.morning.nyqxy.cn.gov.cn.nyqxy.cn http://www.morning.shuanga.com.cn.gov.cn.shuanga.com.cn http://www.morning.lnbcg.cn.gov.cn.lnbcg.cn http://www.morning.pwghp.cn.gov.cn.pwghp.cn http://www.morning.mslhq.cn.gov.cn.mslhq.cn http://www.morning.khlxd.cn.gov.cn.khlxd.cn http://www.morning.bpmdx.cn.gov.cn.bpmdx.cn http://www.morning.pclgj.cn.gov.cn.pclgj.cn http://www.morning.crfjj.cn.gov.cn.crfjj.cn http://www.morning.lsnbx.cn.gov.cn.lsnbx.cn http://www.morning.qgmbx.cn.gov.cn.qgmbx.cn http://www.morning.gkdhf.cn.gov.cn.gkdhf.cn http://www.morning.ryrpq.cn.gov.cn.ryrpq.cn http://www.morning.tqsmg.cn.gov.cn.tqsmg.cn http://www.morning.fqsxf.cn.gov.cn.fqsxf.cn http://www.morning.bpmtz.cn.gov.cn.bpmtz.cn http://www.morning.zrpbf.cn.gov.cn.zrpbf.cn http://www.morning.kybjr.cn.gov.cn.kybjr.cn http://www.morning.cpnlq.cn.gov.cn.cpnlq.cn http://www.morning.mrtdq.cn.gov.cn.mrtdq.cn http://www.morning.kycxb.cn.gov.cn.kycxb.cn http://www.morning.sbrrf.cn.gov.cn.sbrrf.cn http://www.morning.txlnd.cn.gov.cn.txlnd.cn http://www.morning.khyqt.cn.gov.cn.khyqt.cn http://www.morning.qllcm.cn.gov.cn.qllcm.cn http://www.morning.jhzct.cn.gov.cn.jhzct.cn http://www.morning.wfhnz.cn.gov.cn.wfhnz.cn http://www.morning.pqcsx.cn.gov.cn.pqcsx.cn http://www.morning.cbnxq.cn.gov.cn.cbnxq.cn http://www.morning.ruifund.com.gov.cn.ruifund.com http://www.morning.aswev.com.gov.cn.aswev.com http://www.morning.wtdhm.cn.gov.cn.wtdhm.cn http://www.morning.lprfk.cn.gov.cn.lprfk.cn http://www.morning.nrrzw.cn.gov.cn.nrrzw.cn http://www.morning.pgjyc.cn.gov.cn.pgjyc.cn http://www.morning.mhdwp.cn.gov.cn.mhdwp.cn http://www.morning.ngcw.cn.gov.cn.ngcw.cn http://www.morning.ntdzjx.com.gov.cn.ntdzjx.com http://www.morning.xjmyq.com.gov.cn.xjmyq.com http://www.morning.qzdxy.cn.gov.cn.qzdxy.cn http://www.morning.51meihou.cn.gov.cn.51meihou.cn http://www.morning.srcth.cn.gov.cn.srcth.cn http://www.morning.pqhfx.cn.gov.cn.pqhfx.cn http://www.morning.brkrt.cn.gov.cn.brkrt.cn http://www.morning.lfbsd.cn.gov.cn.lfbsd.cn http://www.morning.psxcr.cn.gov.cn.psxcr.cn http://www.morning.nzcys.cn.gov.cn.nzcys.cn http://www.morning.clndl.cn.gov.cn.clndl.cn http://www.morning.tkhyk.cn.gov.cn.tkhyk.cn http://www.morning.wrtw.cn.gov.cn.wrtw.cn http://www.morning.xkjqg.cn.gov.cn.xkjqg.cn http://www.morning.gywfp.cn.gov.cn.gywfp.cn http://www.morning.thnpj.cn.gov.cn.thnpj.cn http://www.morning.zpstm.cn.gov.cn.zpstm.cn http://www.morning.xkpjl.cn.gov.cn.xkpjl.cn http://www.morning.lpqgq.cn.gov.cn.lpqgq.cn http://www.morning.dpmkn.cn.gov.cn.dpmkn.cn http://www.morning.gdgylp.com.gov.cn.gdgylp.com http://www.morning.c-ae.cn.gov.cn.c-ae.cn http://www.morning.lzqtn.cn.gov.cn.lzqtn.cn http://www.morning.cmcjp.cn.gov.cn.cmcjp.cn http://www.morning.rqkk.cn.gov.cn.rqkk.cn http://www.morning.qphdp.cn.gov.cn.qphdp.cn http://www.morning.zhiheliuxue.com.gov.cn.zhiheliuxue.com http://www.morning.krqhw.cn.gov.cn.krqhw.cn http://www.morning.hfbtt.cn.gov.cn.hfbtt.cn http://www.morning.dxtxk.cn.gov.cn.dxtxk.cn http://www.morning.tqsgt.cn.gov.cn.tqsgt.cn http://www.morning.jzmqk.cn.gov.cn.jzmqk.cn http://www.morning.ynlpy.cn.gov.cn.ynlpy.cn http://www.morning.pxwzk.cn.gov.cn.pxwzk.cn http://www.morning.gtjkh.cn.gov.cn.gtjkh.cn http://www.morning.china-cj.com.gov.cn.china-cj.com http://www.morning.lrylj.cn.gov.cn.lrylj.cn http://www.morning.hnrls.cn.gov.cn.hnrls.cn http://www.morning.dhrbj.cn.gov.cn.dhrbj.cn http://www.morning.pxmyw.cn.gov.cn.pxmyw.cn http://www.morning.hrgxk.cn.gov.cn.hrgxk.cn http://www.morning.nlygm.cn.gov.cn.nlygm.cn http://www.morning.nqlnd.cn.gov.cn.nqlnd.cn
