Creating polygons from a one to many relationship of points

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? I feel like there is something obvious I am overlooking.

Thanks!

-
I don't follow: a spatial join of polygons to points will associate the polygon ids with the point attributes. A summary of the point attributes by polygon id ought to finish the job. How is it that you manage to process only one polygon at a time then? –  whuber Apr 18 '11 at 19:18
Because I need it one step further... under each polygon id there are several companies. I need the summary done on the companies. The result I am looking for is a summary of values for companies within each polygon. –  Kapjaki Apr 18 '11 at 19:26
No problem. In ESRI software there's one intermediate step: to summarize on multiple keys like polygon+company, first create a new field in which you calculate the concatenation of those keys. Summarize on the new field. –  whuber Apr 18 '11 at 20:32
Note: Same (concatenation of keys) can be done in QGIS field calculator. –  underdark Apr 18 '11 at 20:38
@Underdark If QGIS could implement joins, summaries, and relates on multiple keys, without requiring an intermediate calculation, it would leapfrog all the Arc* software in terms of flexibility and ease of use with such operations :-). –  whuber Apr 18 '11 at 20:47

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

-
Is this the same as a 'spatil join' in ArcGIS? If it is, then it doesn't quite do what I need... anyway, I am going to try. I'll report back how it works. –  Kapjaki Apr 18 '11 at 19:54
I guess it's same. If that's not what you need, you might have to explain further - maybe with a small example. –  underdark Apr 18 '11 at 19:58
I have points that contain company data. I need to be able to break that out on a company level for polygons that contain multiple points. Therefore I need more than just a summary of the points, I need the different companies values summarized for each polygon. Each point has data on multiple companies. –  Kapjaki Apr 18 '11 at 20:10
Can you post a sample of the point layer's attribute table? –  underdark Apr 18 '11 at 20:24
Unfortunately not, due to privacy issues. However, maybe this example would be easier to understand: County data is stored as a point at the centroid of each state (many overlapping points for each Sate). The county points contain data on ethic group populations in each state (i.e. Hispanic: 40,000). Now you need to add up all the counties various ethnic populations in order to get the overall state numbers. –  Kapjaki Apr 18 '11 at 21:38

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.

HTH

Nicklas

-
sorry, didn't see the arc tag. Was it there all the time? –  Nicklas Avén Apr 18 '11 at 20:59
No, it wasn't. –  underdark Apr 18 '11 at 21:13
I stuck that tag in there after the conversation started to smell ESRI-ish. Nice answer, by the way. –  whuber Apr 18 '11 at 22:33
@whuber, yes, I felt that smell too, but I ignored it since I saw no arc-tag :-) –  Nicklas Avén Apr 19 '11 at 7:15