欧拉体系postgresql 与PostGis 离线环境安装
postgresql 与PostGis 离线环境安装上传文件至服务器
#安装所需依赖
yum install /opt/PGsql-13-gis/rpm/* -yPostgresql安装
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);
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]