Often we want to make a map that displays data from two similar tables. We've created a simple example to demonstrate how to do this when using Map Tools. We consider two simple tables:
demo.nj_counties, which contain the counties in the US states of New York and New Jersey. We'd like to create a map that shows all the counties in both of these tables. This can be done by using the
UNION statement within the query, as we do in the example below.
The fields that one calls must have the same names. In our example, this allows us to use "*" which returns all fields. If, for example the geometry in in
geom_nj and the geometry in
geom_ny, we would write:
sourceTable AS ( SELECT geom_ny as geom FROM demo.ny_counties UNION SELECT geom_nj as geom FROM demo.nj_counties )
More generally, this example shows how PostgreSQL statements can be used within the DB2Vector tool.
WITH boundingbox AS( SELECT ST_MakeEnvelope( %(xmin)s, %(ymin)s, %(xmax)s, %(ymax)s, 3857 ) AS geom ), sourceTable AS ( SELECT * FROM demo.ny_counties UNION SELECT * FROM demo.nj_counties ), mvtgeom AS ( SELECT ST_AsMVTGeom( ST_Transform(sourceTable.geom, 3857), boundingbox.geom ) FROM sourceTable, boundingbox WHERE ST_Intersects( ST_Transform(boundingbox.geom, 4326), sourceTable.geom ) ) SELECT ST_AsMVT(mvtgeom.*) FROM mvtgeom;