<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Peter -</p>
<p>(This comment has nothing to do with your original question;
however, the query caught my eye): <br>
</p>
<p>You query is probably not as efficient as it can be. If you read
the documentation for PostGIS about "ST_Expand":</p>
<p><span style="color: rgb(46, 46, 46); font-family: "Lucida
Grande", Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 14.4px; font-style: normal; font-variant-ligatures:
normal; font-variant-caps: normal; font-weight: 400;
letter-spacing: normal; orphans: 2; text-align: -webkit-left;
text-indent: 0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;
background-color: rgb(255, 255, 238); text-decoration-thickness:
initial; text-decoration-style: initial; text-decoration-color:
initial; display: inline !important; float: none;">-- quote --<br>
</span></p>
<p><span style="color: rgb(46, 46, 46); font-family: "Lucida
Grande", Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 14.4px; font-style: normal; font-variant-ligatures:
normal; font-variant-caps: normal; font-weight: 400;
letter-spacing: normal; orphans: 2; text-align: -webkit-left;
text-indent: 0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;
background-color: rgb(255, 255, 238); text-decoration-thickness:
initial; text-decoration-style: initial; text-decoration-color:
initial; display: inline !important; float: none;">Pre version
1.3, ST_Expand was used in conjunction with<span> </span></span><a
class="xref" href="https://postgis.net/docs/ST_Distance.html"
title="ST_Distance" style="text-decoration: none; color: rgb(85,
85, 221); font-family: "Lucida Grande", Verdana,
Geneva, Arial, Helvetica, sans-serif; font-size: 14.4px;
font-style: normal; font-variant-ligatures: normal;
font-variant-caps: normal; font-weight: 400; letter-spacing:
normal; orphans: 2; text-align: -webkit-left; text-indent: 0px;
text-transform: none; white-space: normal; widows: 2;
word-spacing: 0px; -webkit-text-stroke-width: 0px;
background-color: rgb(255, 255, 238);">ST_Distance</a><span
style="color: rgb(46, 46, 46); font-family: "Lucida
Grande", Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 14.4px; font-style: normal; font-variant-ligatures:
normal; font-variant-caps: normal; font-weight: 400;
letter-spacing: normal; orphans: 2; text-align: -webkit-left;
text-indent: 0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;
background-color: rgb(255, 255, 238); text-decoration-thickness:
initial; text-decoration-style: initial; text-decoration-color:
initial; display: inline !important; float: none;"><span> </span>to
do indexable distance queries. For example,<span> </span></span><code
class="code" style="font-size: 1.05em; font-family: Consolas,
Monaco, monospace; color: black; background-color: rgb(238, 238,
238); font-weight: bold; font-style: normal;
font-variant-ligatures: normal; font-variant-caps: normal;
letter-spacing: normal; orphans: 2; text-align: -webkit-left;
text-indent: 0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;
text-decoration-thickness: initial; text-decoration-style:
initial; text-decoration-color: initial;">geom &&
ST_Expand('POINT(10 20)', 10) AND ST_Distance(geom, 'POINT(10
20)') < 10</code><span style="color: rgb(46, 46, 46);
font-family: "Lucida Grande", Verdana, Geneva, Arial,
Helvetica, sans-serif; font-size: 14.4px; font-style: normal;
font-variant-ligatures: normal; font-variant-caps: normal;
font-weight: 400; letter-spacing: normal; orphans: 2;
text-align: -webkit-left; text-indent: 0px; text-transform:
none; white-space: normal; widows: 2; word-spacing: 0px;
-webkit-text-stroke-width: 0px; background-color: rgb(255, 255,
238); text-decoration-thickness: initial; text-decoration-style:
initial; text-decoration-color: initial; display: inline
!important; float: none;">. This has been replaced by the
simpler and more efficient<span> </span></span><a class="xref"
href="https://postgis.net/docs/ST_DWithin.html"
title="ST_DWithin" style="text-decoration: none; color: rgb(85,
85, 221); font-family: "Lucida Grande", Verdana,
Geneva, Arial, Helvetica, sans-serif; font-size: 14.4px;
font-style: normal; font-variant-ligatures: normal;
font-variant-caps: normal; font-weight: 400; letter-spacing:
normal; orphans: 2; text-align: -webkit-left; text-indent: 0px;
text-transform: none; white-space: normal; widows: 2;
word-spacing: 0px; -webkit-text-stroke-width: 0px;
background-color: rgb(255, 255, 238);">ST_DWithin</a><span
style="color: rgb(46, 46, 46); font-family: "Lucida
Grande", Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 14.4px; font-style: normal; font-variant-ligatures:
normal; font-variant-caps: normal; font-weight: 400;
letter-spacing: normal; orphans: 2; text-align: -webkit-left;
text-indent: 0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;
background-color: rgb(255, 255, 238); text-decoration-thickness:
initial; text-decoration-style: initial; text-decoration-color:
initial; display: inline !important; float: none;"><span> </span>function.</span></p>
<p><span style="color: rgb(46, 46, 46); font-family: "Lucida
Grande", Verdana, Geneva, Arial, Helvetica, sans-serif;
font-size: 14.4px; font-style: normal; font-variant-ligatures:
normal; font-variant-caps: normal; font-weight: 400;
letter-spacing: normal; orphans: 2; text-align: -webkit-left;
text-indent: 0px; text-transform: none; white-space: normal;
widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px;
background-color: rgb(255, 255, 238); text-decoration-thickness:
initial; text-decoration-style: initial; text-decoration-color:
initial; display: inline !important; float: none;">-- quote end
--</span></p>
<p>So if you're using a PostGIS later than 1.3 you could improve the
query by doing this:</p>
<pre class="moz-quote-pre" wrap=""><font color="#ff0000">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 <b>ST_DWithin(urban.geom, forest.geom, 1000.0)</b>
group by urban.id<b>, urban.geom</b>
;</font></pre>
<pre class="moz-signature" cols="72">Med venlig hilsen / Best regards
Bo Victor Thomsen</pre>
<div class="moz-cite-prefix">Den 17-10-2022 kl. 09:04 skrev Hans
Skov-Petersen via Qgis-user:<br>
</div>
<blockquote type="cite"
cite="mid:a96c18f4e0a44374a05d04f98c71c7a6@ign.ku.dk">
<pre class="moz-quote-pre" wrap="">Dear Michael
Functional dependencies are, according the Net, applied in PostgreSQL: <a class="moz-txt-link-freetext" href="https://stackoverflow.com/questions/66065987/postgres-sql-column-must-appear-in-the-group-by-clause-or-be-used-in-an-aggrega">https://stackoverflow.com/questions/66065987/postgres-sql-column-must-appear-in-the-group-by-clause-or-be-used-in-an-aggrega</a>
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 <a class="moz-txt-link-rfc2396E" href="mailto:qgis-user-bounces@lists.osgeo.org"><qgis-user-bounces@lists.osgeo.org></a> On Behalf Of Reetz, Michael (NLPV) via Qgis-user
Sent: 17. oktober 2022 08:47
To: <a class="moz-txt-link-abbreviated" href="mailto:qgis-user@lists.osgeo.org">qgis-user@lists.osgeo.org</a>
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 <a class="moz-txt-link-rfc2396E" href="mailto:qgis-user-bounces@lists.osgeo.org"><qgis-user-bounces@lists.osgeo.org></a> Im Auftrag von Hans Skov-Petersen via Qgis-user
Gesendet: Montag, 17. Oktober 2022 08:00
An: <a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
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
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a class="moz-txt-link-freetext" href="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">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</a>
Unsubscribe: <a class="moz-txt-link-freetext" href="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">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</a>
_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a class="moz-txt-link-freetext" href="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">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</a>
Unsubscribe: <a class="moz-txt-link-freetext" href="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">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</a>
_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a>
Unsubscribe: <a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/qgis-user">https://lists.osgeo.org/mailman/listinfo/qgis-user</a>
</pre>
</blockquote>
</body>
</html>