postgis常用命令

SomeOtherTime

已于 2024-11-07 10:32:32 修改

阅读量2.2k

点赞数

分类专栏：记录典型demo postgresql 文章标签： java

于 2021-12-10 10:47:25 首次发布

本文链接：https://blog.youkuaiyun.com/yaoct/article/details/121851102

版权

典型demo 同时被 3 个专栏收录

34 篇文章

订阅专栏

记录

19 篇文章

订阅专栏

postgresql

3 篇文章

订阅专栏

数据库几何计算基于数据库的geometry类型，其实际格式为HEXEWKB（EWKB的16进制格式），其他类型一般先转换为HEXEWKB。

常用格式为WKT,EWKT文本格式，WKB,EWKB二进制格式，geojson，geometry类型 ,geography地理信息类型（基于球面计算能更精确）

常用函数

三维数据转化为WKT

update "dqyj_msriverbasin" set geomstr=st_asewkt(ST_SetSRID(ST_Transform(ST_Force2D(geom),4326),0),7)

为了从pg导入到mysql，mysql不支持3维，只能将pg的EWKB3维转换为2维，再转换到WKB

0.查询坐标

integer ST_SRID(geometry)

1.转换坐标系

geometry ST_Transform(geometry g1, integer srid);

2.文本EWKT格式转geometry格式

geometry = ST_GeomFromEWKT(text EWKT);

3.geometry格式转文本EWKT格式

text EWKT = ST_AsEWKT(geometry);

4.geometry格式转geojson

text ST_AsGeoJSON(geometry geom, integer maxdecimaldigits=9, integer options=8);

5.geojson转geometry

geometry ST_GeomFromGeoJSON(text geomjson);

6.两个几何图形最近距离

float ST_Distance(geometry g1, geometry g2); 结果单位根据坐标系

float ST_Distance(geography geog1, geography geog2, boolean use_spheroid=true); 结果单位米

例：SELECT ST_Distance(

'SRID=4326;POINT(-72.1235 42.3521)'::geometry,

'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry

);

7.其中:: 为postgre的强转符号

以下语句等价

SELECT CAST('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)' AS geometry);

SELECT ('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)'::geometry);

SELECT ST_GeomFromEWKT('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)');

8.示例

转换：SELECT ST_Transform('SRID=4549;MultiLineString((496028.27459716797 3378316.6738891602,

497178.69659423828 3378207.184753418))'::geometry,4326)

postgre创建序列：数据库：使用navicat创建postgresql数据库的表时设定主键自增和删除主键自增教程 - 灰信网（软件开发博客聚合）

9.springdata-jpa 本地查询对::强转符号转换有误，把::转换成了:

所以用springdata-jpa是应把::符号替换为如上cast方法

例：

SELECT * from t_case d where ST_Distance(concat('SRID=4326;POINT(',?1,' ',?2,')')::geography, \n" +
"d.location::geography)<?3 ORDER BY ST_Distance(concat('SRID=4326;POINT(',?1,' ',?2,')')::geography,\n" +
"d.location::geography) DESC limit ?4

替换为：

SELECT * from t_case d where ST_Distance(CAST(concat('SRID=4326;POINT(',?1,' ',?2,')') as geography), \n" +
"CAST(d.location as geography))<?3 ORDER BY ST_Distance(CAST(concat('SRID=4326;POINT(',?1,' ',?2,')')as geography),\n" +
"CAST(d.location as geography)) DESC limit ?4

10.日期操作

select to_char('2021-11-11 00:00:00'::TIMESTAMP,'YYYY')

date_trunc('month',d.report_time)

date_part(text, timestamp) double precision 获取子域(等效于extract)； date_part('hour', timestamp '2001-02-16 20:38:40') 20

PostgreSQL 时间/日期函数和操作符 | 菜鸟教程

PostgresSQL 时间和日期字段,函数和模式_liguangxianbin的博客-优快云博客_postgresql 日期字段

11.空间关系示例

select ('SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))'::geometry);

select ST_AsGeoJSON('SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))'::geometry);

select ST_Distance('SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)))'::geography,'SRID=4326;POINT(4.1 0)'::geography)

select ST_Distance('SRID=4326;MULTILINESTRING((0 0,4 0,4 4,0 4,0 0))'::geography,'SRID=4326;POINT(1 1)'::geography)

select ST_Within('SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)))'::geometry,'SRID=4326;POINT(1 1)'::geometry)

select ST_Within('SRID=4326;POINT(1 1)'::geometry,'SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)))'::geometry)

交叉

SELECT ST_Intersects('SRID=4326;POLYGON((0 0,-2 2,2 2,0 0))'::geometry,'SRID=4326;MULTILINESTRING((-5 1,5 1))'::geometry)

SELECT
d.*
from dangerous_case_info d where
ST_Within(concat('SRID=4326;POINT(',d.lon,' ',d.lat,')')::geometry,ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[119,29],[121,29],[121,31],[119,31],[119,29]],[[1,1],[2,1],[2,2],[1,2],[1,1]]]]}')::geometry)=TRUE

SELECT ST_Intersects('SRID=4326;MULTIPOLYGON(((4 4,4 5,5 5,5 4,4 4)))'::geometry,'SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)))'::geometry)

SELECT
d.*
from dangerous_case_info d where
ST_Distance(concat('SRID=4326;POINT(',d.lon,' ',d.lat,')')::geometry,ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[119,29],[121,29],[121,31],[119,31],[119,29]],[[1,1],[2,1],[2,2],[1,2],[1,1]]]]}')::geometry)=0

WKB 和EWKB

Select ST_SRID('0020000003000010E60000000100000004405CEB4A7F8012E04040F4E28ED5F139405CEB994185058E4040E94812BE48A6405CF3F2BECEDD484040F1D230B9DC2F405CEB4A7F8012E04040F4E28ED5F139'::geometry)

Select ST_AsGeoJSON('0020000003000010E60000000100000004405CEB4A7F8012E04040F4E28ED5F139405CEB994185058E4040E94812BE48A6405CF3F2BECEDD484040F1D230B9DC2F405CEB4A7F8012E04040F4E28ED5F139'::geometry)

Select ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[115.676422,33.913164],[115.681229,33.822512],[115.811691,33.889227],[115.676422,33.913164]]]}')

Select ST_SRID('0103000020E61000000100000004000000E012807F4AEB5C4039F1D58EE2F440408E05854199EB5C40A648BE1248E9404048DDCEBEF2F35C402FDCB930D2F14040E012807F4AEB5C4039F1D58EE2F44040'::geometry)

Select ST_AsGeoJSON('0103000020E61000000100000004000000E012807F4AEB5C4039F1D58EE2F440408E05854199EB5C40A648BE1248E9404048DDCEBEF2F35C402FDCB930D2F14040E012807F4AEB5C4039F1D58EE2F44040'::geometry)

Select ST_AsEWKT('0020000003000010E60000000100000004405CEB4A7F8012E04040F4E28ED5F139405CEB994185058E4040E94812BE48A6405CF3F2BECEDD484040F1D230B9DC2F405CEB4A7F8012E04040F4E28ED5F139'::geometry)
SRID=4326;POLYGON((115.676422 33.913164,115.681229 33.822512,115.811691 33.889227,115.676422 33.913164))

Select ST_AsEWKT('0103000020E61000000100000004000000E012807F4AEB5C4039F1D58EE2F440408E05854199EB5C40A648BE1248E9404048DDCEBEF2F35C402FDCB930D2F14040E012807F4AEB5C4039F1D58EE2F44040'::geometry)
SRID=4326;POLYGON((115.676422 33.913164,115.681229 33.822512,115.811691 33.889227,115.676422 33.913164))

Select 'SRID=4326;POINT(115.79504 33.867779)'::geometry
Select 'POINT(115.79504 33.867779)'::geometry

12.类型不对报错

数据库重现错误

insert into "district" (name,code,position) VALUES('cesh2i3','ac1v3','SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)))'::VARCHAR)

有时候JDBC拼接sql时缺少类型。postgresql底层也是Socket连接org.postgresql.core.PGStream#createSocket

正确语句insert into "district" (name,code,position) VALUES('cesh2i113','ac1v113','SRID=4326;MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0)))'::geometry)

PG自定义类型都要继承org.postgresql.util.PGobject

其中PGgeometry再postGIS扩展驱动包中

13.同样一句sql，PG的JDBC发出的sql格式与navicate发出的不一样，这就是PG的JDBC严格检查类型的原因。他用了postgresql的 PREPARED语句高效率

http://t.zoukankan.com/kuang17-p-14005372.html

PG的JDBC：

navicate：

14.postgreSQL更改表结构语句

参考：PostgreSQL修改表（ALTER TABLE语句） -PostgreSQL教程

PostgreSQL ALTER TABLE命令用于添加，删除或修改现有表中的列。您还可以使用ALTER TABLE命令在现有表上添加和删除各种约束。

语法：
使用ALTER TABLE语句在现有表中添加新列的基本语法如下：

ALTER TABLE table_name ADD column_name datatype;

SQL

现有表中ALTER TABLE到DROP COLUMN(删除某个字段)的基本语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

SQL

ALTER TABLE更改表中列的DATA TYPE(修改字段类型)的基本语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

SQL

ALTER TABLE向表中的列添加NOT NULL约束的基本语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

SQL

ALTER TABLE添加唯一约束ADD UNIQUE CONSTRAINT到表中的基本语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

SQL

ALTER TABLE将“检查约束”添加到表中的基本语法如下所示：

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

SQL

ALTER TABLE添加主键ADD PRIMARY KEY约束的基本语法如下：

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

SQL

使用ALTER TABLE从表中删除约束(DROP CONSTRAINT)的基本语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

SQL

使用ALTER TABLE从表中删除主键约束(DROP PRIMARY KEY)约束的基本语法如下：

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

//更多请阅读：https://www.yiibai.com/postgresql/postgresql_alter_command.html

15.转WKT

update "dqyj_poi" set geomstr=st_asewkt(ST_SetSRID(ST_Transform(geom,4326),0),7) #转为WKB

16.数据量统计

SELECT
   table_schema AS '数据库',
   table_name AS '表名',
   table_rows AS '记录数',
   TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
   TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
   information_schema.TABLES ORDER BY data_length DESC,
   index_length DESC;

SELECT
table_schema AS '数据库',
table_comment AS '表中文名',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
information_schema.TABLES
ORDER BY data_length DESC,
index_length DESC;

SELECT
   relname,
   pg_size_pretty ( pg_relation_size ( relid ) )
FROM
   pg_stat_user_tables
ORDER BY
   pg_relation_size ( relid ) DESC;

17.坐标转换

update "public"."pipe_point_pollution_water" set
lon=ST_X(ST_Transform(concat('SRID=4549;POINT(',y,' ',x,')')::geometry,4326)),
lat=ST_Y(ST_Transform(concat('SRID=4549;POINT(',y,' ',x,')')::geometry,4326))
where x is not null and y is not null

18.PG字符串操作

数据库PostgreSQL PG 字符串拼接，大小写转换，substring-优快云博客

update flaw_info set distance=case when 
position('-' IN description)>1 then
substring(description,0,position('-' IN description))::numeric 
else null end

19.删除PG数据库

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'pipe_health_archives_pre' AND pid <> pg_backend_pid();
DROP DATABASE pipe_health_archives_pre;