postGIS 'dynamic' SQL queries

Question

I have a postGIS table:

id    value    geometry
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'
...

Now I have a web application where I want the user to see the intersection of the above geometries for any list of id, grouped by value.

For example, the user wants to see the intersection of geometries for the id list = [x,y,z].

So I want a result that looks like this:

value    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)

Can you write such a query if you don't know in advance the size of the list?

More generaly when trying to build dynamic sql queries from Python, do you have to concatenate strings like :

request = "select * from t where id=" + id + ";"

Or is there a cleaner way ?

Answer 1

Your Main problem here is that ST_Intersection() can only take 2 arguments. Maybe there is an array version of it but I Don't know about, maybe if someone already wrote it you can save yourself a lot of time. Have you searched for it?

If not no matter what implementation you choose you'll need a loop of some sort in order to:

1. Make a list of all the geometry you want to get the intersection
2. ST_Intersection 1rst geometry with the 2nd geometry
3. Store the running result
4. ST_Intersection the running result with the next geometry
5. Repeat 3&4 until there is no more n next geometry

---

So IMHO there are 2 roads to investigate.

1. You can write a PL/pgSQL function which allow dynamics query and all the loop you want. You then can use all existing object type of PostgeSQL/PostGis so that can really be easier than developing an external application. On top of that end user will stick to plain SQL and use you PL/pgSQL function as an "in-database API".

2. You might try to use a combination of PostgresSQL Window functions and Recursive CTE Query to reach you goal. Less programming, but more complexity of the resulting SQL. In theory with a clever trick you might totally avoid the need of dynamic query using that method.

Both way require spending some time over the PostgreSQL manual even if you are a power user. And I have no clue of which option would be the most performance friendly (though I might bet on PL/pgSQL).