PostgreSQL【部署 01】离线安装PostgreSQL+PostGIS踩坑及问题解决经验分享（含安装文件PostgreSQL+PostGIS及多个依赖+测试SQL）

链接:https://pan.baidu.com/s/1XPUoyReHOKyjNINpcyrMRQ 
提取码:9f26

1. postgresql-9.5.9.tar.gz
2. postgis-2.2.3.tar.gz
3. bzip2-1.0.6.tar.gz
4. gdal-2.1.2.tar.gz
5. geos-3.3.7.tar.bz2
6. json-c-0.12.1.tar.gz
7. libxml2-2.6.26.tar.gz
8. proj-4.9.3.tar.gz
9. protobuf-cpp-3.1.0.tar.gz

# 组件安装
yum -y install gcc
yum -y install bzip2
yum -y install autoconf
yum -y install zlib-devel
yum -y install gcc gcc-c++ 
yum -y install readline-devel
# 添加用户组和用户（postgreSQL不能以root用户运行）
groupadd postgres
useradd -g postgres postgres
passwd postgres

# 1.解压
tar -zxvf postgresql-9.5.9.tar.gz 
# 2.在解压的目录内执行命令： prefix为安装目录
./configure –prefix=/usr/local/pgsql # 这个阶段要注意error信息，解决完全部error后再进行安装。
make
make install
# 3.环境变量配置
vi /etc/profile
  # ---添加配置---
  export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
  export PATH=/usr/local/pgsql/bin:$PATH
  export MANPATH=/usr/local/pgsql/man:$MANPATH
# ---配置生效---
source /etc/profile
# 4.在/usr/local/pgsql文件夹下创建数据库数据文件夹
mkdir /usr/local/pgsql/data
chown -R postgres.postgres /usr/local/pgsql/data
# 切换为postgres用户
su postgres
# 5.在/usr/local/pgsql/bin文件夹下执行数据库初始化 
# 设置locale为C,并且template编码为UNICODE,使数据库支持中文
./initdb --locale=C -E UNICODE -D ../data/
# 6.使用root用户,更改日志文件的属主为postgres,修改后切回postgres用户
chown postgres /var/log/pgsql.log
# 7.修改配置/usr/local/pgsql/data
vi postgresql.conf # 使监听生效
listen_addresses='*'
port=5432
vi pg_hba.conf
host all all 0.0.0.0/0 md5
# 8.设置密码 /usl/local/pgsql/bin/ 否则用户postgres无法连接数据库
psql
ALTER USER postgres WITH PASSWORD 'postgres'; 
# 9.启动停止数据库 /usl/local/pgsql/bin/
./pg_ctl start -D /usr/local/pgsql/data/
./pg_ctl stop -D /usr/local/pgsql/data/

# 1.解压 配置 编译 安装
tar -zxvf proj-4.8.0.tar.gz
cd proj-4.8.0
./configure --prefix=/opt/proj-4.8.0
make
make install
# 2.库的路径添加到系统中
vi /etc/ld.so.conf.d/proj-4.8.0.conf
  # ---添加配置---
  /opt/proj-4.8.0/lib
# 配置生效
ldconfig

# 1.解压 配置 编译 安装
tar -jxvf geos-3.3.7.tar.bz2
cd geos-3.3.7
./configure --prefix=/opt/geos-3.3.7
make
make install
# 2.库的路径添加到系统中
vi /etc/ld.so.conf.d/geos-3.3.7.conf
  # ---添加配置---
  /opt/geos-3.3.7/lib
# 配置生效
ldconfig

# 解决gdal无法获取的问题
vi /etc/ld.so.conf
  # ---配置---
  include /etc/ld.so.conf.d/*.conf
  /usr/local/pgsql/lib
# 使配置生效
ldconfig –v

tar -zxvf postgis-2.2.3.tar.gz
cd postgis-2.2.3
# 具体目录要根据扩展文件安装目录而定 
./configure --prefix=/opt/postgis --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-geosconfig=/opt/geos-3.3.7/bin/geos-config --with-projdir=/opt/proj-4.9.3/  --with-xml2config=/opt/libxml2-2.6.26/bin/xml2-config --with-gdalconfig=/opt/gdal-2.1.2/bin/gdal-config 
make
make install

-- 建表
CREATE TABLE cities ( id int4, name varchar(50) );
-- 添加位置字段
SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);
-- 插入几条数据
INSERT INTO cities (id, the_geom, name) VALUES (1,ST_GeomFromText('POINT(-5.911 3.115)',4326),'BeiJing,China');
INSERT INTO cities (id, the_geom, name) VALUES (2,ST_GeomFromText('POINT(-5.921 3.215)',4326),'BeiJing,China');
INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(-5.931 3.315)',4326),'BeiJing,China');
INSERT INTO cities (id, the_geom, name) VALUES (4,ST_GeomFromText('POINT(-5.941 3.415)',4326),'BeiJing,China');
INSERT INTO cities (id, the_geom, name) VALUES (5,ST_GeomFromText('POINT(-5.951 3.515)',4326),'BeiJing,China');
INSERT INTO cities (id, the_geom, name) VALUES (6,ST_GeomFromText('POINT(-15.951 13.515)',4326),'Out,BeiJing,China');
-- 查询全表
select id,name,ST_AsText(the_geom) from cities ;
-- 查询任意两点间球面距离，并以id排序
SELECT p1.name,p2.name,ST_Distance_Sphere(p1.the_geom,p2.the_geom) FROM cities AS p1, cities AS p2 WHERE p1.id > p2.id;
-- 查询矩形内的点
select id, name, ST_AsText(the_geom) from cities where the_geom && ST_SetSRID(ST_MakeBox2D(ST_POINT(-10.0,-10.0),ST_POINT(10.0,10.0)),4326);
-- 任意给出几个点，查询该空间范围内的点，第一个点和最后一个点应是同一个点
select id,name,ST_AsText(cities.the_geom) from cities where the_geom && ST_AsText(ST_MakePolygon(ST_GeomFromText('LINESTRING ( -10.31 10.97 , -10.15 -10.09 , 10.35 10.27 , 10.31 -10.97 , -10.31 10.97)')));
-- 同上
select id,name,ST_AsText(cities.the_geom) from cities where the_geom && ST_MakePolygon(ST_GeomFromText('LINESTRING ( -10.31 10.97 , -10.15 -10.09 , 10.35 10.27 , 10.09 -10.88 , 30.31 -40.97 , -1.11 60.33 , -10.31 10.97)'));
-- string应该用单引号，双引号会报错
update cities set name = 'America,LAS' where id = 5;
-- 删除数据
delete from cities where id = 6;
-- 查询距离点（-87.71 43.741）距离为151600000米的所有点
SELECT name,st_astext(the_geom) FROM cities WHERE ST_DWithin(ST_Transform(ST_GeomFromText('POINT(-87.71 43.741)',4326),26986),ST_Transform(the_geom,26986), 151600000);