Más

Consultas SQL 'dinámicas' postGIS

Consultas SQL 'dinámicas' postGIS


Tengo una tabla postGIS:

id valor geometría 0 10 'g_0_10' 0 20 'g_0_20' 1 10 'g_1_10' 1 20 'g_1_20' 2 10 'g_2_10' 2 20 'g_2_20'…

Ahora tengo una aplicación web en la que quiero que el usuario vea la intersección de las geometrías anteriores para cualquier lista de identificación, agrupada por valor.

Por ejemplo, el usuario quiere ver la intersección de geometrías para la lista de id = [x, y, z].

Entonces quiero un resultado que se vea así:

valor geoemtry 10 st_intersection (g_x_10, g_y_10, g_z_10) 20 st_intersection (g_x_20, g_y_20, g_z_20)

¿Puede escribir una consulta de este tipo si no conoce de antemano el tamaño de la lista?

Más generalmente, al intentar construir consultas SQL dinámicas desde Python, ¿tiene que concatenar cadenas como:

request = "seleccionar * de t donde id =" + id + ";"

¿O hay una forma más limpia?


Su principal problema aquí es que ST_Intersection () solo puede tomar 2 argumentos. Tal vez haya una versión de matriz, pero no sé, tal vez si alguien ya lo escribió, puede ahorrarse mucho tiempo. ¿Lo has buscado?

Si no, no importa qué implementación elija, necesitará un bucle de algún tipo para:

  1. Haga una lista de toda la geometría que desea obtener en la intersección
  2. ST_Intersection 1ra geometría con la 2da geometría
  3. Almacenar el resultado de la ejecución
  4. ST_Intersección del resultado de ejecución con la siguiente geometría
  5. Repita 3 y 4 hasta que no haya más n siguiente geometría

Así que en mi humilde opinión, hay 2 caminos para investigar.

  1. Puede escribir una función PL / pgSQL que permita consultas dinámicas y todo el bucle que desee. A continuación, puede utilizar todos los tipos de objetos existentes de PostgeSQL / PostGis para que sea realmente más fácil que desarrollar una aplicación externa. Además de eso, el usuario final se apegará a SQL simple y usará su función PL / pgSQL como una "API en la base de datos".

  2. Puede intentar utilizar una combinación de funciones de ventana de PostgresSQL y consulta CTE recursiva para alcanzar su objetivo. Menos programación, pero más complejidad del SQL resultante. En teoría, con un truco inteligente, podría evitar totalmente la necesidad de una consulta dinámica utilizando ese método.

Ambas formas requieren dedicar algo de tiempo al manual de PostgreSQL, incluso si eres un usuario avanzado. Y no tengo ni idea de qué opción sería la más amigable para el rendimiento (aunque podría apostar por PL / pgSQL).


Ver el vídeo: Postgres. PLSQL. Execute Dynamic SQL