pgsql：范围内半径坐标查询

目录

修订记录

版本：

postgis介绍：

前置准备：

案例实现：

建表

添加数据

坐标获取

数据添加sql

查询

经纬度写死

通过sql查询经纬度

修订记录：

内容	时间
增加目录、修改文章内sql表字段名称	2024.6.10

版本：

        pgsql ：12.16

        postgis：每个pgsql版本有对应的版本（一般与pgsql版本一致）

postgis介绍：

        在pgsql7.1版本引入

        postgis是pgsql的拓展，存储地理特征（如点、线、面（多边形）等），用于处理和分析这些地理数据

前置准备：

 查询数据库中拓展

SELECT * FROM pg_extension WHERE extname = 'postgis';

 有这个就行了（没有的百度下载安装）

 安装完成后执行（新建数据库执行）

执行完成后再按照查询拓展验证是否已存在postgis拓展

-- 启用PostGIS扩展（如果尚未启用）
CREATE EXTENSION IF NOT EXISTS postgis;

成功后数据库会多一个这样的表

是postgis初始化创建的系统表，不用管

案例实现：

                        查询某一坐标半径内所有坐标

建表

-- 创建表，包含一个地理空间列
CREATE TABLE locations_demo (
    id SERIAL PRIMARY KEY,
    name varchar(32),
    --POINT代表点（还有线，多边形），4326是一个标准地理坐标系统的编码（可以换
    location GEOGRAPHY(POINT, 4326) -- 使用WGS 84坐标系（SRID 4326）
);

添加数据

坐标获取

                这些是根据高德地图随便找的真实经纬度坐标

                如果要精细的经纬度需要绑定身份信息
坐标拾取器 | 高德地图API (amap.com)https://lbs.amap.com/tools/picker

数据添加sql

INSERT INTO locations_demo (name, location) VALUES
('泰山风景名胜区', ST_SetSRID(ST_MakePoint(117.10884,36.257434), 4326)),
('天安门', ST_SetSRID(ST_MakePoint(116.397455,39.909187), 4326)),
('北京大兴国际机场', ST_SetSRID(ST_MakePoint(116.428937,39.513655), 4326)),
('青岛大学', ST_SetSRID(ST_MakePoint(120.422859,36.071915), 4326)),
('华山风景名胜区', ST_SetSRID(ST_MakePoint(110.070978,34.493336), 4326)),
('故宫', ST_SetSRID(ST_MakePoint(116.397029,39.917839), 4326));

查询

经纬度写死

查询当前坐标半径小于2000m的坐标

 坐标是故宫附近随便找的

WITH Target AS (
    --POINT括号内是空格 经纬度
    SELECT 'POINT(116.400635 39.908077)'::GEOGRAPHY AS location
)
SELECT l.*
FROM locations_demo l
CROSS JOIN Target t
WHERE ST_Distance(l.location, t.location) <= 2000;  -- 单位：米

 查询结果：

通过sql查询经纬度

添加一条数据假设当前坐标

INSERT INTO locations_demo (name, location) VALUES
('当前位置', ST_SetSRID(ST_MakePoint(116.400635,39.908077), 4326));

查询sql：

WITH Target AS (
    SELECT location
    FROM locations_demo
    WHERE name = '当前位置'
    LIMIT 1  -- 确保只有一个结果，根据实际情况调整，可加可不加
)
SELECT l.*
FROM locations_demo l
CROSS JOIN Target t
WHERE ST_Distance(l.location, t.location) <= 800000;  -- 单位：米

查询结果 ：