Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a shapefile with several regions. It contains basic info like ID, Code, Name1, Name2, etc.

It is overlaid with a shapefile of points (nearly 200k). Each point represents an event with a type and a day (YYYYMMDD). The table for the point layer looks like:

ID  DAY        STAT1  STAT2   STAT3 ....
01  20010901   23.45  53.12   12.34 ....
02  20010901   43.35  13.56   31.84 ....
03  20010902   35.01  21.61   11.47 ....

What I would like to do is:

  1. Iterate through the point data for each day (one day may have one or several rows).
  2. For each day, count the points in each polygon region.
  3. Update the polygon table with a new column for that day with the point count as its value.

Ideally then, the final result would be a polygon feature with a table that resembles:

ID Code Name1 Name2 01 02 03 04 ... 198721
01 abc  ABC   A01   14  2 86 12 ...     27
02 xyz  XYZ   XZ7   66 47 23 88 ...     19

I'm using QGIS and believe this can be done w/ Python but I am not sure a) exactly how b) if another method might be more ideal.

Any help, pointers, or suggestions are greatly appreciated!

share|improve this question
2  
Are you going to have 198721 days? Even with much less data, database-wise it would be more appropriate to have a separate table, with e.g. polygon_id, day, value. Which brings the suggestion of moving your data to a true geodatabase, e.g. SpatiaLite. With that your question is just a matter of the right SQL query. – steko Dec 18 '12 at 19:34
2  
I would like to suggest that for almost all applications, a more flexible and powerful format for the output is a table with [ID], [Date], and [Count] fields, having one record for each unique combination of polygon and date. Producing such a table is straightforward with almost any GIS: a (non-temporal) point-in-polygon operation, to attribute each point with its containing polygon, can furnish a polygon ID for each input record (via a join on point ID), and that in turn can be summarized by date and polygon ID to produce the desired result. That's just three basic, fast operations. – whuber♦ Dec 18 '12 at 21:11
Like steko said, having a database with +198K columns is probably not the way to go. Perhaps a join/relate might work... edit: what whuber said. :) – Mintx Dec 18 '12 at 21:14
Thanks for the comments. I'm going to try @whuber's approach. Thinking about this, it does indeed seem silly to have nearly 200k fields in a table! In terms of databases, would there be an advantage to SpatialLite as steko suggests, or would something like PostGres also suffice? Thanks kindly! – Silmaril Dec 19 '12 at 23:00

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.