Note
Go to the end to download the full example code.
Use CompositeType¶
Some functions return composite types. This example shows how to deal with this kind of functions.
8 import pytest
9 from packaging.version import parse as parse_version
10 from sqlalchemy import Column
11 from sqlalchemy import Float
12 from sqlalchemy import Integer
13 from sqlalchemy import MetaData
14 from sqlalchemy import __version__ as SA_VERSION
15 from sqlalchemy import select
16 from sqlalchemy.dialects.postgresql.psycopg2 import PGDialect_psycopg2
17 from sqlalchemy.orm import declarative_base
18
19 from geoalchemy2 import Raster
20 from geoalchemy2 import WKTElement
21 from geoalchemy2.functions import GenericFunction
22 from geoalchemy2.types import CompositeType
23
24 # Tests imports
25 from tests import test_only_with_dialects
26
27
28 class SummaryStatsCustomType(CompositeType):
29 """Define the composite type returned by the function ST_SummaryStatsAgg."""
30
31 typemap = {
32 "count": Integer,
33 "sum": Float,
34 "mean": Float,
35 "stddev": Float,
36 "min": Float,
37 "max": Float,
38 }
39
40 cache_ok = True
41
42
43 class ST_SummaryStatsAgg(GenericFunction):
44 type = SummaryStatsCustomType()
45 # Set a specific identifier to not override the actual ST_SummaryStatsAgg function
46 identifier = "ST_SummaryStatsAgg_custom"
47
48 inherit_cache = True
49
50
51 metadata = MetaData()
52 Base = declarative_base(metadata=metadata)
53
54
55 class Ocean(Base): # type: ignore
56 __tablename__ = "ocean"
57 id = Column(Integer, primary_key=True)
58 rast = Column(Raster)
59
60 def __init__(self, rast):
61 self.rast = rast
62
63
64 @test_only_with_dialects("postgresql")
65 class TestSTSummaryStatsAgg:
66 @pytest.mark.skipif(
67 parse_version(SA_VERSION) < parse_version("1.4"),
68 reason="requires SQLAlchemy>1.4",
69 )
70 def test_st_summary_stats_agg(self, session, conn):
71 metadata.drop_all(conn, checkfirst=True)
72 metadata.create_all(conn)
73
74 # Create a new raster
75 polygon = WKTElement("POLYGON((0 0,1 1,0 1,0 0))", srid=4326)
76 o = Ocean(polygon.ST_AsRaster(5, 6))
77 session.add(o)
78 session.flush()
79
80 # Define the query to compute stats
81 stats_agg = select(Ocean.rast.ST_SummaryStatsAgg_custom(1, True, 1).label("stats"))
82 stats_agg_alias = stats_agg.alias("stats_agg")
83
84 # Use these stats
85 query = select(
86 stats_agg_alias.c.stats.count.label("count"),
87 stats_agg_alias.c.stats.sum.label("sum"),
88 stats_agg_alias.c.stats.mean.label("mean"),
89 stats_agg_alias.c.stats.stddev.label("stddev"),
90 stats_agg_alias.c.stats.min.label("min"),
91 stats_agg_alias.c.stats.max.label("max"),
92 )
93
94 # Check the query, with the psycopg2 dialect, which does not add bind casts
95 assert str(query.compile(dialect=PGDialect_psycopg2())) == (
96 "SELECT "
97 "(stats_agg.stats).count AS count, "
98 "(stats_agg.stats).sum AS sum, "
99 "(stats_agg.stats).mean AS mean, "
100 "(stats_agg.stats).stddev AS stddev, "
101 "(stats_agg.stats).min AS min, "
102 "(stats_agg.stats).max AS max \n"
103 "FROM ("
104 "SELECT "
105 "ST_SummaryStatsAgg("
106 "ocean.rast, "
107 "%(ST_SummaryStatsAgg_1)s, %(ST_SummaryStatsAgg_2)s, %(ST_SummaryStatsAgg_3)s"
108 ") AS stats \n"
109 "FROM ocean) AS stats_agg"
110 )
111
112 # Execute the query
113 res = session.execute(query).fetchall()
114
115 # Check the result
116 assert res == [(15, 15.0, 1.0, 0.0, 1.0, 1.0)]