背景
最近需要完成一个业务是关于地理信息(数据)间各种判断,如距离测算、中心点计算、近点搜索、判断一个元素(点坐标)是否包含在一个元素(多边形)内,mysql在这方面就显得有些力不从心了,所以我们选择使用postgre。特别说明:所有做地图业务相关的,国家规定坐标要经过偏(移)转(换)后再存储,避免泄密!!!本文涉及到的坐标为随机找一个坐标进行偏转,无任何意义。
过(cai)来(keng)人(ji)
create table 时指定主键自增不再是auto_increment,而是serial4,示例如下:
create table tablename (
id serial4 not null, -- 自增id,
geo geometry null, -- 偏转后的地理信息
constraint example_pk primary key (id)
);
如果表已经有了,再指定序列自增,示例如下:
<!--设置序列从1开始以1的步长递增-->
重置表的自增字段,示例如下
在Mysql中直接使用:truncate table tablename 即可让表的自增Id重新从1开始,但PostgreSQL中使用该语句只能清除数据,从上述b中可以看出,PostgreSQL中的自增有单独的序列维护,所以想要重置自增列即要重置序列,正确的脚本如下:
truncate table tablename restart identity;
geometry 数据类型需要扩展
(已安装)扩展组件检测
数据库类型与应用数据类型间如何转换
数据可视化插件安装
几何信息入库后可以安装插件,方便开发者确认数据及格式是否正确,可以通过安装插件来预览数据。因本人使用的是DBeaver,所以相关步骤仅适用该软件,其它的需要自行百度一下呢。
找到面板->数据查看器,配置相应的插件
点击Manage后在弹出来的框中点击+(加号)配置插件(左右单引号不能少):
'https://webrd01.is.autonavi.com/appmaptile?x={x}&y={y}&z={z}&lang=zh_cn&size=1&scale=1&style=8'
选择坐标系和配置的插件名称就可以预览啦
首尾呼应
本文背景中提到要解决几何数据间的关系问题、在设计之初如何确定使用何种数据类型、空间数据如何快速查询等问题,墙裂建议以下三个链接(复制)读一下就当扩展知识也蛮不错的!!!
空间数据查询:
https://blog.csdn.net/wzf1993/article/details/79547037
PostGIS常用函数:
https://blog.csdn.net/business122/article/details/62881905
数据类型选择(Geometry Or Geography)
https://blog.csdn.net/wzf1993/article/details/79547037
P.S:本文主要记录使用PGSQL过程中一些收获,其实还有一些地方与Mysql存在差异,如group_concat、like查询 、(json字符串的获取)json列 -> key值,区分大小写等,这边建议,亲方便的时候试一把呢。
有问题欢迎指出,听说吐槽与关注更配哦!!!
上手并不难,只要你是一个合格的码农,关系型数据库的SQL语法或多或少知道一些,就可以切换了;然后就多多少少会遇到一些问题了,比如我:
create sequence tablename_id_seq start with 1 increment by 1 no minvalue no maxvalue cache 1;
<!--设置序列-->
alter table tablename alter column id set default nextval('tablename_id_seq');
上述a中的DDL执行会失败,因为geo字段指定的数据类型为geometry,需要先在数据库上设置扩展组件,然后再执行,脚本如:
create database database_name;
create extension postgis; -- postgis 为扩展组件名字
如何检测自己的库是否安装postgis扩展,有2种方式——执行内置的地理函数是否报错或SQL查询内置扩展表是否有自己指定的名字
select ST_GeomFromGeoJSON('{"type":"LineString","coordinates":[[113.452151427045,22.9505024012178],[113.451721435413,22.9502232838422]]}') as demo;
说明:内置的函数有很多,可以百度或通过工具提示(本人使用的是DBeaver)随便选择一个ST_开头的,传入正确的参数进行测试
select * from pg_catalog.pg_available_extensions where name='postgis';-- postgis 为扩展组件名字
通过mybatis-generator插件生成后列的类型被指定为OTHER,如下:
<result column="geo" jdbcType="OTHER" property="geo" />
Java的数据类型我用了JSONObject:
private JSONObject geo;
这二者之间肯定要在读写时进行交换,如何解决呢?自然想到第一步弄一个统一的地方进行数据转换:
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.apache.ibatis.type.TypeHandler;
import org.springframework.stereotype.Component;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
(value = JSONObject.class)
(value = {JdbcType.OTHER})
public class JsonObjectTypeHandler implements TypeHandler<JSONObject> {
/**
* 写转换
* @param ps 执行语句
* @param i 对应的列下标
* @param parameter 这里解释代码为什么要定义为JSONObject
* @param jdbcType
* @throws SQLException
*/
public void setParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType) throws SQLException {
if (ps != null) {
ps.setObject(i, JSONUtil.toJsonStr(parameter));
}
}
//查询操作:通过列名转换返回结果
public JSONObject getResult(ResultSet rs, String columnName) throws SQLException {
if(null != rs.getString(columnName)){
return JSONUtil.parseObj(rs.getString(columnName));
}
return null;
}
//查询操作:通过列下标转换返回结果
public JSONObject getResult(ResultSet rs, int columnIndex) throws SQLException {
if(null != rs.getString(columnIndex)){
return JSONUtil.parseObj(rs.getString(columnIndex));
}
return null;
}
//查询操作:存储过程返回结果转换
public JSONObject getResult(CallableStatement cs, int columnIndex) throws SQLException {
if(null != cs.getString(columnIndex)){
return JSONUtil.parseObj(cs.getString(columnIndex));
}
return null;
}
}
程序写数据时需要应用代码组装正确的数据类型,XML中使用合适的postgis内置函数将数据转换存储:
//我这里是构建点 List<Double>
// 也可以构建线 -List<List<Double>>
// 面 -List<List<List<Double>>>
List<Object> geo = new ArrayList<>(1<<1);
geo.add(r.getLongitude());
geo.add(r.getLatitude());
JSONObject geoJson = new JSONObject();
geoJson.set("type","Point"); //Point LINESTRING POLYGON
geoJson.set("coordinates",geo); //根据type构建相应的类型就OK
<insert id="insert" parameterType="com.siri.demo.entity.TableName">
insert into tablename (geo)
values (ST_GeomFromGeoJSON(#{geo,jdbcType=OTHER}))
</insert>
程序读数据时相对来说比较简单,根据业务需要选择合适的内置函数,将数据进行转换,再结果上述定义的统一处理类就可以还原出我们组装的对象了
<select id="selectGEOInfoById" resultMap="BaseResultMap">
SELECT ST_ASGEOJSON(geo)
FROM tablename WHERE id=
</select>