Project

General

Profile

Actions

Feature #1631

closed

Join database GIS tables

Added by Alexander Watzinger about 2 years ago. Updated almost 2 years ago.

Status:
Closed
Priority:
Normal
Category:
Backend
Target version:
Start date:
2022-01-20
Estimated time:

Description

Currently we are storing geometry data in 3 tables (linestring, point, polygon). We decided to:
  • Join all 3 to one table
  • Remove the GIS schema and add the new GIS table to the model schema:
Update
In the new version we dropped the gis schema and instead added a model.gis table with same fields except that instead having one geom field we now have:
  • geom_point
  • geom_linestring
  • geom_polygon

Related issues 1 (1 open0 closed)

Precedes OpenAtlas - Feature #1573: 3d geometriesAcknowledged2022-01-21Actions
Actions #1

Updated by Alexander Watzinger about 2 years ago

  • Target version changed from 7.1.0 to 7.3.0
Actions #2

Updated by Stefan Eichert about 2 years ago

Technically it is possible. For better performance I suggest, the geometry type should be stored in a separate column in order to avoid queries on all geometries if you only want to query e.g. linestrings and not a million of polygons.

https://gis.stackexchange.com/questions/340031/postgis-create-table-with-multiple-geometry
https://gis.stackexchange.com/questions/284304/storing-different-data-types-in-single-column-using-postgis

Actions #3

Updated by Stefan Eichert about 2 years ago

Actions #4

Updated by Alexander Watzinger almost 2 years ago

  • Status changed from Acknowledged to In Progress
  • Assignee set to Alexander Watzinger
Actions #5

Updated by Alexander Watzinger almost 2 years ago

I started working on it and second Stefan's suggestion to use multiple columns for different geoms (currently: point, linestring and polygon) but will also add a constraint to guarantee that (exactly) one of these columns has to have data in it.

Actions #6

Updated by Alexander Watzinger almost 2 years ago

Actions #7

Updated by Alexander Watzinger almost 2 years ago

Actions #8

Updated by Alexander Watzinger almost 2 years ago

  • Assignee changed from Alexander Watzinger to Stefan Eichert
  • Description updated (diff)
  • Status changed from In Progress to Resolved

I almost finished merging the GIS tables (branch feature_gis_join), see description for new database structure. It seemed to have been a good idea, besides cleaner code the performance improved a little too.

@ @Stefan Eichert: I would like to test it on THANADOS.
Bernhard is working there on API improvement in the develop branch anyway but I wanted to ask you before merging in develop too in case adaptions on your side (e.g. at the THANADOS database update scripts) are needed.
Could you please test it locally and/or give feedback about that. The GIS merge changes are in the feature_gis_join branch and the install/upgrade/7.3.0.sql is needed when updating.
Also I noticed that in the THANADOS database some gis rows had capitalized names in the "type" field, the update script will take care of these too.
Thanks.

Actions #9

Updated by Stefan Eichert almost 2 years ago

Alexander Watzinger wrote:

I almost finished merging the GIS tables (branch feature_gis_join), see description for new database structure. It seemed to have been a good idea, besides cleaner code the performance improved a little too.

@ @Stefan Eichert: I would like to test it on THANADOS.
Bernhard is working there on API improvement in the develop branch anyway but I wanted to ask you before merging in develop too in case adaptions on your side (e.g. at the THANADOS database update scripts) are needed.
Could you please test it locally and/or give feedback about that. The GIS merge changes are in the feature_gis_join branch and the install/upgrade/7.3.0.sql is needed when updating.
Also I noticed that in the THANADOS database some gis rows had capitalized names in the "type" field, the update script will take care of these too.
Thanks.

I managed to update the THANADOS code to work with the new table. Locally it seems to work fine. I will do some more extensive testing and get back to you

Actions #10

Updated by Alexander Watzinger almost 2 years ago

  • Subject changed from Join GIS tables to Join database GIS tables
  • Status changed from Resolved to Closed
Actions

Also available in: Atom PDF