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

[Even Rouault]

Johannes,

The issue is exactly that you checked "Don't resolve type of 
unrestricted columns (GEOMETRY)". If you uncheck it, the content of 
GEOMETRY columns will be scanned and you'll get one layer per found 
geometry type.

Even

Le 28/11/2022 à 14:39, Johannes Echterhoff via Qgis-user a écrit :
>
> 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:
>
> One can add ft_geometry via the Data Source Manager, by manually 
> selecting the spatial type:
>
> Another way to add ft_geometry is via the DB Manager; here is the view 
> of ft_geometry in the DB Manager:
>
> And here is the view in the DB Manager for ft_multi (just in case):
>
> 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:
>
> 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):
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info:https://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
http://www.spatialys.com
My software is free, but my time generally not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image015.png
Type: image/png
Size: 58739 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0007.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image016.png
Type: image/png
Size: 81103 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image017.png
Type: image/png
Size: 140948 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0009.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image018.png
Type: image/png
Size: 128430 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0010.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image019.png
Type: image/png
Size: 135573 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0011.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image020.png
Type: image/png
Size: 37135 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0012.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image021.png
Type: image/png
Size: 15175 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20221230/6a1d213e/attachment-0013.png>