ST_Distance beetwen多点

Question

我有两张桌子，public.street_lights和public.roads。

这两个文件中都有一个列wkb_geometry (类型为geometry)。

这是我的问题：

SELECT 
    ST_Distance(ST_Transform((SELECT wkb_geometry 
                              FROM public.street_lights 
                              WHERE ogc_fid = 1), 2855),
    ST_Transform((SELECT wkb_geometry 
                  FROM public.roads 
                  WHERE ogc_fid = 1), 2855))

返回道路(id = 1)和灯光(id = 1)之间的距离。

我的问题是:如何修改我的查询来检查灯光(id = 1)与每条道路之间的距离？

第二个问题:我应该如何修改查询以将新列添加到距离最小的public.street_lights中，其中将是road_id。换句话说:我需要从roads找到距离最小的每个light - road_id。

Answer 1

我相信您需要的是去除子查询，只需将表放在WHERE子句中即可。如果我没理解错的话，您希望获得与给定灯光最接近的道路的单个值，所以您所需要的就是计算到所有可能道路的距离，ORDER BY结果并获得第一个值，例如：

样本数据

CREATE TABLE public.street_lights (ogc_fid INT, wkb_geometry GEOMETRY);
CREATE TABLE public.roads (ogc_fid INT, wkb_geometry GEOMETRY);

INSERT INTO street_lights VALUES (1,'SRID=4326;POINT(1 2)');
INSERT INTO roads VALUES (1,'SRID=4326;POINT(1 2.50)'),
                         (2,'SRID=4326;POINT(1 2.42)');

查询

SELECT roa.ogc_fid, ST_Distance(ST_Transform(lig.wkb_geometry, 2855),
                                ST_Transform(roa.wkb_geometry, 2855)) AS distance
FROM public.street_lights lig, public.roads roa
WHERE lig.ogc_fid = 1
ORDER BY distance LIMIT 1;

 ogc_fid |     distance     
---------+------------------
       2 | 61533.2214579388
(1 Zeile)

问题2：

我应该如何修改查询以将新列添加到public.street_lights中，其中road_id的距离是最小的。

您可以使用ALTER TABLE语句创建新列，例如

ALTER TABLE public.street_lights ADD COLUMN road_id_min_dist INT;

要用road_id填充这个新列，可以在CTE中使用相同的查询

WITH j AS (
  SELECT lig.ogc_fid AS light_id, roa.ogc_fid AS road_id, 
    ST_Distance(ST_Transform(lig.wkb_geometry, 2855),
                ST_Transform(roa.wkb_geometry, 2855)) AS distance
  FROM public.street_lights lig, public.roads roa
  ORDER BY distance LIMIT 1)
UPDATE public.street_lights SET road_id_min_dist = j.road_id
FROM j WHERE j.light_id = ogc_fid;

这是你的数据..

SELECT ogc_fid,ST_AsText(wkb_geometry), road_id_min_dist 
FROM street_lights;

 ogc_fid | st_astext  | road_id_min_dist 
---------+------------+------------------
       1 | POINT(1 2) |                2
(1 Zeile)