pgsql扩展-地理位置

在地理信息系统（GIS）领域，PostgreSQL 的PostGIS插件是绝对的行业霸主。但是我们的pgsql里面没有，我们可以用自带的earthdistance CREATE EXTENSIO

在地理信息系统（GIS）领域，PostgreSQL 的PostGIS插件是绝对的行业霸主。

但是我们的pgsql里面没有，我们可以用自带的earthdistance

CREATE EXTENSION cube;          -- earthdistance 依赖它
CREATE EXTENSION earthdistance;

-- 计算 (经度1, 纬度1) 到 (经度2, 纬度2) 的距离
SELECT earth_distance(
    ll_to_earth(23.45, 113.12), 
    ll_to_earth(23.50, 113.15)
) AS distance_meters;

示例

CREATE TABLE file_locations (
    id SERIAL PRIMARY KEY,
    file_name TEXT NOT NULL,
    lng DOUBLE PRECISION, -- 经度
    lat DOUBLE PRECISION  -- 纬度
);

-- 插入一些演示数据（以长沙市几个坐标为例）
INSERT INTO file_locations (file_name, lng, lat) VALUES
('项目文档.pdf', 112.937, 28.228),    -- 岳麓山附近
('假期照片.zip', 112.971, 28.191),    -- 五一广场附近
('代码备份.tar.gz', 113.007, 28.224); -- 长沙火车站附近

可以用百度地图的坐标拾取系统

https://lbs.baidu.com/maptool/getpoint

计算两点间的距离

假设你当前的位置是(112.94, 28.23)，我们要看看这些文件离你有多远。

-- earth_distance 函数返回的结果单位是“米”
SELECT 
    file_name,
    earth_distance(
        ll_to_earth(28.23, 112.94), -- 当前位置 (纬度, 经度)
        ll_to_earth(lat, lng)       -- 目标位置 (纬度, 经度)
    ) AS distance_meters
FROM file_locations;

查找 5 公里范围内的文件

-- 搜索 5000 米范围内的文件，并按距离从近到远排序
SELECT 
    file_name,
    earth_distance(ll_to_earth(28.23, 112.94), ll_to_earth(lat, lng)) / 1000 AS distance_km
FROM file_locations
WHERE earth_box(ll_to_earth(28.23, 112.94), 5000) @> ll_to_earth(lat, lng)
ORDER BY distance_km ASC;

如果说earthdistance是只能算距离的“皮尺”，那么PostGIS 就是一套完整的“卫星测绘系统”。

它最特别的地方在于，它让数据库真正理解了空间几何关系，而不仅仅是存两个数字（经纬度）。

PostGIS 的四大核心“超能力”

丰富的空间几何类型

除了“点”（Point），它还支持复杂的图形：

线（LineString）：比如存储一段航线、公路。
面（Polygon）：比如存储一个学校、一个行政区、甚至是一个不规则的湖泊。
集合（Multi-Geometry）：比如存储由多个岛屿组成的群岛。

强大的空间关系判断（不仅仅是距离）

这是 PostGIS 最牛的地方。它可以回答各种复杂的逻辑问题：

包含关系 (ST_Contains)：这个文件上传的坐标，是在“北京市”这个多边形范围内吗？
相交/重叠 (ST_Intersects)：这两条航线是否有交叉点？
接触 (ST_Touches)：这两个地块是紧邻的吗？

几何计算与加工

它能在数据库里直接生成新的图形：

缓冲区 (ST_Buffer)：以这条公路为中心，左右扩展 500 米，生成一个“拆迁补偿区”的多边形。
合并/差异 (ST_Union/ST_Difference)：把两个相邻的校区合并成一个大校区，或者算出两个地块重叠的部分。

坐标系转换（解决“地图偏移”神器）

地图界有个很头疼的问题：GPS 坐标（WGS84）、高德/腾讯坐标（GCJ-02）、百度坐标（BD-09）是有偏移的。

PostGIS 内置了全球几千种坐标系（SRID）。
它可以一行代码完成转换：ST_Transform(geometry, 4326)。