如何通过使用几个查询从许多表中计算事物? [英] How calculate things from many tables by using a few queries?
问题描述
注意:这个问题与PostGIS和Postgresql有关，并且是通过PHP实现的
note: this question is related to PostGIS and Postgresql and is implemented with 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)，在这种情况下，我将其随机化以提高可读性
note: the_geom is a geometry value (TYPE: LINE), in this case i random them for readability
表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条连续线连接到一条线中，并包含来自表B的两个POINT.我们仅知道距离{MAX(kend)-MIN(kstart)} LINE及其在LINE上的公里(x).
[PostGIS description] These two tables are related by ctrl_sec_no, which means 3 continuous LINEs on ctrl_sec_no 120802 from Table A, are connected into one LINE and contains two POINTs from Table B. We only know the distance {MAX(kend) - MIN(kstart)} of the LINE and the kilometer (x) where it is on the LINE.
问题是PostgreSQL的查询是什么.
The question is what is PostgreSQL's query to..
(a.)从A.kend中选择最高值，从A.kstart中选择最低值-> 250-238 = 12
(a.) select the highest value from A.kend, minus with the lowest value from A.kstart -> 250 - 238 = 12
(b.)从A.kend中选择最大值，减去B中的"x"值-> 250-239 = 11
(b.) select the highest value from A.kend, minus with 'x' value in B -> 250 - 239 = 11
(c.)从这两个值((b.)/(a.))-> 11/12
(c.) calculate ratio from these two value ((b.)/(a.)) -> 11/12
(d.)使用PostGIS:ST_Interpolate-> ST_Interpolate(A.the_geom，11/12) 注意:此功能用于与LINE一起找到POINT，另一方面定义POINT所在的位置
(d.) using PostGIS : ST_Interpolate -> ST_Interpolate(A.the_geom, 11/12) note: this function is used to find the POINT along with the LINE, in other hand to define a position where the POINT is
(e.)我们将从(d.)中获得一个值，并将其用于更新'the_geom'列上的表B，该列最初为NULL.
(e.) we will get a value from (d.) and use it to UPDATE Table B on 'the_geom' column, which is initially NULL.
(f.)对表B中的每一行循环这组查询.
(f.) loop this set of queries for every rows in Table B.
[PostGIS描述] 这组查询的目的是通过计算一些数学运算来确定表B中的the_geom并将输出放入ST_Interpolate函数中，以获取表B中POINT所在位置的the_geom.
[PostGIS Description] The purpose of this set of queries is to determine the_geom in Table B by calculate some math and put the output into a ST_Interpolate function to get the_geom of where the POINT in Table B is.
感谢Advanced，我知道这是一个安静而复杂的问题.我不介意您是否会使用太多查询.只是为了获得正确的值.
Thanks in Advanced, I know this is quiet complicated question. I don't mind if you will use too many queries. Just to get the correct value.
这些是在danihp的帮助下的实际查询(最终).
These are the actual query (final) with the help from 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;
推荐答案
好，放手.
(a)
SELECT MAX(A.kend) - MIN( A.kstart) as a
FROM Table A
(b)
已编辑，假设表B的gid为PK ...
EDITED Assuming that gid is PK for Table B ...
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
免责声明，而不是睾丸.
disclaimer, not testet.
已编辑
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;
这篇关于如何通过使用几个查询从许多表中计算事物?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
