postgis操作

PostGIS导入导出shp的注意事项

1.数据导入之前需要给需要导入的数据库增加空间扩展功能。

　　　　CREATE EXTENSION postgis   // 启用PostGIS

　　　　CREATE EXTENSION postgis_topology // 启用拓扑

　　　　CREATE EXTENSION fuzzystrmatch // Tiger所需的模糊匹配

　　　　CREATE EXTENSION postgis_tiger_geocoder // 启用美国Tiger Geocoder

2.当往postgis导入shp文件的时候，忘记指定srid时，默认是0，此时可以通过这个函数更改数据的坐标系：

SELECT UpdateGeometrySRID(‘表名’,'geom’,26918);

3.查询SRID

select st_srid(geom) from table;

 4.查询geom字段信息

select ST_AsText(geom) from 'table_name'

5. 数据库单个表的大小，单位（text）；

--数据库中单个表的大小（不包含索引） 把以字节计算的数值转换成一个人类易读的尺寸单位

select pg_size_pretty(pg_relation_size('"GA_P"'));

6.查询pg库所有的表和表所对应的schemaname

SELECT t.schemaname as schemaname,t.tablename tablename FROM pg_tables t

7.根据表名查询表的schemaname

SELECT t.schemaname as schemaname,t.tablename tablename FROM pg_tables t where t.tablename = 'SX_MZSX_PG'

String sql = "SELECT t.schemaname as schemaname,t.tablename tablename FROM pg_tables t where t.tablename = ?";
PreparedStatement stmt = this.connection.prepareStatement(sql);
try {
　　stmt.setString(1, pTN);
　　ResultSet rs = stmt.executeQuery();
　　try {
　　　　if (rs.next()) {
　　　　　　// 基本信息
　　　　　　String owner = rs.getString("schemaname");
　　　　　　String tableName = rs.getString("tablename");
　　　　　　try {
　　　　　　　　// 添加图层
　　　　　　　　return new SDO_Table(this.connection, owner, tableName);
　　　　　　} catch (EzSqlSrvProxyException e) {
　　　　　　　　// 出现错误的图层不予考虑
　　　　　　}
　　　　}
　　} finally {
　　　　rs.close();
　　}
　　} finally {
　　　　stmt.close();
}

8.根据表名和schemaname查询表的字段

select * from "public"."SXGJX" where 1=0

 9.连接pg数据库报错：no pg_hba.conf entry for host

解决办法：
修改pg_hba.conf，在第一行添加一行：
host all all 0.0.0.0/0 md5
表示允许任何用户连接到任何数据库，用一个加密的密码

10.函数ST_MakeEnvelope

简介

geometry ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer srid=unknown);

描述

从X和Y的最小值和最大值创建矩形多边形。输入值必须位于SRID指定的空间参照系统中。如果未指定SRID，则使用未知空间参照系统（SRID 0）

 

11函数ST_AsMVTGeom

简介

geometry ST_AsMVTGeom(geometry geom, box2d bounds, integer extent=4096, integer buffer=256, boolean clip_geom=true);

描述

将几何图形转换为对应于图层的一组行的Mapbox矢量图块的坐标空间。尽最大努力保持甚至纠正有效性，并可能在过程中将几何体折叠为较低的维度

 

12.函数ST_AsBinary

简介

bytea ST_AsBinary(geometry g1);

描述

返回表示几何的二进制字符串

13.函数ST_Intersects

简介

ST_Intersects( geometry geomA , geometry geomB );

描述

判断两个几何体是否相交，相交返回1,否则返回0

14.函数ST_AsGeoJSON

text ST_AsGeoJSON(record feature, text geomcolumnname, integer maxdecimaldigits=9, boolean pretty_bool=false);
​
text ST_AsGeoJSON(geometry geom, integer maxdecimaldigits=9, integer options=8);
​
text ST_AsGeoJSON(geography geog, integer maxdecimaldigits=9, integer options=0);

描述

将几何体作为GeoJSON“geometry”对象返回，或将行作为GeoJSON“feature”对象返回

maxdecimaldigits参数可用于减少输出中使用的最大小数位数（默认为9）。如果您使用的是EPSG:4326并且输出的几何图形仅用于显示，那么maxdecimaldigits=6对于许多贴图来说都是一个不错的选择

 

15.将postgis查询结果转换为geojson

WITH features AS ( SELECT st_asgeojson ( pipe_line.* ) :: json AS feature FROM pipe_line_wgs84 AS pipe_line ) SELECT
json_build_object ( 'type', 'FeatureCollection', 'features', json_agg ( features.feature ) ) 
FROM
    features

16.查询表字段

String tableName = "table_name"; 
String queryFildSql = "SELECT a.attnum," +
                "a.attname AS field" +
                " FROM pg_class c," +
                " pg_attribute a" +
                " LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid," +
                " pg_type t" +
                " WHERE c.relname = '" + tableName + "'" +
                " and a.attnum > 0" +
                " and a.attrelid = c.oid" +
                " and a.atttypid = t.oid" +
                " ORDER BY a.attnum";

 

17.创建表

DROP TABLE IF EXISTS "public"."road";
CREATE TABLE "public"."road" (
  "id" int4 NOT NULL,
  "geom" geometry(geometry, 4326),
  "FID" float8,
  "FNode" float8,
  "TNode" float8,
  "Length" float8,
  "Name" varchar COLLATE "pg_catalog"."default",
  "PopName" varchar COLLATE "pg_catalog"."default",
  "District" varchar COLLATE "pg_catalog"."default",
  "Attribute" varchar COLLATE "pg_catalog"."default",
  "RouteNum" varchar COLLATE "pg_catalog"."default",
  "Speed" varchar COLLATE "pg_catalog"."default",
  "Limit" varchar COLLATE "pg_catalog"."default",
  "Toll" int4,
  "Doorplate" varchar COLLATE "pg_catalog"."default",
  "CName" varchar COLLATE "pg_catalog"."default",
  "EName" varchar COLLATE "pg_catalog"."default",
  "InsiteType" varchar COLLATE "pg_catalog"."default",
  "Update" date,
  "Source" varchar COLLATE "pg_catalog"."default",
  "GUID" varchar COLLATE "pg_catalog"."default",
  "路面类型" varchar COLLATE "pg_catalog"."default"
);

18.查询版本

show server_version; // 查询PostGIS版本 POSTGIS="3.1.2 3.1.2" [EXTENSION] PGSQL="130" GEOS="3.9.1-CAPI-1.14.1" PROJ="7.1.1" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY 
​
select version(); // 查询PostgreSQL版本
​
select postgis_full_version(); // 13.3  查询PostgreSQL 版本
​

19. geometry转wkt

SELECT st_astext('{"coordinates":[[106.49824956236046,29.56679821936578],[106.49844680963861,29.566098772209088]],"type":"LineString"}')
// LINESTRING(106.49824956236046 29.56679821936578,106.49844680963861 29.566098772209088)

20. wkt转geometry

SELECT ST_GeomFromText('POLYGON((106.93679809570278 30.11215209960944,106.93817138671841 30.11215209960944,106.93817138671841 30.110778808593814,106.93679809570278 30.110778808593814,106.93679809570278 30.11215209960944))',4326)
// 0103000020E61000000100000005000000E8FFFF7FF4BB5A4012000000B61C3E40E8FFFFFF0ABC5A4012000000B61C3E40E8FFFFFF0ABC5A40120000005C1C3E40E8FFFF7FF4BB5A40120000005C1C3E40E8FFFF7FF4BB5A4012000000B61C3E40

21. 查询表的空间类型

SELECT type FROM geometry_columns where f_table_name=''

表空间类型查询结果和实际空间类型映射

Map dict = new HashMap(); // 建立SDO_GTYPE和  几何类型 对应的字典
dict.put("2000", "unkonwn");
dict.put("2001", "point");
dict.put("2002", "Polyline");
dict.put("2003", "polygon");
dict.put("2004", "COLLECTION");
dict.put("2005", "MutliPoint");
dict.put("2006", "MutliPolyline");
dict.put("2007", "MutliPolygon");

 使用随机查询一条记录确定表的几何类型

 String sqlType = "select ST_GeometryType(geom) AS type from\"" +  layerName+"\" ORDER BY random() LIMIT 1";

22. 用于生成矢量切片的函数ST_AsMVT

 

ST_AsMVT聚合函数用于将基于MapBox VectorTile坐标空间的几何图形转换为MapBox VectorTile二进制矢量切片。