课程思政

来源：https://www.bilibili.com/video/BV1HA411w7mY/?spm_id_from=333.337.search-card.all.click&vd_source=5e5a2d3253018edcad726eaed8290b0a

想一个问题：如何购买房子？订外卖？

1 准备数据

运行数据恢复

psql -U postgres -d postgres -f D:\postgisdata\ch09\data\ch09_data.sql

数据对应的数据库密码。

实例练习：根据条件查询。

SELECT * from airports WHERE "type"='small_airport';

SELECT count(0) from airports WHERE "type"='small_airport';

2 最近邻搜索

解决两个主要问题：哪些位置在X距离内？N个最近的位置是什么？这类问题通常被称为最近邻搜索。

2.1 哪些位置在X距离之内

使用的主要函数：ST_DWithin

Geometry和Geography：这两种数据类型的最大不同之处是它们对空间数据的计算方式不同，这也是最能体现Geography数据类型价值的地方。例如，计算两个坐标点的距离，Geometry数据类型的数据是基于平面坐标系来计算的，也就是将两个坐标点看作是处于同一个平面，使用类似勾股定理之类的公式，计算它们之间的平面距离。而Geography数据类型的数据是基于球面坐标来计算的，也就是将两个坐标点看作地球参考椭球体上的两个坐标点，计算它们之间的**[大圆航线](https://link.zhihu.com/?target=https%3A//baike.baidu.com/item/%E5%A4%A7%E5%9C%86%E8%88%AA%E7%BA%BF/8760252%3Ffr%3Daladdin)**的距离。

实例与练习：查找距离某个位置100KM以内的机场：

SELECT name, iso_country, iso_region
FROM ch09.airports
WHERE ST_DWithin(geog, ST_Point(-75.0664, 40.2003)::geography, 100000);

思考：如何查看ST_DWithin函数的使用方法？

官方文档：https://postgis.net/docs/ST_DWithin.html

2.2 对N个最接近的结果使用ST_DWithin和ST-Distance函数

修改前面的查询，通过在LIMIT N基础上添加一个按距离排序的子句来返回最近的N个机场。

SELECT ident, name
FROM 
    ch09.airports 
    CROSS JOIN 
    (SELECT ST_Point(-75.0664, 40.2003)::geography AS ref_geog) As r
WHERE ST_DWithin(geog, ref_geog, 100000)
ORDER BY ST_Distance(geog, ref_geog)
LIMIT 5;

2.3 使用ST_DWithin函数和DISTINCT ON 查找最近的位置

实例练习：查找距离每个机场最近的助航设备（导航设备）

SELECT DISTINCT ON (a.ident) -- 不同字段
    a.ident, a.name As airport, n.name As closest_navaid, 
	(ST_Distance(a.geog,n.geog)/1000)::integer As dist_km
FROM ch09.airports As a LEFT JOIN ch09.navaids As n 
ON ST_DWithin(a.geog, n.geog,100000) -- 半径
ORDER BY a.ident, dist_km; -- 按距离排序

2.4 距离之间的数据查询

针对某个距离区间的数据感兴趣。

实例练习：查找距离大于90km，并且小于100km范围内的机场。

SELECT name, iso_country, iso_region
FROM ch09.airports
WHERE ST_DWithin(geog, 
        ST_Point(-75.0664, 40.2003)::geography, 100000)
  AND NOT ST_DWithin(geog, 
            ST_Point(-75.0664, 40.2003)::geography, 90000);

2.5 使用KNN距离运算符查找N个最近的位置

实例练习：距离一个点最近的十个几何图形

SELECT 
    pid, 
    geom 
    <-> 
    ST_SetSRID(ST_Point(-71.09368, 42.35857),26986) AS dist -- <1>

FROM ch09.land
WHERE land_type = 'apartment'
ORDER BY dist -- <2>
LIMIT 10;

等同于st_distance

SELECT 
    pid, 
    st_distance(ST_SetSRID(ST_Point(-71.09368, 42.35857),26986), geom) AS dist -- <1>

FROM ch09.land
WHERE land_type = 'apartment'
ORDER BY dist -- <2>
LIMIT 10;

实例练习：查找距离另一块地最近的十个地块。

SELECT pid,
  geom <-> (SELECT geom FROM ch09.land WHERE pid = '58-162') AS dist
FROM ch09.land
WHERE land_type = 'apartment'
ORDER BY dist
LIMIT 10;

实例练习：使用距离运算符查询距离每个地块最近的购物中心。

SELECT 
    l.pid, (
        SELECT s.pid 
        FROM ch09.land As s 
        WHERE s.land_type = 'shopping' 
        ORDER BY s.geom <-> l.geom LIMIT 1
    ) As n_closest
FROM ch09.land AS l
WHERE land_type = 'apartment';

3 作业

• 完成本教程所有实例练习，有截图、有文字说明，并将空间结果展示到QGIS中；

• 下载辽宁省市级点位空间数据，导入到数据库中，并计算你所在学校的位置（经纬度）距离最近的三个城市点位是什么？

数据下载地址：2023年行政区划

链接：https://pan.baidu.com/s/1rwb0jACMtpVqoo96rvu6Cw

提取码：xtgp