[SOLVED] Summing values from intersecting points and save the result in a polygon column

Summing values from intersecting points and save the result in a polygon column

Using this link I could count all points intersecting polygons:

ogrinfo -sql "select count(*) from tmppol, tmppoi where st_intersects(tmppol.geometry,tmppoi.geometry) group by tmppol.fid" -dialect sqlite input.vrt

My doubt is how it can be modified to get the sum of all point values and put the result in a column of the polygon shapefile? Perhaps as an update call such as:

ogrinfo tmppol.shp -dialect sqlite -sql "update tmppol set sum = sum(tmpoi.tn) from tmppol where st_intersects(tmppol.geom, tmppoi.geom) ???"

Any hints are much appreciated,

MWE files can be downloaded from here.

Solution

calculate the sum of point values for each polygon

ogrinfo -sql "SELECT tmppol.fid, SUM(tmppoi.value) as point_sum FROM tmppol, tmppoi WHERE ST_Intersects(tmppol.geometry, tmppoi.geometry) GROUP BY tmppol.fid" -dialect sqlite input.vrt

create a file called input.vrt

<OGRVRTDataSource>
    <OGRVRTLayer name="tmppol">
        <SrcDataSource>tmppol.shp</SrcDataSource>
        <GeometryType>wkbPolygon</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
    </OGRVRTLayer>
    <OGRVRTLayer name="tmppoi">
        <SrcDataSource>tmppoi.shp</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
    </OGRVRTLayer>
</OGRVRTDataSource>

use:

ogr2ogr -f "ESRI Shapefile" tmppol.shp -dialect sqlite -sql "UPDATE tmppol SET sum = (SELECT SUM(tmppoi.value) FROM tmppoi WHERE ST_Intersects(tmppol.geometry, tmppoi.geometry))" input.vrt