postgis2.2安装教程
目录
PostGIS-2.2
安装PostgreSQL-9.5
#配置好yum源,yum安装postgresql,安装yum源方法见最后的备注
yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel
#可依据需要修改postgresql的数据存储目录,默认为/var/lib/pgsql/9.5/data(可选项)
1. vim /usr/lib/systemd/system/postgresql-9.5.service
2. 修改Environment=PGDATA=/var/lib/pgsql/9.5/data为新建数据目录
#初始化postgresql
/usr/pgsql-9.5/bin/postgresql95-setup initdb
#配置远程访问postgresql,即修改data目录下的pg_hba.conf和postgresql.conf,默认的data目录是/var/lib/pgsql/9.5/data(可选项)
1. 修改postgresql.conf
listen_addresses = '*'
2. 修改pg_hba.conf,添加如下内容
host all all 0.0.0.0/0 trust
#启动服务,并配置开机自启
systemctl start postgresql-9.5
systemctl enable postgresql-9.5
su - postgres
cd ~/
/usr/pgsql-9.5/bin/psql -p 5432 -c "CREATE EXTENSION adminpack;"
安装PostGIS-2.2
yum install postgis2_95 postgis2_95-client
安装ogrfdw-1.0
yum install ogr_fdw95
安装pgRouting-2.4
yum install pgrouting_95
Enabling Extensions in a database
su - postgres
/usr/pgsql-9.5/bin/psql -p 5432
CREATE DATABASE gistest;
\connect gistest;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION ogr_fdw;
SELECT postgis_full_version();
CREATE EXTENSION pgrouting;
SELECT * FROM pgr_version();
SQL(验证gis)
//建表
CREATE TABLE cities ( id int4, name varchar(50) );
//添加位置字段
SELECT AddGeometryColumn ('cities', 'the_geom', 4326, 'POINT', 2);
//插入几条数据
INSERT INTO cities (id, the_geom, name) VALUES (3,ST_GeomFromText('POINT(-5.911 3.015)',4326),'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 = 33";
//查询距离点(-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);
PostgreSQL数据目录迁移
若想将原有的postgresql数据目录迁移至更大的磁盘空间,可使用如下方法,仅限同一服务器不同磁盘目录迁移,不同服务器未验证。
#创建新的数据目录,修改用户属主,修改权限
1. mkdir /data/volume_b/postgresql/data
2. cd /data/volume_b/postgresql
3. chown postgres:postgres data
4. chomd 0700 data/
#修改postgresql-9.5.service
1. vim /usr/lib/systemd/system/postgresql-9.5.service
2. 修改Environment=PGDATA=/data/volume_b/postgresql/data
#修改postgresql.conf,默认在/var/lib/pgsql/9.5/data
1. vim postgresql.conf
2. 修改data_directory = '/data/volume_b/postgresql/data'
#迁移原来数据目录中的数据至新的数据目录,若未修改过数据目录,默认的为/var/lib/pgsql/9.5/data/*
//以防万一也可使用cp命令
1. mv -r /var/lib/pgsql/9.5/data/* /data/volume_b/postgresql/data
2. cd data/volume_b/postgresql/data
3. chown postgres:postgres -R *
#由于修改systemd下面的文件,所以需重新加载
systemctl daemon-reload
#启动服务,完成数据目录迁移工作
systemctl start postgresql-9.5
备注:安装postgis的yum源
1.下载安装postgis所需的所有rpm包
http://download.csdn.net/download/jc_xian/10022303
http://download.csdn.net/download/jc_xian/10022511
http://download.csdn.net/download/jc_xian/10022513
2.配置yum源
http://blog.csdn.net/jc_xian/article/details/78241576
转载自:https://blog.csdn.net/jc_xian/article/details/78241471