Re: [Qgis-user] aggregate points within pre-defined grid squares

Micha Silver Sat, 18 Aug 2012 23:32:19 -0700

On 19/08/2012 03:43, PB wrote:

Hi - I have a large set of points (x,y,z) on a map. Overlayed on the map is a
grid that is currently a separate polygon shapefile layer (defined by
lat/lons).  What i'd like to do is aggregate the z values within each grid
square (sum, mean, count, etc).  I'm new-ish to GIS, and brand new to QGIS,
have searched the archive, and tried a number of vector tools and queries
but no joy.  I have also tried converting layers to rasters, but things
start getting weird quickly.


Any help would be great.  Many thanks!  Paul.

Sounds like a job for Spatialite. If you import both shapefiles into spatialite, then you can run a query to aggregate the z values, such as:

# Assuming two tables: 'points' with z values in column 'z'
# and the 'grid' table with primary key pk_uid.
# First add columns to the grid for sum, mean, count:
ALTER TABLE grid ADD COLUMN sum_pts INTEGER;
ALTER TABLE grid ADD COLUMN mean_pts INTEGER;
ALTER TABLE grid ADD COLUMN count_pts INTEGER;

# The aggregate query will be
SELECT SUM(p.z), AVG(p.z), COUNT(p.z)
FROM points AS p, grid AS g
WHERE ST_Within(p.Geometry, g.Geometry)
GROUP BY g.pk_uid;

# Now put that query into update statements to get the values into the grid table
UPDATE grid SET sum_pts=(
    SELECT SUM(p.z)
    FROM points AS p, grid AS g
    WHERE ST_Within(p.Geometry, g.Geometry) AND
    g.pk_uid=grid.pk_uid
);

UPDATE grid SET count_pts=(
    SELECT COUNT(p.z)
    FROM points AS p, grid AS g
    WHERE ST_Within(p.Geometry, g.Geometry) AND
    g.pk_uid=grid.pk_uid
);

UPDATE grid SET mean_pts=(
    SELECT AVG(p.z)
    FROM points AS p, grid AS g
    WHERE ST_Within(p.Geometry, g.Geometry) AND
    g.pk_uid=grid.pk_uid
);



--
View this message in context: http://osgeo-org.1560.n6.nabble.com/aggregate-points-within-pre-defined-grid-squares-tp4996130.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.
_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user

This mail was received via Mail-SeCure System.

-- 
Micha Silver
052-3665918

_______________________________________________
Qgis-user mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/qgis-user

Re: [Qgis-user] aggregate points within pre-defined grid squares

Reply via email to