本文共 4633 字,大约阅读时间需要 15 分钟。
主要是postgis空间地理位置计算函数的使用,非常方便。
一:PostGIS的Geometry数据类型
Geometry可以说是PostGIS最重要的一个概念,是“几何体”的意思,由于PostGIS很 好地遵守OGC的”Simple Feature for Specification for SQL”规范,目前支持的几何体类型包 含其实例有: POINT(1 1) MULTIPOINT(1 1, 3 4, -1 3) LINESTRING(1 1, 2 2, 3 4) POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)) MULTIPOLYGON((0 0, 0 1, 1 1, 1 0, 0 0), (5 5, 5 6, 6 6, 6 5, 5 5)) MULTILINESTRING((1 1, 2 2, 3 4),(2 2, 3 3, 4 5)) 而geometry具体表现又有两种形式,一种叫做WKT(Well Known Text)形式,如上面的 例子。或者使用如下SQL语句浏览: select AsText(pt) from test1; 另一种叫做“Canonical Form”形式,看上去是一串古怪的数字,其实是一种增强的十六 进制编码,使用如下SQL语句就可以浏览了:select pt from test1;
更多介绍可以参考:
二、用到的一些函数sql:计算多边形是否包含该point
select ST_Contains(polygon,st_MakePoint(121,61)) from bc_contact where contact_id = 12508;
select st_astext(f.polygon) from bc_contact f where f.contact_id = 12508;计算距离经纬度坐标转化
select
ST_Distance( ST_SetSRID(ST_MakePoint(115.97166453999147,28.716493914230423),4326)::geography, ST_SetSRID(ST_MakePoint(106.00231199774656,29.719258550486572),4326)::geography ), ST_Length( ST_MakeLine( ST_MakePoint(115.97166453999147,28.716493914230423), ST_MakePoint(106.00231199774656,29.719258550486572) )::geography )更多函数可以参考
三、文中几个连接把介绍,以及函数说明的比较详细了,下面主要总结一下spring cloud + jpa的使用总结
1.就是pom中引入的jar包版本号需要注意,版本的兼容问题需要重视
2.Entity重要是使用com.vividsolutions.jts.geom包下处理,声明Geometry类型,这种写法就是hibernate高版本支持,当时查询资料时,这个地方耗时间较多,跟数据库之间的交互。
@Type(type = "jts_geometry") @Column(name = "polygon") private Geometry polygon ;
3.就是Java处理生成Geometry对象方法,Point Polygon(闭合数据)
private GeometryFactory factory = new GeometryFactory(new PrecisionModel(),4326);// 处理4326经纬度 private Gson gson = new Gson(); public BcContactServiceImpl(BcContactRepository bcContactRepository){ this.bcContactRepository = bcContactRepository ; } @Override @Transactional public void addBcContact(BcContactPostgresVo bcv) throws PostgresException { log.info("addBcContact param:{}",bcv); if(bcv == null){ throw new PostgresException(11001, "param must not null");//PARAM_INVALID } BcContact bc = new BcContact(); try { BeanUtils.copyProperties(bc,bcv); bc.setElectFence(gson.toJson(bcv.getCoordinateList())); Point pt = factory.createPoint(new Coordinate(bc.getLon(),bc.getLat())); bc.setPoint(pt); if("1".equals(bcv.getElectFenceType())){//ROUNDNESS //圆形,临时未存到Polygon面中 }else if("2".equals(bcv.getElectFenceType())){//POLYGON //多边形 if(bcv.getCoordinateList()!=null && bcv.getCoordinateList().size()>0){ String wkt = "POLYGON((" ; for(BcContactLocationVo l :bcv.getCoordinateList()){ wkt = wkt + l.getLon()+" "+l.getLat()+"," ; } wkt = wkt + bcv.getCoordinateList().get(0).getLon()+" "+ bcv.getCoordinateList().get(0).getLat(); wkt = wkt + "))"; bc.setPolygon(wktToGeometry(wkt)); } } this.bcContactRepository.save(bc); } catch (Exception e) { log.error("add bcContact exception,exception:{}",e); throw new PostgresException(10002, "service exception");//SERVICE_INTERNELEXCEPTION } } /* 生成postgis的几何类型 */ private Geometry wktToGeometry(String wkt) { WKTReader fromText = new WKTReader(); Geometry geom = null; try { geom = fromText.read(wkt); } catch (ParseException e) { log.info("wktToGeometry ParseException:{}",e); throw new RuntimeException("Not a WKT string:" + wkt); } return geom; }
4.就是repository中使用原生态的sql,以及经纬度::geography时:需要转译\\
@Query(value = "select f from bc_contact f where f.data_end='2' and " + " ST_Distance(ST_SetSRID(f.point,4326)\\:\\:geography," + " ST_SetSRID(ST_MakePoint(:lon,:lat),4326)\\:\\:geography) < :distance ",nativeQuery = true) ListnearbyList(@Param("lon") double lon,@Param("lat") double lat,@Param("distance") double distance) ;
四、菜鸟公司使用的pg的一个介绍链接
五、postgres 的启动
pg_ctl start stop restart 进行pg的启动、停止、重启
需要注意:
1、pg启动不支持root,超级管理员用户启动,
报错信息:pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process.切换到pg当时分配的用户进行启动
2、pg启动需要指定pgdata路径
报错信息:pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.vi ~/.bash_profile 将pgdata的路径进行指定到环境变量中
source /etc/profile
source ~/.bash_profile再执行pg_ctl命令就可以
更多pg的命令:
经过慢慢摸索,排除各种报错,也对地理空间数据库有一个基本了解,上面是基本开发使用的总结。
转载地址:http://hradi.baihongyu.com/