26、地理数据与数据库视图：提升数据分析效率

地理数据与数据库视图：提升数据分析效率

在数据分析领域，地理数据和数据库视图是两个强大的工具。地理数据能帮助我们了解地理位置相关的信息，而数据库视图则可以简化复杂的查询操作。下面将详细介绍如何使用这些工具进行数据分析。

地理数据的分析与应用

地理数据的分析在很多领域都有重要应用，比如城市规划、灾害预警等。下面以美国的地理数据为例，介绍如何进行地理数据的分析。

确定坐标所属的县

可以使用 ST_Within() 函数来确定一个坐标点位于哪个县。这个函数接受两个几何输入，如果第一个几何对象在第二个几何对象内部，则返回 true 。以下是一个示例查询：

SELECT name10,
       statefp10
FROM us_counties_2010_shp
WHERE ST_Within('SRID=4269;POINT(-118.3419063 34.0977076)'::geometry, geom);

在这个查询中，第一个输入是一个包含 SRID （空间参考标识符）的点的扩展WKT（Well-Known Text）表示，第二个输入是表中的 geom 列。运行这个查询，会得到如下结果：
| name10 | statefp10 |
| — | — |
| Los Angeles | 06 |
这表明提供的点位于加利福尼亚州的洛杉矶县。

空间连接的应用

空间连接可以基于地理位置将不同的表连接起来，为数据分析提供更多的可能性。例如，可以将咖啡店的表与县的表连接起来，以确定每个县有多少家咖啡店。下面以圣达菲县的道路和水道数据为例，介绍如何进行空间连接。

1. 数据准备

   • 下载并解压美国人口普查TIGER/Line形状文件 tl_2016_35049_linearwater.zip 和 tl_2016_35049_roads.zip 。
   • 启动Shapefile和DBF加载器导出器，将这两个形状文件导入到 gis_analysis 数据库中，分别命名为 santafe_linearwater_2016 和 santafe_roads_2016 。
   • 刷新数据库，运行 SELECT * FROM 查询查看数据。道路表应该有12,926行，线性水道表应该有1,198行。

2. 确定几何类型
   可以使用 ST_GeometryType() 函数来确定表中 geom 列的几何类型。以下是示例查询：

SELECT ST_GeometryType(geom)
FROM santafe_linearwater_2016
LIMIT 1;

SELECT ST_GeometryType(geom)
FROM santafe_roads_2016
LIMIT 1;

这两个查询应该返回相同的值 ST_MultiLineString ，表示水道和道路存储为多线串对象。

3. 查找交叉的道路和水道
   使用 ST_Intersects() 函数来查找圣达菲县中与圣达菲河交叉的道路。以下是示例查询：

SELECT water.fullname AS waterway,
       roads.rttyp,
       roads.fullname AS road
FROM santafe_linearwater_2016 water JOIN santafe_roads_2016 roads
     ON ST_Intersects(water.geom, roads.geom)
WHERE water.fullname = 'Santa Fe Riv'
ORDER BY roads.fullname;

这个查询应该返回54行结果，以下是前五行：
| waterway | rttyp | road |
| — | — | — |
| Santa Fe Riv | M | Baca Ranch Ln |
| Santa Fe Riv | M | Cam Alire |
| Santa Fe Riv | M | Cam Carlos Rael |
| Santa Fe Riv | M | Cam Dos Antonios |
| Santa Fe Riv | M | Cerro Gordo Rd |

4. 确定交叉位置
   可以使用 ST_Intersection() 函数来确定道路和河流交叉的具体位置。以下是示例查询：

SELECT water.fullname AS waterway,
       roads.rttyp,
       roads.fullname AS road,
       ST_AsText(ST_Intersection(water.geom, roads.geom))
FROM santafe_linearwater_2016 water JOIN santafe_roads_2016 roads
    ON ST_Intersects(water.geom, roads.geom)
WHERE water.fullname = 'Santa Fe Riv'
ORDER BY roads.fullname;

这个查询的结果将包含河流与道路交叉的精确坐标位置。

数据库视图的使用

数据库视图是一种虚拟表，可以通过保存的查询动态创建。视图可以简化查询操作，提高数据访问的安全性。

创建和查询视图

以下是一个创建视图的示例，该视图显示内华达州2010年各县的人口数据：

CREATE OR REPLACE VIEW nevada_counties_pop_2010 AS
    SELECT geo_name,
           state_fips,
           county_fips,
           p0010001 AS pop_2010
    FROM us_counties_2010
    WHERE state_us_abbreviation = 'NV'
    ORDER BY county_fips;

创建视图后，可以像查询普通表一样查询视图：

SELECT *
FROM nevada_counties_pop_2010
LIMIT 5;

查询结果如下：
| geo_name | state_fips | county_fips | pop_2010 |
| — | — | — | — |
| Churchill County | 32 | 001 | 24877 |
| Clark County | 32 | 003 | 1951269 |
| Douglas County | 32 | 005 | 46997 |
| Elko County | 32 | 007 | 48818 |
| Esmeralda County | 32 | 009 | 783 |

插入、更新和删除数据

可以通过视图对底层表进行插入、更新和删除操作，但视图必须满足一定的条件，例如视图必须引用单个表，查询不能包含 DISTINCT 、 GROUP BY 等子句。以下是一个创建员工视图的示例：

CREATE OR REPLACE VIEW employees_tax_dept AS
    SELECT emp_id,
           first_name,
           last_name,
           dept_id
    FROM employees
    WHERE dept_id = 1
    ORDER BY emp_id
    WITH LOCAL CHECK OPTION;

这个视图只显示税务部门的员工信息，并使用 WITH LOCAL CHECK OPTION 来限制插入和更新操作。

以下是通过视图插入数据的示例：

INSERT INTO employees_tax_dept (first_name, last_name, dept_id)
VALUES ('Suzanne', 'Legere', 1);

INSERT INTO employees_tax_dept (first_name, last_name, dept_id)
VALUES ('Jamil', 'White', 2);

第一个插入操作会成功，因为 dept_id 为1满足视图的条件；第二个插入操作会失败，因为 dept_id 为2不满足视图的条件。

更新和删除操作也遵循类似的规则。通过视图进行数据操作可以更好地控制用户对数据的访问，提高数据的安全性。

通过地理数据的分析和数据库视图的使用，可以更高效地进行数据分析，为决策提供有力的支持。

地理数据与数据库视图：提升数据分析效率（续）

进一步的地理数据分析尝试

可以使用之前导入的空间数据进行更多的分析，以下是几个具体的分析示例及操作步骤。

计算各州面积

要聚合县数据以计算每个州的面积（单位：平方英里），可以按照以下步骤操作：
1. 以 us_counties_2010_shp 表中的 statefp10 列作为分组依据。
2. 使用聚合函数计算每个州内所有县面积之和。

示例代码如下：

-- 这里假设可以通过一些方式计算面积，实际可能需要根据数据具体情况调整
SELECT statefp10, SUM( -- 这里假设存在面积列，如 area_column
       area_column) AS state_area
FROM us_counties_2010_shp
GROUP BY statefp10;

然后可以对比计算出的各州面积与育空 - 科尤库克地区的面积，统计出比其面积大的州的数量。

计算两个农贸市场之间的距离

要确定两个农贸市场（橡树叶绿色市场和哥伦比亚农贸市场）之间的距离，可以按照以下步骤操作：
1. 在 farmers_markets 表中找到这两个市场的坐标。
2. 使用 ST_Distance() 函数计算它们之间的距离。

可以使用公共表表达式（CTE）来实现，示例代码如下：

WITH market1 AS (
    SELECT geog_point
    FROM farmers_markets
    WHERE address = '9700 Argyle Forest Blvd, Jacksonville, Florida'
),
market2 AS (
    SELECT geog_point
    FROM farmers_markets
    WHERE address = '1701 West Ash Street, Columbia, Missouri'
)
SELECT ST_Distance(market1.geog_point, market2.geog_point) / 1609.34 AS distance_miles
FROM market1, market2;

这里将距离结果转换为英里（1 英里约等于 1609.34 米）。

查找缺失的县名

farmers_markets 表中有超过 500 行的 county 列值缺失，可以使用 us_counties_2010_shp 表和 ST_Intersects() 函数进行空间连接来查找缺失的县名，具体步骤如下：
1. 由于 farmers_markets 表中的 geog_point 列是 geography 类型且 SRID 为 4326，需要将 us_counties_2010_shp 表中的 geom 列转换为 geography 类型并设置 SRID 为 4326。
2. 使用 ST_Intersects() 函数进行空间连接，找到每个市场所在的县。

示例代码如下：

UPDATE farmers_markets
SET county = (
    SELECT name10
    FROM us_counties_2010_shp
    WHERE ST_Intersects(ST_SetSRID(geom::geography, 4326), geog_point)
)
WHERE county IS NULL;

数据库视图的更多应用场景和优势

视图在数据库操作中具有多种应用场景和显著优势，除了前面提到的简化查询和控制数据访问外，还可以从以下几个方面体现。

简化复杂查询

对于一些复杂的查询，如涉及多个表连接和复杂计算的查询，可以将其封装在视图中。例如，计算美国各县从 2000 年到 2010 年的人口变化百分比的查询：

CREATE OR REPLACE VIEW county_pop_change_2010_2000 AS
    SELECT c2010.geo_name,
           c2010.state_us_abbreviation AS st,
           c2010.state_fips,
           c2010.county_fips,
           c2010.p0010001 AS pop_2010,
           c2000.p0010001 AS pop_2000,
           round( (CAST(c2010.p0010001 AS numeric(8,1)) - c2000.p0010001)
                 / c2000.p0010001 * 100, 1 ) AS pct_change_2010_2000
    FROM us_counties_2010 c2010 INNER JOIN us_counties_2000 c2000
      ON c2010.state_fips = c2000.state_fips
         AND c2010.county_fips = c2000.county_fips
    ORDER BY c2010.state_fips, c2010.county_fips;

之后可以直接查询这个视图来获取所需数据，而不需要每次都编写复杂的查询语句：

SELECT geo_name,
       st,
       pop_2010,
       pct_change_2010_2000
FROM county_pop_change_2010_2000
WHERE st = 'NV'
LIMIT 5;

数据安全和隐私保护

通过视图可以限制用户对敏感数据的访问。例如，在员工表中，工资信息属于敏感数据，可以创建一个不包含工资列的视图：

CREATE OR REPLACE VIEW employees_safe_view AS
    SELECT emp_id,
           first_name,
           last_name,
           dept_id
    FROM employees;

这样，用户只能通过视图访问非敏感信息，从而保护了数据的安全性和隐私性。

总结与展望

地理数据的分析和数据库视图的使用为数据分析提供了强大的工具和方法。地理数据的分析可以帮助我们更好地了解地理位置相关的信息，如确定坐标所属的县、分析道路和水道的交叉情况等；数据库视图则可以简化复杂的查询操作，提高数据访问的安全性和控制用户对数据的操作。

在未来的数据分析工作中，可以进一步探索地理数据和数据库视图的更多应用场景。例如，可以结合地理信息系统（GIS）软件，如 QGIS，对地理数据进行可视化分析；可以创建更多复杂的视图来满足不同的业务需求。同时，也可以不断学习和掌握更多的数据库技术和函数，以提升数据分析的效率和质量。

通过合理运用地理数据和数据库视图，能够更高效地进行数据分析，为各个领域的决策提供有力的支持。

以下是一个简单的 mermaid 流程图，展示了地理数据分析和数据库视图使用的主要流程：

graph LR
    classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px;

    A(地理数据导入):::process --> B(地理数据分析):::process
    B --> B1(确定坐标所属县):::process
    B --> B2(空间连接分析):::process
    B --> B3(进一步分析尝试):::process
    C(创建数据库视图):::process --> D(查询视图):::process
    C --> E(通过视图操作数据):::process
    D --> D1(简化复杂查询):::process
    E --> E1(控制数据访问):::process

这个流程图展示了地理数据从导入到分析的过程，以及数据库视图的创建和使用流程，体现了两者在数据分析中的重要作用。