Calculating point layer values aggregated by sub-polygons in QGIS 2
Clash Royale CLAN TAG#URR8PPP
up vote
2
down vote
favorite
I simply have
- a polygon layer
BereichBerechnung
with a field"Are_Number"
- a point layer
EW2017
with a field"EWjeAdr"
In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr"
for points that are within each sub-polygon from the layer BereichBerechnung
.
I have found a related thread on this topic: STIntersects - Update field to give me count of points in polygon. Nevertheless, cannot figure it out how to adjust my expression properly, any suggestions?
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON BereichBerechnung.ogr_geometry.STContains(EW2017.ogr_geometry) = 1;
GROUP BY Are_Number
I am getting the following error
Any other smart suggestions/hints on how to aggregate values of a point layer based on polygons, e.g. IDs
, should exclude application of Select Within
plugin, Select by Location
, Select Feature(s)
, Join attributes by location (summary)
etc.
sql qgis-2.0 select-by-location aggregation virtual-layer
add a comment |
up vote
2
down vote
favorite
I simply have
- a polygon layer
BereichBerechnung
with a field"Are_Number"
- a point layer
EW2017
with a field"EWjeAdr"
In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr"
for points that are within each sub-polygon from the layer BereichBerechnung
.
I have found a related thread on this topic: STIntersects - Update field to give me count of points in polygon. Nevertheless, cannot figure it out how to adjust my expression properly, any suggestions?
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON BereichBerechnung.ogr_geometry.STContains(EW2017.ogr_geometry) = 1;
GROUP BY Are_Number
I am getting the following error
Any other smart suggestions/hints on how to aggregate values of a point layer based on polygons, e.g. IDs
, should exclude application of Select Within
plugin, Select by Location
, Select Feature(s)
, Join attributes by location (summary)
etc.
sql qgis-2.0 select-by-location aggregation virtual-layer
Do you have to do this in SQL?
– TeddyTedTed
Dec 6 at 9:00
If you can suggest any other solution it would be great as well
– Taras
Dec 6 at 9:22
This source says.STContains ( other_geometry )
– Taras
Dec 6 at 10:19
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I simply have
- a polygon layer
BereichBerechnung
with a field"Are_Number"
- a point layer
EW2017
with a field"EWjeAdr"
In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr"
for points that are within each sub-polygon from the layer BereichBerechnung
.
I have found a related thread on this topic: STIntersects - Update field to give me count of points in polygon. Nevertheless, cannot figure it out how to adjust my expression properly, any suggestions?
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON BereichBerechnung.ogr_geometry.STContains(EW2017.ogr_geometry) = 1;
GROUP BY Are_Number
I am getting the following error
Any other smart suggestions/hints on how to aggregate values of a point layer based on polygons, e.g. IDs
, should exclude application of Select Within
plugin, Select by Location
, Select Feature(s)
, Join attributes by location (summary)
etc.
sql qgis-2.0 select-by-location aggregation virtual-layer
I simply have
- a polygon layer
BereichBerechnung
with a field"Are_Number"
- a point layer
EW2017
with a field"EWjeAdr"
In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr"
for points that are within each sub-polygon from the layer BereichBerechnung
.
I have found a related thread on this topic: STIntersects - Update field to give me count of points in polygon. Nevertheless, cannot figure it out how to adjust my expression properly, any suggestions?
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON BereichBerechnung.ogr_geometry.STContains(EW2017.ogr_geometry) = 1;
GROUP BY Are_Number
I am getting the following error
Any other smart suggestions/hints on how to aggregate values of a point layer based on polygons, e.g. IDs
, should exclude application of Select Within
plugin, Select by Location
, Select Feature(s)
, Join attributes by location (summary)
etc.
sql qgis-2.0 select-by-location aggregation virtual-layer
sql qgis-2.0 select-by-location aggregation virtual-layer
edited Dec 6 at 21:15
PolyGeo♦
53k1779238
53k1779238
asked Dec 6 at 7:35
Taras
1,8962622
1,8962622
Do you have to do this in SQL?
– TeddyTedTed
Dec 6 at 9:00
If you can suggest any other solution it would be great as well
– Taras
Dec 6 at 9:22
This source says.STContains ( other_geometry )
– Taras
Dec 6 at 10:19
add a comment |
Do you have to do this in SQL?
– TeddyTedTed
Dec 6 at 9:00
If you can suggest any other solution it would be great as well
– Taras
Dec 6 at 9:22
This source says.STContains ( other_geometry )
– Taras
Dec 6 at 10:19
Do you have to do this in SQL?
– TeddyTedTed
Dec 6 at 9:00
Do you have to do this in SQL?
– TeddyTedTed
Dec 6 at 9:00
If you can suggest any other solution it would be great as well
– Taras
Dec 6 at 9:22
If you can suggest any other solution it would be great as well
– Taras
Dec 6 at 9:22
This source says
.STContains ( other_geometry )
– Taras
Dec 6 at 10:19
This source says
.STContains ( other_geometry )
– Taras
Dec 6 at 10:19
add a comment |
2 Answers
2
active
oldest
votes
up vote
1
down vote
For QGIS 3.4
In the Processing Toolbox use the tool Join attributes by location (summary)
, make sure it's the summary tool and not the other one.
For QGIS 2.18
In the Processing Toolbox use the tool Join attributes by location
and change the Attribute summary
option to Take summary of intersecting features
.
The Input layer should be the polygon and the Join Layer should be the points.
I created two scratch layers in a project, in the point layer I populated a field with a random number ("rand_num"
), see the result of the joined polygon here:
Are you using QGIS 3 and higher? In QGIS 2.18, there are noJoin attributes by location (summary)
. Thank you for the answer anyway.
– Taras
Dec 6 at 10:08
Ah sorry yes I am using 3.4. Apologies. Instead use the 'Join attributes by location' tool and change the attribute summary option to 'Take summary of intersecting features'. Edited answer to reflect this. Looks like they split it into two tools in QGIS 3
– TeddyTedTed
Dec 6 at 10:41
add a comment |
up vote
1
down vote
accepted
With a bit of luck and suggestions from @Kazuhito, I ended up with
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON contains(BereichBerechnung.geometry, EW_Data.geometry)
GROUP BY Are_Number
References:
- QGIS Documentation | Supported language
- SpatiaLite SQL functions reference list
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f305165%2fcalculating-point-layer-values-aggregated-by-sub-polygons-in-qgis-2%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
For QGIS 3.4
In the Processing Toolbox use the tool Join attributes by location (summary)
, make sure it's the summary tool and not the other one.
For QGIS 2.18
In the Processing Toolbox use the tool Join attributes by location
and change the Attribute summary
option to Take summary of intersecting features
.
The Input layer should be the polygon and the Join Layer should be the points.
I created two scratch layers in a project, in the point layer I populated a field with a random number ("rand_num"
), see the result of the joined polygon here:
Are you using QGIS 3 and higher? In QGIS 2.18, there are noJoin attributes by location (summary)
. Thank you for the answer anyway.
– Taras
Dec 6 at 10:08
Ah sorry yes I am using 3.4. Apologies. Instead use the 'Join attributes by location' tool and change the attribute summary option to 'Take summary of intersecting features'. Edited answer to reflect this. Looks like they split it into two tools in QGIS 3
– TeddyTedTed
Dec 6 at 10:41
add a comment |
up vote
1
down vote
For QGIS 3.4
In the Processing Toolbox use the tool Join attributes by location (summary)
, make sure it's the summary tool and not the other one.
For QGIS 2.18
In the Processing Toolbox use the tool Join attributes by location
and change the Attribute summary
option to Take summary of intersecting features
.
The Input layer should be the polygon and the Join Layer should be the points.
I created two scratch layers in a project, in the point layer I populated a field with a random number ("rand_num"
), see the result of the joined polygon here:
Are you using QGIS 3 and higher? In QGIS 2.18, there are noJoin attributes by location (summary)
. Thank you for the answer anyway.
– Taras
Dec 6 at 10:08
Ah sorry yes I am using 3.4. Apologies. Instead use the 'Join attributes by location' tool and change the attribute summary option to 'Take summary of intersecting features'. Edited answer to reflect this. Looks like they split it into two tools in QGIS 3
– TeddyTedTed
Dec 6 at 10:41
add a comment |
up vote
1
down vote
up vote
1
down vote
For QGIS 3.4
In the Processing Toolbox use the tool Join attributes by location (summary)
, make sure it's the summary tool and not the other one.
For QGIS 2.18
In the Processing Toolbox use the tool Join attributes by location
and change the Attribute summary
option to Take summary of intersecting features
.
The Input layer should be the polygon and the Join Layer should be the points.
I created two scratch layers in a project, in the point layer I populated a field with a random number ("rand_num"
), see the result of the joined polygon here:
For QGIS 3.4
In the Processing Toolbox use the tool Join attributes by location (summary)
, make sure it's the summary tool and not the other one.
For QGIS 2.18
In the Processing Toolbox use the tool Join attributes by location
and change the Attribute summary
option to Take summary of intersecting features
.
The Input layer should be the polygon and the Join Layer should be the points.
I created two scratch layers in a project, in the point layer I populated a field with a random number ("rand_num"
), see the result of the joined polygon here:
edited Dec 6 at 11:51
Taras
1,8962622
1,8962622
answered Dec 6 at 9:49
TeddyTedTed
47819
47819
Are you using QGIS 3 and higher? In QGIS 2.18, there are noJoin attributes by location (summary)
. Thank you for the answer anyway.
– Taras
Dec 6 at 10:08
Ah sorry yes I am using 3.4. Apologies. Instead use the 'Join attributes by location' tool and change the attribute summary option to 'Take summary of intersecting features'. Edited answer to reflect this. Looks like they split it into two tools in QGIS 3
– TeddyTedTed
Dec 6 at 10:41
add a comment |
Are you using QGIS 3 and higher? In QGIS 2.18, there are noJoin attributes by location (summary)
. Thank you for the answer anyway.
– Taras
Dec 6 at 10:08
Ah sorry yes I am using 3.4. Apologies. Instead use the 'Join attributes by location' tool and change the attribute summary option to 'Take summary of intersecting features'. Edited answer to reflect this. Looks like they split it into two tools in QGIS 3
– TeddyTedTed
Dec 6 at 10:41
Are you using QGIS 3 and higher? In QGIS 2.18, there are no
Join attributes by location (summary)
. Thank you for the answer anyway.– Taras
Dec 6 at 10:08
Are you using QGIS 3 and higher? In QGIS 2.18, there are no
Join attributes by location (summary)
. Thank you for the answer anyway.– Taras
Dec 6 at 10:08
Ah sorry yes I am using 3.4. Apologies. Instead use the 'Join attributes by location' tool and change the attribute summary option to 'Take summary of intersecting features'. Edited answer to reflect this. Looks like they split it into two tools in QGIS 3
– TeddyTedTed
Dec 6 at 10:41
Ah sorry yes I am using 3.4. Apologies. Instead use the 'Join attributes by location' tool and change the attribute summary option to 'Take summary of intersecting features'. Edited answer to reflect this. Looks like they split it into two tools in QGIS 3
– TeddyTedTed
Dec 6 at 10:41
add a comment |
up vote
1
down vote
accepted
With a bit of luck and suggestions from @Kazuhito, I ended up with
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON contains(BereichBerechnung.geometry, EW_Data.geometry)
GROUP BY Are_Number
References:
- QGIS Documentation | Supported language
- SpatiaLite SQL functions reference list
add a comment |
up vote
1
down vote
accepted
With a bit of luck and suggestions from @Kazuhito, I ended up with
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON contains(BereichBerechnung.geometry, EW_Data.geometry)
GROUP BY Are_Number
References:
- QGIS Documentation | Supported language
- SpatiaLite SQL functions reference list
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
With a bit of luck and suggestions from @Kazuhito, I ended up with
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON contains(BereichBerechnung.geometry, EW_Data.geometry)
GROUP BY Are_Number
References:
- QGIS Documentation | Supported language
- SpatiaLite SQL functions reference list
With a bit of luck and suggestions from @Kazuhito, I ended up with
SELECT Are_Number, SUM(EWjeAdr)
FROM BereichBerechnung
JOIN EW_Data
ON contains(BereichBerechnung.geometry, EW_Data.geometry)
GROUP BY Are_Number
References:
- QGIS Documentation | Supported language
- SpatiaLite SQL functions reference list
edited Dec 10 at 5:59
answered Dec 6 at 11:23
Taras
1,8962622
1,8962622
add a comment |
add a comment |
Thanks for contributing an answer to Geographic Information Systems Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f305165%2fcalculating-point-layer-values-aggregated-by-sub-polygons-in-qgis-2%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Do you have to do this in SQL?
– TeddyTedTed
Dec 6 at 9:00
If you can suggest any other solution it would be great as well
– Taras
Dec 6 at 9:22
This source says
.STContains ( other_geometry )
– Taras
Dec 6 at 10:19