Union Over Two Tables

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;