[Qgis-user] QGIS behavior for DB table with single general geometry column vs table with multiple specific geometry columns

[Johannes Echterhoff]

Hi all,

This topic may have come up before, in which case I apologize up-front. I just want to make sure that I am not missing anything.

My question is about the behavior in QGIS for a database table, let's call it "ft_geometry", with a single, general geometry column (geometry type is GEOMETRY), compared to the behavior of a table "ft_multi" which has multiple columns with a specific geometry type (for example, one column with MULTIPOINT and one with MULTILINESTRING). When I connect to such a database - e.g., a PostgreSQL one - and open it in the project browser, then ft_geometry is not shown at all, while ft_multi is shown as many times as there are columns with specific geometry type.

QGIS allows adding ft_geometry manually, thereby choosing a specific geometry type for editing, via the Data Source Manager and via the DB Manager. I'll explain that using an example database schema and QGIS screenshots in more detail below, just for reference. However, what I am wondering is: Is it possible to somehow achieve, that, when opening the database in the project browser, ft_geometry is also shown with a list of specific geometry types (e.g., point and line)? Is there maybe a configuration option where one could set the desired specific geometry types, to be used in such a scenario, or a plugin? I've scanned the list of available plugins in QGIS 3.28.0, without success - but I may very well have missed something there. The background of why I'd like to have a certain list of specific geometry types is that in my use case, objects represented by tables like ft_geometry always have the same list of specific geometry types - and there can be many such tables in the database, which is why I am searching for a way to avoid manually adding such tables (each as many times as the specific geometry types that are needed).

Any help or guidance would be very appreciated.

Best regards,
Johannes

P.S.: Here is the database schema as well as QGIS screenshots

------

CREATE EXTENSION postgis;

CREATE TABLE ft_geometry (

   _id bigserial NOT NULL PRIMARY KEY,
   name text,
   position geometry(GEOMETRY,25832)
);

CREATE TABLE ft_multi (

   _id bigserial NOT NULL PRIMARY KEY,
   name text,
   position_point geometry(MULTIPOINT,25832),
   position_line geometry(MULTILINESTRING,25832),
   position_polygon geometry(MULTIPOLYGON,25832)
);

CREATE INDEX idx_ft_geometry_position ON ft_geometry USING GIST (position);
CREATE INDEX idx_ft_multi_position_point ON ft_multi USING GIST (position_point);
CREATE INDEX idx_ft_multi_position_line ON ft_multi USING GIST (position_line);
CREATE INDEX idx_ft_multi_position_polygon ON ft_multi USING GIST (position_polygon);

------

My test project, in which I've connected to the "test_geometry" database; table ft_geometry is not shown in the project browser ("Also list tables with no geometry" is enabled in the connection settings, as is "Don't resolve type of unrestricted columns (GEOMETRY)"); table ft_multi is shown three times:

[cid:image015.png at 01D90337.3B7331B0]

One can add ft_geometry via the Data Source Manager, by manually selecting the spatial type:

[cid:image016.png at 01D90337.3B7331B0]

[cid:image017.png at 01D90337.3B7331B0]

Another way to add ft_geometry is via the DB Manager; here is the view of ft_geometry in the DB Manager:

[cid:image018.png at 01D90337.3B7331B0]

And here is the view in the DB Manager for ft_multi (just in case):

[cid:image019.png at 01D90337.3B7331B0]

By selecting ft_geometry in the DB Manager view of database test_geometry, then right-clicking, the dialog with the option to "Add Layer (Advanced)..." opens:

[cid:image020.png at 01D90337.3B7331B0]

There, one can choose the specific geometry type - as well as CRS (which should be chosen to match the one defined in the column geometry definition):

[cid:image021.png at 01D90337.3B7331B0]
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image015.png
Type: image/png
Size: 58739 bytes
Desc: image015.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0007.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image016.png
Type: image/png
Size: 81103 bytes
Desc: image016.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image017.png
Type: image/png
Size: 140948 bytes
Desc: image017.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0009.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image018.png
Type: image/png
Size: 128430 bytes
Desc: image018.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0010.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image019.png
Type: image/png
Size: 135573 bytes
Desc: image019.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0011.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image020.png
Type: image/png
Size: 37135 bytes
Desc: image020.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0012.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image021.png
Type: image/png
Size: 15175 bytes
Desc: image021.png
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221128/82e96fcd/attachment-0013.png>