Meeting 2023-04-07 DB

Protocolled by: Nursultan

Attendees

  • Nursultan
  • Moritz
  • Benjamin
  • Thorben
  • Ramzan
  • Paul
  • Markus
  • Luciano
  • Yvonne
  • (everyone on the mailing list is welcomed)

Preparations

  1. given all the use cases, how should the tables for a map look like?
  2. think about how we do the plants hierarchy (that plants from lower ranks inherit attributes from higher ranks, e.g. the specific Tomato https://www.reinsaat.at/shop/DE/tomaten_paradeiser/cocktailtomate/anabelle/ should contain all the data the general Tomato https://permapeople.org/plants/solanum-lycopersicum-tomato has, but without duplicating this data, see also https://github.com/ElektraInitiative/PermaplanT/blob/master/doc/decisions/database_structure.md for current status

Please make a sketch or similar of the tables.

Furthermore, following individual tasks as preparation:

  • Moritz: focus on question 2. of above
  • Nursultan: results of feasibility (which data of canvas needs to be stored in DB etc.)
  • Benjamin: look at how we do the plants hierarchy (that plants from lower ranks inherit what higher ranks have)
  • Ramzan: Which PostGIS datatypes will we use for 2D/3D points? Focus on question 1. of above
  • Thorben: ER/SQL for users and maps
  • Paul: how to sync map data for offline use

Previous Tasks

  • Nursultan:
    • DB Design
    • feasibility study
      • undo in DB
      • canvas zoom
      • what coordinates we have in the canvas? e.g. zoom-in
      • differences between databases
    • permapeople.org https://github.com/ElektraInitiative/PermaplanT/issues/102
    • reinsaat.at https://github.com/ElektraInitiative/PermaplanT/issues/123
  • Ramzan:
    • guidelines
      • singular vs. plural
      • varchar vs. text
    • layers/alternatives/time support in DB (together with Nursultan)
    • continue researching/discovering PostGIS e.g. projection algorithms
  • Moritz:
    • connect plants with seeds (together with Paul)
    • handover of scraper tasks
  • Thorben:
    • create ER/SQL for users and maps tables (coordinate with Samuel)
  • Benjamin:
    • continue investigation in polyculture task

New Tasks

  • Yvonne: check if latin name is unique
  • Benjamin: plant hierachy, relationship
  • Moritz: latin_name rename
  • Nursultan: plant DB names i.e. scrapers
  • Paul:
  • Ramzan: rest DB
  • Thorben: rest DB

Agenda

  • 14:00 start
  • welcome ☺️
  • goals:
    • stable migrations
    • maintenance
    • performance
  • DB Design
  • scraper/new columns:
    • permapeople.org https://github.com/ElektraInitiative/PermaplanT/issues/102
    • reinsaat.at https://github.com/ElektraInitiative/PermaplanT/issues/123
  • which important decisions do we need to make?
    • plant relations: https://github.com/ElektraInitiative/PermaplanT/pull/89
    • undo
    • alternatives
    • singular vs. plural
    • ?
  • who wants to work on which task?

Notes

  • Thorben presented the map
    • re-check the requirements
  • coordinates in the database
    • can use different coordinate systems. but costly
    • kepler.gl can use lang/lat coordinate systems
    • LeafletJS
    • KonvaJS
  • cartesian vs lang/lat coordinates
  • balance between separate tables for each canvas object in the database vs. one table for the whole map as a stringified JSON
  • PostGIS can store raster information in the database
    • user can brush to draw raster areas