Support for tables with multiple geometry columns in PostGIS

[sorokine]

Description kart fails to import PostGIS tables that have more than one geometry column. Imported layers are unusable.

To Reproduce

1. Create a table with two or more geometry columns:

   
   create table geom_x2 (
   fid integer generated by default as identity primary key ,
   geom1 geometry(Point, 4326),
   geom2 geometry(Point, 4326)
   );

insert into geom_x2(geom1, geom2) VALUES (ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), ST_GeomFromText('POINT(-71.060316 48.432044)', 4326))

2. import table into kart:
```bash
kart import postgresql://user@host/db geom_x2

Expected behaviour Table to appear in the kart repository.

Output

Starting git-fast-import...
Importing 1 features from postgresql://user@host/db/geom_x2 to geom_x2/ ...
Added 1 Features to index in 0.0s
Overall rate: 81 features/s)
Closed in 0s
Updating networks-versioned.gpkg ...
Writing features for dataset 1 of 1: geom_x2
kart helper: unhandled exception
Traceback (most recent call last):
  File "kart/helper.py", line 260, in helper
  File "click/core.py", line 1157, in __call__
  File "click/core.py", line 1078, in main
  File "kart/cli_util.py", line 72, in invoke
  File "click/core.py", line 1688, in invoke
  File "click/core.py", line 1434, in invoke
  File "click/core.py", line 783, in invoke
  File "click/decorators.py", line 34, in new_func
  File "kart/import_.py", line 137, in import_
  File "click/core.py", line 804, in forward
  File "click/core.py", line 783, in invoke
  File "click/decorators.py", line 34, in new_func
  File "kart/tabular/import_.py", line 349, in table_import
  File "kart/working_copy.py", line 256, in reset_to_head
  File "kart/working_copy.py", line 309, in reset
  File "kart/tabular/working_copy/base.py", line 1307, in reset
  File "kart/tabular/working_copy/base.py", line 971, in write_full
  File "kart/tabular/working_copy/gpkg.py", line 232, in _write_meta
  File "kart/sqlalchemy/adapter/gpkg.py", line 146, in all_gpkg_meta_items
  File "kart/sqlalchemy/adapter/gpkg.py", line 263, in generate_gpkg_contents
  File "kart/crs_util.py", line 195, in get_identifier_int_from_dataset
ValueError: Dataset has more than one geometry column

> kart diff
The GPKG working copy appears to be out of sync with the repository:
  * The working copy's own records show it is tracking the empty tree;
  * Based on the repository it should be tracking tree 033d8c9ff558394aea148c678c8ae942c947e5b4.
The simplest fix is generally to recreate the working copy (losing any uncommitted changes in the process.)

Do you want to recreate the working copy? [y/N]:
Error: The GPKG working copy appears to be out of sync with the repository.

Version Info

• OS: MacOS 13.5.1 (22G90)
• Version:

  Kart v0.14.1, Copyright (c) Kart Contributors
  » GDAL v3.6.3; PROJ v9.2.0; PDAL v2.5.3
  » PyGit2 v1.12.1; Libgit2 v1.6.4; Git v2.38.1; Git LFS v3.3.0
  » SQLAlchemy v1.4.45; pysqlite3 v2.6.0/v3.40.1; SpatiaLite v5.0.1; Libpq v15.0.3

[rcoup]

@sorokine this is something we want to support eventually, but there's a few complications we need to resolve first: particularly with respect to format conversion/working copies (eg: GeoPackage/GeoJSON/Shapefile/FGDB don't support multiple geometry fields), and Kart's own spatial indexing.

We're accumulating use cases though, can you explain what you're using multiple geometries in a table for? Detail/overview? Polygons/Lines with associated point(s)? CRS?

[sorokine]

In this specific case I am using multiple columns for geometries for the same feature but sourced from different datasets. Multiple geometries are rarely needed but it happens sometimes, especially at data wrangling stage.

Other software (pgsql2shp, ogr2ogr) let user specify which geometry column to import. An option like kart table-import --geometry-column ... I think will cover 99%+ of all use cases.

[olsen232]

Just re-discovered this - apologies for very untimely reply. Although not as convenient, the same end result should be possible if you are able to create a view of the original table that selects only one of the geometry columns, and then use kart to import the view of the table.