关于PostGIS插件
PostGIS是对象关系型数据库PostgreSQL的一个插件，PostGIS提供如下空间信息服务功能：空间对象、空间索引、空间操作函数和空间操作符。同时，PostGIS遵循OpenGIS的规范。关于PostGIS具体支撑的GIS功能，可以在http://postgis.net/官网查看其官方手册，报告PostGIS插件的版本要求、依赖要求等均有说明。
PostGIS版本选择及依赖组件安装
PostGIS在安装前需要选择与PostgreSQL版本适配的版本来进行安装，PostGIS版本与PostgreSQL版本的适配说明在http://postgis.net/官网中有具体说明，由于我本地的是PostgreSQL15.4，所以用最新的PostGIS3.4.0的版本是没有任何问题的。
同时我们可以看到PostGIS插件对依赖的组件的要求，官网中关于组件依赖说明如下：
The PostGIS Team is pleased to release PostGIS 3.4.0! This version works with versions PostgreSQL 12-16, GEOS 3.6 or higher, and Proj 6.1+. To take advantage of all features, GEOS 3.12+ is needed. To take advantage of all SFCGAL features, SFCGAL 1.4.1+ is needed.
需要特别提醒一点，事实证明，组件一般选择满足要求的最低版本，个组件尽量选择发版时间相近的就可以了，不要盲目追求最新的，不然可能会涉及其他的很多的依赖安装。
PostgreSQL —— PostGIS构建于PostgreSQL之上，所以PostgreSQL必须要安装。
GNU C 编译器(gcc) —— gcc是一个Linux中最标准的C语言编译器，需要安装gcc来编译PostGIS和其他软件或函数库的源码。
GNU Make(gmake或make) —— 这个也是用于编译源码。
Proj4 —— Proj4 重投影库用于在PostGIS中提供坐标重投影功能。
GEOS —— GEOS几何图形库，用于支持PostGIS中的几何信息处理、分析等功能，也可以直接认为GEOS是一个几何算法库。
LibXML2 —— LibXML2目前用于PostGIS中的一些导入函数，比如ST_GeomFromGML()和ST_GeomFromKML()。
JSON-C —— 目前使用JSON-C通过ST_GeomFromGeoJSON()函数导入GeoJSON格式的数据
GDAL —— 用于PostGIS对栅格数据的支持。
SFCGAL —— 用于PostGIS对三维数据的支持。
PostGIS —— PostgreSQL的空间数据、空间索引和空间函数的扩展。PROJ组件安装
proj组件下载：http://download.osgeo.org/proj/，选择适合版本即可，不建议过高版本，适配的最低版本就可以，避免新版本可能依赖新的组件。
[root@node01 tools]# tar xzvf proj-6.2.0.tar.gz
[postgres@localhost pg_tools]$ ls
proj-6.2.0 proj-6.2.0.tar.gz
[postgres@localhost pg_tools]$ cd proj-6.2.0/
[postgres@localhost proj-6.2.0]$ pwd
/soft/postgresql/pg_tools/proj-6.2.0
[postgres@localhost proj-6.2.0]$ mkdir -p /soft/postgresql/pg
pgdata/ pgplugin/ pgsql/ pg_tools/
[postgres@localhost proj-6.2.0]$ mkdir -p /soft/postgresql/pgplugin/geo_proj
[postgres@localhost proj-6.2.0]$ ./configure --prefix=/soft/postgresql/pgplugin/geo_proj
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for SQLITE3... configure: error: Package requirements (sqlite3 >= 3.7) were not met:
Package 'sqlite3', required by 'virtual:world', not found
Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.
Alternatively, you may set the environment variables SQLITE3_CFLAGS
and SQLITE3_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
[postgres@localhost proj-6.2.0]$
[root@localhost ~]# yum install sqlite-devel
[postgres@localhost proj-6.2.0]$ ./configure --prefix=/soft/postgresql/pgplugin/geo_proj
[postgres@localhost proj-6.2.0]$ make -j8
[postgres@localhost proj-6.2.0]$ make install安装proj组件后配置和加载动态链接库：
[root@localhost ~]# ll /soft/postgresql/pgplugin/geo_proj/lib/
total 181804
-rw-r--r-- 1 postgres postgres 135338288 Aug 23 20:52 libproj.a
-rwxr-xr-x 1 postgres postgres 964 Aug 23 20:52 libproj.la
lrwxrwxrwx 1 postgres postgres 17 Aug 23 20:52 libproj.so -> libproj.so.15.2.0
lrwxrwxrwx 1 postgres postgres 17 Aug 23 20:52 libproj.so.15 -> libproj.so.15.2.0
-rwxr-xr-x 1 postgres postgres 50819976 Aug 23 20:52 libproj.so.15.2.0
drwxrwxr-x 2 postgres postgres 21 Aug 23 20:52 pkgconfig
[root@localhost ~]# vi /etc/ld.so.conf.d/proj-6.2.0.conf
[root@localhost ~]# cat /etc/ld.so.conf.d/proj-6.2.0.conf
/soft/postgresql/pgplugin/geo_proj/lib
[root@localhost ~]# ldconfig
[root@localhost ~]# GEOS组件安装
geos组件下载链接：http://download.osgeo.org/geos/，选择合适的版本进行下载，这里选择最新版本geos-3.6.0。一定先安装proj组件，因为geos组件会依赖它。
[postgres@localhost pg_tools]$ ls
[postgres@localhost pg_tools]$ rz
[postgres@localhost pg_tools]$ ls
geos-3.6.0.tar.bz2
[postgres@localhost pg_tools]$ bzip2 -d geos-3.6.0.tar.bz2
[postgres@localhost pg_tools]$ ls
geos-3.6.0.tar
[postgres@localhost pg_tools]$ tar -xf geos-3.6.0.tar
[postgres@localhost pg_tools]$ ls
geos-3.6.0 geos-3.6.0.tar
[postgres@localhost pg_tools]$
[postgres@localhost geos-3.6.0]$ pwd
/soft/postgresql/pg_tools/geos-3.6.0
[postgres@localhost geos-3.6.0]$ mkdir -p /soft/postgresql/pgplugin/geos
[postgres@localhost geos-3.6.0]$ ./configure --prefix=/soft/postgresql/pgplugin/geos
checking build system type… x86_64-pc-linux-gnu
checking host system type… x86_64-pc-linux-gnu
……
config.status: executing libtool commands
Swig: false
Python bindings: false
Ruby bindings: false
编译中的Swig: false Python && bindings: false && Ruby bindings: false 三个失败可以忽略，不影响后面的安装
[postgres@localhost geos-3.6.0]$ make -j4
[postgres@localhost geos-3.6.0]$ make install安装后配置和加载动态链接库：
[root@localhost ~]# ll /soft/postgresql/pgplugin/geos/
total 0
drwxrwxr-x 2 postgres postgres 25 Aug 24 01:12 bin
drwxrwxr-x 3 postgres postgres 48 Aug 24 01:12 include
drwxrwxr-x 2 postgres postgres 191 Aug 24 01:12 lib
[root@localhost ~]# vi /etc/ld.so.conf.d/geo-3.6.0.conf
[root@localhost ~]# cat /etc/ld.so.conf.d/geo-3.6.0.conf
/soft/postgresql/pgplugin/geos/lib
[root@localhost ~]# ldconfig
[root@localhost ~]# GDAL组件安装
gdal组件下载链接：http://download.osgeo.org/gdal/，选择适配版本下载，这里选择版本为gdal2.0.1
[postgres@localhost pg_tools]$ ls
gdal-2.0.1.tar.gz geos-3.6.0 geos-3.6.0.tar proj-6.2.0 proj-6.2.0.tar.gz
[postgres@localhost pg_tools]$ tar -xzvf gdal-2.0.1.tar.gz
[postgres@localhost pg_tools]$ cd gdal-2.0.1
[postgres@localhost gdal-2.0.1]$ mkdir -p /soft/postgresql/pgplugin/geo_gdal
[postgres@localhost gdal-2.0.1]$ ./configure --prefix=/soft/postgresql/pgplugin/geo_gdal
[postgres@localhost gdal-2.0.1]$ make -j8
[postgres@localhost gdal-2.0.1]$ make install
(cd port; make)
make[1]: Entering directory '/soft/postgresql/pg_tools/gdal-2.0.1/port'
make[1]: Nothing to be done for 'default'.
degrib18/g2clib-1.0.4/dec_jpeg2000.cpp:18:10: fatal error: jasper/jasper.h: No such file or directory
#include <jasper/jasper.h>
^~~~~~~~~~~~~~~~~
compilation terminated.
make[2]: *** [GNUmakefile:31: ../o/dec_jpeg2000.lo] Error 1
make[2]: Leaving directory '/soft/postgresql/pg_tools/gdal-2.0.1/frmts/grib'
make[1]: *** [GNUmakefile:10: grib-install-obj] Error 2
make[1]: Leaving directory '/soft/postgresql/pg_tools/gdal-2.0.1/frmts'
make: *** [GNUmakefile:61: frmts-target] Error 2
[postgres@localhost gdal-2.0.1]$
[root@localhost pg_tools]$ wget http://download.osgeo.org/gdal/jasper-1.900.1.uuid.tar.gz
--2023-08-24 01:44:12-- http://download.osgeo.org/gdal/jasper-1.900.1.uuid.tar.gz
Resolving download.osgeo.org (download.osgeo.org)... 140.211.15.30
Connecting to download.osgeo.org (download.osgeo.org)|140.211.15.30|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1139115 (1.1M) [application/octet-stream]
Saving to: ‘jasper-1.900.1.uuid.tar.gz’
jasper-1.900.1.uuid.tar. 100%[=================================>] 1.09M 706KB/s in 1.6s
2023-08-24 01:44:19 (706 KB/s) - ‘jasper-1.900.1.uuid.tar.gz’ saved [1139115/1139115]
[root@localhost pg_tools]$ tar -xzvf jasper-1.900.1.uuid.tar.gz
[root@localhost pg_tools]$ cd jasper-1.900.1.uuid/
[root@localhost jasper-1.900.1.uuid]$ ./configure
[root@localhost jasper-1.900.1.uuid]$ make -j8 && make install
[root@localhost ~]# su - postgres
Last login: Thu Aug 24 01:23:15 PDT 2023 on pts/1
[postgres@localhost data]$ cd /soft/postgresql/pg_tools/gdal-2.0.1/
[postgres@localhost gdal-2.0.1]$ make install安装成功后配置和加载动态链接库：
[root@localhost ~]# ll /soft/postgresql/pgplugin/geo_gdal/
total 12
drwxr-xr-x 2 postgres postgres 4096 Aug 24 01:48 bin
drwxr-xr-x 2 postgres postgres 4096 Aug 24 01:48 include
drwxr-xr-x 4 postgres postgres 141 Aug 24 01:48 lib
drwxrwxr-x 3 postgres postgres 18 Aug 24 01:48 share
[root@localhost ~]# vi /etc/ld.so.conf.d/gdal-2.0.1.conf
[root@localhost ~]# cat /etc/ld.so.conf.d/gdal-2.0.1.conf
/soft/postgresql/pgplugin/geo_gdal/lib
/soft/postgresql/pgsql/lib
#最后一行添加原因是后期可能会遇到postgis在configure阶段报错*configure: error: could not find gdal*
[root@localhost ~]#
[root@localhost ~]# ldconfig
[root@localhost ~]#
POSTGIS安装
postgis下载链接地址：http://download.osgeo.org/postgis/source/
[postgres@localhost pg_tools]$ ls
gdal-2.0.1 geos-3.6.0.tar postgis-3.4.0.tar.gz
gdal-2.0.1.tar.gz jasper-1.900.1.uuid proj-6.2.0
geos-3.6.0 jasper-1.900.1.uuid.tar.gz proj-6.2.0.tar.gz
[postgres@localhost pg_tools]$ tar -xzvf postgis-3.4.0.tar.gz
[postgres@localhost pg_tools]$ cd postgis-3.4.0
[postgres@localhost postgis-3.4.0]$ pwd
/soft/postgresql/pg_tools/postgis-3.4.0
[postgres@localhost postgis-3.4.0]$ mkdir -p /soft/postgresql/pgplugin/postgis
[postgres@localhost postgis-3.4.0]$ ./configure --prefix=/soft/postgresql/pgplugin/postgis --with-pgconfig=/soft/postgresql/pgsql/bin/pg_config --with-geosconfig=/soft/postgresql/pgplugin/geos/bin/geos-config --with-gdalconfig=/soft/postgresql/pgplugin/geo_gdal/bin/gdal-config --with-projdir=/soft/postgresql/pgplugin/geo_proj
--编译检查最后报错
checking for protobuf-c/protobuf-c.h... no
configure: error: unable to find protobuf-c/protobuf-c.h using CPPFLAGS. You can disable MVT and Geobuf support using --without-protobuf
[postgres@localhost postgis-3.4.0]$
--手动安装protobuf
--地址
https://github.com/google/protobuf/releases/download/v3.6.1/protobuf-all-3.6.1.tar.gz
[root@localhost ~]# mkdir -p /usr/local/protobuf
[root@localhost ~]# chown postgres:postgres /usr/local/protobuf/ -R
[root@localhost ~]# chmod 755 /usr/local/protobuf/ -R
[root@localhost ~]# su - postgres
Last login: Thu Aug 24 05:36:21 PDT 2023 on pts/2
[postgres@localhost data]$ cd /soft/postgresql/pg_tools/protobuf-3.6.1
[postgres@localhost protobuf-3.6.1]$ ./configure --prefix=/usr/local/protobuf
[postgres@localhost protobuf-3.6.1]$ make -j8
[postgres@localhost protobuf-3.6.1]$ make install
--手动安装protobuf-c
--地址：https://github.com/google/protobuf/releases
[root@localhost ~]# mkdir -p /usr/local/protobuf-c
[root@localhost ~]# su - postgres
Last login: Thu Aug 24 06:05:51 PDT 2023 on pts/2
[postgres@localhost data]$ cd /soft/postgresql/pg_tools
[postgres@localhost pg_tools]$ tar -xzvf protobuf-c-1.4.1.tar.gz
[postgres@localhost pg_tools]$ cd protobuf-c-1.4.1/
[postgres@localhost protobuf-c-1.4.1]$ ./configure --prefix=/usr/local/protobuf-c
……
checking for protobuf... no
configure: error: Package requirements (protobuf >= 2.6.0) were not met:
Package 'protobuf', required by 'virtual:world', not found
Consider adjusting the PKG_CONFIG_PATH environment variable if you
installed software in a non-standard prefix.
Alternatively, you may set the environment variables protobuf_CFLAGS
and protobuf_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
[postgres@localhost protobuf-c-1.4.1]$
[root@localhost ~]# find / -name protobuf.pc
/usr/local/protobuf/lib/pkgconfig/protobuf.pc
[postgres@localhost protobuf-c-1.4.1]$ export PKG_CONFIG_PATH=/usr/local/protobuf/lib/pkgconfig
[postgres@localhost protobuf-c-1.4.1]$ ./configure --prefix=/usr/local/protobuf-c
……
config.status: executing libtool commands
protobuf-c 1.4.1
CC: gcc
CFLAGS: -g -O2
CXX: g++ -std=c++11
CXXFLAGS: -g -O2
LDFLAGS:
LIBS:
prefix: /usr/local/protobuf-c
sysconfdir: ${prefix}/etc
libdir: ${exec_prefix}/lib
includedir: ${prefix}/include
pkgconfigdir: ${libdir}/pkgconfig
bigendian: no
protobuf version: libprotoc 3.6.1
[postgres@localhost protobuf-c-1.4.1]$ make -j8
[postgres@localhost protobuf-c-1.4.1]$ make install
[postgres@localhost pg_tools]$ cd postgis-3.4.0/
[postgres@localhost postgis-3.4.0]$ ls
aclocal.m4 configure.ac fuzzers NEWS SECURITY.md
astyle.sh CONTRIBUTING.md GNUmakefile.in postgis sfcgal
autogen.sh COPYING liblwgeom postgis_config.h.in spatial_ref_sys.sql
build-aux CREDITS libpgcommon postgis_revision.h STYLE
ChangeLog deps LICENSE.TXT raster TODO
CODE_OF_CONDUCT.md doc loader README.md topology
config.log extensions macros README.postgis utils
configure extras Makefile regress Version.config
[postgres@localhost postgis-3.4.0]$ ./configure --prefix=/soft/postgresql/pgplugin/postgis --with-pgconfig=/soft/postgresql/pgsql/bin/pg_config --with-geosconfig=/soft/postgresql/pgplugin/geos/bin/geos-config --with-gdalconfig=/soft/postgresql/pgplugin/geo_gdal/bin/gdal-config --with-projdir=/soft/postgresql/pgplugin/geo_proj
--编译检查最后报错
checking for protobuf-c/protobuf-c.h... no
configure: error: unable to find protobuf-c/protobuf-c.h using CPPFLAGS. You can disable MVT and Geobuf support using --without-protobuf
[postgres@localhost postgis-3.4.0]$
--仍然无法解决protobuf问题，即使做了配合，protobuf相关bin/lib变量也均已添加无效，如下：
[postgres@localhost postgis-3.4.0]$ cat ~/.bash_profile
#PG15 configs
export PG_HOME=/soft/postgresql/pgsql
export PG_BIN=/soft/postgresql/pgsql/bin
export PATH=/soft/postgresql/pgplugin/geos/bin://soft/postgresql/pgplugin/geo_gdal/bin:/soft/postgresql/pgplugin/geo_proj/bin:/usr/local/protobuf/bin:/usr/local/protobuf-c/bin:$PATH:$PG_BIN
export PGDATA=/soft/postgresql/pgdata/data
# cd $PGDAA
export LD_LIBRARY_PATH=/usr/local/protobuf/lib:/usr/local/protobuf-c/lib:$LD_LIBRARY_PATH
export PKG_CONFIG_PATH=/usr/local/protobuf/lib/pkgconfig
[postgres@localhost postgis-3.4.0]$
--暂时忽略protobuf
[postgres@localhost postgis-3.4.0]$ ./configure --prefix=/soft/postgresql/pgplugin/postgis --with-pgconfig=/soft/postgresql/pgsql/bin/pg_config --with-geosconfig=/soft/postgresql/pgplugin/geos/bin/geos-config --with-gdalconfig=/soft/postgresql/pgplugin/geo_gdal/bin/gdal-config --with-projdir=/soft/postgresql/pgplugin/geo_proj --without-protobuf
……
PostGIS is now configured for x86_64-pc-linux-gnu
-------------- Compiler Info -------------
C compiler: gcc -std=gnu99 -g -O2 -fno-math-errno -fno-signed-zeros -Wall
C++ compiler (FlatGeobuf): gcc -std=c++11 -x c++
CPPFLAGS: -I/soft/postgresql/pgplugin/geos/include -I/soft/postgresql/pgplugin/geo_proj/include -I/usr/include/libxml2 -DNDEBUG
LDFLAGS: -lm
SQL preprocessor: /usr/bin/cpp -traditional-cpp -w -P -Upixel -Ubool
Archiver: gcc-ar rs
-------------- Additional Info -------------
Interrupt Tests: ENABLED
-------------- Dependencies --------------
GEOS config: /soft/postgresql/pgplugin/geos/bin/geos-config
GEOS version: 3.6.0
GDAL config: /soft/postgresql/pgplugin/geo_gdal/bin/gdal-config
GDAL version: 2.0.1
PostgreSQL config: /soft/postgresql/pgsql/bin/pg_config
PostgreSQL version: PostgreSQL 15.4
PROJ4 version: 62
Libxml2 config: /usr/bin/xml2-config
Libxml2 version: 2.9.7
JSON-C support: no
protobuf support: no
PCRE support: Version 2
Perl: /usr/bin/perl
--------------- Extensions ---------------
PostgreSQL EXTENSION support: enabled
PostGIS Raster: enabled
PostGIS Topology: enabled
SFCGAL support: disabled
Address Standardizer support: enabled
-------- Documentation Generation --------
xsltproc: /usr/bin/xsltproc
xsl style sheets:
dblatex:
convert:
mathml2.dtd: http://www.w3.org/Math/DTD/mathml2/mathml2.dtd
configure: WARNING: --------- GEOS VERSION WARNING ------------
configure: WARNING: You are building against GEOS 3.6.0.
configure: WARNING:
configure: WARNING: To take advantage of _all_ the features of
configure: WARNING: PostGIS, GEOS 3.12.0 or higher is required.
configure: WARNING:
configure: WARNING: You can download the latest versions from
configure: WARNING: http://geos.osgeo.org/
configure: WARNING:
configure: WARNING:
configure: WARNING: | You are building using --with-projdir. This option isn't standard and |
configure: WARNING: | might be incompatible with future releases of PROJ. |
configure: WARNING: | You can instead adjust the PKG_CONFIG_PATH environment variable if you |
configure: WARNING: | installed software in a non-standard prefix. |
configure: WARNING: | Alternatively, you may set the environment variables PROJ_CFLAGS and |
configure: WARNING: | PROJ_LIBS to avoid the need to call pkg-config. |
[postgres@localhost postgis-3.4.0]$
[postgres@localhost postgis-3.4.0]$ make -j8
[postgres@localhost postgis-3.4.0]$ make install
--如果权限不足用root去安装，然后调整权限
[root@localhost ~]# chown postgres:postgres /soft/postgresql/ -R
[root@localhost ~]# chmod 755 /soft/postgresql/ -R
[root@localhost ~]#
PostgreSQL加载使用postgis
查验postgis是否安装成功
[postgres@localhost ~]$ psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
psql (15.4)
Type "help" for help.
postgres=# FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%';
ERROR: syntax error at or near "FROM"
LINE 1: FROM pg_available_extensions WHERE name LIKE 'postgis%' or n...
^
postgres=# SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%';
name | default_version | installed_version
------------------------------+-----------------+-------------------
postgis | 3.4.0 |
postgis_tiger_geocoder | 3.4.0 |
postgis_raster | 3.4.0 |
postgis_topology | 3.4.0 |
address_standardizer | 3.4.0 |
address_standardizer_data_us | 3.4.0 |
(6 rows)
postgres=#
启用postgis
每个数据库想要使用PostGIS必须先在该数据库中使PostGIS可用。假设我们想在postgres这个默认的数据库中使用PostGIS,先进入gisdb数据库，执行以下步骤：
[postgres@localhost ~]$ psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
psql (15.4)
Type "help" for help.
postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgres=# CREATE EXTENSION postgis_topology;
CREATE EXTENSION
postgres=#
输入\dx，查看已安装的插件
postgres-# \dx
List of installed extensions
Name | Version | Schema | Description
------------------+---------+------------+---------------------------------------------------------
---
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.4.0 | public | PostGIS geometry and geography spatial types and functio
ns
postgis_topology | 3.4.0 | topology | PostGIS topology spatial types and functions
(3 rows)
postgres-#
可以看到已经安装了postgis和postgis_topology。
原创文章，作者：lzb，如若转载，请注明出处：https://www.wlkjzx.com/2023/08/25/postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e6%8f%92%e4%bb%b6postgis%e5%ae%89%e8%a3%85-redhat8/
微信扫一扫 