什么是postgresql的查询来解决这些相当复杂的步骤？

Question

stackoverflow

这可能会受到质疑，因为它包含了PostGIS函数，用于根据我们现有的旧数据计算每个资产的丢失数据(the_geom)。无论你是否了解PostGIS，你都可以解决这个问题。

• (注意)我清楚地知道我想从这个问题中得到什么，但我不知道将所有这些合并到单个查询或PHP脚本中的步骤。

让我解释一下问题的细节。首先，想想街上的自行车道(资产)。自行车道本身是一条直线线，位于一条或多条街道(子路段)。

要可视化这个问题，以下是一些可能的结果。

==============|=============
x---------------------y

==============|======|==========
   x-------------------------y

==============
    x------y

注："=======“是km_start (每个子ctrl-节)，"x”是每个资产的km_end (结束点)，“x”是每个资产的辅助(每个资产)，"y“是每个资产的km_end(结束点)。

资产(表)-包含5项资产

aid | km_start | km_end | ctrl_sec_no | the_geom
1 | 10 | 15 | 1234 | null
2 | 10 | 25 | 1234 | null
3 | 13 | 15 | 5678 | null
4 | 11 | 15 | 5678 | null
5 | 13 | 17 | 5678 | null

中心线(表) --包含5个子ctrl段.

 cid | km_start | km_end | ctrl_sec_no | the_geom(LINESTRING)
   1 | 10 | 12 | 1234 | xxxx...
   2 | 13 | 15 | 1234 | xxxx...
   3 | 16 | 30 | 1234 | xxxx...
   4 | 10 | 15 | 5678 | xxxx...
   5 | 16 | 20 | 5678 | xxxx...

我希望结果是

aid(1) -> cid(1) + cid(2)
aid(2) -> cid(1) + cid(2) + cid(3)
aid(3) -> cid(4)
aid(4) -> cid(4)
aid(5) -> cid(4) + cid(5)

注：“资产(1)包含在中心线(1)中，中线(2)包含在cid中”。

然后，在我们从上面的步骤中得到每个(中线)之后，我们希望通过使用"子串“来查找我们得到的每个the_geom的子字符串来更新每个援助(资产)的子串。

但是现在我们没有资产(表)的the_geom，所以我们必须计算它！

例如(ctrl_sec_no:1234) --这不是完美的尺度，只是为了可视化

10======12======13======15======16======30
x-------|-------|-------y (aid:1)
x-------|-------|-------------------y (aid:2)
                x-------y (aid:3)

例如(ctrl_sec_no:5678)

10=====15/16=====20
  x-----y (aid:4)
     x--|----y (aid:5)

这意味着我们必须使用ST_Line_Substring计算每个子ctrl段中的资产比率。现在，我们将展示这个函数是如何工作的:)

例如：(援助:5)在两个分区中被占用。

SELECT ST_Line_Substring('the_geom',13-10/15-10,1.0) FROM centerline WHERE ctrl_sec_id = 4567 and cid = 4

SELECT ST_Line_Substring('the_geom',0.0,17-16/20-16) FROM centerline WHERE ctrl_sec_id = 4567 and cid = 5

(13-10)/(15-10) = 3/5是(援助:5)在(cid:4) - a.km_start - c.km_start / c.km_end - c.km_start中所占的第一部分资产的比率。

(17-16)/(20-16) = 1/4是(援助:5)在(cid:5) - a.km_start - c.km_start / c.km_end - c.km_start中所占资产的第2部分的比率。

然后，我们必须从上面的查询中计算出每个部分(从ST_Union返回的the_geom)。

问题是执行所有这些步骤的查询是什么，如果有必要，可以使用PHP脚本完成这些步骤。

Answer 1

create table asset (
    aid integer primary key, 
    km_start integer not null, 
    km_end integer not null, 
    ctrl_sec_no integer not null, 
    the_geom text default null
)
;

insert into asset (aid, km_start, km_end, ctrl_sec_no)
values
(1, 10, 15, 1234),
(2, 10, 25, 1234),
(3, 10, 12, 5678), 
(4, 11, 15, 5678),
(5, 13, 17, 5678)
;

create table centerline (
    cid integer primary key, 
    km_start integer not null, 
    km_end integer not null, 
    ctrl_sec_no integer not null, 
    the_geom text not null default 'xxxx...'
)
;

insert into centerline (cid, km_start, km_end, ctrl_sec_no)
values
(1, 10, 12, 1234),
(2, 13, 15, 1234),
(3, 16, 30, 1234),
(4, 10, 15, 5678),
(5, 16, 17, 5678)
;

select aid, cid
from asset a
inner join centerline c on 
    a.ctrl_sec_no = c.ctrl_sec_no
    and (
        a.km_start between c.km_start and c.km_end
        or
        a.km_end between c.km_start and c.km_end
        or
        a.km_start <= c.km_start and a.km_end >= c.km_end
        )
order by aid, cid
;

 aid | cid 
-----+-----
   1 |   1
   1 |   2
   2 |   1
   2 |   2
   2 |   3
   3 |   4
   4 |   4
   5 |   4
   5 |   5
(9 rows)

我不明白你问题的第二部分。你能详细说明一下吗？