Unzip a PostGIS / PostgreSQL record using SQLAlchemy

Question

How to write the following query using SQLAlchemy ORM?

SELECT filename, (stats).* FROM ( SELECT filename, ST_SummaryStats(rast, 1, TRUE) AS stats FROM tiles ) AS stats_table; 

Here ST_SummaryStats is a PostGIS function that returns the record I want to unzip. tiles is a PostGIS table with filename and rast (raster) columns. My attempt is this:

 sub_q = db_session.query( Tiles.filename, func.ST_SummaryStats(Tiles.rast, 1, True).label('stats'), ).subquery() q = db_session.query( sub_q.columns.filename, sub_q.columns.stats, ) 

However, I do not know how to write an expression (stats).* - and therefore unpack the entry - using SQLAlchemy ORM. Therefore, stats seems to be a tuple.

Thanks in advance for your help.

Answer 1

ST_SummaryStats() returns a record , so instead of using SELECT as the expression (which will return record ), use it as a FROM and select the desired statistics in SELECT , so it becomes very simple:

 SELECT filename, count, sum, mean, stddev, min, max FROM tiles, ST_SummaryStats(tiles.rast, 1, true); 

This leads to the so-called LATERAL JOIN , and since ST_SummaryStats() returns only one row for the specified raster in tiles , you do not need a join condition, filter, or anything else.

I'm not sure about the ability of SQLAlchemy to use the result of a function as a class, but the surefire way to do this is to wrap the SELECT in VIEW above and then access the view from SQLAlchemy:

 CREATE VIEW raster_stats AS SELECT filename, count, sum, mean, stddev, min, max FROM tiles, ST_SummaryStats(tiles.rast, 1, true);