[Qgis-user] Using aggregates functions to count intersected features from another layer

Wed Oct 19 12:42:11 PDT 2016

 Hi all,
Thank you all for your answers. This is really a great community! I already
knew that but let me say it loudly. It also shows all the capabilities of
QGIS to solve a problem.

Nicolas, as Andreas said, I'm looking for something dynamic, live.

The virtual layer can indeed be an option despite the creation of an
intermediate but somehow live layer. Would be nice to have Andreas
questions documented in the user manual so that users are fully aware of
any potential limitations of the feature or its great power.

Another powerful feature I often neglect is the function editor. Maybe
because there are so many functions in the list that I end up thinking that
their combination would be enough. I think it's the way I will go as no
intermediate layer is involved and despite your remarks (which are not far
from Andreas questions about virtual layers). Thank you Germán for
reminding me.
That makes me wonder: we have in QGIS, ways to share processing scripts and
models, to share symbols, styles... What about something to share custom
expression functions? Plugins somehow already do that but I think there are
many people that have an operational code (that fills some small and common
needs like mine) but don't have time/skills to create the Gui around. Does
it sound reasonable/feasible? Maybe should I open a discussion in dev list
(or a ticket)?

Back to the approach I was following to solve the issue (combining default
functions in expression) may I understand that it was endless? No way to
group by when using aggregate function...?

Greetings,
Harrissou

Envoyé depuis mon HTC

----- Reply message -----
De : "Germán Carrillo" <carrillo.german at gmail.com>
Pour : "Neumann, Andreas" <a.neumann at carto.net>, "Harrissou 'DelazJ'" <
delazj at gmail.com>
Cc : "Nathan Woodrow" <madmanwoo at gmail.com>, "qgis-user" <
qgis-user at lists.osgeo.org>, "Nicolas Cadieux" <nicolas.cadieux at archeotec.ca>
Objet : [Qgis-user] Using aggregates functions to count intersected
features from another layer
Date : mer., oct. 19, 2016 18:04

Hi All,

Harrissou, have a look at [1] for a very similar question and one possible
solution using the Function Editor (of course, instead of sum you would use
count).

Do you other devs find that approach useful? Any hint for keeping the index
up-to-date?

Is there any interest in having this kind of functions available for QGIS
expressions? I could help with that.

Regards,

Germán
--
[1] http://gis.stackexchange.com/a/212762/4972

2016-10-19 7:42 GMT-05:00 Neumann, Andreas <a.neumann at carto.net>:

> Good idea to use a virtual layer for that! It is probably also more
> efficient than a QGIS expression.
>
> Just to understand the virtual layers correctly:
>
> when is the query behind the virtual layer executed?
>
> - at every redraw?
>
> - once at creation?
>
> - at project reload?
>
> Aren't the virtual layers automatically updated, at least when the project
> reloads?
>
> Are the results cached or not?
>
> Would also make sense to add a "update" option for a virtual layer, if
> that doesn't exist yet.
>
> Greetings,
>
> Andreas
>
>
>
> On 2016-10-19 14:32, Nathan Woodrow wrote:
>
> Maybe using a virtual layer query via DB Manager will work.  You have full
> SQL join support. Speed isn't always the best but gives you the full power
> of SQL which is the correct way to do this IMO.
>
> E.g SELECT LayerA.id, COUNT(*) FROM layerA
> JOIN layerB ON ST_WithIn(layerA.geometry, layerB.geometry)
> GROUP BY layerA.type
>
> Although you can't do an update but you can make a new layer with the new
> counts.
>
> - Nathan
>
> On Wed, Oct 19, 2016 at 10:22 PM, Neumann, Andreas <a.neumann at carto.net>
> wrote:
>
>> Well yes - but that is not a "live" point in polygon. It create separate
>> layers which need to be kept up-to-date.
>>
>> Harrissou was after a "live" point in polygon, which is automatically
>> kept up-to-date through a QGIS expression.
>>
>> Maybe Nyall knows how to write the correct aggregate expression for that?
>>
>> Greetings,
>>
>> Andreas
>>
>> On 2016-10-19 14:11, Nicolas Cadieux wrote:
>>
>>
>> Try this.
>>
>> http://www.qgistutorials.com/fr/docs/points_in_polygon.html
>>
>> Nicolas
>>
>> Le 19 oct. 2016 à 06:37, DelazJ [via OSGeo.org] <[hidden email]> a
>> écrit :
>>
>> Hi,
>>
>> I have a polygon layer and a point one.
>> I'd like to calculate for each polygon feature, the number of points that
>> it covers. I thought the aggregates functions could help me find an easier
>> and straight expression to perform this (without creating intermediate
>> layers) but I fail to find any correct syntax.
>>
>> I tried aggregate ('mypoint', 'count', "id", intersects ($geometry,
>> geometry($currentfeature)))
>>
>>  And many variants of the fourth option but It always return the total of
>> points or an error. Actually this option is a filter and I wonder if i'm
>> not missing a "group by" option in the aggregate function (which seems to
>> be the appropriate one in my case). Is it possible?
>> The work around I found is to populate an ad'hoc field in the point layer
>> with overlapping polygon id, thanks to the SpatialJoin plugin, then I
>> create a one-to-many relation in the project between the two layers in
>> other to use relation_aggregate function.
>> But I expected something more direct.
>>
>> Any hint, please?
>>
>> Thanks,
>> Harrissou
>>
>> _______________________________________________
>> Qgis-user mailing list
>> [hidden email]
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>> ------------------------------
>> If you reply to this email, your message will be added to the discussion
>> below:
>> http://osgeo-org.1560.x6.nabble.com/Using-aggregates-functio
>> ns-to-count-intersected-features-from-another-layer-tp5291420.html
>> To start a new topic under Quantum GIS - User, email [hidden email]
>> To unsubscribe from Quantum GIS - User, click here.
>> NAML
>> <http://osgeo-org.1560.x6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>
>>
>> ------------------------------
>> View this message in context: Re: Using aggregates functions to count
>> intersected features from another layer
>> <http://osgeo-org.1560.x6.nabble.com/Using-aggregates-functions-to-count-intersected-features-from-another-layer-tp5291420p5291443.html>
>> Sent from the Quantum GIS - User mailing list archive
>> <http://osgeo-org.1560.x6.nabble.com/Quantum-GIS-User-f4125267.html> at
>> Nabble.com.
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>>
>>
>>
>> _______________________________________________
>> Qgis-user mailing list
>> Qgis-user at lists.osgeo.org
>> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>

-- 
-----------
   |\__
(:>__)(
   |/
Soluciones Geoinformáticas Libres
http://geotux.tuxfamily.org/
http://twitter.com/GeoTux2
http://about.me/germancarrillo

<http://gis.stackexchange.com/users/4972/germ%c3%a1n-carrillo>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20161019/09445831/attachment.html>