PostGIS学习笔记

PostGIS是PostgreSQL的一个extension，用来处理空间地理信息(Geographic Information Systems Extensions to PostgreSQL)。

一、PostGIS安装与配置

不同的PostgreSQL对PostGIS的版本有要求，可以先yum search postgis查看源中有哪些版本，挑选自己合适的版本。安装语句：

yum  install  postgis24_10.x86_64  -y

安装后，可以查看可用的扩展查看是否安装正确。并直接安装

postgres=# select name from pg_available_extensions where name ~ 'gis';
          name
------------------------
 btree_gist
 postgis
 postgis_sfcgal
 postgis_tiger_geocoder
 postgis_topology
(5 rows)
​
postgres=# create extension postgis;
ERROR:  extension "postgis" already exists
postgres=# \dx
                                       List of installed extensions
    Name     | Version |   Schema   |                             Description
-------------+---------+------------+---------------------------------------------------------------------
 pageinspect | 1.6     | public     | inspect the contents of database pages at a low level
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis     | 2.4.4   | public     | PostGIS geometry, geography, and raster spatial types and functions
(3 rows)
​
postgres=#

二、数据类型

数据类型主要分两大类，geometry(几何类型)和geography(地理类型)，每种类型包括点、线、多边形、复合几何体等。PostGIS提供了大量实用的函数。

postgres=# create table my_geometry(
postgres(# t_point geometry(point),
postgres(# t_linestring geometry(linestring),
postgres(# t_polygon geometry(polygon),
postgres(# t_multipoint geometry(multipoint),
postgres(# t_multilinestring geometry(multilinestring),
postgres(# t_multipolygon geometry(multipolygon),
postgres(# t_all geometry);
CREATE TABLE
postgres=#
postgres=# create table my_geography(
t_point geography(point),
t_linestring geography(linestring),
t_polygon geography(polygon),
t_multipoint geography(multipoint),
t_multilinestring geography(multilinestring),
t_multipolygon geography(multipolygon),
t_all geography);
CREATE TABLE
postgres=#

Geometry和Geography的区别，geometry是一个二维平面坐标系，geography是一个地理坐标系（4326号坐标系，指代WGS84国际标准GPS坐标系）

Geometry和Geography是所有类型的超类，可以存储所有的Geometry和Geography类型。

三、类型的输入与输出

点的输入

postgres=#
postgres=# insert into my_geometry (t_point) values
('point(0 0)'::geometry),
('010100000000000000000000000000000000000000'::geometry),
(st_point(0,0));
INSERT 0 3
postgres=# insert into my_geography (t_point) values
('point(0 0)'::geography),
('010100000000000000000000000000000000000000'::geography),
(st_point(0,0)::geography);
INSERT 0 3
postgres=#
postgres=#
postgres=# select t_point from my_geometry ;
                  t_point
--------------------------------------------
 010100000000000000000000000000000000000000
 010100000000000000000000000000000000000000
 010100000000000000000000000000000000000000
(3 rows)
​
postgres=# select t_point from my_geography ;
                      t_point
----------------------------------------------------
 0101000020E610000000000000000000000000000000000000
 0101000020E610000000000000000000000000000000000000
 0101000020E610000000000000000000000000000000000000
(3 rows)
​
postgres=#

点的输出

postgres=# select st_astext(t_point),st_asgeojson(t_point) from my_geometry limit 1;
 st_astext  |             st_asgeojson
------------+--------------------------------------
 POINT(0 0) | {"type":"Point","coordinates":[0,0]}
(1 row)
​
postgres=# select st_astext(t_point),st_asgeojson(t_point) from my_geography limit 1;
 st_astext  |             st_asgeojson
------------+--------------------------------------
 POINT(0 0) | {"type":"Point","coordinates":[0,0]}
(1 row)
​
postgres=#

四、计算

1、计算两点距离。

Geometry类型，（0，0）到（1，1）的距离，应该是根号2，与实际符合。

Geography类型，（北纬0度，东经0度，到北纬0度，东经180度），也就是半个赤道长度的一半20000公里，与实际符合。在北极点（北纬90度)，东经0度与东经180距离为0，与实际符合。

postgres=# select 'point(0 0)'::geometry <-> 'point(1 1)'::geometry
postgres-# ;
    ?column?
-----------------
 1.4142135623731
(1 row)
​
postgres=# select 'point(0 0)'::geography <-> 'point(180 0)'::geography;
     ?column?
------------------
 20015114.3522337
(1 row)
postgres=# select 'point(0 90)'::geography <-> 'point(180 90)'::geography;
       ?column?
----------------------
 7.80223549925316e-10
(1 row)

2、计算线的长度

(北纬0度，东经0度，到北纬1度，东经1度）的距离，大约是157千米。线段（0，0）到（1，1）的距离，应该是根号2。

postgres=# select st_length('linestring(0 0, 1 1)'::geography);
    st_length
-----------------
 156899.56829134
(1 row)
​
postgres=# select st_length('linestring(0 0, 1 1)'::geometry);
    st_length
-----------------
 1.4142135623731
(1 row)
​
postgres=#

3、计算面积

变长为1的正方形面积是1。（东经0度北纬0度，东经1度北纬0度，东经1度北纬1度，东经0度北纬1度）四个点位图区域的面积是12308.77平方千米。

postgres=# select st_area('polygon((0 0,1 0,1 1,0 1,0 0))'::geometry);
 st_area
---------
       1
(1 row)
​
postgres=# select st_area('polygon((0 0,1 0,1 1,0 1,0 0))'::geography);
     st_area
------------------
 12308778361.4695
(1 row)
​
postgres=#

五、空间索引

空间类型可以建立gist索引。

postgres=# create index idx_my_geometry_t_all on my_geometry using gist(t_all);
CREATE INDEX
postgres=# create index idx_my_geography_t_all on my_geography using gist(t_all);
CREATE INDEX
postgres=#