Project

General

Profile

Feature #1631

Join database GIS tables

Added by Alexander Watzinger 4 months ago. Updated about 1 month 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

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

History

#1

Updated by Alexander Watzinger 4 months ago

  • Target version changed from 7.1.0 to 7.3.0
#2

Updated by Stefan Eichert 3 months 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

#3

Updated by Stefan Eichert 3 months ago

#4

Updated by Alexander Watzinger about 1 month ago

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

Updated by Alexander Watzinger about 1 month 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.

#6

Updated by Alexander Watzinger about 1 month ago

#7

Updated by Alexander Watzinger about 1 month ago

#8

Updated by Alexander Watzinger about 1 month 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.

#9

Updated by Stefan Eichert about 1 month 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

#10

Updated by Alexander Watzinger about 1 month ago

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

Also available in: Atom PDF