Postgreql：查找距离海岸的道路终点

Question

我在一个表中有2.2M的线路几何图形（道路），在另一个表中有1500条线路几何图形（海岸线）。两个表都有一个空间索引。我需要找到距离海岸一定距离内的道路endpoint，并将点几何体与距离一起存储。目前的解决方案，似乎是无效的，需要很多小时才能完成一个非常快的机器；

使用ST_STARTPOINT、ST_ENDPOINT和ST_DWITHIN创建临时表，其中包含距离内道路几何图形的起点和终点。为临时表中的两个几何图形列创建空间索引。

执行两个INSERT INTO操作，一个用于起点，一个用于终点；选择几何形状和距离，使用从点到海岸线的ST_DISTANCE和WHEREST_DWITHIN仅考虑所选距离内的点。

代码大致如下所示：

create temp table roadpoints_temp as select st_startpoint(road.geom) as geomstart, st_endpoint(road.geom) as geomend from 
    coastline_table coast, roadline_table road where st_dwithin(road.geom, coast.geom, 100);

create index on roadpoints_temp (geomstart);

create index on roadpoints_temp (geomend);

create table roadcoast_points as select roadpoints_temp.geomstart as geom, round(cast(st_distance(roadpoints_temp.geomstart,kyst.geom) as numeric),2) as dist 
    from roadpoints_temp, coastline_table coast where st_dwithin(roadpoints_temp.geomstart, coast.geom, 100);

insert into roadcoast_points select roadpoints_temp.geomend as geom, round(cast(st_distance(roadpoints_temp.geomend,kyst.geom) as numeric),2) as dist 
    from roadpoints_temp, coastline_table coast where st_dwithin(roadpoints_temp.geomend, coast.geom, 100);

drop table roadpoints_temp;

欢迎所有意见和建议：-）

Answer 1

您需要有效地利用您的索引。似乎最快的计划是为每个海岸找到所有距离之内的道路。分别进行两次重新检查意味着你失去了与道路最近的海岸线的连接，需要一次又一次地重新找到这对海岸线。

您需要使用EXPLAIN检查您的执行计划，以便对海岸线表进行序列扫描，并对道路表进行GiST索引扫描。

select road.* 
from coastline_table coast, roadline_table road
where 
    ST_DWithin(coast.geom, road.geom, 100) -- indexed query    
    and -- non-indexed recheck
    (
        ST_DWithin(ST_StartPoint(road.geom), coast.geom, 100)
        or ST_DWithin(ST_EndPoint(road.geom), coast.geom, 100)
    );