PG数据库GIS基础

最新推荐文章于 2023-08-11 13:59:44 发布
最新推荐文章于 2023-08-11 13:59:44 发布
阅读量198 收藏
点赞数
文章标签： 数据库 postgresql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43037331/article/details/130502933
版权

基础知识

https://www.cnblogs.com/denny402/p/4968201.html interior：内部，boundary：边界，exterior：外部
postgis:https://postgis.net/docs/
SRID(空间参考信息):https://spatialreference.org/
知乎文章：https://www.zhihu.com/people/li-yang-qiao-89/posts?page=10

几何类别

普通的几何类别：(无法进行GIS操作)
名字存储空间说明表现形式
point16 字节平面中的点(x,y)
line32 字节(无穷)直线(未完全实现)((x1,y1),(x2,y2))
lseg32 字节(有限)线段((x1,y1),(x2,y2))
box32 字节矩形((x1,y1),(x2,y2))
path16+16n 字节闭合路径(与多边形类似)((x1,y1),…)
path16+16n 字节开放路径[(x1,y1),…]
polygon40+16n 字节多边形(与闭合路径相似)((x1,y1),…)
circle24 字节圆<(x,y),r> (圆心和半径)
参考：
https://blog.csdn.net/liyazhen2011/article/details/82993954
注意点：
lseg：必须是一条线段，多条线段报错
多条线段可以使用path
test=# create table test_circle(id varchar(10), geom circle);
CREATE TABLE
test=# insert into test_circle values('1', circle'<( 1 , 2 ) , 2 >');
INSERT 0 1
test=# select * from test_circle;
 id |   geom    
----+-----------
 1  | <(1,2),2>
(1 行记录)
地理几何类型(Geometry)
创建数据库
create database test
create extension postgis(否则test数据库不支持gis操作)
几何类型
点（POINT）

最多4维，常用2维
创建：

test=# select ST_GeomFromText('POINT(118.7830931 32.0450039)', 4326);
                  st_geomfromtext                   
----------------------------------------------------
 0101000020E61000004A8E85321EB25D40A25813B0C2054040
(1 行记录)

test=# select ST_NDims(ST_GeomFromText('POINT(1 2 3 4)', 4326));
 st_ndims 
----------
        4
(1 行记录)

test=# select ST_NDims(ST_GeomFromText('POINT(1 2 3 4 5)', 4326));
ERROR:  parse error - invalid geometry
提示:  "POINT(1 2 3 4 5)" <-- parse error at position 16 within geometry

常用方法，详细使用见常用GIS操作：

  • ST_X(geometry) —— 返回X坐标
  • ST_Y(geometry) —— 返回Y坐标
  • ST_Z(geometry) —— 返回Z坐标
  • ST_M(geometry) —— 返回M信息
线（LINESTRING）

由点组成，可以多维
创建

select ST_GeomFromText('LINESTRING(118.7830931 32.0450039, 118.7840931 32.0750039)', 4326);

test=# select ST_NDims(ST_GeomFromText('LINESTRING(118.7830931 32.0450039 2 , 118.7840931 32.0750039 2)', 4326));
 st_ndims 
----------
        3
(1 行记录)

所有点的维度也必须一致
test=# select ST_NDims(ST_GeomFromText('LINESTRING(118.7830931 32.0450039 2 , 118.7840931 32.0750039 )', 4326));
ERROR:  can not mix dimensionality in a geometry
提示:  "...0450039 2 , 118.7840931 32.0750039 )" <-- parse error at position 63 within geometry

常用方法，详细使用见常用GIS操作：

  • ST_Length(geometry) —— 返回线串的长度
  • ST_StartPoint(geometry) —— 将线串的第一个坐标作为点返回
  • ST_EndPoint(geometry） —— 将线串的最后一个坐标作为点返回
  • ST_NPoints(geometry) —— 返回线串的坐标数量
多边形（POLYGON）

创建：
当有多个多边形时：
A,B,C
D = AB的并集减去，AB的交集
res=DC的并集减去DC的交集

select ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039))', 4326);

select ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039))', 4326);

select ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326);

常用方法，详细使用见常用GIS操作：

  • ST_Area(geometry) —— 返回多边形的面积
  • ST_NRings(geometry) —— 返回多边形中环的数量（通常为1个，其他是孔）
  • ST_ExteriorRing(geometry) —— 以线串的形式返回多边形最外面的环
  • ST_InteriorRingN(geometry, n) —— 以线串形式返回指定的内部环
  • ST_Perimeter(geometry) —— 返回所有环的长度
图形集合（GEOMETRYCOLLECTION）

包括：

  • MultiPoint —— 点集合
  • MultiLineString —— 线串集合
  • MultiPolygon —— 多边形集合
  • GeometryCollection —— 由任意几何图形（包括其他GeometryCollection）组成的异构集合
    创建：
    MultiPoint
test=# select ST_GeomFromText('MultiPoint ((118.7830931 32.0450039),(118.8840931 32.0450039),(118.8840931 32.0650039))', 4326);
                                                                     st_geomfromtext                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------
 0104000020E61000000300000001010000004A8E85321EB25D40A25813B0C20540400101000000A2C739FB94B85D40A25813B0C20540400101000000A2C739FB94B85D40654E3C0C52084040
(1 行记录)

test=# select ST_GeomFromText('MultiPoint (118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039)', 4326);
                                                                     st_geomfromtext                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------
 0104000020E61000000300000001010000004A8E85321EB25D40A25813B0C20540400101000000A2C739FB94B85D40A25813B0C20540400101000000A2C739FB94B85D40654E3C0C52084040
(1 行记录)

test=# select ST_GeomFromText('MultiPoint ((118.7830931 32.0450039))', 4326);
                           st_geomfromtext                            
----------------------------------------------------------------------
 0104000020E61000000100000001010000004A8E85321EB25D40A25813B0C2054040
(1 行记录)

test=# select ST_GeomFromText('MultiPoint (118.7830931 32.0450039)', 4326);
                           st_geomfromtext                            
----------------------------------------------------------------------
 0104000020E61000000100000001010000004A8E85321EB25D40A25813B0C2054040
(1 行记录)

MultiLineString:

select ST_GeomFromText('MultiLineString(118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039)', 4326);
ERROR:  parse error - invalid geometry
提示:  "MultiLineString(118.7830931 " <-- parse error at position 28 within geometry
test=# select ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039))', 4326);
                                                               st_geomfromtext                                                                
----------------------------------------------------------------------------------------------------------------------------------------------
 0105000020E6100000010000000102000000030000004A8E85321EB25D40A25813B0C2054040A2C739FB94B85D40A25813B0C2054040A2C739FB94B85D40654E3C0C52084040
(1 行记录)

select ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039),(118.9830931 32.0450039, 119.3840931 32.0450039))', 4326);

MultiPolygon:

select ST_GeomFromText('MultiPolygon(((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0450039)),((118.9830931 32.0450039, 119.3840931 32.0450039,119.3840931 32.5450039,118.9830931 32.0450039)))', 4326);

GeometryCollection:

需要指定类型
select ST_GeomFromText('GeometryCollection(Polygon((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0450039)),Polygon((118.9830931 32.0450039, 119.3840931 32.0450039,119.3840931 32.5450039,118.9830931 32.0450039)))', 4326);

select ST_GeomFromText('GeometryCollection(Polygon((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0450039)),Point(118.9830931 32.0450039))', 4326);

常用方法，详细使用见常用GIS操作：

  • ST_NumGeometries(geometry) —— 返回集合中的组成部分的数量
  • ST_GeometryN(geometry, n) —— 返回集合中指定的组成部分
  • ST_Area(geometry) —— 返回集合中所有多边形组成部分的总面积
  • ST_Length(geometry) —— 返回所有线段组成部分的总长度

常用的GIS操作

创建几何图形的几种方式

类型的转换

olddata::newtype
SELECT ST_GeomFromText('POINT(2 2)',4326)::text;
ST_GeomFromText/ST_GeogFromText

ST_GeomFromText(text, srid) —— 返回geometry
ST_GeogFromText(text) —— 返回geography
注意SRID
0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040本身就代表是geometry

test=# select ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))',4326)
test-# ;
                                               st_geomfromtext                                                
--------------------------------------------------------------------------------------------------------------
 0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040
(1 行记录)
test=# select ST_GeogFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))');
                                               st_geogfromtext                                                
--------------------------------------------------------------------------------------------------------------
 0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040
(1 行记录)
test=# select 'MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))'::geography ;
                                                  geography                                                   
--------------------------------------------------------------------------------------------------------------
 0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040
(1 行记录)

test=# select 'MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))'::Geometry;
                                               geometry                                               
------------------------------------------------------------------------------------------------------
 0105000000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040
(1 行记录)
ST_GeomFromGeoJSON

ST_AsGeoJSON(geometry) —— 返回text
默认SRID为0

SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt;
wkt
------
POINT(-48.23456 20.12345)

SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}'))::geography As wkt;
ST_GeomFromWKB
test=# select st_ASText(ST_GeomFromWKB('0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040'::geometry));
WARNING:  OGC WKB expected, EWKB provided - use GeometryFromEWKB() for this
                            st_astext                             
------------------------------------------------------------------
 MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))
(1 行记录)

test=# select st_ASText(ST_GeomFromWKB('0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040'));
ERROR:  Invalid endian flag value encountered.
test=# select st_ASText(ST_GeomFromWKB('0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040'::geography));
WARNING:  OGC WKB expected, EWKB provided - use GeometryFromEWKB() for this
                            st_astext                             
------------------------------------------------------------------
 MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))
(1 行记录)
返回text获取经纬度
ST_ASText

ST_AsText(geometry) —— 返回text

test=# select ST_ASText(ST_GeomFromText('POINT(118.7830931 32.0450039)', 4326));
           st_astext           
-------------------------------
 POINT(118.7830931 32.0450039)
(1 行记录)

test=# select ST_ASText(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.784 32.075, 118 32, 118.7830931 32.0450039))', 4326));
                                   st_astext                                    
--------------------------------------------------------------------------------
 POLYGON((118.7830931 32.0450039,118.784 32.075,118 32,118.7830931 32.0450039))
(1 行记录)

test=# select ST_AsText('0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040');
                            st_astext                             
------------------------------------------------------------------
 MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))
(1 行记录)
ST_AsEWKT

ST_AsEWKT(geometry) —— 返回text
与ST_ASText用法基本一致

test=# select ST_AsEWKT('0105000020E6100000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040');
                                 st_asewkt                                  
----------------------------------------------------------------------------
 SRID=4326;MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))
(1 行记录)

test=# select ST_AsEWKT('0105000000010000000102000000020000004A8E85321EB25D40A25813B0C2054040A1F2542C23B25D40A99894CCFB054040');
                            st_asewkt                             
------------------------------------------------------------------
 MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))
(1 行记录)
其他操作
POINT
ST_X(只能用于POINT)

ST_X(geometry)

获取点的第一个字段

test=# select ST_X(ST_GeomFromText('POINT(118.7830931 32.0450039)', 4326));
    st_x     
-------------
 118.7830931
(1 行记录)

test=# select ST_X(ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))', 4326));
ERROR:  Argument to ST_X() must be a point

SELECT ST_X(ST_GeomFromText('POINT(2 2)',4326)::text);
ST_Y(只能用于POINT)

ST_Y(geometry)

获取点的第二个字段

test=# select ST_Y(ST_GeomFromText('POINT(118.7830931 32.0450039)', 4326));
    st_y    
------------
 32.0450039
(1 行记录)
ST_Z(只能用于POINT)

ST_Z(geometry)

获取点的第三个字段，没有则为空

test=# select ST_Z(ST_GeomFromText('POINT(118.7830931 32.0450039 2)', 4326));
 st_z 
------
    2
(1 行记录)

test=# select ST_Z(ST_GeomFromText('POINT(118.7830931 32.0450039)', 4326));
 st_z 
------
     
(1 行记录)
ST_M(只能用于POINT)

ST_M(geometry) 
获取点的第四个字段，没有则为空

test=# select ST_M(ST_GeomFromText('POINT(118.7830931 32.0450039 2 3)', 4326));
 st_m 
------
    3
(1 行记录)
LINESTRING
ST_Length

ST_Length(geometry)

计算线段长度，每两点读一个欧几里得距离并求和
其他geometry也可以计算，但是结果都为0

test=# select ST_Length(ST_GeomFromText('LINESTRING(114 35, 118 32)', 4326));
 st_length 
-----------
         5
(1 行记录)

test=# select ST_Length(ST_GeomFromText('LINESTRING(114 35, 118 32, 114 35)', 4326));
 st_length 
-----------
        10
(1 行记录)

test=# select ST_Length(ST_GeomFromText('POINT(118.7830931 32.0450039)', 4326));
 st_length 
-----------
         0
(1 行记录)

test=# select ST_Length(ST_GeomFromText('Polygon((114 35, 118 32, 122 35, 114 35))', 4326));
 st_length 
-----------
         0
(1 行记录)

参数是geography时，计算地理距离

test=# select ST_Length(ST_GeogFromText('LINESTRING(114 35, 118 32)'));
    st_length     
------------------
 498778.107681175
(1 行记录)

图形集合

test=# select ST_Length(ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039),(118.9830931 32.0450039, 118.9840931 32.0450039))', 4326));
     st_length     
-------------------
 0.121999999999993
(1 行记录)

test=# select ST_Length(ST_GeometryN(ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039),(118.9830931 32.0450039, 118.9840931 32.0450039))', 4326),1));
     st_length     
-------------------
 0.121000000000002
(1 行记录)

test=# select ST_Length(ST_GeometryN(ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039),(118.9830931 32.0450039, 118.9840931 32.0450039))', 4326),2));
      st_length       
----------------------
 0.000999999999990564
(1 行记录)
ST_StartPoint/ST_EndPoint

ST_StartPoint/ST_EndPoint(geometry)
获取线段的开始/结束点
其他geometry返回空值

test=# select ST_StartPoint(ST_GeomFromText('Polygon((114 35, 118 32, 122 35, 114 35))', 4326));
 st_startpoint 
---------------
 
(1 行记录)

test=# select ST_StartPoint(ST_GeomFromText('LINESTRING(114 35, 118 32, 114 35)', 4326));
                   st_startpoint                    
----------------------------------------------------
 0101000020E61000000000000000805C400000000000804140
(1 行记录)

test=# select ST_ASText(ST_StartPoint(ST_GeomFromText('LINESTRING(114 35, 118 32, 114 35)', 4326)));
   st_astext   
---------------
 POINT(114 35)
(1 行记录)
ST_NPoints

ST_NPoints(geometry)

获取geometry中点的个数

test=# select ST_NPoints(ST_GeomFromText('LINESTRING(114 35, 118 32, 114 35)', 4326));
 st_npoints 
------------
          3
(1 行记录)

test=# select ST_NPoints(ST_GeomFromText('LINESTRING(114 35, 114 35, 118 32, 114 35)', 4326));
 st_npoints 
------------
          4
(1 行记录)

test=# select ST_NPoints(ST_GeomFromText('Polygon((114 35, 118 32, 122 35, 1 1,114 35))', 4326));
 st_npoints 
------------
          5
(1 行记录)
ST_Area

ST_Area(geometry)

计算面积
当时点和线段时结果为0
输入为geometry时是用平面距离
输入为geography时是用地理面积

test=# select ST_Area(ST_GeomFromText('LINESTRING(114 35, 118 32, 114 35)', 4326));
 st_area 
---------
       0
(1 行记录)

test=# select ST_Area(ST_GeomFromText('Polygon((114 35, 118 35, 118 38,114 38,114 35))', 4326));
 st_area 
---------
      12
(1 行记录)

test=# select ST_Area(ST_GeogFromText('Polygon((114 35, 118 35, 118 38,114 38,114 35))'));
     st_area      
------------------
 119284641095.694
(1 行记录)

select ST_Area(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326));
A-B-C

select ST_Area(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039))', 4326));

select ST_Area(ST_GeomFromText('Polygon((118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039))', 4326));

select ST_Area(ST_GeomFromText('Polygon((118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326));

图形集合

select ST_Area(ST_GeomFromText('GeometryCollection(Polygon((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0450039)),Point(118.9830931 32.0450039))', 4326));
ST_NRings

ST_NRings(geometry)

返回polygon中环的个数

test=# select ST_NRings(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326));
 st_nrings 
-----------
         3
(1 行记录)

test=# select ST_NRings(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039))', 4326));
 st_nrings 
-----------
         2
(1 行记录)
ST_ExteriorRing

ST_ExteriorRing(geometry)

以线串的形式返回多边形最外面的环

test=# select ST_ASText(ST_ExteriorRing(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326)));
                                                           st_astext                                                            
--------------------------------------------------------------------------------------------------------------------------------
 LINESTRING(118.7830931 32.0450039,118.7830931 32.0750039,118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039)
(1 行记录)
ST_InteriorRingN

ST_InteriorRingN(geometry, n)

以线串形式返回指定的内部环，不存在返回空

test=# select ST_ASText(ST_InteriorRingN(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326), 1));
                                                           st_astext                                                            
--------------------------------------------------------------------------------------------------------------------------------
 LINESTRING(118.6830931 32.0350039,118.6830931 32.0650039,118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039)
(1 行记录)

test=# select ST_ASText(ST_InteriorRingN(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326), 2));
                                                           st_astext                                                            
--------------------------------------------------------------------------------------------------------------------------------
 LINESTRING(118.7830931 32.0450039,118.8840931 32.0450039,118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039)
(1 行记录)

test=# select ST_ASText(ST_InteriorRingN(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326), 3));
 st_astext 
-----------
 
(1 行记录)
ST_Perimeter

ST_Perimeter(geometry)

返回所有环的长度之和

test=# select ST_Perimeter(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326));
   st_perimeter   
------------------
 1.16600000000001
(1 行记录)

test=# select ST_Perimeter(ST_GeogFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))'));
   st_perimeter   
------------------
 112745.336562741
 
(1 行记录)
test=# select ST_Perimeter(ST_GeogFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039))'));
   st_perimeter   
------------------
 44614.7485352211
(1 行记录)

test=# select ST_Perimeter(ST_GeogFromText('Polygon((118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039))'));
   st_perimeter   
------------------
 44618.8672026453
(1 行记录)

test=# select ST_Perimeter(ST_GeogFromText('Polygon((118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))'));
   st_perimeter   
------------------
 23511.7208248746
(1 行记录)
针对一个多边形的方法
ST_GeometryType

ST_GeometryType(geometry) —— 返回几何图形的类型

test=# select ST_GeometryType(ST_GeogFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))'));
ERROR:  function st_geometrytype(geography) does not exist
第1行select ST_GeometryType(ST_GeogFromText('MULTILINESTRING((118...
            ^
提示:  No function matches the given name and argument types. You might need to add explicit type casts.
test=# select ST_GeometryType(ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))',4326));
  st_geometrytype   
--------------------
 ST_MultiLineString
ST_NDims

ST_NDims(geometry) —— 返回几何图形的维数

test=# select ST_NDims(ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))'));
 st_ndims 
----------
        2
ST_SRID

ST_SRID(geometry) —— 返回几何图形的空间参考标识码

test=# select ST_SRID(ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))'));
 st_srid 
---------
       0
(1 行记录)

test=# select ST_SRID(ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))', 4326));
 st_srid 
---------
    4326
(1 行记录)
ST_IsClosed

ST_IsClosed(geomtery)
判断geometry是否闭合

test=# select ST_IsClosed(ST_GeomFromText('LINESTRING(118.7830931 32.0450039, 118.7840931 32.0750039)', 4326));
 st_isclosed 
-------------
 f
(1 行记录)

test=# select ST_IsClosed(ST_GeomFromText('LINESTRING(118.7830931 32.0450039, 118.7840931 32.0750039, 118 32, 118.7830931 32.0450039)', 4326));
 st_isclosed 
-------------
 t
(1 行记录)

test=# select ST_IsClosed(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.784 32.075, 118 32, 118.7830931 32.0450039))', 4326));
 st_isclosed 
-------------
 t
(1 行记录)
ST_IsSimple

ST_IsSimple(geometry)
判断geometry是否与与自身交叉或接触

test=# select ST_IsSimple(ST_GeomFromText('LINESTRING(118.7830931 32.0450039, 118 32)', 4326));
 st_issimple 
-------------
 t
(1 行记录)

test=# select ST_IsSimple(ST_GeomFromText('LINESTRING(118.7830931 32.0450039, 118 32, 118.7830931 32.0450039)', 4326));
 st_issimple 
-------------
 f
(1 行记录)

test=# select ST_IsSimple(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.784 32.075, 118 32, 118.7830931 32.0450039))', 4326));
 st_issimple 
-------------
 t
(1 行记录)
ST_NumGeometries

ST_NumGeometries(geometry)
集合中的组成部分的数量

test=# select ST_NumGeometries(ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039),(118.9830931 32.0450039, 118.9840931 32.0450039))', 4326));
 st_numgeometries 
------------------
                2
(1 行记录)

test=# select ST_NumGeometries(ST_GeomFromText('Polygon((118.7830931 32.0450039, 118.7830931 32.0750039, 118.9840931 32.0750039,118.9840931 32.0450039,118.7830931 32.0450039), (118.6830931 32.0350039, 118.6830931 32.0650039, 118.8840931 32.0650039,118.8840931 32.0350039,118.6830931 32.0350039), (118.7830931 32.0450039,118.8840931 32.0450039, 118.8840931 32.0650039,118.7830931 32.0650039,118.7830931 32.0450039))', 4326));
 st_numgeometries 
------------------
                1
(1 行记录)
ST_GeometryN

ST_GeometryN(geometry, n) —— 返回集合中指定的组成部分

test=# select ST_ASText(ST_GeometryN(ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039),(118.9830931 32.0450039, 118.9840931 32.0450039))', 4326),2));
                         st_astext                         
-----------------------------------------------------------
 LINESTRING(118.9830931 32.0450039,118.9840931 32.0450039)
(1 行记录)

test=# select ST_ASText(ST_GeometryN(ST_GeomFromText('MultiLineString((118.7830931 32.0450039, 118.8840931 32.0450039, 118.8840931 32.0650039),(118.9830931 32.0450039, 118.9840931 32.0450039))', 4326),3));
 st_astext 
-----------
 
(1 行记录)
ST_Buffer

ST_Buffer(geometry, distance)
缓冲区操作,接受几何图形和缓冲区距离作为参数，并输出一个多边形，这个多边形的边界与输入的几何图形之间的距离与输入的缓冲区距离相等。
ST_Buffer函数也接受负的距离值，从而在输入的多边形内构建内接多边形。而对于线串和点，只会返回空值。

select ST_GeomFromText( ST_ASText( ST_Buffer(ST_Boundary(geom)::geography, 1000)), 4326) as bm from bt_boundary_info where properties -> 'all_tags' ->> 'name:zh' like '%江宁%'

select ST_Buffer(ST_Boundary(geom)::geography, 1000)::geometry as bm from bt_boundary_info where properties -> 'all_tags' ->> 'name:zh' like '%江宁%'1

select station_name,ci,geom
from cmcc_metro_bts_with_geom a
where ST_Contains(
(select 
	   st_buffer(geom::geography,500)::geometry as geom
	   from nanjing_station_coordinate_with_geom b where b.station_id = a.station_id), geom)
	   
select station_name,ci,
ST_Contains(
(select 
	   st_buffer(geom::geography,500)::geometry as geom
	   from nanjing_station_coordinate_with_geom b where b.station_id = a.station_id), geom)
from cmcc_metro_bts_with_geom a
geometry与geometry的关系
ST_Distance

计算两个geometry/geography间的距离

2维：
点与点：

3维计算，只与X,Y有关
test=# select ST_Distance(ST_GeomFromText('MULTILINESTRING((118 35 2,119 35 2))', 4326), ST_GeomFromText('POINT(118 32 6)', 4326));
 st_distance 
-------------
           3
(1 行记录)

test=# select ST_Distance(ST_GeomFromText('MULTILINESTRING((118 35 2,119 35 2))', 4326), ST_GeomFromText('POINT(118 32 5)', 4326));
 st_distance 
-------------
           3
(1 行记录)

test=# select ST_Distance(ST_GeomFromText('MULTILINESTRING((118 35 2,119 35 2))'), ST_GeomFromText('POINT(118 32 5)'));
 st_distance 
-------------
           3
(1 行记录)

计算地理距离：

结果正确，单位：M
select ST_Distance('POINT(121 31)'::geography, 'POINT(124 35)'::geography)
select ST_Distance(ST_GeogFromText('MULTILINESTRING((118 35 2,119 35 2))'), ST_GeogFromText('POINT(118 32 5)'));
ST_DWithin

ST_DWithin(geometry A, geometry B, radius)
测试两个几何图形之间的距离是否在某个范围之内

SELECT name FROM nyc_streets WHERE ST_DWithin(
        geom,
        ST_GeomFromText('POINT(583571 4506714)',26918),
        10
      );
      
 select 
ST_GeomFromText('POINT(118 32)', 4326), 
ST_GeomFromText('POINT(118 32.001)', 4326),
ST_DISTANCE('POINT(118 32)'::geography, 'POINT(118 32.001)'::geography),
ST_DWithin(
        ST_GeogFromText('POINT(118 32)'),
        ST_GeogFromText('POINT(118 32.001)'),
        100
      );
ST_Contains

ST_Contains(geometry A, geometry B)
如果第二个几何图形B完全包含在第一个几何图形A内，则返回TRUE
只支持geometry之间的判断

test=# select ST_Contains(ST_GeogFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))'), ST_GeogFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))'));

ERROR: syntax error at or near "ST_Contains" LINE 1: ST_Contains(ST_GeogFromText('MULTILINESTRING((118.7830931 32...

test=# select ST_Contains(ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))',4326), ST_GeomFromText('MULTILINESTRING((118.7830931 32.0450039,118.7833968 32.0467468))',4326));
 st_contains 
-------------
 t
(1 行记录)
ST_Equals

ST_Equals(geometry A, geometry B)用于测试两个图形的空间相等性。

SELECT name, geom, ST_AsText(geom) FROM nyc_subway_stations WHERE name = 'Broad St';

SELECT name
FROM nyc_subway_stations
WHERE ST_Equals(geom, ST_GeomFromText('POINT(583571.905921312 4506714.34119218)',26918));
没有结果

SELECT ST_AsText('0101000020266900000EEBD4CF27CF2141BC17D69516315141')
POINT(583571.905921312 4506714.34119218)
SELECT ST_AsText('01010000202669000010EBD4CF27CF2141BA17D69516315141')
POINT(583571.905921312 4506714.34119218)

SELECT ST_Equals(ST_GeomFromText(ST_AsText('0101000020266900000EEBD4CF27CF2141BC17D69516315141'),4326),
ST_GeomFromText(ST_AsText('01010000202669000010EBD4CF27CF2141BA17D69516315141'),4326));

(true)

select st_distance(
	'0101000020266900000EEBD4CF27CF2141BC17D69516315141',
	'01010000202669000010EBD4CF27CF2141BA17D69516315141'
);

select ST_DWithin(
	'0101000020266900000EEBD4CF27CF2141BC17D69516315141',
	'01010000202669000010EBD4CF27CF2141BA17D69516315141',
	0.001
);

补充：
select 
	'0101000020266900000EEBD4CF27CF2141BC17D69516315141'::geography,
	'01010000202669000010EBD4CF27CF2141BA17D69516315141'::geography;
    报错
    
select 
	ST_SetSRID('0101000020266900000EEBD4CF27CF2141BC17D69516315141'::geometry, 4326),
	ST_SetSRID('01010000202669000010EBD4CF27CF2141BA17D69516315141'::geometry, 4326);
    
 select 
	ST_ASTEXT(ST_SetSRID('0101000020266900000EEBD4CF27CF2141BC17D69516315141'::geometry, 4326)::geography),
	ST_ASTEXT(ST_SetSRID('01010000202669000010EBD4CF27CF2141BA17D69516315141'::geometry, 4326));
    
 select 
	ST_distance(ST_SetSRID('0101000020266900000EEBD4CF27CF2141BC17D69516315141'::geometry, 4326)::geography,
	ST_SetSRID('01010000202669000010EBD4CF27CF2141BA17D69516315141'::geometry, 4326)::geography);
ST_Intersects

ST_Intersects(geometry A, geometry B)
如果两个图形有相同的空间部分，即如果它们的边界或内部相交，则为true。

SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(583571 4506714)', 26918));

select ST_Intersects(ST_GeomFromText('LINESTRING(118.8840931 32.0450039, 115.7840 30.0750,115 30, 116 30)', 4326), 
				  ST_GeomFromText('MultiPoint ((116 30),(115.78401 30.07501))', 4326))
 (true)
ST_Crosses

ST_Crosses(geometry A, geometry B)
对于multipoint/polygon、multipoint/linestring、linestring/linestring、linestring/polygon和linestring/multipolygon的比较，如果相交生成的几何图形的维度小于两个源几何图形的最大维度，且相交集位于两个源几何图形的内部，则ST_Crosses(geometry A, geometry B)将返回TRUE（其实就是判断两个几何图形是否交叉）

select ST_Crosses(ST_GeomFromText('LINESTRING(118.8840931 32.0450039, 115.7840 30.0750,115 30)', 4326),
	ST_GeomFromText('MultiPoint ((115.7840 30.0750),(118.8840 32.045))', 4326))
    
 (true)
 
 select ST_Crosses(ST_GeomFromText('LINESTRING(118.8840931 32.0450039, 115.7840 30.0750,115 30)', 4326), 
				  ST_GeomFromText('MultiPoint ((115 30),(118.8840 32.045))', 4326))
 （false）
 
 select ST_Crosses(ST_GeomFromText('LINESTRING(118.8840931 32.0450039, 115.7840 30.0750,115 30, 116 30)', 4326), 
				  ST_GeomFromText('MultiPoint ((115 30),(115.7840 30.0750))', 4326))
 (false)
ST_Touches

ST_Touches(geometry A, geometry B)
测试两个几何图形是否在它们的边界上接触，但在它们的内部不相交。

select ST_Touches(ST_GeomFromText('LINESTRING(118.8840931 32.0450039, 115.7840 30.0750,115 30)', 4326),
	ST_GeomFromText('MultiPoint ((115 30),(118.8840 32.045))', 4326))
    
    (true)
ST_Intersection

ST_Intersection(geometry A, geometry B)
函数返回两个参数共有的空间区域（或直线，或点）。如果参数不相交，该函数将返回一个空几何图形。

SELECT name, boroname,ST_ASText(geom),ST_Intersection(geom, ST_GeomFromText('POINT(583571 4506714)', 26918))
FROM nyc_neighborhoods
ST_Union

ST_Union(geometry, geometry)
接受两个几何图形参数并返回合并的并集

SELECT ST_Union(
  ST_Buffer('POINT(0 0)', 2),
  ST_Buffer('POINT(3 0)', 2));
注意点

对于geography类型，只有相关的少量空间函数：
ST_AsText(geography) returns text
ST_GeographyFromText(text) returns geography
ST_AsBinary(geography) returns bytea
ST_GeogFromWKB(bytea) returns geography
ST_AsSVG(geography) returns text
ST_AsGML(geography) returns text
ST_AsKML(geography) returns text
ST_AsGeoJson(geography) returns text
ST_Distance(geography, geography) returns double
ST_DWithin(geography, geography, float8) returns boolean
ST_Area(geography) returns double
ST_Length(geography) returns double
ST_Covers(geography, geography) returns boolean
ST_CoveredBy(geography, geography) returns boolean
ST_Intersects(geography, geography) returns boolean
ST_Buffer(geography, float8) returns geography[1]
ST_Intersection(geography, geography) returns geography[1]

常用场景

获取一个区域的道路或者基站数据

select * 
from dw_bts
where ST_Contains((select geom from gis_osm_boundary where local_name = '江宁区'), geom)

select *
from gis_osm_roads_free_1 
where ST_Contains((select geom from gis_osm_boundary where local_name = '江宁区'), geom);

获取一个点，道路，区域边界范围一定距离内的poi

select gid, geom as am
from gis_osm_pois_free_1 
where ST_Contains((select 
ST_Buffer(
 ST_GeomFromText('POINT(121.08679 31.44893)',4326)::geography, 10000)::geometry), geom);
 
 select gid, geom as am
from gis_osm_pois_free_1 
where ST_Contains((select 
  ST_Buffer(ST_Boundary(geom)::geography, 1000)::geometry from gis_osm_boundary where local_name = '江宁区'), geom)
Risey易木
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
利用pgsql插件PostGIS 实现地理坐标系数据转换
洛阳泰山的博客
08-01 2606
pgsql数据库添加PostGIs空间扩展查询坐标系数据转换sql的mapper.xmlmapper类service类注计算结果和专业测量软件转换的结果，误差在1毫米之间。
PostgreSQL（PgSQL）根据经纬度计算距离
malimingwq的博客
03-17 8682
今天在使用PostgreSQL的PostGIS功能来实现根据经纬度计算距离和计算某一经纬度附近X米之内的地点，实现SQL语句如下： 计算两经纬度之间距离： select ST_Distance(ST_GeomFromEWKT('SRID=4326;POINT(lng1 lat1)'),ST_SetSRID(ST_Point(lng2::double precision, lat2::double precision), 4326)) 计算(lng2, lat2)是否在(lng1, lat1)的X米之内 s
PostgreSQL对空间数据和Arcgis sde库的支持暨postgis空间数据（geometry/geographic）和st_geometry的区别与联系（详解篇）
sRhee的博客
06-13 3554
PostgreSQL对空间数据和Arcgis sde库的支持 暨 postgis空间数据（geometry/geographic）和st_geometry的区别与联系<详解篇> 兄弟，别走a，看完你就懂了，懂了文末扫码支持一下。 一、发现坑 最近在用pgsql搭建空间数据库，使用过程中突然发现多部件图形怎么也保存不进去。 二、测试坑 分别在前端applyEdit接口保存多部件图形，以...
postgresql高可用及postgis安装
qq_42988315的博客
12-30 2614
postgis
PostGIS的安装与建库(Windows)
fungis
05-12 2380
PostGIS的安装与建库(Windows)
PostGIS总结
爱是与世界平行
03-15 6787
PostGreSQL（三）PostGIS PostGreSQL（四）PostGIS-空间数据存储 PostGreSQL（五）PostGIS-常用函数 PostGreSQL（六）PostGIS-空间连接和空间索引 PostGreSQL（七）PostGIS-几何图形创建函数 PostGreSQL（八）PostGIS-图形有效性和简单性 PostGreSQL（九）PostGIS-几何图形的相等 PostGreSQL（十）PostGIS-最近领域搜索 PostGreSQL（十一）PostGIS-其他函数 一、P.
pgGIS坐标计算
qq_36069670的博客
01-06 489
增加geometry类型字段 SELECT AddGeometryColumn ('test1（表名）', 'the_geom', 4326, 'POINT', 2); 插入数据 INSERT INTO test1 (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England'); INSERT INTO test1 (id, the_geom, name) VALUES (2
PostGIS教程二：PostGIS安装和创建空间数据库
挣钱花388的博客
08-11 1806
如果是第一次运行pgAdmin，应该有一个已在pgAdmin中配置的PostGIS服务器条目（localhost:5432）。双击该条目，并在密码框中输入密码，以连接到数据库。打开数据库的树结构选项，查看可用的数据库。postgres数据库是默认的postgres用户所属的用户数据库，我们不用理会这个数据库。双击下载的文件，所有设置都使用默认设置即可，只是需要设置超级用户postgres的密码。，在pgAdmin中完成的所有查询都可以使用psql完成。，一个输入SQL命令查询的命令行工具。
PostgreSQL(PostGIS)安装与卸载
热门推荐
Shaway的博客
05-28 1万+
PostgreSQL(PostGIS)安装 第一步：安装PostgreSQL 运行postgresql-11.10-1-windows-x64.exe。 选择安装目录，我的是"E:\PostgreSQL\11"。 全选，next。 存储路径选择默认路径，next。 为数据库默认超级账户 postgres 设置密码,next。 选择默认端口5432，next。 区域选择：默认，一直next，等待安装完毕。 是否运行拓展下载工具，取消勾选，Finish,安装完毕。 第二步：安装PostGIS拓展
postgis手册。。。
10-26
英文文档。PostGIS 2.1.3 Manual。SVN Revision (12547)
批量将本地gis数据导入postgis数据库
02-23
本地topojson素材，自从接触postgis数据之后，深感使用规范的存储系统来统一管理gis数据的好处，特别是数据量大了之后，优势便更加明显，你可以选择将很多需要做空间计算的步骤转移到Postgis数据库内进行计算，要...
ms2pg数据库转换移植工具
07-19
实现从SQLserver到PostgreSQL数据库进行构架和数据内容的移植工具。
解密PGSQL数据库引擎
最新发布
01-18
 在1994年，POSTGRES被重新命名为PostgreSQL（后来缩写为PG）并发布为开源软件。自此以后，PostgreSQL的发展进入了一个快速的阶段，吸引了全球许多开发者的加入。  随着时间的推移，PGSQL的功能和性能不断得到提升...
开源GIS视频教程优化版
02-26
通过该课程的学习，主要掌握地理信息系统(GIS)概念，空间分析功能；开源GIS及内容；了解常见开源桌面、Web和移动GIS等各种平台软件功能和设计；学习开源开发方法和开源GIS项目设计。达到以下教学目的： 1、了解开源...
GIS视频教程下载
10-12
通过该课程的学习，主要掌握地理信息系统(GIS)概念，空间分析功能；开源GIS及内容；了解常见开源桌面、Web和移动GIS等各种平台软件功能和设计；学习开源开发方法和开源GIS项目设计。 课时1:试听 课时2:付费学员...
pg PostGIS教程:空间关系
zhangkaixuan456的博客
11-12 1687
到目前为止，我们只使用了测量（ST_Area、ST_Length）、序列化（ST_GeomFromText）或者反序列化（ST_AsGML）几何图形（geometry）的空间函数。这些函数的共同之处在于它们一次只能处理一个几何图形。 空间数据库之所以强大，是因为它们不仅能存储几何图形，而且还能够分析几何图形之间的关系。 诸如"哪一个是离公园最近的自行车位？"或者"地铁线路和街道的交叉路口在哪里?"这样的问题，只能通过比较、分析表示自行车位、街道和地铁线路的几何图形来回答。 OGC标准定义了以下一组用于
pg数据库中postgis的使用
Night_of_light的博客
12-27 2767
做项目中遇到一个问题，计算经纬度坐标之间的距离，每次都使用方法计算可能非常耗时，恰好pg数据库中有postGIS这个插件，可以建立空间数据库，直接使用数据库的方法计算，非常方便 安装postGIS插件 首先去网站下载对应的版本的插件插件网址，我的pg是13版本的，所以下载13版本，下载之后就可以安装了，但是需要注意，插件和数据库安装的同一目录下，我的数据库安装目录是D:\software\PostgreSQL\13，所以插件也要安装在这个目录下面。不然会报错 postGIS的使用 1、在postgrsql中
pg PostGIS教程:几何图形（geometry）
zhangkaixuan456的博客
11-11 3953
一、本文涉及的函数 ST_Area: Returns the area of the surface if it is a polygon or multi-polygon. For “geometry” type area is in SRID units. For “geography” area is in square meters. ST_AsText: Returns the Well-Known Text (WKT) representation of the geometry/geogr
postgresql | 数据库 |PostGIS的主要Extension（扩展插件）
zsk_john的技术分享专用博客
03-18 3378
PostGIS的主要Extension（扩展插件） 一，postgis postgis的基本核心功能，仅支持地理图形（矢量要素），在其他Extension前启用。这个是基础插件，必须要安装的。 二，postgis_raster 对栅格数据的支持。安装过postgis后可直接启用该扩展，默认不启用。 三，postgis_topology 拓扑功能的支持。安装过postgis后可直接启用该扩展，默认不启用。 四，postg...
pg gis拓展添加
05-26
要添加PostGIS扩展，需要遵循以下步骤： 1. 确认PostgreSQL已经安装并正常运行。 2. 安装PostGIS软件包。可以使用以下命令在Ubuntu上安装： ``` sudo apt-get update sudo apt-get install postgis postgresql-<version>-postgis-<version> ``` 其中，`<version>`应为PostgreSQL和PostGIS的版本号，例如，对于PostgreSQL 12和PostGIS 3.0，命令应为： ``` sudo apt-get install postgis postgresql-12-postgis-3 ``` 3. 在PostgreSQL数据库中启用PostGIS扩展。可以使用以下命令： ``` sudo -u postgres psql <database_name> CREATE EXTENSION postgis; ``` 其中，`<database_name>`应为要启用PostGIS扩展的数据库名称。 4. 验证PostGIS扩展是否已启用。可以使用以下命令： ``` SELECT PostGIS_Version(); ``` 如果已成功启用PostGIS扩展，则会显示PostGIS的版本号。 以上就是添加PostGIS扩展的基本步骤。需要注意的是，不同的操作系统有不同的安装和配置方法。如果您遇到困难，请参考相关的文档或社区支持。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值