The "join" in your select makes it possible that the the primary key
urban.id won't be unique in the result set. You have to either
* Put the column geom in a agregating function (ex. min(), max() )
* Or put column geom into your group by clause
Med venlig hilsen / Best regards
Bo Victor Thomsen
Den 17-10-2022 kl. 09:37 skrev Reetz, Michael (NLPV) via Qgis-user:
Hello Hans,
I didn't know that you are working with PostgreSQL. Since I don't work with
PostgreSQL, I'm not familiar with the implementation of functional dependencies
there. Maybe the problem is that you are using subselects or that geom is part
of both tables. But this is just a guess, not knowledge.
Cheers,
Michael
-----Ursprüngliche Nachricht-----
Von: Hans Skov-Petersen<[email protected]>
Gesendet: Montag, 17. Oktober 2022 09:05
An: Reetz, Michael (NLPV)<[email protected]>
Cc:[email protected]
Betreff: RE: Functional dependency seem not working in group
Dear Michael
Functional dependencies are, according the Net, applied in
PostgreSQL:https://stackoverflow.com/questions/66065987/postgres-sql-column-must-appear-in-the-group-by-clause-or-be-used-in-an-aggrega
And accordingly, you shouldn't worry about it in PostGIS. The funny thing is
that sometime it works. Sometimes it doesn't.
Cheers
Hans
-----Original Message-----
From: Qgis-user<[email protected]> On Behalf Of Reetz, Michael
(NLPV) via Qgis-user
Sent: 17. oktober 2022 08:47
To:[email protected]
Subject: Re: [Qgis-user] Functional dependency seem not working in group
Hello Hans,
as far as I know, it is an SQL rule, that all selected attributes that are not
queried with an aggregate function have to be part of the group by clause. If
you run that query, your database server will check the syntax and throw that
error. I'm not sure if the SQL server realizes the 'functional dependency' in
your query as assumed by you.
Cheers,
Michael
-----Ursprüngliche Nachricht-----
Von: Qgis-user<[email protected]> Im Auftrag von Hans
Skov-Petersen via Qgis-user
Gesendet: Montag, 17. Oktober 2022 08:00
An:[email protected]
Betreff: [Qgis-user] Functional dependency seem not working in group
Dear Friends
I have, what appears to be a classic problem: When running a selection including a group
statement I get the good old 'ERROR: column "urban.geom" must appear in the
GROUP BY clause or be used in an aggregate function'.
I thought 'functional dependency' was in place to take care of that. The way I
understand it, is that functional dependency will, as applied in e.g. the group
statement, realize that e.g. the geom field is uniquely related to the id field
(which is referred in the group statement). Am I right in my assumption?
This is the ode causing the trouble:
drop table if exists stevns_test_urban_ped; create table stevns_test_urban_ped
as
select urban.id as id, urban.geom as geom, min(urban.pop2019) as pop2019,
count(forest) as ped, min(urban.pop2019)/(count(forest)+1)::float as
pop_forest_ped
from (select * from stevns_test_p where lu_type = 1) as urban
join (select * from stevns_test_p where lu_type = 2) as forest on
ST_Distance(urban.geom, forest.geom) < 1000 and forest.geom &&
(ST_Expand(urban.geom, 1000))
group by urban.id
;
Never mind the logics or meaning :-)
Thanks
Cheers
Hans
_______________________________________________
Qgis-user mailing list
[email protected]
List
info:https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=05%7C01%7Chsp%40ign.ku.dk%7C1eb7efbdf32e4c3e425908dab00c602f%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C638015864588678289%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=outXdPjG2JC0yvxdhzHRhHRtLv%2FOsq9%2B6%2FaxU4df1%2Bk%3D&reserved=0
Unsubscribe:https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=05%7C01%7Chsp%40ign.ku.dk%7C1eb7efbdf32e4c3e425908dab00c602f%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C638015864588678289%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=outXdPjG2JC0yvxdhzHRhHRtLv%2FOsq9%2B6%2FaxU4df1%2Bk%3D&reserved=0
_______________________________________________
Qgis-user mailing list
[email protected]
List
info:https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=05%7C01%7Chsp%40ign.ku.dk%7C1eb7efbdf32e4c3e425908dab00c602f%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C638015864588678289%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=outXdPjG2JC0yvxdhzHRhHRtLv%2FOsq9%2B6%2FaxU4df1%2Bk%3D&reserved=0
Unsubscribe:https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.osgeo.org%2Fmailman%2Flistinfo%2Fqgis-user&data=05%7C01%7Chsp%40ign.ku.dk%7C1eb7efbdf32e4c3e425908dab00c602f%7Ca3927f91cda14696af898c9f1ceffa91%7C0%7C0%7C638015864588678289%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=outXdPjG2JC0yvxdhzHRhHRtLv%2FOsq9%2B6%2FaxU4df1%2Bk%3D&reserved=0
_______________________________________________
Qgis-user mailing list
[email protected]
List info:https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user
_______________________________________________
Qgis-user mailing list
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
