Feature #1631
closed
Join database GIS tables
Description
- Join all 3 to one table
- Remove the GIS schema and add the new GIS table to the model schema:
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
Updated by Alexander Watzinger almost 3 years ago
- Target version changed from 7.1.0 to 7.3.0
Updated by Stefan Eichert almost 3 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
Updated by Stefan Eichert almost 3 years ago
- Related to Feature #1573: 3d geometries added
Updated by Alexander Watzinger over 2 years ago
- Status changed from Acknowledged to In Progress
- Assignee set to Alexander Watzinger
Updated by Alexander Watzinger over 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.
Updated by Alexander Watzinger over 2 years ago
- Related to deleted (Feature #1573: 3d geometries)
Updated by Alexander Watzinger over 2 years ago
- Precedes Feature #1573: 3d geometries added
Updated by Alexander Watzinger over 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.
Updated by Stefan Eichert over 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
Updated by Alexander Watzinger over 2 years ago
- Subject changed from Join GIS tables to Join database GIS tables
- Status changed from Resolved to Closed