<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
span.E-MailFormatvorlage17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="DE" link="#0563C1" vlink="#954F72" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US">Hi all,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Any help or guidance would be very appreciated.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Best regards,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Johannes<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">P.S.: Here is the database schema as well as QGIS screenshots<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">------<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE EXTENSION postgis;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE TABLE ft_geometry (<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> _id bigserial NOT NULL PRIMARY KEY,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> name text, <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> position geometry(GEOMETRY,25832)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE TABLE ft_multi (<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> _id bigserial NOT NULL PRIMARY KEY,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> name text, <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> position_point geometry(MULTIPOINT,25832),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> position_line geometry(MULTILINESTRING,25832),<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"> position_polygon geometry(MULTIPOLYGON,25832)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE INDEX idx_ft_geometry_position ON ft_geometry USING GIST (position);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE INDEX idx_ft_multi_position_point ON ft_multi USING GIST (position_point);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE INDEX idx_ft_multi_position_line ON ft_multi USING GIST (position_line);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">CREATE INDEX idx_ft_multi_position_polygon ON ft_multi USING GIST (position_polygon);<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">------<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img width="447" height="524" style="width:4.6562in;height:5.4583in" id="Grafik_x0020_1" src="cid:image015.png@01D90337.3B7331B0"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">One can add ft_geometry via the Data Source Manager, by manually selecting the spatial type:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img width="944" height="409" style="width:9.8333in;height:4.2604in" id="Grafik_x0020_2" src="cid:image016.png@01D90337.3B7331B0"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img width="947" height="674" style="width:9.8645in;height:7.0208in" id="Grafik_x0020_3" src="cid:image017.png@01D90337.3B7331B0"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Another way to add ft_geometry is via the DB Manager; here is the view of ft_geometry in the DB Manager:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img width="1067" height="878" style="width:11.1145in;height:9.1458in" id="Grafik_x0020_4" src="cid:image018.png@01D90337.3B7331B0"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">And here is the view in the DB Manager for ft_multi (just in case):<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img width="1064" height="940" style="width:11.0833in;height:9.7916in" id="Grafik_x0020_5" src="cid:image019.png@01D90337.3B7331B0"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img width="369" height="358" style="width:3.8437in;height:3.7291in" id="Grafik_x0020_6" src="cid:image020.png@01D90337.3B7331B0"></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">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):<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><img width="384" height="181" style="width:4.0in;height:1.8854in" id="Grafik_x0020_7" src="cid:image021.png@01D90337.3B7331B0"></span><span lang="EN-US"><o:p></o:p></span></p>
</div>
</body>
</html>