migrate priority datasets into database

[bbest]

Priority databases to migrate

• [ ] hydrographic
• [ ] larval fish
• [ ] zooplankton: only krill, or all?

[bbest]

Loading spatial data for vector tiles, starting with sanctuaries

To get this sanctuaries to load into tile.calcofi.io after loading the spatial features into the database:

• load features via R: https://github.com/CalCOFI/scripts/blob/28dcc8a9bb483b5d92b8c2eb3ec27f56eb0d34b0/load_sanctuaries.Rmd#L50-L61

• tile.calcofi.io

  

Ran the following SQL in DBeaver:

SELECT UpdateGeometrySRID('aoi_fed_sanctuaries','geom',4326);

after checking with:

SELECT
    nspname AS SCHEMA,
    relname AS TABLE,
    relkind, 
    typname, 
    attname AS geometry_column,
    postgis_typmod_srid (atttypmod) AS srid,
    postgis_typmod_type (atttypmod) AS geometry_type
FROM
    pg_class c
    JOIN pg_namespace n ON (c.relnamespace = n.oid)
    JOIN pg_attribute a ON (a.attrelid = c.oid)
    JOIN pg_type t ON (a.atttypid = t.oid)
WHERE
  relkind IN('r', 'v', 'm') AND 
  typname = 'geometry' AND 
  postgis_typmod_srid (atttypmod) != 0 AND
  relname = 'aoi_fed_sanctuaries';

References:

• pg_tileserv A very thin PostGIS-only tile server in Go. Takes in HTTP tile requests, executes SQL, returns MVT tiles.
• PostGIS: UpdateGeometrySRID