PostgreSQL 日常SQL语句查询记录--空间查询

具体查询示例如下：

在pg数据库中，如果需要使用空间查询，需要先进行安装空间扩展；

CREATE EXTENSION POSTGIS;
CREATE EXTENSION PGROUTING;
CREATE EXTENSION POSTGIS_TOPOLOGY;
CREATE EXTENSION FUZZYSTRMATCH;
CREATE EXTENSION POSTGIS_TIGER_GEOCODER;
CREATE EXTENSION ADDRESS_STANDARDIZER;

在表中定义空间字段时，通常使用 geometry 或 geography 数据类型。geometry 用于平面坐标系，geography 用于地理坐标系（地球表面坐标）。

例如，定义一个存储点数据的表:

CREATE TABLE table_point(id SERIAL PRIMARY KEY,name TEXT,geom geometry(Point, 4326)
);

• 插入空间数据

插入点、线、面的数据可以通过 ST_GeomFromText、ST_SetSRID 等函数来完成。
例如，插入一个点数据：

INSERT INTO table_point (name, geom)
VALUES ('Location A', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)); -- 经度, 纬度

• 查询缓冲区（Buffer Query）

你可以使用 ST_Buffer 函数来查询某个点、线、面周围的缓冲区（例如，查询某个地点 1 公里范围内的区域）。
例如，查询某个点周围 1 公里的缓冲区：

SELECT name
FROM table_point
WHERE ST_DWithin(geom,ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326),1000 -- 1 公里（单位为米）
);

ST_DWithin 用于检查两个几何对象是否在指定的距离范围内。

• 点、线、面是否在某个范围中

你可以使用 ST_Within 来判断一个几何对象是否在另一个几何对象的范围内。
例如，检查一个点是否在某个多边形（如城市的边界）内：

SELECT name
FROM table_point
WHERE ST_Within(geom,(SELECT geom FROM boundaries WHERE name = 'City Boundary')
);

• 查询点与多边形是否相交

你可以使用 ST_Intersects 函数来查询点是否与某个面相交。例如，检查某个点是否在某个行政区域内（假设 boundary 表是存储多边形的表）：

SELECT name
FROM table_point
WHERE ST_Intersects(geom,(SELECT geom FROM boundary WHERE name = 'Region A')
);

• 查询线段与多边形的交集
  例如，查询某条道路是否与某个区域有交集：

SELECT road_name
FROM roads
WHERE ST_Intersects(geom,(SELECT geom FROM region WHERE name = 'Park Area')
);

• 查询距离（Distance Query）

你还可以使用 ST_Distance 函数来计算两个几何对象之间的距离：

SELECT name, ST_Distance(geom, ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)) AS distance
FROM table_point
ORDER BY distance
LIMIT 10;

这个查询会返回距离指定点最近的 10 个位置。

• 空间索引优化查询

为了提高空间查询的效率，通常会创建空间索引。PostGIS 使用 GiST（Generalized Search Tree）索引来加速空间查询。
例如，创建空间索引：
CREATE INDEX table_point_geom_idx ON locations USING GIST (geom);
空间索引能显著提高如 ST_Within、ST_Intersects 等空间查询的性能。

• 查询包含特定形状的对象（如矩形）

你可以使用 ST_Envelope 来获取几何对象的最小外包矩形，然后查询是否包含在某个矩形内。

SELECT name
FROM table_point
WHERE ST_Within(geom,ST_SetSRID(ST_MakeBox2D(ST_MakePoint(-73.9, 40.7), ST_MakePoint(-73.8, 40.8)), 4326)
);

这个查询会查找位于指定矩形范围内的所有位置。

---

•  查询某个点是否在指定的多边形内（如城市边界内的一个地址）

假设你有一个表 locations，其中有一些地理坐标，你想查询哪些地点在某个多边形（如城市的行政区域）内。

SELECT name
FROM table_point
WHERE ST_Within(geom,(SELECT geom FROM regions WHERE name = 'City Boundary')
);

ST_Within 用于判断 geom 是否完全在指定的多边形 geom 内。

•  查询某个点周围一定距离内的地点（缓冲区查询）

假设你想查找某个特定点周围 500 米内的所有地点。可以使用 ST_DWithin 来实现。

SELECT name
FROM table_point
WHERE ST_DWithin(geom,ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326), 500
);

ST_DWithin 用于判断两个几何对象是否在指定的距离范围内（单位：米）。如果你查询的是地理坐标，通常单位是米。

• 查询某条线段与多边形是否相交（例如，查询某条道路是否穿过某个区域）

可以使用 ST_Intersects 来判断两条几何对象是否有交集。假设你有一个表 roads 存储道路的几何数据，boundary 表存储区域的几何数据：

SELECT road_name
FROM roads
WHERE ST_Intersects(geom,(SELECT geom FROM boundary WHERE name = 'Park Area')
);

ST_Intersects 判断两几何对象是否有交集。如果道路与公园区域有交集，则返回该道路名称。

• 查询某个多边形和另一个多边形的交集（例如，查找两个区域重叠的部分）

假设你有一个表 zones，存储不同的地理区域，你可以使用 ST_Intersection 来找出两个区域的交集。

SELECT ST_AsText(ST_Intersection((SELECT geom FROM zones WHERE name = 'Zone 1'),(SELECT geom FROM zones WHERE name = 'Zone 2')
)) AS intersection_geom;

ST_Intersection 返回两个几何对象的交集。如果两个区域有重叠部分，将返回该交集的几何形状。

• 查询点到多边形的最短距离（例如，查询某个地点到最近的道路的距离）

假设你有一个表 locations 和 roads，你想查询每个地点到最近的道路的距离：

SELECT l.name, r.road_name, ST_Distance(l.geom, r.geom) AS distance
FROM locations l, roads r
ORDER BY distance
LIMIT 10;

ST_Distance 计算两个几何对象之间的最短距离。

• 查询包含特定矩形的区域（如查询某个矩形区域内的所有城市）

你可以使用 ST_MakeBox2D 来定义一个矩形范围，并查询该范围内的所有城市。假设你有一个 cities 表，存储了城市的地理数据：

SELECT name
FROM cities
WHERE ST_Within(geom,ST_SetSRID(ST_MakeBox2D(ST_MakePoint(-73.9, 40.7), ST_MakePoint(-73.8, 40.8)), 4326)
);

ST_MakeBox2D 用于创建一个矩形，ST_Within 检查城市是否在该矩形内。

• 查询某一地点与其他地点的最近邻（例如，查找离某个地点最近的商店）

假设你想查询距离某个地点最近的 5 个商店，可以使用 ST_Distance 结合 ORDER BY 排序：

SELECT store_name, ST_Distance(geom, ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)) AS distance
FROM stores
ORDER BY distance
LIMIT 5;

这会返回距离指定点（比如某个坐标的商店）最近的 5 个商店。

• 查询两个多边形是否相交（例如，两个行政区域是否重叠）

你可以使用 ST_Intersects 判断两个多边形是否有交集。例如，判断两个行政区域是否重叠：

SELECT a.name, b.name
FROM regions a, regions b
WHERE a.id != b.id AND ST_Intersects(a.geom, b.geom);

ST_Intersects 返回那些在空间上有交集的区域。

• 计算几何对象的面积或周长（适用于面对象）

你可以使用 ST_Area 和 ST_Perimeter 来计算面对象的面积和周长。例如，计算某个区域的面积：

SELECT name, ST_Area(geom) AS area
FROM zones;

ST_Area 返回多边形的面积（如果是投影坐标系的话，单位通常是平方米）。对于面对象，ST_Perimeter 则返回周长。

• 查询多个点（如商店）与一条线（如街道）是否相交

假设你想查找哪些商店位于某条街道上，或与其有交集：

SELECT store_name
FROM stores
WHERE ST_Intersects(geom,(SELECT geom FROM streets WHERE name = 'Main Street')
);

这会返回与“Main Street”街道有交集的商店名称。

• 查找多边形（区域）包含的所有点（例如，查询所有在某个区域内的建筑物）

假设有一个表 buildings，你想查找某个特定区域内的所有建筑物：

SELECT name
FROM buildings
WHERE ST_Within(geom,(SELECT geom FROM zones WHERE name = 'Downtown Area')
);

ST_Within 会返回所有位于指定区域内的建筑物。

• 计算两个几何对象的重叠面积

如果你想知道两个区域之间的重叠面积，可以使用 ST_Intersection 和 ST_Area：

SELECT ST_Area(ST_Intersection(a.geom, b.geom)) AS overlap_area
FROM zones a, zones b
WHERE a.id != b.id AND ST_Intersects(a.geom, b.geom);

这会返回两个区域交集部分的面积。

其他：

在 PostgreSQL 中，如果你想要在查询时检查某个字段是否为空（NULL），并且在为空时使用另一个字段或者固定值返回，可以使用 COALESCE 函数。COALESCE 函数会返回第一个非 NULL 的值。

COALESCE(field1, field2, 'default_value') 会检查 field1 是否为 NULL，如果是 NULL，就检查 field2 是否为 NULL，如果 field2 也是 NULL，那么返回 'default_value'。示例如下：

SELECT tid,user_id,COALESCE(user_name, loginname, 'admin')           as user_name,user_type,email,COALESCE(phonenumber, mobileno, '')               as phonenumber,sex,COALESCE(avatar, photourl, '')                    as avatar,COALESCE(status::INTEGER, userstatus::INTEGER, 0) as status,COALESCE(del_flag, '0')                           as del_flag,login_ip,login_date,create_by,create_time,update_by,update_time,remark
FROM cloud.biz_user_info;

好了，今天分享的内容就到这里啦！