Database Schema for Spatial Clustering

Each region record contains the geographical area and centroid of the region, and is assigned to zero, one, or many map zoom levels via records in the region_zoom table. The tree_region table is populated by matching a the location of a given tree with all regions in a given zoom level, and then selecting the highest priority region from that set. This process is carried out for all trees at each zoom level.

active_tree_region is a materialized view that is used as an optimization. The current implementation inserts a record for each tree at each zoom level. This occurs either during the recalculation of matching regions for all trees in a zoom level, such as when new regions are loaded for that zoom level, or for a single tree when that tree first enters the system. Cluster totals reported to the web map are currently computed by totaling all records in active_tree_region that match regions currently within the bounds of and assigned to the current zoom level of the web map.

SQL

active_tree_region Materialized View

  SELECT tree_region.id,
     region.id AS region_id,
     region.centroid,
     box2d(region.geom) AS bounding_box,
     region.type_id,
     tree_region.zoom_level
    FROM tree_region
      JOIN trees ON trees.id = tree_region.tree_id
      JOIN region ON region.id = tree_region.region_id
   WHERE trees.active = true;