postgresql+postgis+geoserver+opstreetmap+osm2pgsql 搭建使用（整理，亲测可用）

最新推荐文章于 2025-03-25 20:51:10 发布

ljl668

最新推荐文章于 2025-03-25 20:51:10 发布

阅读量630

点赞数 1

本文链接：https://blog.youkuaiyun.com/qingshu0400/article/details/88655417

版权

首先：postgressql 搭建

1. 创建postgres用户和组

# groupadd -g 101 dba

# useradd -u 501 -g dba -G root -d /usr/local/pgsql postgres
2. 添加postgres用户环境变量

# tail -n 12 /etc/sysctl.conf
vm.overcommit_memory=1
vm.overcommit_ratio=90
fs.aio-max-nr=1048576
fs.file-max= 7672460
net.ipv4.ip_local_port_range=9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586
kernel.sem= 50100 64128000 50100 1280
kernel.shmall=5242880
kernel.shmmax=12884901888
# tail -n 4 /etc/security/limits.conf
postgres soft nproc 8192
postgres hard nproc 16384
postgres soft nofile 4096
postgres hard nofile 65536
4. 安装依赖包
# yum install -y python-devel perl-ExtUtils-Embed python-devel gcc-c++ openssl-devel readline readline-devel zlib zlib-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel openldap openldap-devel libgeos-dev libproj-dev libgdal-dev xsltproc docbook-xsl docbook-xml imagemagick libmagickcore-dev dblatex tcl tcl-devel unixODBC unixODBC-devel libpng12 libpng12-devel
5. 安装PostgreSQL
$ wget https://ftp.postgresql.org/pub/source/v9.5.7/postgresql-9.5.7.tar.bz2
$ tar -jxf postgresql-9.5.7.tar.bz2
$ cd postgresql-9.5.7
$ ./configure --prefix=/usr/local/pgsql --with-wal-segsize=32 --with-perl --with-python --with-gssapi --with-pam --with-ldap --with-openssl --with-tcl --with-libxml --with-libxslt
$ make
$ make install
$ cd contrib/
$ make && make install
$ mkdir /usr/local/pgsql/{data,arch,plugin}
$ sudo echo "su - postgres -c 'pg_ctl start -D /usr/local/pgsql/data'" >> /etc/rc.local
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/
$ cat /usr/local/pgsql/data/postgresql.conf | grep -v "^$" | grep -v "^#"
listen_addresses = '*'        # what IP address(es) to listen on;
port = 5432                # (change requires restart)
max_connections = 300            # (change requires restart)
shared_buffers = 2048MB            # min 128kB
logging_collector = on        # Enable capturing of stderr and csvlog
log_directory = 'pg_log'        # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
$ cat /usr/local/pgsql/data/pg_hba.conf | grep -v "^$" | grep -v "^#"
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               md5
host    all             all             ::1/128                 trust
$ pg_ctl start -W
$ psql 
psql (9.5.7)
Type "help" for help.

postgres=# select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)
6.安装PostGIS
 6.1 安装Proj4
$ wget http://download.osgeo.org/proj/proj-4.9.3.tar.gz
$ tar -xf proj-4.9.3.tar.gz
$ cd proj-4.9.3
$ ./configure --prefix=/usr/local/pgsql/plugin/proj
$ make
$ make install
# echo "/usr/local/pgsql/plugin/proj/lib" > /etc/ld.so.conf.d/proj-4.9.3.conf
# ldconfig
6.2 安装GEOS
$ wget http://download.osgeo.org/geos/geos-3.6.1.tar.bz2
$ tar -jxf geos-3.6.1.tar.bz2
$ cd geos-3.6.1
$ ./configure --prefix=/usr/local/pgsql/plugin/geos
$ make
$ make install
# echo "/usr/local/pgsql/plugin/geos/lib" > /etc/ld.so.conf.d/geos-3.6.1.conf
# ldconfig
6.3 安装GDAL
$ wget http://download.osgeo.org/gdal/2.1.2/gdal-2.1.2.tar.gz
$ tar -xf gdal-2.1.2.tar.gz
$ cd gdal-2.1.2
$ ./configure --prefix=/usr/local/pgsql/plugin/gdal
$ make
$ make install
# echo "/usr/local/pgsql/plugin/gdal/lib" > /etc/ld.so.conf.d/gdal-2.1.2.conf
# ldconfig
6.4 安装PostGIS
$ wget http://download.osgeo.org/postgis/source/postgis-2.2.5.tar.gz
$ tar -xf postgis-2.2.5.tar.gz
$ cd postgis-2.2.5
$ ./configure --prefix=/usr/local/pgsql/plugin/postgis --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-geosconfig=/usr/local/pgsql/plugin/geos/bin/geos-config --with-gdalconfig=/usr/local/pgsql/plugin/gdal/bin/gdal-config --with-projdir=/usr/local/pgsql/plugin/proj
..............................................................................................
  PostGIS is now configured for x86_64-pc-linux-gnu

 -------------- Compiler Info ------------- 
  C compiler:           gcc -g -O2
  SQL preprocessor:     /usr/bin/cpp -traditional-cpp -w -P

 -------------- Dependencies -------------- 
  GEOS config:          /usr/local/pgsql/plugin/geos/bin/geos-config
  GEOS version:         3.6.1
  GDAL config:          /usr/local/pgsql/plugin/gdal/bin/gdal-config
  GDAL version:         2.1.2
  PostgreSQL config:    /usr/local/pgsql/bin/pg_config
  PostgreSQL version:   PostgreSQL 9.5.7
  PROJ4 version:        49
  Libxml2 config:       /usr/bin/xml2-config
  Libxml2 version:      2.7.6
  JSON-C support:       no
  PCRE support:         no
  PostGIS debug level:  0
  Perl:                 /usr/bin/perl

 --------------- Extensions --------------- 
  PostGIS Raster:       enabled
  PostGIS Topology:     enabled
  SFCGAL support:       disabled
  Address Standardizer support:       disabled

 -------- Documentation Generation -------- 
  xsltproc:             /usr/bin/xsltproc
  xsl style sheets:     /usr/share/sgml/docbook/xsl-stylesheets
  dblatex:              
  convert:              
  mathml2.dtd:          http://www.w3.org/Math/DTD/mathml2/mathml2.dtd
$ make
$ make install
7. 检查PostGiS是否安装成功
postgres=# CREATE EXTENSION postgis;
ERROR:  could not load library "/usr/local/pgsql/lib/rtpostgis-2.2.so": libgdal.so.20: cannot open shared object file: No such file or directory
postgres=# \! sudo find / -name libgdal.so.20
/opt/soft/gdal-2.1.2/.libs/libgdal.so.20
/usr/local/pgsql/plugin/gdal/lib/libgdal.so.20
postgres=# \! sudo cp /usr/local/pgsql/plugin/gdal/lib/libgdal.so.20 /usr/local/pgsql/lib/
postgres=# \! sudo chown postgres:dba /usr/local/pgsql/lib/libgdal.so.20
postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgres=# CREATE EXTENSION postgis_topology;
CREATE EXTENSION
postgres=# CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
postgres=# CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
postgres=# \dx
                                            List of installed extensions
          Name          | Version |   Schema   |                             Description                             
------------------------+---------+------------+---------------------------------------------------------------------
 fuzzystrmatch          | 1.0     | public     | determine similarities and distance between strings
 plpgsql                | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis                | 2.2.5   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_tiger_geocoder | 2.2.5   | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 2.2.5   | topology   | PostGIS topology spatial types and functions
(5 rows)

注意：啟動時切換到postgres用戶！！

引用博文：①https://www.cnblogs.com/ilifeilong/p/6979288.html?utm_source=itdadao&utm_medium=referral ② https://www.jianshu.com/p/e08dbc60a3b2

postgres數據庫創建及簡單命令參考 https://www.cnblogs.com/weihengblog/p/10082570.html

其次：geoserver搭建可参考：https://mp.youkuaiyun.com/postedit/87930659

然后：openstreetmap 数据准备直接去官网下载osm数据

osm2pgsql 搭建直接在win下面直接下载exe安装版通过window直接将数据导入centos的postgresql

可参考博文：https://blog.youkuaiyun.com/qq_31709249/article/details/72842150