本文安装环境是 CentOS7.6。安装版本是 postgresql-11 。推荐关闭SELinux和防火墙打开相应端口。

安装 postgresql-server。postgesql-server 官方安装 。

#设置安装源[root@promote ~]# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-ppc64le/pgdg-centos11-11-2.noarch.rpm#安装postgresql11-server[root@promote ~]# yum install postgresql11-server postgresql11-client postgresql11-libs postgresql11-contrib postgresql11-devel#初始化数据库[root@promote ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb#添加开机启动[root@promote ~]# systemctl enable postgresql-11

安装 postgis 。参考postgis 官方安装Red Hat / Centos / Scientific Linux 选项。

#安装epel-release[root@promote ~]# yum -y install epel-release#安装postgis[root@promote ~]# sudo yum install postgis25_11 postgis25_11-devel postgis25_11-client postgis25_11-debuginfo postgis25_11-utils#安装ogr_fdw11扩展[root@promote ~]# yum install ogr_fdw11 ogr_fdw11-debuginfo pgrouting_11 pgrouting_11-debuginfo#可选添加开机启动[root@promote ~]# systemctl enable postgresql-11#启动和查看postgresql-11服务[root@promote ~]# systemctl start postgresql-11[root@promote ~]# systemctl status postgresql-11[root@promote ~]# systemctl list-unit-files | grep postgresqlpostgresql-11.service                         enabled #查看开机启动[root@promote ~]# systemctl list-unit-files | grep postgresqlpostgresql-11.service                         enabled [root@promote ~]# #查看数据库服务端口开启情况[root@promote ~]# netstat -ntlp | grep posttcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      10592/postmaster    tcp6       0      0 ::1:5432                :::*                    LISTEN      10592/postmaster    [root@promote ~]#

创建用户，创建数据库gistest，切换用户到postgres，连接和切换数据库，查看postgis版本信息。

[root@promote ~]# su - postgres#非第一次登录[root@promote ~]# su - postgres上一次登录：五 4月 12 16:51:07 CST 2019pts/1 上-bash-4.2$ psqlpsql (11.2)输入 "help" 来获取帮助信息.postgres=# #安装EXTENSIONpostgres=# CREATE EXTENSION postgis;CREATE EXTENSIONpostgres=# CREATE EXTENSION postgis_topology;CREATE EXTENSIONpostgres=# CREATE EXTENSION adminpack;CREATE EXTENSIONpostgres=# CREATE EXTENSION postgis_sfcgal;CREATE EXTENSIONpostgres=# CREATE EXTENSION fuzzystrmatch;CREATE EXTENSIONpostgres=# CREATE EXTENSION address_standardizer;CREATE EXTENSIONpostgres=# CREATE EXTENSION address_standardizer_data_us;CREATE EXTENSIONpostgres=# CREATE EXTENSION postgis_tiger_geocoder;CREATE EXTENSIONpostgres=# postgres=# \dx                                                                             已安装扩展列表             名称             | 版本  |  架构模式  |                                                        描述                                                         ------------------------------+-------+------------+--------------------------------------------------------------------------------------------------------------------- address_standardizer         | 2.5.2 | public     | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step. address_standardizer_data_us | 2.5.2 | public     | Address Standardizer US dataset example adminpack                    | 2.0   | pg_catalog | administrative functions for PostgreSQL fuzzystrmatch                | 1.1   | public     | determine similarities and distance between strings plpgsql                      | 1.0   | pg_catalog | PL/pgSQL procedural language postgis                      | 2.5.2 | public     | PostGIS geometry, geography, and raster spatial types and functions postgis_sfcgal               | 2.5.2 | public     | PostGIS SFCGAL functions postgis_tiger_geocoder       | 2.5.2 | tiger      | PostGIS tiger geocoder and reverse geocoder postgis_topology             | 2.5.2 | topology   | PostGIS topology spatial types and functions(9 行记录)postgres=# \d                        关联列表 架构模式 |           名称           |  类型  |  拥有者  ----------+--------------------------+--------+---------- public   | city                     | 数据表 | postgres public   | geography_columns        | 视图   | postgres public   | geometry_columns         | 视图   | postgres public   | raster_columns           | 视图   | postgres public   | raster_overviews         | 视图   | postgres public   | spatial_ref_sys          | 数据表 | postgres public   | us_gaz                   | 数据表 | postgres public   | us_gaz_id_seq            | 序列数 | postgres public   | us_lex                   | 数据表 | postgres public   | us_lex_id_seq            | 序列数 | postgres public   | us_rules                 | 数据表 | postgres public   | us_rules_id_seq          | 序列数 | postgres tiger    | addr                     | 数据表 | postgres tiger    | addr_gid_seq             | 序列数 | postgres tiger    | addrfeat                 | 数据表 | postgres tiger    | addrfeat_gid_seq         | 序列数 | postgres tiger    | bg                       | 数据表 | postgres tiger    | bg_gid_seq               | 序列数 | postgres tiger    | county                   | 数据表 | postgres tiger    | county_gid_seq           | 序列数 | postgres tiger    | county_lookup            | 数据表 | postgres tiger    | countysub_lookup         | 数据表 | postgres tiger    | cousub                   | 数据表 | postgres tiger    | cousub_gid_seq           | 序列数 | postgres tiger    | direction_lookup         | 数据表 | postgres tiger    | edges                    | 数据表 | postgres tiger    | edges_gid_seq            | 序列数 | postgres tiger    | faces                    | 数据表 | postgres tiger    | faces_gid_seq            | 序列数 | postgres tiger    | featnames                | 数据表 | postgres tiger    | featnames_gid_seq        | 序列数 | postgres tiger    | geocode_settings         | 数据表 | postgres tiger    | geocode_settings_default | 数据表 | postgres tiger    | loader_lookuptables      | 数据表 | postgres tiger    | loader_platform          | 数据表 | postgres tiger    | loader_variables         | 数据表 | postgres tiger    | pagc_gaz                 | 数据表 | postgres tiger    | pagc_gaz_id_seq          | 序列数 | postgres tiger    | pagc_lex                 | 数据表 | postgres tiger    | pagc_lex_id_seq          | 序列数 | postgres tiger    | pagc_rules               | 数据表 | postgres tiger    | pagc_rules_id_seq        | 序列数 | postgres tiger    | place                    | 数据表 | postgres tiger    | place_gid_seq            | 序列数 | postgres tiger    | place_lookup             | 数据表 | postgres tiger    | secondary_unit_lookup    | 数据表 | postgres tiger    | state                    | 数据表 | postgres tiger    | state_gid_seq            | 序列数 | postgres tiger    | state_lookup             | 数据表 | postgres tiger    | street_type_lookup       | 数据表 | postgres tiger    | tabblock                 | 数据表 | postgres tiger    | tabblock_gid_seq         | 序列数 | postgres tiger    | tract                    | 数据表 | postgres tiger    | tract_gid_seq            | 序列数 | postgres tiger    | zcta5                    | 数据表 | postgres tiger    | zcta5_gid_seq            | 序列数 | postgres tiger    | zip_lookup               | 数据表 | postgres tiger    | zip_lookup_all           | 数据表 | postgres tiger    | zip_lookup_base          | 数据表 | postgres tiger    | zip_state                | 数据表 | postgres tiger    | zip_state_loc            | 数据表 | postgres topology | layer                    | 数据表 | postgres topology | topology                 | 数据表 | postgres topology | topology_id_seq          | 序列数 | postgres(64 行记录)postgres=# postgres=# create database gistest;postgres=#  \c gistest#\x 开启或关闭扩展显示gistest=# \x扩展显示已关闭。gistest=# \d                     关联列表 架构模式 |       名称        |  类型  |  拥有者  ----------+-------------------+--------+---------- public   | geography_columns | 视图   | postgres public   | geometry_columns  | 视图   | postgres public   | raster_columns    | 视图   | postgres public   | raster_overviews  | 视图   | postgres public   | spatial_ref_sys   | 数据表 | postgres topology | layer             | 数据表 | postgres topology | topology          | 数据表 | postgres topology | topology_id_seq   | 序列数 | postgres(8 行记录)gistest=# \l                                     数据库列表   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        -----------+----------+----------+-------------+-------------+----------------------- gistest   | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |  template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres(4 行记录)#查看版本信息postgres=# gistest=# \q-bash-4.2$ psqlpsql (11.2)输入 "help" 来获取帮助信息.postgres=# select postgis_full_version();                                                                                                  postgis_full_version                                                                                                   ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" SFCGAL="1.2.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER(1 行记录)postgres=# exit-bash-4.2$ exit登出[root@promote ~]#

连接postgres数据库，创建测试表city。添加测算表和值。

postgres=# \c postgrespostgres=# \x扩展显示已打开。#创建测试表postgres=# CREATE TABLE city (id int4, name varchar(50));CREATE TABLEpostgres=# SELECT AddGeometryColumn ('city', 'the_geom', 4326, 'POINT', 2);-[ RECORD 1 ]-----+--------------------------------------------------addgeometrycolumn | public.city.the_geom SRID:4326 TYPE:POINT DIMS:2 postgres=# select * from city;(0 行记录)postgres=# INSERT INTO city (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-0.1257 51.508)',4326),'London, England');INSERT 0 1postgres=# INSERT INTO city (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-81.233 42.983)',4326),'London, Ontario');INSERT 0 1postgres=# INSERT INTO city (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(27.91162491 -33.01529)',4326),'East London,SA');INSERT 0 1postgres=#

使用简单查询和空间查询。

postgres=# SELECT * FROM city;-[ RECORD 1 ]------------------------------------------------id       | 1name     | London, Englandthe_geom | 0101000020E6100000BBB88D06F016C0BF1B2FDD2406C14940-[ RECORD 2 ]------------------------------------------------id       | 2name     | London, Ontariothe_geom | 0101000020E6100000F4FDD478E94E54C0E7FBA9F1D27D4540-[ RECORD 3 ]------------------------------------------------id       | 3name     | East London,SAthe_geom | 0101000020E610000040AB064060E93B4059FAD005F58140C0postgres=# SELECT id, ST_AsText(the_geom), ST_AsEwkt(the_geom), ST_X(the_geom), ST_Y(the_geom) FROM city;-[ RECORD 1 ]-------------------------------------id        | 1st_astext | POINT(-0.1257 51.508)st_asewkt | SRID=4326;POINT(-0.1257 51.508)st_x      | -0.1257st_y      | 51.508-[ RECORD 2 ]-------------------------------------id        | 2st_astext | POINT(-81.233 42.983)st_asewkt | SRID=4326;POINT(-81.233 42.983)st_x      | -81.233st_y      | 42.983-[ RECORD 3 ]-------------------------------------id        | 3st_astext | POINT(27.91162491 -33.01529)st_asewkt | SRID=4326;POINT(27.91162491 -33.01529)st_x      | 27.91162491st_y      | -33.01529postgres=# SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM city AS p1, city AS p2 WHERE p1.id > p2.id;警告:  ST_Distance_Sphere signature was deprecated in 2.2.0. Please use ST_DistanceSphere-[ RECORD 1 ]------+-----------------name               | London, Ontarioname               | London, Englandst_distance_sphere | 5875787.03777356-[ RECORD 2 ]------+-----------------name               | East London,SAname               | London, Englandst_distance_sphere | 9789680.59961472-[ RECORD 3 ]------+-----------------name               | East London,SAname               | London, Ontariost_distance_sphere | 13892208.6782928postgres=# postgres=# SELECT p1.name,p2.name,ST_DistanceSphere(p1.the_geom,p2.the_geom) FROM city AS p1, city AS p2 WHERE p1.id > p2.id;警告:  ST_Distance_Sphere signature was deprecated in 2.2.0. Please use ST_DistanceSphere-[ RECORD 1 ]------+-----------------name               | London, Ontarioname               | London, Englandst_distance_sphere | 5875787.03777356-[ RECORD 2 ]------+-----------------name               | East London,SAname               | London, Englandst_distance_sphere | 9789680.59961472-[ RECORD 3 ]------+-----------------name               | East London,SAname               | London, Ontariost_distance_sphere | 13892208.6782928postgres=# SELECT p1.name,p2.name,ST_DistanceSphere(p1.the_geom,p2.the_geom) FROM city AS p1, city AS p2 WHERE p1.id > p2.id;-[ RECORD 1 ]-----+-----------------name              | London, Ontarioname              | London, Englandst_distancesphere | 5875787.03777356-[ RECORD 2 ]-----+-----------------name              | East London,SAname              | London, Englandst_distancesphere | 9789680.59961472-[ RECORD 3 ]-----+-----------------name              | East London,SAname              | London, Ontariost_distancesphere | 13892208.6782928postgres=#