[数据库]使用标准的 PostgreSQL/PostGIS 查询

使用标准的 PostgreSQL/PostGIS 查询语句来判断一个点是否包含在数据库记录中的几种方法：

准备数据库表和数据

-- 连接到 gis_db 数据库
-- \c gis_db

-- 创建表
CREATE TABLE map_line_rectangle (
    id SERIAL PRIMARY KEY,
    row_number VARCHAR(20) NOT NULL,
    rectangle_geom GEOMETRY(POLYGON, 4326)
);

-- 为行号字段创建索引（提高查询性能）
CREATE INDEX idx_map_line_rectangle_row_number ON map_line_rectangle(row_number);

-- 为几何字段创建空间索引（重要！）
CREATE INDEX idx_map_line_rectangle_geom ON map_line_rectangle USING GIST(rectangle_geom);




-- 插入一个矩形数据（四个点构成的多边形）
INSERT INTO map_line_rectangle (row_number, rectangle_geom) VALUES
('ROW001', ST_GeomFromText('POLYGON((116.3974 39.9093, 116.4074 39.9093, 116.4074 39.8993, 116.3974 39.8993, 116.3974 39.9093))', 4326));

-- 插入另一个矩形
INSERT INTO map_line_rectangle (row_number, rectangle_geom) VALUES
('ROW002', ST_MakeEnvelope(116.3, 39.8, 116.4, 39.9, 4326));

基本查询语句

-- 假设点的经纬度为：经度 116.402, 纬度 39.905
SELECT 
    id,
    row_number,
    ST_AsText(rectangle_geom) as geometry_wkt
FROM map_line_rectangle 
WHERE ST_Contains(rectangle_geom, ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326));

更完整的查询示例

-- 方法1：使用 ST_Contains - 判断几何体是否完全包含点
SELECT 
    id,
    row_number,
    ST_Area(rectangle_geom) as area,  -- 计算矩形面积
    ST_AsText(rectangle_geom) as geometry_wkt
FROM map_line_rectangle 
WHERE ST_Contains(rectangle_geom, ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326));

-- 方法2：使用 ST_Within - 判断点是否在几何体内部（与 ST_Contains 相反）
SELECT 
    id,
    row_number
FROM map_line_rectangle 
WHERE ST_Within(ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326), rectangle_geom);

-- 方法3：使用 ST_Intersects - 判断点是否与几何体相交（在边界上也返回true）
SELECT 
    id,
    row_number
FROM map_line_rectangle 
WHERE ST_Intersects(rectangle_geom, ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326));

带参数的可复用查询函数

-- 创建函数以便重复使用
CREATE OR REPLACE FUNCTION find_rectangles_containing_point(
    p_longitude NUMERIC,
    p_latitude NUMERIC
) 
RETURNS TABLE(
    rect_id INTEGER,
    rect_row_number VARCHAR,
    rect_area NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        mlr.id,
        mlr.row_number,
        ST_Area(mlr.rectangle_geom) as area
    FROM map_line_rectangle mlr
    WHERE ST_Contains(mlr.rectangle_geom, ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326));
END;
$$ LANGUAGE plpgsql;

-- 使用函数查询
SELECT * FROM find_rectangles_containing_point(116.402, 39.905);

性能优化的查询

-- 使用边界框预先过滤，提高查询性能
SELECT 
    id,
    row_number,
    ST_AsText(rectangle_geom) as geometry_wkt
FROM map_line_rectangle 
WHERE 
    -- 先用边界框快速过滤（利用空间索引）
    rectangle_geom && ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326)
    -- 再精确判断包含关系
    AND ST_Contains(rectangle_geom, ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326));

查询多个点

-- 批量查询多个点
WITH points_to_check AS (
    SELECT 1 as point_id, ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326) as point_geom
    UNION ALL
    SELECT 2 as point_id, ST_SetSRID(ST_MakePoint(116.450, 39.950), 4326) as point_geom
)
SELECT 
    p.point_id,
    mlr.id as rectangle_id,
    mlr.row_number,
    ST_AsText(mlr.rectangle_geom) as rectangle_wkt
FROM points_to_check p
CROSS JOIN map_line_rectangle mlr
WHERE ST_Contains(mlr.rectangle_geom, p.point_geom)
ORDER BY p.point_id, mlr.id;

完整的实际应用示例

-- 完整的查询，包含所有相关信息
SELECT 
    mlr.id,
    mlr.row_number,
    ST_AsText(mlr.rectangle_geom) as rectangle_geometry,
    ST_Area(mlr.rectangle_geom) as area_sq_degrees,
    -- 将面积转换为平方米（近似）
    ST_Area(mlr.rectangle_geom::geography) as area_sq_meters,
    ST_Centroid(mlr.rectangle_geom) as center_point
FROM map_line_rectangle mlr
WHERE ST_Contains(mlr.rectangle_geom, ST_SetSRID(ST_MakePoint(116.402, 39.905), 4326))
ORDER BY mlr.id;

关键函数说明

• ST_MakePoint(longitude, latitude): 创建点几何对象
• ST_SetSRID(geometry, srid): 设置坐标系（4326 = WGS84）
• ST_Contains(geometryA, geometryB): 判断A是否完全包含B
• ST_Within(geometryA, geometryB): 判断A是否在B内部
• ST_Intersects(geometryA, geometryB): 判断两个几何体是否相交
• &&: 边界框重叠运算符（快速过滤）

使用建议

1. 推荐使用 ST_Contains：它明确表示"几何体包含点"
2. 确保坐标系一致：点和面必须使用相同的SRID（这里是4326）
3. 利用空间索引：查询时会自动使用GIST索引加速
4. 对于边界情况：如果希望点在边界上也返回true，使用 ST_Intersects

这个查询会返回所有包含指定经纬度点的矩形记录。