1、基本SQL操作 - 图1

PostgreSQL是一个免费、开源的数据库服务器，它在灵活的BSD许可证下发行。与其他开放源代码数据库系统（比如MySQL和Firebird）和商业数据库系统（比如Oracle、Sybase、IBM的DB2和Microsoft SQL Server）之外，为用户又提供了一种选择。

  • 1996年，PostgreSQL发行了第一个版本6.0，目前的稳定版本是11.2。
  • PostgreSQL一开始是被设计运行在类UNIX操作系统上，目前支持的操作系统有Linux、OS X、Solaris、Windows等。支持的数据类型有文本、图片、音频和视频，且提供了很多可编程接口，如C/C++、Python、Java、Ruby、Perl。
  • PostGIS是一个开源的，免费提供的PostgreSQL数据库管理系统（又称DBMS）的空间数据库扩展器。所以PostgreSQL（又名Postgres）是数据库，PostGIS就像是该数据库的附加组件。简而言之，PostGIS将空间函数（如距离，面积，并集，交集和特殊几何数据类型）添加到PostgreSQL。空间数据库存储和操作空间对象，就像数据库中的任何其他对象一样。PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型和空间操作函数，相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

下面是目前数据库引擎的使用趋势及排名：

1、基本SQL操作 - 图2

在本教程中，我们将使用PostGIS和pgRouting提供的空间及网络功能模块来直接处理数据，进行空间查询，为什么我们舍弃诸如ArcGIS、QGIS这样的软件，而直接使用数据库呢？原因如下：

1、基本SQL操作 - 图3

1.1 创建数据库

采用SQL语句与pgAdmin4对照方式进行：

  1. CREATE DATABASE testdb
  2.     WITH 
  3.     OWNER = postgres
  4.     ENCODING = 'UTF8'
  5.     CONNECTION LIMIT = -1;

创建postgis和pgrouting能力

create extension postgis;
create extension pgrouting;

查看当前数据库版本

SELECT version();
SELECT postgis_version();

1.2 创建数据表

非空间数据表

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低温度
    temp_hi         int,           -- 最高温度
    prcp            real,          -- 湿度
    date            date
);

空间数据表

CREATE TABLE cities (
    name            varchar(80),
    location        point
);

删除数据表

DROP TABLE weather;

1.3 插入及删除数据

INSERT INTO weather VALUES ('San Francisco', 5, 12, 0.25, '2019-01-10');
INSERT INTO weather VALUES ('San Francisco', 26, 32, 0.25, '2017-09-12');
INSERT INTO weather VALUES ('San Francisco', 15, 17, 0.25, '2018-04-02');
INSERT INTO weather VALUES ('Beijing', -5, 6, 0.25, '2018-02-10');
INSERT INTO weather VALUES ('Beijing', 1, 3, 0.25, '2019-01-10');

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

但我们在pgAdmin4中可以看到，这个空间数据似乎并不以WKT或WKB形式存在，而是简单的字符串形式。

CREATE TABLE cities (
    name            varchar(80),
    location        geometry(point, 4326)
);
INSERT INTO cities VALUES ('San Francisco', ST_GeomFromText('POINT(-122.41 37.5)', 4326));

删除数据

DELETE FROM cities

1.4 查看数据

下面使用一般SQL来对数据进行查询：

# 查看所有字段及数据
SELECT * FROM weather;
# 对特定字段进行计算
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
# 使用WHERE语句进行date区间查询
SELECT * FROM weather WHERE date between '2018-10-01' AND '2019-01-20';

对数据进行排序：

SELECT geom, name_zh, labelrank, postal FROM countries ORDER BY labelrank ASC, postal DESC;

对数据进行分类汇总：

## 使用GROUP BY进行分类
SELECT city , SUM(temp_hi) FROM weather GROUP BY city;
## 使用HAVING 进行过滤
SELECT city , SUM(temp_hi) FROM weather GROUP BY city HAVING SUM(temp_hi) > 10;

寻找非重复项

SELECT DISTINCT city FROM weather;

寻找指定项目数据

# 前2项数据
SELECT * FROM weather FETCH FIRST 2 ROW ONLY;

# 第3-4项数据
SELECT * FROM weather OFFSET 2 ROWS FETCH FIRST 2 ROW ONLY;

值指定的WHERE查询

SELECT * FROM weather WHERE temp_lo IN (-5, 5);

模糊查询

SELECT * FROM weather WHERE city LIKE '%ei%';

JOIN查询

关于JOIN查询，是SQL中一个非常重要的部分，其查询结果可以参见以下图形：

1、基本SQL操作 - 图4

CREATE TABLE basket_a (
    id INT PRIMARY KEY,
    fruit VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    id INT PRIMARY KEY,
    fruit VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (id, fruit) VALUES (1, 'Apple'),(2, 'Orange'),(3, 'Banana'),(4, 'Cucumber');

INSERT INTO basket_b (id, fruit) VALUES (1,'Orange'), (2,'Apple'),(3,'Watermelon'),(4,'Pear');

INNER JOIN

## inner join查询
SELECT
    a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b
FROM
    basket_a a
INNER JOIN basket_b b ON a.fruit = b.fruit;

1、基本SQL操作 - 图5

LEFT JOIN

SELECT
    a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b
FROM
    basket_a a
LEFT JOIN basket_b b ON a.fruit = b.fruit;

1、基本SQL操作 - 图6

RIGHT JOIN

SELECT
    a.id id_a, a.fruit fruit_a, b.id id_b, b.fruit fruit_b
FROM
    basket_a a
RIGHT JOIN basket_b b ON a.fruit = b.fruit

1、基本SQL操作 - 图7

FULL JOIN

SELECT
    a.id id_a,
    a.fruit fruit_a,
    b.id id_b,
    b.fruit fruit_b
FROM
    basket_a a
FULL OUTER JOIN basket_b b ON a.fruit = b.fruit;

1、基本SQL操作 - 图8

关于Postgresql更多SQL语句知识点，可以参见http://www.postgresqltutorial.com/postgresql-union/

1.5 空间数据加载

从https://www.naturalearthdata.com/downloads/ 下载数据ne_110m_admin_0_countries、10m_places、10m_roads等数据。

1、使用QGIS的DBManager进行加载（首先要建立数据连接）

2、使用命令行工具shp2pgsql

shp2pgsql -c -g geom -D -s 4326 -i -I ne_110m_admin_0_countries  countries2 | psql -d testdb -U postgres -h 127.0.0.1 -p 5432

3、使用ogr2ogr工具

ogr2ogr -append -f "PostgreSQL" PG:"dbname=testdb" ne_110m_admin_0_countries.shp -nln countries3 -nlt PROMOTE_TO_MULTI

1.6 空间数据查询

SELECT id, name_zh, economy, continent, geom FROM countries;

# 计算国土面积
SELECT id, name_zh, economy, continent, ST_Area(geom) as Area, geom FROM countries;

# 查看WKT
SELECT id, name_zh, economy, continent, ST_Area(geom) as Area, ST_AsText(geom) as geom FROM countries;

# 查询洛杉矶所在的国家
SELECT id, name_zh, economy, continent, ST_Area(geom) as Area, geom FROM countries
WHERE ST_Contains(geom, 'SRID=4326;POINT(-122.41 37.5)');
或
SELECT id, name_zh, economy, continent, ST_Area(geom) as Area, geom FROM countries
WHERE ST_Contains(geom, ST_GeomFromText('POINT(-122.41 37.5)', 4326));

# 查询洛杉矶构造的缓冲区类的国家
SELECT id, name_zh, economy, continent, ST_Area(geom) as Area, geom FROM countries
WHERE ST_Distance(geom, ST_GeomFromText('POINT(-122.41 37.5)', 4326)) < 20;
等价于
SELECT id, name_zh, economy, continent, geom FROM countries
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(-122.41 37.5)', 4326), 20);

# 计算洛杉矶到世界各国距离
SELECT id, name_zh, economy, continent, 
    ST_Distance(geom, ST_GeomFromText('POINT(-122.41 37.5)', 4326)) AS distance, 
    geom FROM countries;

关联查询

# 两张表之间的关联查询，寻找洛杉矶所在的国家
SELECT countries.name_zh FROM places, countries
WHERE ST_Contains(countries.geom, places.geom) AND places.name_zh = '洛杉矶';

# 美国所有的城市
SELECT places.name_zh, places.geom as geom
FROM places, countries
WHERE ST_Contains(countries.geom, places.geom) AND countries.name_zh = '美国';

寻找领土面积最大的前5个国家和地区

SELECT name_zh, ST_Area(geom)/10000 AS hectares
FROM countries
ORDER BY hectares DESC
LIMIT 5;

寻找加拿大境内所有道路

SELECT roads.name, roads.geom AS geom FROM roads, countries 
WHERE ST_Contains(countries.geom, roads.geom) AND countries.name_zh='加拿大';

加拿大道路长度

SELECT sum(ST_Length(roads.geom)) AS length FROM roads, countries 
WHERE ST_Contains(countries.geom, roads.geom) AND countries.name_zh='加拿大';

各国道路长度列表

SELECT countries.name_zh, sum(ST_Length(roads.geom)) AS length FROM roads, countries 
WHERE ST_Contains(countries.geom, roads.geom)
GROUP BY countries.name_zh
ORDER BY length DESC;

北京附近的道路

SELECT
  r.*
FROM
  roads AS r,
  places AS p
WHERE
  p.name_zh = '北京' AND ST_Distance(r.geom, p.geom) < 1;