Union Over Polygons

Often we need to take the union over polygons returned. An example would be merging all of the parcels that satisfy a criterion. Another is where polygons overlap and one only wants to display their unions. This could occur, for example, if one has flood regions over multiple floods and wants to display the area that had any flooding. DB2Vector makes this very easy to do.

An advantage of using DB2Vector for such unions is that the union is performed only when needed and only where it is needed. These are two items to looks at more closely:

1. When needed: Consider a large dataset, such as regions of the United States that received precipitation on each day. Suppose you want to display all regions that showed precipitation over the preceding seven days. If you precompute tiles to display areas that have received precipitation in the preceding seven days, you will have to run and store this computation daily and manage a smooth transition from one day to the next. DB2Vector runs the computation on demand when queried, so there is no updating stored tiles. The tiles are created on demand based on the query.
2. Where needed: The union over precipitation polygons for a week and conversion to tiles for the entire United States would be a very large computation to run. DB2Vector allows you to only run this computation where a viewer is looking. If the end user zooms in on a map of a certain region, the computation is run for that region on demand. This is fast and saves running and storing a huge output for the entire United States.

We've created a very simple example based on the counties of the states in New England. The query is simple, but shows the principle that can be used for much more difficult queries.

WITH boundingbox AS(
  SELECT
    ST_MakeEnvelope(
      %(xmin)s,
      %(ymin)s,
      %(xmax)s,
      %(ymax)s,
      3857
    ) AS geom
),
sourceTable AS (
  SELECT
    ST_UNION(geom) AS geom
  FROM
    demo.counties_us
  WHERE
    iso_3166_2 IN ('US-09', 'US-23', 'US-25', 'US-33', 'US-44', 'US-50')
),
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;