spatial consideration: ST_INTERSECTION() vs ST_INTERSECTS()

[bbest]

Hi @geocoug,

To tabulate spatial data wrt area of interest (aoi) drawn, we're currently using ST_INTERSECTS(), which returns the entire features from the dataset, whereas ST_INTERSECTION() would return just the spatial intersection of aoi with the dataset.

This would be especially relevant to datasets in which we summarize data within based on area, like cetacean-pacific-summer Fin Whale, Pacific Summer Density;Humpback Whale, Pacific Summer Density;Sperm Whale, Pacific Summer Density;Blue Whale, Pacific Summer Density in spatial | marineenergy.app - Google Sheets.

functions.R: tabulate_dataset_shp_within_aoi():

if (!is.na(ds$summarize_sql)){
    x_df <- dbGetQuery(
      con,
      glue("
        with 
          tmp_selarea as (
            select ST_BUFFER({aoi_sql}, {ds$buffer_nm}) as geom ),
          tmp_aoi as (
            {ds$select_sql} as ds
            inner join tmp_selarea on ST_INTERSECTS(ds.geometry, tmp_selarea.geom) )
         {ds$summarize_sql}
         "))
  } else {
    x_sf <- st_read(
      con, query = glue("
        with 
          tmp_selarea as (
            select ST_BUFFER({aoi_sql}, {ds$buffer_nm} * 1852) as geom)
          {ds$select_sql} as ds
          inner join tmp_selarea on ST_INTERSECTS(ds.geometry, tmp_selarea.geom )
          "))

[bbest]

Would want an area-weighted avg.