Creating polygons from one to many relationship of points using QGIS or PostGIS?

Question

I have a set of polygons that are in a one to many relationship with points.

The polygons are administrative boundaries and the points have information on companies and percentage of ownership .

I need to get a summary of the company data for each polygon.

The way I know how to do it is to do a relate. I did a spatial join on the points to attach the polygon information and then did a relate on the polygon table to highlight points that fall within it. From there I can do a summarize to get the values I need (sums/averages of company data within each polygon).

But that process is only good for one at a time processing. I have several hundred polygons I want the information for.

Any ideas?

Answer 1

In QGIS, you can calculate those sums and means with "Join attributes by location" tool. It will create a new shapefile with additional attributes.

Answer 2

I think it sounds like what you want to do is a grouping on more than one field. you want to group the points by both their polygon belonging and their company belonging.

If that is what you want it is easy done with sql.

In PostGIS it would look something like:

SELECT c.name, p.name, sum(some_attribute) 
FROM polygon_table p INNER JOIN company_points c ON 
ST_Intersects(p.the_geom, c.the_geom)
GROUP BY p.id, c.company_id, p.name, c.name
ORDER BY c.name, p.name;

The name of the administrative boundary and the company is in the group clause just because you otherwise can not use them in the select-part.