optimize Spatial

[bbest]

• [ ] Ingest: shp to db
  • run existing select_sql in spatial | marineenergy.app - Google Sheets to generate new dedicated tables per dataset. For example fish-species does a UNION on 162 tables.
  • ∆ update_spatial() in scripts/update.R
  • To Test: Does performing this ST_Intersects() on an ST_Union of the dataset table (generated at ingest) speed up this query?
• [ ] App: ST_Intersects(), not ST_Intersection()
  • only need to perform full intersection in Report; App just needs to show datasets after filtering by Location
  • To Test: Does performing this ST_Intersects() on an ST_Union of the dataset table (generated at ingest) speed up this query?
• [ ] Report: ST_Intersection() FROM ST_Intersects()
  • See PostGIS — Getting intersections the faster way, so perform ST_Intersection() only on features from ST_Intersects()