超级会员免费看
空间数据处理与分析：从农贸市场到人口普查数据
在地理信息系统（GIS）和空间数据分析领域，我们常常需要处理各种空间数据，以解决实际问题。本文将介绍如何使用PostGIS进行空间数据处理，包括创建空间对象、分析农贸市场数据以及处理人口普查形状文件等内容。
1. 创建空间对象的函数
在PostGIS中，有几个重要的函数可用于从WKT（Well-Known Text）创建空间对象：
-
ST_PolygonFromText(WKT, SRID)
：该函数从WKT POLYGON和可选的SRID创建一个多边形。与创建点和线的类似命名函数一样，它包含一个验证步骤，因此比ST_GeomFromText()慢。
-
ST_MakePolygon(linestring)
：该函数从一个闭合的LineString创建一个多边形，LineString的起点和终点坐标必须相同，以确保对象是闭合的。
-
ST_MPolyFromText(WKT, SRID)
：该函数从WKT和可选的SRID创建一个MultiPolygon。
2. 分析农贸市场数据
美国农业部的国家农贸市场目录列出了8600多个农贸市场的位置和供应信息。我们可以使用SQL空间查询来查找距离某个点较近的农贸市场。
2.1 创建和加载农贸市场表
首先，从https://www.nostarch.com/practicalSQL/下载farmers_markets.csv文件，并将其保存到计算机上。然后，运行以下代码创建并加载farmers_markets表：
CREATE TABLE farmers_markets (
fmid bigint PRIMARY KEY,
market_name varchar(100) NOT NULL,
street varchar(180),
city varchar(60),
county varchar(25),
st varchar(20) NOT NULL,
zip varchar(10),
longitude numeric(10,7),
latitude numeric(10,7),
organic varchar(1) NOT NULL
);
COPY farmers_markets
FROM 'C:\YourDirectory\farmers_markets.csv'
WITH (FORMAT CSV, HEADER);
导入数据后，可以使用
SELECT count(*) FROM farmers_markets;
来检查行数。如果一切导入正确，应该有8681行。
2.2 创建和填充地理列
为了对市场的经度和纬度进行空间查询，我们需要将这些坐标转换为一个空间数据类型的列。由于我们处理的是覆盖整个美国的位置，并且准确测量大球面距离很重要，因此我们使用geography类型。
ALTER TABLE farmers_markets ADD COLUMN geog_point geography(POINT,4326);
UPDATE farmers_markets
SET geog_point =
ST_SetSRID(
ST_MakePoint(longitude,latitude),4326
)::geography;
CREATE INDEX market_pts_idx ON farmers_markets USING GIST (geog_point);
SELECT longitude,
latitude,
geog_point,
ST_AsText(geog_point)
FROM farmers_markets
WHERE longitude IS NOT NULL
LIMIT 5;
上述代码首先使用
ALTER TABLE
语句创建一个名为geog_point的地理类型列，该列将保存点并引用WSG 84坐标系（SRID 4326）。然后，使用
UPDATE
语句填充该列，将经度和纬度转换为地理点。最后，创建一个GiST索引以加快查询速度。
2.3 添加GiST索引
对于空间数据，PostgreSQL的B-Tree索引不太有用，因为无法轻松地沿一个轴对GIS数据进行排序。因此，PostGIS推荐使用Generalized Search Tree（GiST）索引。上述代码中的
CREATE INDEX
语句为geog_point列添加了GiST索引。
2.4 查找给定距离内的地理对象
PostGIS的ST_DWithin()函数可用于查找距离某个点指定距离内的空间对象。例如，我们可以使用该函数查找距离得梅因市中心农贸市场10公里内的所有农贸市场：
SELECT market_name,
city,
st
FROM farmers_markets
WHERE ST_DWithin(
geog_point,
ST_GeogFromText('POINT(-93.6204386 41.5853202)'),
10000
)
ORDER BY market_name;
该查询返回9行结果，包括得梅因市中心农贸市场以及其他位于得梅因或附近西得梅因的市场。
2.5 查找地理对象之间的距离
ST_Distance()函数可用于计算两个空间对象之间的最小距离。例如，计算纽约洋基体育场到花旗球场的距离：
SELECT ST_Distance(
ST_GeogFromText('POINT(-73.9283685 40.8296466)'),
ST_GeogFromText('POINT(-73.8480153 40.7570917)')
) / 1609.344 AS mets_to_yanks;
我们也可以将此技术应用于农贸市场数据，查找距离得梅因市中心农贸市场10公里内的所有市场，并显示它们的距离（以英里为单位）：
SELECT market_name,
city,
round(
(ST_Distance(geog_point,
ST_GeogFromText('POINT(-93.6204386 41.5853202)')
) / 1609.344)::numeric(8,5), 2
) AS miles_from_dt
FROM farmers_markets
WHERE ST_DWithin(geog_point,
ST_GeogFromText('POINT(-93.6204386 41.5853202)'),
10000)
ORDER BY miles_from_dt ASC;
3. 处理人口普查形状文件
形状文件是一种常见的GIS数据格式，由Esri开发。它包含描述地理特征形状的信息以及包含其属性的数据库。
3.1 形状文件的内容
形状文件通常是一个包含多个不同扩展名文件的集合，每个文件有不同的用途：
| 扩展名 | 用途 |
| ---- | ---- |
| .shp | 存储特征几何的主文件 |
| .shx | 存储特征几何索引的文件 |
| .dbf | 以dBASE格式存储特征属性信息的数据库表 |
| .xml | 存储形状文件元数据的XML格式文件 |
| .prj | 存储坐标系信息的投影文件 |
3.2 通过GUI工具加载形状文件
有两种方法可以将形状文件加载到数据库中，这里我们介绍使用GUI工具的方法。
- Windows系统 ：选择“开始” ▸ “PostGIS Bundle x.y for PostgreSQL x64 x.y” ▸ “PostGIS 2.0 Shapefile and DBF Loader Exporter”。
- macOS系统 ：目前postgres.app安装不包含GUI工具，可参考“Loading Shapefiles with shp2pgsql”的说明。
- Linux系统 ：可使用pgShapeLoader（shp2pgsql-gui），访问http://postgis.net/install/ 并按照说明进行操作。
连接到数据库并加载形状文件的步骤如下：
1. 点击“View connection details”。
2. 在打开的对话框中，输入“postgres”作为用户名，如果在初始设置时为服务器添加了密码，则输入密码。
3. 确保“Server Host”默认设置为“localhost”和“5432”，除非你使用的是不同的服务器或端口。
4. 输入“gis_analysis”作为数据库名称。
5. 点击“OK”，应该在日志窗口中看到“Connection Succeeded”消息。
成功建立连接后，加载形状文件的步骤如下：
1. 在“Options”下，将DBF文件字符编码更改为“Latin1”，因为形状文件属性包含需要此编码的县名。保持默认选中的复选框，包括为空间列创建索引的选项，然后点击“OK”。
2. 点击“Add File”，选择保存的tl_2010_us_county10.shp文件，然后点击“Open”。
3. 在“Table”列中，双击选择表名，将其替换为“us_counties_2010_shp”。
4. 在“SRID”列中，双击并输入“4269”，这是北美1983年大地基准（North American Datum 1983）坐标系的ID。
5. 点击“Import”。
3.3 探索2010年人口普查县形状文件
us_counties_2010_shp表包含每个县的名称、FIPS代码以及每个县边界的空间数据。我们可以使用ST_AsText()函数检查geom列中存储的空间对象类型：
SELECT ST_AsText(geom)
FROM us_counties_2010_shp
LIMIT 1;
结果是一个包含数百个坐标对的MultiPolygon，每个坐标对标记了县边界上的一个点。
3.4 查找面积最大的县
使用ST_Area()函数可以计算多边形或MultiPolygon对象的面积。为了以平方英里为单位计算县的面积，我们需要将geom列的数据类型转换为geography：
SELECT name10,
statefp10 AS st,
round(
( ST_Area(geom::geography) / 2589988.110336 )::numeric, 2
) AS square_miles
FROM us_counties_2010_shp
ORDER BY square_miles DESC
LIMIT 5;
查询结果显示，面积最大的五个县都在阿拉斯加州。
3.5 通过经度和纬度查找县
我们可以使用空间查询根据经度和纬度查找包含该点的地理区域，这在许多网站广告的地理位置，服务中很常见。
通过以上步骤，我们可以看到PostGIS在空间数据处理和分析方面的强大功能。从创建空间对象到分析农贸市场数据，再到处理人口普查形状文件，PostGIS提供了丰富的工具和函数，帮助我们解决各种空间分析问题。无论是查找附近的商店、分析地理区域的面积，还是根据地理位置定位信息，PostGIS都能发挥重要作用。
空间数据处理与分析：从农贸市场到人口普查数据
4. 进一步的空间分析应用
在前面的内容中，我们已经学习了如何使用PostGIS进行基本的空间数据处理和分析。接下来，我们将探讨一些更高级的应用场景。
4.1 空间连接操作
空间连接是一种强大的分析工具，它允许我们根据空间关系将两个不同的数据集连接起来。例如，我们可以将农贸市场数据与人口普查县数据进行连接，以了解每个县内有多少个农贸市场。
首先，我们需要确保两个表都有合适的空间索引。之前我们已经为
farmers_markets
表的
geog_point
列创建了GiST索引，现在我们也为
us_counties_2010_shp
表的
geom
列创建一个索引：
CREATE INDEX county_geom_idx ON us_counties_2010_shp USING GIST (geom);
然后，我们可以使用
ST_Within()
函数进行空间连接，找出每个县内的农贸市场数量：
SELECT us_counties_2010_shp.name10, us_counties_2010_shp.statefp10 AS st, COUNT(farmers_markets.fmid)
FROM us_counties_2010_shp
LEFT JOIN farmers_markets
ON ST_Within(farmers_markets.geog_point, us_counties_2010_shp.geom::geography)
GROUP BY us_counties_2010_shp.name10, us_counties_2010_shp.statefp10
ORDER BY COUNT(farmers_markets.fmid) DESC;
这个查询将返回每个县的名称、州FIPS代码以及该县内的农贸市场数量，并按数量降序排列。
4.2 缓冲区分析
缓冲区分析是另一种常见的空间分析技术，它可以帮助我们找出距离某个空间对象一定距离内的其他对象。例如，我们可以为每个农贸市场创建一个缓冲区，然后找出与这些缓冲区相交的县。
以下是创建缓冲区并进行相交分析的代码：
-- 为每个农贸市场创建10公里的缓冲区
WITH market_buffers AS (
SELECT fmid, ST_Buffer(geog_point::geography, 10000) AS buffer
FROM farmers_markets
)
-- 找出与缓冲区相交的县
SELECT us_counties_2010_shp.name10, us_counties_2010_shp.statefp10 AS st, COUNT(market_buffers.fmid)
FROM us_counties_2010_shp
JOIN market_buffers
ON ST_Intersects(us_counties_2010_shp.geom::geography, market_buffers.buffer)
GROUP BY us_counties_2010_shp.name10, us_counties_2010_shp.statefp10
ORDER BY COUNT(market_buffers.fmid) DESC;
这个查询首先为每个农贸市场创建了一个10公里的缓冲区，然后找出与这些缓冲区相交的县，并统计每个县内相交的农贸市场数量。
5. 空间数据可视化
虽然PostgreSQL和PostGIS本身不提供可视化功能，但我们可以将处理好的空间数据导出到其他工具进行可视化。以下是一些常见的可视化工具和方法：
5.1 使用QGIS进行可视化
QGIS是一个开源的GIS软件，它可以轻松地连接到PostgreSQL数据库并可视化空间数据。
-
步骤
：
- 打开QGIS软件。
- 点击“数据库” ▸ “新建数据库连接” ▸ “PostgreSQL”。
- 在弹出的对话框中，输入数据库的连接信息，包括主机名、端口、数据库名、用户名和密码。
- 点击“测试连接”，确保连接成功后点击“确定”。
-
在“浏览器”面板中，展开数据库连接，找到我们之前创建的表（如
farmers_markets和us_counties_2010_shp）。 - 右键点击表名，选择“添加到项目”，即可将数据加载到QGIS中进行可视化。
5.2 使用Python和Matplotlib进行简单可视化
如果你熟悉Python，可以使用
matplotlib
和
geopandas
库进行简单的空间数据可视化。
import geopandas as gpd
import matplotlib.pyplot as plt
# 连接到PostgreSQL数据库并读取数据
conn_str = "postgresql://username:password@localhost:5432/gis_analysis"
counties = gpd.read_postgis("SELECT * FROM us_counties_2010_shp", conn_str)
markets = gpd.read_postgis("SELECT * FROM farmers_markets", conn_str)
# 设置图形大小
plt.figure(figsize=(10, 10))
# 绘制县边界
counties.plot(ax=plt.gca(), edgecolor='black', facecolor='none')
# 绘制农贸市场位置
markets.plot(ax=plt.gca(), marker='o', color='red', markersize=5)
# 显示图形
plt.show()
6. 总结
通过本文的学习，我们深入了解了如何使用PostGIS进行空间数据处理和分析。从创建空间对象的基本函数，到分析农贸市场数据、处理人口普查形状文件，再到更高级的空间连接和缓冲区分析，以及最后的数据可视化，我们掌握了一系列强大的工具和技术。
空间数据分析在许多领域都有广泛的应用，如城市规划、商业选址、环境监测等。通过合理运用PostGIS和相关工具，我们可以更好地理解和利用地理空间数据，为决策提供有力支持。
以下是本文涉及的主要操作流程总结：
graph LR
A[创建空间对象] --> B[分析农贸市场数据]
B --> C[处理人口普查形状文件]
C --> D[高级空间分析]
D --> E[空间数据可视化]
同时，我们还可以将整个过程中的关键操作步骤整理成一个表格：
| 操作步骤 | 描述 | 代码示例 |
| ---- | ---- | ---- |
| 创建和加载表 | 创建
farmers_markets
表并加载数据 |
CREATE TABLE ...; COPY ...;
|
| 创建地理列 | 为
farmers_markets
表添加地理列并填充数据 |
ALTER TABLE ...; UPDATE ...;
|
| 添加索引 | 为地理列添加GiST索引 |
CREATE INDEX ...;
|
| 查找距离内对象 | 查找距离某个点指定距离内的空间对象 |
SELECT ... WHERE ST_DWithin(...);
|
| 计算距离 | 计算两个空间对象之间的距离 |
SELECT ST_Distance(...);
|
| 加载形状文件 | 通过GUI工具将形状文件加载到数据库 | 按步骤操作GUI工具 |
| 空间连接 | 根据空间关系连接两个数据集 |
SELECT ... FROM ... JOIN ... ON ST_Within(...);
|
| 缓冲区分析 | 创建缓冲区并进行相交分析 |
WITH ... SELECT ... JOIN ... ON ST_Intersects(...);
|
| 数据可视化 | 使用QGIS或Python进行可视化 | 按对应工具步骤操作 |
希望本文能帮助你更好地掌握空间数据处理和分析的技能，在实际应用中发挥更大的作用。
扫一扫
78
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
