欧拉系统postgresql 与PostGis 离线环境安装

postgresql 与PostGis 离线环境安装

上传文件至服务器

#安装所需依赖
yum install /opt/PGsql-13-gis/rpm/* -y

Postgresql安装

tar -zxvf postgresql-13.2.tar.gz
#进入该目录
 ./configure --prefix=/usr/local/pgsql --with-uuid=ossp --with-libxml
make
make install
 #添加用户
 useradd postgres
 mkdir /data/pgsql/data -p
 mkdir /data/pgsql/log -p
 touch /data/pgsql/log/server.log
 chown postgres:postgres /data/pgsql/ -R
 #关闭selinux
  setenforce 0 
  sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
  #使用systemd管理pgsql
  cp contrib/start-scripts/linux /etc/init.d/pgsql
  
  ---
  #添加init.d为可选项
  vi /etc/init.d/pgsql
  ###
  PGLOG="/usr/local/pgsql/log/server.log" #修改此项
  ###
  
  chmod a+x /etc/init.d/pgsql
  chkconfig --add /etc/init.d/pgsql
  echo '/usr/local/pgsql/lib' >> /etc/ld.so.conf
  ldconfig
  ---
  
  echo 'export PATH=/usr/local/pgsql/bin/:$PATH' >> /etc/profile #配置环境变量
  echo 'export PGDATA=/data/pgsql/data'  >> /etc/profile
  echo 'export PGHOME=/usr/local/pgsql/:$PATH' >> /etc/profile
  echo 'export LD_LIBRARY_PATH=/usr/local/pgsql/lib/:$LD_LIBRARY_PATH' >> /etc/profile
  source /etc/profile
  su - postgres
  ###
  initdb -D /data/pgsql/data/
  pg_ctl -D /data/pgsql/data/ -l /data/pgsql/log/server.log start
  pg_ctl -D /data/pgsql/data/ -l /data/pgsql/log/server.log stop
  exit
  ###
  systemctl start pgsql #启动数据库

#initdb报错解决方案
echo "kernel.shmmni = 8192" >> /etc/sysctl.conf
sysctl -p

设置外部访问

vi /usr/local/pgsql/data/pg_hba.conf
###
# IPv4 local connections:
host    all             all             0.0.0.0/0               trust
###

vi /usr/local/pgsql/data/postgresql.conf
###
listen_addresses = '*'          # what IP address(es) to listen on;
###

firewall-cmd --zone=public --add-port=5432/tcp --permanent     ##配置防火墙放行5432端口
firewall-cmd --reload       ##防火墙重新加载配置文件
systemctl restart pgsql

安装PostGis

 echo 'export CMAKE_HOME=/usr/bin/cmake' >> /etc/profile

安装proj

tar -zxvf proj-6.1.0.tar.gz
./configure --prefix=/usr/local/proj
make
make install
echo "/usr/local/proj/lib/" >> /etc/ld.so.conf
ldconfig

安装geos

tar -xvf geos-3.8.0.tar.bz2
./configure --prefix=/usr/local/geos
make
make install
echo "/usr/local/geos/lib/" >> /etc/ld.so.conf
ldconfig

安装gdal

tar -zxvf gdal-3.3.3.tar.gz
./configure --prefix=/usr/local/gdal --with-proj=/usr/local/proj
make
make install
echo "/usr/local/gdal/lib/" >> /etc/ld.so.conf
ldconfig

安装json-c

unzip json-c-json-c-0.13.zip
./configure --prefix=/usr/local/json-c/
make
make install
echo "/usr/local/json-c/lib/" >> /etc/ld.so.conf
ldconfig

安装protobuf

tar -zxvf protobuf-3.20.3.tar.gz
./autogen.sh
./configure --prefix=/usr/local/protobuf
make
make install 
echo '/usr/local/protobuf/lib/' >> /etc/ld.so.conf
ldconfig
echo 'export PKG_CONFIG_PATH=/usr/local/protobuf/lib/pkgconfig/' >> /etc/profile
echo 'export PROTOBUF=/usr/local/protobuf' >> /etc/profile
source /etc/profile

安装protobuf-c

tar -zxvf protobuf-c-1.4.0.tar.gz
./autogen.sh
./configure --prefix=/usr/local/protobuf-c/
make
make install
echo "/usr/local/protobuf-c/lib/" >> /etc/ld.so.conf
ldconfig
echo 'export PATH=$CMAKE_HOME/bin:$PROTOBUF_HOME/bin:$PATH:/usr/local/protobuf-c/bin' >> /etc/profile
echo 'export PATH=$CMAKE_HOME/bin:$PROTOBUF_HOME/bin:$PATH:/usr/local/protobuf-c/bin:/usr/local/gadl/bin' >> /etc/profile
source /etc/profile

安装CGAL

tar -zxvf cgal-releases-CGAL-4.7.tar.gz
cmake3 . -DCMAKE_INSTALL_PREFIX=/usr/local/cgal
make
make install
echo "/usr/local/cgal/lib/" >> /etc/ld.so.conf
ldconfig

安装SFCGAL

tar -zxvf SFCGAL-v1.3.10.tar.gz
cmake3 . -DCMAKE_INSTALL_PREFIX=/usr/local/sfcgal
make
make install
echo "/usr/local/sfcgal/lib64" >>  /etc/ld.so.conf
ldconfig

安装postgis

 tar -zxvf postgis-3.1.6.tar.gz
 ./configure --prefix=/usr/local/postgis --with-pgsql=/usr/local/pgsql/bin/pg_config --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-geosconfig=/usr/local/geos/bin/geos-config  --with-projdir=/usr/local/proj --with-gdalconfig=/usr/local/gdal/bin/gdal-config --with-jsondir=/usr/local/json-c  --with-protobufdir=/usr/local/protobuf-c --with-sfcgal=/usr/local/sfcgal/bin/sfcgal-config
 make
 make install

安装pointcloud

tar -zxvf pointcloud-1.2.2.tar.gz
 ./autogen.sh
./configure --prefix=/usr/local/pointcloud --with-pgconfig=/usr/local/pgsql/bin/pg_config --with-xml2config=/usr/bin/xml2-config
make
make install

安装插件

#进入postgresql-13.2下的contrib目录
cd .../postgresql-13.2/contrib/
#安装插件
cd adminpack/ && make && make install && cd ..
cd dblink/ && make && make install && cd ..
cd fuzzystrmatch/ && make && make install && cd ..
cd hstore/ && make && make install && cd ..
cd postgres_fdw/ && make && make install && cd ..
cd tablefunc/ && make && make install && cd ..
cd uuid-ossp/ && make && make install && cd ..
cd xml2/ && make && make install && cd .. 

进入数据库激活插件

ldconfig
su - postgres
psql
##
create extension postgis;
create extension postgis_raster;
create extension postgis_sfcgal;
create extension fuzzystrmatch ;
create extension postgis_tiger_geocoder;
create extension postgis_topology;
create extension adminpack;
create extension dblink ;
create extension hstore ;
create extension postgres_fdw ;
create extension tablefunc ;
create extension "uuid-ossp";
create extension xml2 ;
create extension pointcloud;
create extension pointcloud_postgis ;

测试插件是否安装成功

-- 建表
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_DistanceSphere(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);