How to calculate things from many tables using multiple queries?

Question

Note: this question is related to PostGIS and Postgresql and is implemented using PHP

Now I have table A:

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

note: the_geom is a geometric value (TYPE: LINE), in which case I read them randomly

And table B:

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

[PostGIS Description] These two tables are linked by ctrl_sec_no, which means that 3 continuous LINEs on ctrl_sec_no 120802 from table A are connected to one LINE and contain two POINTs from table B. We only know the distance {MAX (kend) - MIN (kstart)} of LINE and kilometer (x) where it is located on LINE.

The question is what is a PostgreSQL query ..

(a.) select the highest value from A.kend, minus the lowest value from A.kstart → 250 - 238 = 12

(b.) select the highest value from A.kend, minus the value of “x” in B → 250 - 239 = 11

(c.) calculate the ratio of these two values ​​((b.) / (a.)) → 11/12

(d.) using PostGIS: ST_Interpolate → ST_Interpolate (A.the_geom, 11/12) note: this function is used to find the POINT along with LINE, in the other hand, to determine the position at which the POINT point

(e.) we get the value from (d.) and use it for UPDATE Table B in the "the_geom" column, which is initially NULL.

(f.) encode this set of queries for each row in table B.

[PostGIS Description] The purpose of this set of queries is to determine the_geom in table B, calculate some math, and put the output in the ST_Interpolate function to get the_geom where the POINT is in table B.

Thanks, Advanced, I know this is a pretty tricky question. I don't mind if you use too many queries. To get the correct value.

This is the actual request (final) using 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

Ok, let it go.

(a)

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

(b)

EDITED Assuming 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 

(from)

 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 

(g)

 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.

EDITED

 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;