地理空间数据库复习笔记：关系数据库标准语言、几何对象模型与查询

我的GIS/CS学习笔记：https://github.com/yunwei37/ZJU-CS-GIS-ClassNotes <一个浙江大学本科生的计算机、地理信息科学知识库 >

Lecture 3 关系数据库标准语言

SQL概述

SQL (stands for Structured Query Language)

• A standard language for querying and manipulating data
• A very high-level (optimized) programming language

Current standard is SQL-2016

内容:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)

特点：综合统一、高度非过程化、面向集合的操作方式、以同一种语法结构提供两种使用方式、语言简捷，易学易用

数据定义

定义功能包括：

定义基本表

CREATE TABLE <表名>
(<列名> <数据类型>[ <列级完整性约束条件> ]
[，<列名> <数据类型>[ <列级完整性约束条件>] ] …
[，<表级完整性约束条件> ] );

• 实体完整性：PRIMARY KEY (主码，主键)
• 参照完整性：FOREIGN KEY (外码，外键)
• 用户定义完整性：NOT NULL UNIQUE DEFAULT CHECK
• 域约束: SQL语言可以使用CREATE DOMAIN语句定义新的值域

创建Students\Enrolled关系

CREATE TABLE Students (
sid CHAR(10) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age INT CHECK(age > 0));

CREATE TABLE Enrolled (
student_id CHAR(10) REFERENCES Students(sid),
cid CHAR(20),
grade INT,
PRIMARY KEY(student_id, cid));

修改和删除表

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]

DROP TABLE <表名>

举例:

ALTER TABLE Students ADD Scome DATE;
ALTER TABLE Students ALTER COLUMN Scome type timestamp;
ALTER TABLE Students DROP Scome;

ALTER TABLE Enrolled ADD CONSTRAINT grade_check

ALTER TABLE Enrolled DROP CONSTRAINT pk_En;

DROP TABLE Students;

数据更新

数据插入

INSERT
INTO <表名> [(<属性列1>[，<属性列2 >] … )]
VALUES (<常量1> [，<常量2>] … )

将新元组插入指定表中:

Insert into Students Values(‘200011’, ‘张三’, 19);
Insert into Students(sid, age, name)
Values(‘200012’, 20, ‘李四’);
Insert into Students(sid, name)
Values(‘200013’, ‘王五’);

数据修改

UPDATE <表名>
SET <列名>=<表达式>[，<列名>=<表达式>]…
[WHERE <条件>]

修改指定表中满足WHERE子句条件的元组:

Update Students Set age = 20 Where sid = ‘200011’
Update Students Set age = 21 Where name = ‘王五’
Update Students Set age = age + 1;
Update Students Set sid = ‘200012’ Where sid = ‘200011’;

数据删除

DELETE
FROM <表名>
[WHERE <条件>]；

删除指定表中满足WHERE子句条件的元组

Delete From Students Where sid = ‘200011’;
Delete From Students Where sid = ‘200000’;
Delete From Students

数据查询

SELECT

SELECT A1, A2, …, An            #3: what to return
FROM R1, R2, …, Rn              #1: relations to query
WHERE condition                 #2: combine, filter relations
GROUP BY Ai, Aj, …, Ak
HAVING condition
ORDER BY <列名1> [ASC|DESC]

Max/Min value problem:

▬ Solution 0
SELECT sID FROM Student ORDER BY GPA DESC LIMIT 1;
▬ Solution 1 (all/any?)
SELECT sID FROM Student
WHERE GPA >= all (SELECT GPA FROM Student);
▬ Solution 2
SELECT sID FROM Student
WHERE GPA = (SELECT max(GPA) FROM Student)

(Left | Right | Full) Outer Join:

If join condition don’t match for certain tuples, include those tuples in the result, but pad with NULL values

Write a SQL query that returns the number ofcolleges applied to by each student, including 0 for those who applied nowhere

SELECT Student.sID, count(distinct cName)
FROM Student, Apply
WHERE Student.sID = Apply.sID
GROUP BY Student.sID
union
SELECT sID, 0
FROM Student
WHERE sID NOT IN (SELECT sID FROM Apply);

查询每个城市最受欢迎的站点:

select station.city,station_name,max as visit_count from 
  (select city,max(count)
  from
      (select s1.id as id, s1.count+s2.count as count from 
          (select start_station_id as id, count(*)
              from trip group by start_station_id) as s1,
          (select end_station_id as id, count(*)
              from trip group by end_station_id) as s2 
      where s1.id = s2.id) as counts, station
  where counts.id =station_id 
  group by city
) as maxCity,station,
  (select s1.id as id, s1.count+s2.count as count from 
        (select start_station_id as id, count(*)
            from trip group by start_station_id) as s1,
        (select end_station_id as id, count(*)
            from trip group by end_station_id) as s2 
    where s1.id = s2.id
  ) as counts
where counts.id = station_id and counts.count = max and maxCity.city = station.city
order by station.city;

在PostgreSQL中，可使用extract函数从timestamp类型变量中提取年月日信息，如：extract(year from date)

绘制日均租车量-时间变化直方图（在sql中仅需输出month与number）:

select extract(month from date) as month ,avg(count) as number from (
    select extract(doy from start_time),count(*) from trip 
    where start_station_id in (select station_id from station where zip_code = '94107') 
        and extract(year from start_time) = 2014
    group by extract(doy from start_time)
) as days, weather where extract(doy from date) = date_part 
group by  extract(month from date) order by month;

NULL values

• For numerical operations, NULL -> NULL:
• For boolean operations, in SQL there are three values:
  • FALSE = 0
  • UNKNOWN = 0.5
  • TRUE = 1
• WHERE子句只有条件为True才保留这个记录
• HAVING子句只有条件为True才保留这个GROUP
• JOIN NULL != NULL
• GROUP BY NULL算一个GROUP
• NULL在ORDER BY时默认排序最前面
• 如果COUNT(*)，NULL的记录参与计算，COUNT属性，NULL的记录忽略

Text comparison

• s LIKE p: pattern matching on strings
• % = any sequence of characters
• _ = any single character

其他

• 天气关系数据weather导入，未给出的数据默认为NULL copy weather from ‘E://weather.txt’ delimiter ‘,’ NULL ‘’;

Lecture 4 几何对象模型与查询

空间数据模型

空间数据模型：

• 空间信息的一种数据组织方式
• 对象模型、场模型、网络模型

GIS中常见的两大数据模型：栅格模型、矢量模型

矢量模型的优点：

• 数据结构紧凑，冗余度低，表达精度高，图形显示质量好，有利于网络和检索分析等
• 在GIS中应用广泛，特别在小区域（大比例尺）制图中充分利用了它的精度高的优点

栅格模型的优缺点：

• 数据结构简单、空间分析和地理现象的模拟较为容易等
• 数据量大、投影转换比较困难

矢量模型：

• 点数据
• 网络数据

几何对象模型

概念模型

地理要素(feature):

• 对现实世界空间现象的抽象
• 由几何(geometry)、属性(attribute)、行为(behavior)等三类信息构成
• 地理要素的属性和行为等信息的建模是由应用系统的设计者，根据实际应用需求进行建模
• 几何的建模是数据库管理系统关心的基础问题
  • 关键是选择一组基本空间数据类型来满足地图常用几何信息的建模要求

几何对象模型利用对象关系型数据库中的扩展数据类型实现.

几何对象模型的核心:

• 一个依赖于空间参考系(Spatial Reference System)和测量参考系(Measure Reference System)的几何(Geometry)类
• 派生出点(Point)、线(Curve)、面(Surface)、多点(MultiPoint)、多线(MultiCurve)、多面(MultiPolygon)等类型

模型层次关系 (数据):

• 点(Point): 零维几何对象类，代表空间中的一个点，如城市
• 曲线(Curve):
  • 由点序列描述一维的几何对象类，如街道、管线
  • 相邻两点间的插值方法：线性插值和非线性插值
• 折线(LineString): 曲线的子类，采用线性插值
• 线段(Line): 折线的特例，只有两个点的线串
• 环线(LineRing): 由折线派生而来，闭合的、不自相交或相切的折线
• 面(Surface): 二维几何对象类，代表一个外边界、零到多个内边界组成的几何对象
  • 在三维空间中，可能是一个同构的曲面
• 多边形(Polygon)
  • 二维坐标空间中由一个外边界、零到多个内边界定义的平坦表面，由一个或一个以上的线环聚合而成，如省份
  • 仅支持由折线串围成的多边形，暂不支持曲线
• 体表面(PolyhedraSurface)
  • 由简单面沿着它们的边界“缝合”而成
  • 三维空间中的多面体曲面总体上可以不平坦
  • 相互接触的一对多边形的公共边可以表达为有限折线的集合
• 三角形(Triangle): 多边形类的一个特例
• 不规则三角网(Triangulated irregular network, TIN): 体表面的一个特例，由多个共享公共边的连续三角形聚合而成
• 几何集合(GeometryCollection):
  • 由一个或多个几何对象组成的集合，其中的元素必须具有相同的空间参考系和测量参考系
• 多点(MultiPoint): 零维的几何类集合，由多个点聚合而成，代表空间中的多个点，例如多个岛屿
• 多面(MultiSurface): 二维的几何集合类，由多个面聚合而成
• 多曲线(MultiCurve): 一维的几何类，由多条曲线聚合而成
• 多折线(MultiLineString):
  • 多曲线类的子类，由多条折线聚合而成，如由多条河流组成的水系
  • 多曲线允许出现弧线，多折线由折线组成
• 多多边形(MultiPolygon): 多面的子类，由多个多边形对象聚合而成，例如多个岛屿组成的群岛（大比例尺）

坐标维数和几何维数的区别:

• 坐标维数是指在一个坐标系统描述一个位置所需的测量或坐标轴的个数（空间维数）
• 几何维度是在一定前提下描述一个几何对象所需的参数个数

任何几何模型都有其边界(boundary)、内部(interior)和外部(exterior)：

• 边界：一个几何实体界限的集合，几何维数是其本身几何形状的维数减一
  • 点：空
  • 线：端点
  • 曲线及其子类：起始点和终止点
  • 多曲线及其子类：各曲线的起始点和终止点
  • 面：构成它的线串
• 内部：几何对象除边界外的所有直接位置(direct position)的集合
  • 直接位置是用坐标参考系中的一组坐标描述的位置
  • 几何维数与其本身的维数一致
  • 所有几何对象都有内部，即几何对象形状减去其边界后的部分
• 外部：空间全域与几何闭包之差
  • 任意几何对象外部的维数总是2
  • 所有的几何形状都有外部，即其几何形状的补集

几何对象模型的坐标维数为3，但目前仅能描述二维几何对象。 z值仅用于记录点在坐标空间中第3个坐标轴的测量值。

M值：点类除了x, y, z坐标外，还有一个M坐标

• M值是线性参考系统的一个重要的度量值
• 例如，高速公路上的里程碑点可用其M值表示从高速公路起点到当前位置的距离

由于体表面违反了“多边形元素只能相交在有限数量的点上”的规则，所以体表面不是多多边形

几何对象的方法 (函数)：

• 常规方法：
  • Dimension() : Integer
  • CoordinateDimension() : Integer
  • GeometryType() : String
  • SRID() : Integer
  • Envelope() : Geometry
  • AsText() : String
  • AsBinary() : String
  • IsEmpty() : Boolean
  • IsSimple() : Boolean
  • Is3D() : Boolean
  • IsMeasured() : Boolean
  • Boundary() : Geometry
• 常规GIS分析方法 (空间分析)：
  • Distance(another: Geometry) : Distance
  • Buffer(distance: Distance) : Geometry
  • ConvexHull() : Geometry
  • Intersection(another : Geometry) : Geometry
  • Union(another : Geometry) : Geometry
  • Difference(another : Geometry) : Geometry
  • SymDifference(another : Geometry) : Geometry
• 空间查询方法 (拓扑分析）：
  • Equals(another : Geometry) : Boolean
  • Disjoint(another : Geometry) : Boolean
  • Intersects(another : Geometry) : Boolean
  • Touches(another : Geometry) : Boolean
  • Crosses(another : Geometry) : Boolean；
  • Within(another : Geometry) : Boolean
  • Contains(another : Geometry) : Boolean
  • Overlaps(another : Geometry) : Boolean
  • Relates(another : Geometry, matrix : String) : Boolean
  • LocateAlong(mValue : Double) : Geometry
  • LocateBetween(mStart : Double, mEnd : Double) : Geometry

九交模型：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nlJYaLUq-1598684054065)(pictures\Nine-Intersection.png)]

空间拓扑关系: 前面加上 st_ 就是对应函数

• 相离(disjoint)
  • FF*FF****
  • 若 A ∩ B = ∅，则a和b相离
• 相交(intersects)
  • 若 A ∩ B ≠ ∅，则a和b相交
  • a.Intersects(b) ↔ !a.Disjoint(b)
• 相等(equals)
  • TFFFTFFFT
• 交叠(overlaps)
  • T*T***T**
  • 点/点、线/线、面/面交叠关系，没有线/面等交叠关系
• 包含于(within)
  • T*F**F***
• 包含(contains)
• 相接(touches)
  • FT*******
  • F**T*****
  • F***T****
  • 面/面、线/线、线/面、点/面、点/线相接关系，但没有点/点相接关系
• 穿越(crosses)
  • T*T******
  • 点/线、点/面、线/线、线/面穿越关系

线性参考系查询方法

• LocateAlong和LocateBetween用于动态构造线性要素的各个部分的方法
  • LocateAlong用于选取几何中M值为mValue的点，并构造成一个新的几何对象
  • LocateBetween用于选取几何中M值在mStart和mEnd之间的点，并构造一个新的几何对象

几何对象逻辑模型与物理模型

基于概念模型，OGC提出了基于预定义数据类型和基于扩展几何类型的两种逻辑模型实现方法：

• 基于预定义数据类型的实现
  • 利用关系数据库中已有的数字(numeric)类型、二进制大对象(BLOB)类型实现空间数据的存储和管理，这些数据类型的解释和维护由DBMS负责
  • 相关空间数据访问方法作为扩展函数嵌入到DBMS中
  • 与空间数据引擎(如ArcSDE)的区别
  • 基于numeric和BLOB的实现，要素表(Feature)、几何列表(GEOMETRY_COLUMNS)和空间参考系(SPATIAL_REF_SYS)表的结构都一样，不同之处在于几何(Geometry)表的结构
  • Feature表：
    • 记录一组具有相同属性和行为的地理要素的集合，要素表的列代表要素的属性，而不同的行代表不同的要素
    • Geometry_Column列是几何对象的逻辑几何数据类型，其存储的是几何对象的唯一标识(geometry ID, GID)，而几何数据实际存储在Geometry表中，因此，可以将GID作为指针到Geometry表找到其空间数据
  • Geometry表：Numeric类型
    • 将几何类型的空间坐标作为数值对存储在表中，每行最多可存储MAX_PPR个空间坐标
  • Geometry表：BLOB类型
    • 将空间数据以WKB(Well-Known Binary Representation)形式存储在名为WKB_Geometry的BLOB类型的字段中
• GEOMETRY_COLUMNS表：记录数据库中所有要素表及其几何列的属性
• SPATIAL_REF_SYS表
• 基于扩展几何类型的实现
  • 利用对象关系数据库中对抽象数据类型的支持，定义Geometry类型及其相关的方法与函数，并用该扩展几何类型实现空间数据的存储和管理
  • 扩展几何类型的解释和维护由定义者负责
  • Oracle Spatial中的SDO_GEOMETRY、PostGIS中的Geometry 是扩展的空间数据类型
  • GEOMETRY_COLUMNS和SPATIAL_REF_SYS是系统表，用于存储元数据信息
  • Feature表是用户表，用于存储空间数据

几何对象物理模型：

• WKB表达：FA SQL给出了一种较为紧凑的几何数据的二进制方式的存储格式
• WKT表达：一种基于文本格式几何数据交汇标准表达方式

举例：

    POINT(0 0)
    LINESTRING(0 0, 1 1, 1 2)
    POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))
    POLYGON((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))
    MULTIPOINT((0 0), (1 2))
    MULTILINESTRING((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))
    MULTIPOLYGON(((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))
    GEOMETRYCOLLECTION(POINT(2 3), LINESTRING(2 3,3 4))

PostGIS：

PostGIS中常用数据类型：

• boolean 布尔类型，其值为T或F
• box2d 矩形框类型，由矩形左下角和右上角组成
• box3d 长方体类型，由左前下角和右后上角组成
• bytea 相当于BLOB类型，可变长的二进制值
• integer 字节为4的整数(int4)
• double precision 字节为8的浮点数(float8)
• geometry 几何类型
• geometry[] 几何数据类型数组
• geometry set 几何数据类型的集合
• raster 栅格类型
• setof geomval 几何对象与值对的集合，用于返回几个对象及与该集合对象对应的栅格值
• text 文本类型
• character varying 相当于BLOB类型，可变长的文本描述

表的定义：

create table landuse (
landuse_id integer NOT NULL,
name varchar(20),
the_geom geometry,
area double precision,
perimeter double precision,
constraint landuse_key primary key (landuse_id));

数据插入:

insert into landuse
values(12, ‘Timber-forest’,
‘01010000001DDB93F460BB4241A84E5AC86F455441’,
47806700, 34246.2);

insert into landuse
values(12, ‘Timber-forest’,
ST_GeomFromText(‘Polygon((10 10, 10 20, 20 20, 20 10, 10 10))’, 4326),
47806700, 34246.2);

• AddGeometryColumn 增加几何属性： select AddGeometryColumn('testgeom', 'geom', 4326, 'MULTILINESTRING', 2);
• Dimension() ST_Dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0), POINT(0 0))');
• SRID() : Integer 用于获取几何类型的空间参考系 SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
• 空间参考系更改为4326 select UpdateGeometrySRID('ushighways', 'geom', 4326);
• Envelope() : Geometry 用于获取Geometry的最小边界矩形 SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
• Boundary() : Geometry 获取几何类型的边界 SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')));
• Distance(another: Geometry) : Distance 求本Geometry与另一个Geometry间的距离
  • geometry在空间参考系4326下空间计算单位为度，如果单位需要转成米，可以通过以下三种方式：
    • ST_Distance(ST_Transform(geom1, 26986), ST_Transform(geom2, 26986))
    • ST_Distance(geom1::geography, geom1::geograpy)
    • ST_Distance(geom1, geom2, false) (推荐方法)
  • ST_DistanceSphere 以几何所定义的SRID椭球体进行计算，返回单位为米；比ST_DistanceSpheroid快；
  • ST_DistanceSpheroid 需要另外提供椭球体信息进行计算，返回单位为米；
  • ST_Distance 返回二维平面上的笛卡尔距离，单位是投影单位。

SELECT ST_Distance(
          ST_GeomFromText('POINT(-72.1235 42.3521)',4326),
          ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326)
      );

比较~=(操作符)、=(操作符)、ST_Equals和ST_OrderingEquals四个函数的异同

boolean =( geometry A , geometry B )：仅将在所有方面完全相同，坐标相同，顺序相同的几何视为相等。 boolean ~=( geometry A , geometry B );将边界框相同的几何要素视为相等。（PostGIS 1.5前测试实际相等性） boolean ST_Equals(geometry A, geometry B);几何在空间上相等则返回true，不考虑点的顺序。即 ST_Within（A，B）= true 且 ST_Within（B，A）= true 。 boolean ST_OrderingEquals(geometry A, geometry B);如果几何相等且坐标顺序相同，则返回TRUE。

可以将MultiXXX转换XXX，如MultiPolygon转换获得多个Polygon

select ST_Dump(ST_GeomFromText('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))'))

查询点的数目:

select gid, 'Lake Superior''s number of point' as name, ST_NPoints(geom) as number
from uslakes
where name like '%Superior%';

ConvexHull() : Geometry 求本Geometry的凸包

ST_AsText(ST_ConvexHull(
  ST_Collect(
      ST_GeomFromText('MULTILINESTRING((100 190,10 8),(150 10, 20 30))'),
      ST_GeomFromText('MULTIPOINT(50 5, 150 30, 50 10, 10 10)')
          )) );

查询距离ST_CASE = 10012交通事故最近的城市

select uc.name || ' in ' || uc.state as name from uscities uc, usaccidents ua where st_case = 10012
  and ST_Distance(uc.geom,ua.geom) = ( select min(ST_Distance(uc.geom,ua.geom)) 
  from uscities uc, usaccidents ua where st_case = 10012);

查询哪条高速公路上的交通事故最多：该高速公路上的交通事故

select ua.gid, hw1.name,ua.geom from
  (select hw.gid, hw.full_name as name, hw.geom, count(*) c 
  from (select * from usaccidents where month = 8 or month = 9) ua, ushighways hw 
  where ST_DWithin(hw.geom::geography, ua.geom::geography,500)  
  group by hw.gid order by c desc limit 1) hw1, (select * from usaccidents where month = 8 or month = 9) ua 
  where ST_DWithin(hw1.geom::geography, ua.geom::geography,500);

空间网格关联查询：

select x || '0' || y as gid, x || ' ' || y as name, grid1.geom ,count(*) as value
from usaccidents ua, 
(WITH 
  usext AS (
      SELECT 
          ST_SetSRID(CAST(ST_Extent(geom) AS geometry),
          4326) AS geom_ext, 50 AS x_gridcnt, 46 AS y_gridcnt
      FROM cal
  ),
  grid_dim AS (
      SELECT 
          (
              ST_XMax(geom_ext)-ST_XMin(geom_ext)
              ) / x_gridcnt AS g_width, 
          ST_XMin(geom_ext) AS xmin, ST_xmax(geom_ext) AS xmax,
          (
              ST_YMax(geom_ext)-ST_YMin(geom_ext)
              ) / y_gridcnt AS g_height,     
          ST_YMin(geom_ext) AS ymin, ST_YMax(geom_ext) AS ymax
      FROM usext                                    
  ), 
  grid AS (                    
      SELECT 
          x, y, 
          ST_MakeEnvelope(  
              xmin + (x - 1) * g_width, ymin + (y - 1) * g_height,  
              xmin + x * g_width, ymin + y * g_height,
              4326
          ) AS grid_geom 
      FROM 
          (SELECT generate_series(1,x_gridcnt) FROM usext) AS x(x)    
          CROSS JOIN 
          (SELECT generate_series(1,y_gridcnt) FROM usext) AS y(y) 
          CROSS JOIN 
          grid_dim                                                 
  )   
  SELECT 
      g.x x, g.y y, 
      ST_Intersection(s.geom, grid_geom) AS geom                   
  FROM cal AS s INNER JOIN grid AS g 
  ON ST_Intersects(s.geom,g.grid_geom)) grid1
  where ST_Within(ua.geom,grid1.geom)
  group by grid1.x,grid1.y,grid1.geom;

查询在加州范围内的交通事故，通过heatMap进行可视化

select gid, tway_id as name, geom from usaccidents where ST_Within(geom, (select geom from cal ));