¿Cómo se calculan las cosas de muchas tablas usando algunas consultas?

nota: esta pregunta está relacionada con PostGIS y Postgresql y se implementa con PHP

Ahora tengo la Tabla A:

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

nota: the_geom es un valor de geometry (TYPE: LINE), en este caso los aleatorio para la legibilidad

Y la Tabla B:

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

[Descripción de PostGIS] Estas dos tablas están relacionadas por ctrl_sec_no, lo que significa que 3 LINEAS continuas en ctrl_sec_no 120802 de la Tabla A, están conectadas en una LÍNEA y contienen dos PUNTOS de la Tabla B. Solo conocemos la distancia {MAX (kend) – MIN ( kstart)} de LINE y el kilómetro (x) donde está en LINE.

La pregunta es para qué sirve la consulta de PostgreSQL.

(a.) select el valor más alto de A.kend, less con el valor más bajo de A.kstart -> 250 – 238 = 12

(b.) select el valor más alto de A.kend, less con 'x' en B -> 250 – 239 = 11

(c.) calcular la razón de estos dos valores ((b.) / (a.)) -> 11/12

(d.) usando PostGIS: ST_Interpolate -> ST_Interpolate (A.the_geom, 11/12) nota: esta function se usa para encontrar el PUNTO junto con la LÍNEA, en otra parte para definir una position donde el PUNTO es

(e) obtendremos un valor de (d.) y lo usaremos para ACTUALIZAR la Tabla B en la columna 'the_geom', que inicialmente es NULL.

(f.) itere este set de consultas para cada fila en la Tabla B.

[Descripción de PostGIS] El propósito de este set de consultas es determinar the_geom en la Tabla B mediante el cálculo de algunos cálculos matemáticos y el resultado en una function ST_Interpolate para get el_geom de donde está el PUNTO de la Tabla B.

Gracias en Advanced, sé que esta es una pregunta tranquila y complicada. No me importa si usará demasiadas consultas. Solo para get el valor correcto.

Estas son la consulta real (final) con la ayuda de 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; 

OK vamos.

(un)

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

(segundo)

EDITADO Asumiendo que gid es PK para la Tabla 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 

(do)

 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 

(re)

 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 

descargo de responsabilidad, no testet

EDITADO

 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;