Meeting 2023-04-27 DB

Protocolled by: Gabriel

Attendees

  • Luciano
  • Markus
  • Yvonne
  • (everyone on the mailing list is welcomed)

Could not come on short notice:

  • Moritz
  • Thorben
  • Ramzan

Preparations

  1. add yourself in https://nextcloud.markus-raab.org/nextcloud/index.php/apps/polls/vote/16
  2. add previous outcomes
  3. add questions for Luciano

Previous outcomes

  • We use KonvaJS as Canvas
  • Alterantives would be:
    • kepler.gl can use lang/lat coordinate systems
    • LeafletJS
  • Canvas has cartesian coordinate system
  • We use 3D geometry, z-value is 0 if no height given
  • The user draws a polygon to define the size of the map
  • coordinates in the database
  • can use different coordinate systems. but costly
  • PostGIS can store raster information in the database
    • user can brush to draw raster areas

Please add relevant outcomes (what we already know)

Questions

  • Which coordinate system do we use?
    • real-world longitude and latitude?
    • what if user does not want to give longitude and latitude?
    • how to performantly transform to cartesian coordinate system?
  • How to store coordinates in the database?
    • allow different coordinate systems? -> no, we use WGS84
    • migration of data, trivial with (startpoint, endpoint)
    • store both real-world and cartesian for performance? -> no, it is efficient enough
  • 5D mapping? How to efficiently select for time+alternatives?
  • geo referencing with ST_SetGeoReference? -> polygon for rastering maybe, it is only for rastering inside the database
  • Rastering for rain, sun, etc? -> we won't do, use polygons instead
  • Does the size of the map need to be static? -> expanding should be okay, warning for plants that are outside

Please add further questions to Luciano

Example query:

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES ('Point', 'POINT(0 0)'),
('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

SELECT name, ST_AsText(geom) FROM geometries;

Agenda

  • 13:00 start
  • protocol
  • welcome ☺️
  • maybe Moritz shows current status?
  • DB Design
  • answer/discuss questions
  • who wants to work on which task?

Tasks

  • Luciano: real-world example for (inserting) DB (for people that don't have/want to use real location), including transformation to cartesian coordinate system, boundary polygon (ST_SetGeoReference?), relationship of elements to boundary, PostGIS computation/algorithms for the use cases, smoothing of polygons

Notes

Currently we use cartesian coordinates with pixels representing x,y coordinates. Switch? Use lat,long. If users have privacy concerns (don't want to give their coords use 0,0 for lower left).

Coord Sys: WGS84 code: EPSG 4326

How to store coords in database? store real coords in database translate to the ones used by the frontend when querying? see tasks

Asking users for coords: Allow for inprecise input? How to change coords

Don't allow different coordinate systems

5D mapping: Luciano looks into it, prob just timestamp

Rastering requires some thinking