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.ny_counties
and 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 demo.nj_counties
were geom_nj
and the geometry in demo.ny_counties
were 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;