Count points in polygon
Q: How can I count the number of points which fall into polygons?
A: Carry over an attribute from the polygons to the points, then count the number of equal attributes by SQL grouping.
Example: How many archaeological sites does each parcel owner have?
# Spearfish area # (Note: DBF driver unsupported, please use SQLite, MySQL or PostgreSQL) g.copy vect=archsites,myarchsites v.db.addcol myarchsites col="owner varchar(25)"
We now transfer the owner names to the archaeological sites (points):
v.what.vect myarchsites qvect=fields column=owner qcolumn=label # verification: v.db.select myarchsites d.mon x0 d.vect -c fields d.vect myarchsites icon=basic/diamond size=10 # test run echo "SELECT *,count(owner) FROM myarchsites GROUP BY owner" | db.select # upload to polygons map g.copy vect=fields,myfields v.db.addcol myfields col="numpoints integer" v.db.update myfields column=numpoints value="(SELECT count(owner) FROM myarchsites WHERE \ myarchsites.owner=myfields.label GROUP BY owner);" # verification: v.db.select myfields
The resulting table contains the number of points falling into each polygon.
Caveat: The above approach counts attributes and not features. This means that if there is more than one category linking a feature to rows in the table you will count more categories than there are features. It is also possible that many features link to one category and in this case you will under count the number of features. The first scenario may arise by using v.edit to remove duplicates e.g. Point A and point B are snapped to produce point C (features A and B are deleted) and feature C inherits the attributes of A and B. Two categories now link to feature C. To use the above approach to count points with extra categories it is necessary to create a new table and populate it with categories such that there is a 1:1 correspondence between features and categories. The new table (MyCats) can then be used as a filter e.g.
# test run echo "SELECT *,count(owner) FROM myarchsites WHERE ""cat in (select * from MyCats)"" GROUP \ BY owner" | db.select