如何通过使用几个查询从多个表中计算数据？

Question

注意：这个问题与PostGIS和Postgresql相关，并用PHP实现

现在我有表A：

gid | kstart  | kend    | ctrl_sec_no | the_geom | 
626 | 238     | 239     | 120802      | 123456   |
638 | 249     | 250     | 120802      | 234567   |
4037| 239     | 249     | 120802      | 345678   |

注意：the_geom是一个几何值（类型：LINE），在这种情况下我随机它们以提高可读性

和表B：

gid | ctrl_sec_no | x   | the_geom
543 | 120802      | 239 | null
544 | 120802      | 247 | null

[PostGIS描述] 这两个表通过 ctrl_sec_no 关联，这意味着表 A 中 ctrl_sec_no 120802 上的 3 个连续 LINE 连接成一条 LINE，并包含表 B 中的两个 POINT。我们只知道距离 {MAX(kend) - MIN(kstart)} LINE 以及该线路上的公里数 (x)。

问题是 PostgreSQL 的查询是什么..

(a.) 从 A.kend 中选择最高值，减去 A.kstart 中的最低值 -> 250 - 238 = 12

(b.) 从 A.kend 中选择最高值，减去 B 中的“x”值 -> 250 - 238 = 12 250 - 239 = 11

(c.) 根据这两个值计算比率 ((b.)/(a.)) → 11/12

(d.) 使用 PostGIS : ST_Interpolate -> ST_Interpolate(A.the_geom, 11/12) 注意：此函数用于查找 POINT 和 LINE，另一方面定义 POINT 所在的位置

(e.) 我们将从 (d.) 中获取一个值，并使用它来更新 'the_geom 上的表 B ' 列，最初为 NULL。

(f.) 对表 B 中的每一行循环这组查询。

[PostGIS 描述] 这组查询的目的是通过计算一些数学结果来确定表 B 中的 the_geom，并将输出放入 ST_Interpolate 函数中，以获得表 B 中 POINT 所在位置的 the_geom。

感谢高级，我知道这是一个非常复杂的问题。我不介意您是否会使用太多查询。只是为了得到正确的值。

这些是在 danihp 帮助下的实际查询（最终）。

with CTE( max_kend) as (
 SELECT MAX(A.kend)
 FROM centerline A
),
r_b as (
 select B.ctrl_sec_no,B.gid, MAX(CTE.max_kend) - B.km as b
  FROM land_inventory B cross join CTE group by B.gid,B.ctrl_sec_no,B.km
),
r_a as (
    SELECT MAX(A.kend) - MIN(A.kstart) as a
    FROM centerline A
),
r_ratio as (
  select r_b.gid, r_b.b / r_a.a  as my_ratio
  from r_a cross join r_b
),
r_new_int as (
select B.gid,r_ratio.my_ratio,B.ctrl_sec_no,B.km,ST_AsText(ST_Envelope(ST_Collect(ST_line_interpolate_point(A.the_geom,  r_ratio.my_ratio )))) as new_int from centerline A, land_inventory B inner join r_ratio on B.gid = r_ratio.gid where A.ctrl_sec_no = B.ctrl_sec_no group by B.ctrl_sec_no,B.gid,r_ratio.my_ratio,B.km order by B.ctrl_sec_no
) 
UPDATE land_inventory
set land_inventory.the_geom = n.new_int
from r_new_int n
where 
  n.gid = land_inventory.gid and
  land_inventory.the_geom is NULL;

Answer 1

好吧，我们走吧。

(a)

SELECT MAX(A.kend) - MIN( A.kstart) as a
FROM Table A

(b)

已编辑 假设 gid 是表 B 的 PK ...

with CTE( max_kend, min_x) as (
 SELECT MAX(A.kend), NULL
 FROM TableA A
)
select B.gid, MAX(CTE.max_kend) - B.min_x as b
 FROM TableB B 
 cross join CTE

(c)

with CTE( max_kend, min_x) as (
 SELECT MAX(A.kend), NULL
 FROM TableA A
),
r_b as (
 select B.gid, MAX(CTE.max_kend) - B.min_x as b
  FROM TableB B 
  cross join CTE
),
r_a as (
    SELECT MAX(A.kend) - MIN( A.kstart) as a
    FROM Table A
)
select r_b.gid, r_a.a / r_b.b as my_ratio
from r_a cross join r_b

(d)

with CTE( max_kend, min_x) as (
 SELECT MAX(A.kend), NULL
 FROM TableA A
),
r_b as (
 select B.gid, MAX(CTE.max_kend) - B.min_x as b
  FROM TableB B 
  cross join CTE
),
r_a as (
    SELECT MAX(A.kend) - MIN( A.kstart) as a
    FROM Table A
),
r_ratio as (
  select r_b.gid, r_a.a / r_b.b as my_ratio
  from r_a cross join r_b
)
select ST_Interpolate(A.the_geom,  r_ratio.my_ratio )   
from TableB B 
inner join r_ratio on B.gid = r_ratio.gid

(e, f)

with CTE( max_kend, min_x) as (
 SELECT MAX(A.kend), NULL
 FROM TableA A
),
r_b as (
 select B.gid, MAX(CTE.max_kend) - B.min_x as b
  FROM TableB B 
  cross join CTE
),
r_a as (
    SELECT MAX(A.kend) - MIN( A.kstart) as a
    FROM Table A
),
r_ratio as (
  select r_b.gid, r_a.a / r_b.b as my_ratio
  from r_a cross join r_b
),
r_new_int as (
  select ST_Interpolate(A.the_geom,  r_ratio.my_ratio ) as new_int
  from TableB B 
  inner join r_ratio on B.gid = r_ratio.gid
)
UPDATE tableB
set tableB.the_geom = n.new_int
from r_new_int n
where 
  n.gid = tableB.gid and
  tableB.the_geom  is NULL

免责声明，而不是测试。

已编辑

with CTE( max_kend) as (
 SELECT MAX(A.kend)
 FROM centerline A
),
r_b as (
 select B.ctrl_sec_no,B.gid, MAX(CTE.max_kend) - B.km as b
  FROM land_inventory B cross join CTE group by B.gid,B.ctrl_sec_no,B.km
),
r_a as (
    SELECT MAX(A.kend) - MIN(A.kstart) as a
    FROM centerline A
),
r_ratio as (
  select r_b.gid, r_b.b / r_a.a  as my_ratio
  from r_a cross join r_b
),
r_new_int as (
  select     
    B.gid,
    r_ratio.my_ratio,
    B.ctrl_sec_no,B.km,
    ST_AsText(ST_Envelope(ST_Collect(
          ST_line_interpolate_point(A.the_geom,  r_ratio.my_ratio 
    )))) as new_int 
  from 
    centerline A inner join
    land_inventory B 
       on A.ctrl_sec_no = B.ctrl_sec_no 
       inner join 
    r_ratio on B.gid = r_ratio.gid 
  group by B.ctrl_sec_no,B.gid,r_ratio.my_ratio,B.km order by B.ctrl_sec_no
) 
UPDATE land_inventory
set the_geom = n.new_int
from r_new_int n
where 
  n.gid = land_inventory.gid and
  land_inventory.the_geom is NULL;