PostGIS常用函数介绍
目录
1.将multipolygon重构为polygon,并转为geojson格式
SELECT gid, st_asgeojson((ST_DUMP(geom)).geom::geometry(Polygon)) AS geom FROM res_deference
查询结果如下图所示:
但是,如果给SQLQuery传入类似 select name::varchar as n from table 包含双冒号的SQL, hibernate会认为是参数, 其中::应该转义才能避过,即SQL语句应该如下:
/**
* 根据条件查询deference
* @param propertyName,propertyValue
* @return
*/
@Query(value="select gid, area, perimeter, ld63272105, ld632721_1, yjfl, srys, chys, bhmj, shape_leng,"
+ " shape_area, year, flag, st_asgeojson((ST_DUMP(geom)).geom \\:\\: geometry(Polygon)) AS geom from res_deference WHERE year LIKE CONCAT('%',?1,'%')",nativeQuery = true)
public List<Object> findDeferenceByCondition(String propertyValue);
2.其他相关函数介绍
(1)几何类型转换函数
wkt转geometry:st_geomfromtext(wkt)
select st_geomfromtext('Point(122 33)')
geometry转wkt:st_astext(geometry)
select st_astext(st_geomfromtext('Point(122 33)'))
geometry转geojson:st_asgeojson(geometry)
select st_asgeojson(st_geomfromtext('Point(122 33)'))
geojson转geometry:st_geomfromgeojson(geojson)
select st_geomfromgeojson(st_asgeojson(st_geomfromtext('Point(122 33)')))
geometry转geohash:st_geohash(geometry)
select st_geohash(st_geomfromtext('Point(116 39)'))
geohash转geometry:st_geomfromgeohash
select st_geomfromgeohash('wwfmzesx7yvjugxr3nzv')
(2)判断两个geometry之间的关系
St_within(geom A,geom B)返回A是否处于B中
St_disjoint(geom A,geom B)返回A是否不在B中
St_intersects(geom A,geom B)返回A是否和B有接触
St_union(geom A,geom B)返回A+B两个几何的合并
St_intersection(geom A,geom B)返回A和B的交集
St_difference(geom A,geom B)返回A与B不相交的部分几何
作者:忘我怎么解
链接:https://www.jianshu.com/p/4714e656ef38
來源:简书
简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。