在空间数据管理与应用中，PostgreSQL几乎是最具性价比的解决方案，它同时具备了开源、稳定和功能强大的特性。在本课程中，我们将通过图形界面GUI、SQL语言和简单的Python代码，带领大家领略PostgreSQL及其空间模块PostGIS和网络模块pgrouting的神奇魅力。我们将了解时空数据的输入与输出，空间数据管理工作流，带领学员一起下载数据，构建道路拓扑网络，从而实现最短路径分析及服务区域分析（DrivingDistance）等高级主题的全过程。

0、安装与登录 - 图1

课程内容:

课程1：PostgreSQL数据管理基本操作

  • PostgreSQL、PostGIS及pgRouting模块介绍
  • pgAdmin4的使用简介
  • PostgreSQL建库及基本SQL操作
  • 将空间数据导入PostgreSQL的方法
  • 空间数据查询
  • 空间数据分析

课程2：PostgreSQL网络建构及分析

  • 拓扑道路网的建构及展示
  • 如何在PostgreSQL道路网中实现最短路径分析
  • 如何在PostgreSQL中实现服务区域分析

课程3：使用Python展示PostgreSQL的数据分析成果

  • 在Jupyter lab中展示地图
  • 使用Python来访问PostgreSQL数据库
  • 将PostgreSQL的分析结果的地图可视化

下载和安装PostgreSQL

0、安装与登录 - 图2

  • 下载PostgreSQL

https://www.postgresql.org/

0、安装与登录 - 图3

  • 下载pgAdmin4

https://www.pgadmin.org/

0、安装与登录 - 图4

  • 使用pgcli命令行
  1. brew upgrade pgcli
  2. pgcli -U postgres

1.OSX平台

参考材料

  • 删除之前的版本
brew uninstall --force pgrouting
brew uninstall --force postgis
brew uninstall --force postgresql
rm -rf /usr/local/var/postgres
  • OSX平台上安装PostgreSQL和PostGIS

brew会给OSX平台安装上最新版本的postgresql和postgis服务。

brew install postgresql
brew services start postgresql
brew link postgresql --force
brew install postgis
brew install pgrouting

如果是要安装9.6及相关插件，可以使用http://www.kyngchaos.com/software/postgres/ 中编译的dmg版本。安装完dmg后，必须执行一系列操作：

cd  /usr/local/pgsql/bin
./initdb -D /usr/local/var/postgres
./pg_ctl -D /usr/local/var/postgres start

如果无法启动，而是出现address被占用的情况，则查看占用进程并使用Kill 杀掉

ps -ef | grep postmaster | awk '{print $2}'

./createuser postgres
./psql postgres
  • 新建用户postgres并设置密码
createuser postgres
psql  postgres

会以管理员身份进入界面，然后修改postgres用户的密码及给予其superuser身份

postgres=# ALTER USER postgres with password 'archesadmin';
postgres=# ALTER ROLE postgres Superuser;

可以使用 \du 查看用户权限

\du

然后以postgres的身份登入

psql -U postgres
  • 启动和关闭服务
brew services start postgresql
brew services stop postgresql
  • 连接错误

如果在psql访问数据库时发现问题

psql: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

解决方法是删除/usr/local/var/postgres/postmaster.pid即可。

2.Ubuntu安装

2.1 14.04

安装过程：

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
wget -q -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update
sudo apt-get install postgresql-9.6 postgresql-contrib  postgresql-9.6-postgis-2.3  postgresql-9.6-pgrouting

2.2 18.04

  • 1、预处理
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt bionic-pgdg main" >> /etc/apt/sources.list'

wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -

sudo apt update
  • 2、安装postgresql+postgis
sudo apt install postgresql-10
sudo apt install postgresql-10-postgis-2.5
  • 3、修改用户密码
sudo -u postgres psql
ALTER USER postgres with encrypted password 'archesadmin';
  • 4、添加数据库和表
sudo -u postgres psql
create database geodb;
\c geodb
create extension postgis;
\q

3.CentOS安装9.6

rpm -U https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm

yum install epel-release
yum install postgresql96 postgresql96-contrib postgresql96-devel postgresql96-server postgis2_96

新建数据库

# cd /var/lib/pgsql/9.6/
# su postgres
$ /usr/pgsql-9.6/bin/initdb data/
$ exit

启动服务

service postgresql-9.6 start

登录

psql -U postgres
# ALTER USER postgres with password 'archesadmin';

4.数据处理

  • 将shapefile数据插入postgis

使用shp2pgsql:

shp2pgsql -c -g geom -D -s 4326 -S -i -I guangzhou_china_osm_line2 gzroads | psql -d pgdb -U postgres -h 127.0.0.1 -p 5432

shp2pgsql -I -s 4326 -W 'latin1' india.shp PlaceName | psql -d osmdata -U postgres -h 127.0.0.1 -p 5432

如果是向已经存在的表中加入数据，可以使用

shp2pgsql -I -s 4326 -W 'latin1' -a india.shp PlaceName | psql -d osmdata -U postgres -h 127.0.0.1 -p 5432
  • 转换为sql
shp2pgsql -I -s 4326 -W 'latin1' pk.shp PlaceName > PlaceName.sql
psql -d osmdata -U postgres -h 127.0.0.1 -p 5432 < PlaceName.sql
  • shp2pgsql的安装

一般地，在windows中安装postgresql和postgis会自动安装shp2pgsql的工具，但在ubuntu中，这个工具是需要另外安装的。

sudo apt-get install postgis-gui
  • postgresql数据库备份与恢复

备份

pg_dump -h localhost -U postgres databasename > databasename.bak

恢复

psql -h localhost -U postgres -d databasename < databasename.bak