Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a database of almost 10k crimes, which are divided in to 10 categories and plotted on a map

I also have a database of 12k addresses, which are also plotted on the map.

I aim to perform a multiple regression to determine whether there are correlations between types of crime and house price.

I have created a buffer of 250 meters around each of the addresses, and would like to list a count of each of the crimes which are located within this buffered zone for each of the addresses, however, I can only get QGIS to give me a count of all types of crime, rather than the specifics.

Does anyone know how to do a point to polygon analysis of this type? I am in the middle of breaking the crime databases to analyse each one separately, which is a bit of a ballache as you can imagine!

share|improve this question
    
Laughed at the "ballache" comment. Is QGIS your only choice or do you have access to Esri ArcMap? –  MLowry Aug 16 '13 at 20:58
    
I have ArcMap but had so much trouble with the projections (Which I have never quite understood!) that I downloaded QGIS! –  Christography Aug 16 '13 at 21:21
    
Hmmm but now I have managed to reporoject in QGIS, I think it is now working in Arc. Go me! –  Christography Aug 16 '13 at 21:58

2 Answers 2

Where your crime points have numeric fields which are amenable to simple statistics (mean, max, min, sum and median), this is straightforward. In QGIS go Vector->Data Management->Join Attributes by Location and set your target layer to the polygons and the 'join vector layer' to your points, then choose the summary statistics (second option). In ArcGIS, use Spatial Join and in the field mappings set the statistics options.

Text fields and numeric fields that represent classes or types of objects (i.e. where statistics would be meaningless) pose a bit more of a problem. If this is what you need to get, I would write a script using Python or ArcPy and have an iterative process that cycles through the polygons, selects points by the current polygon and then iterates over my selection to build a comma delimited text field. Alternatively, you could have a number of fields that are perhaps 'bins' to count crimes by type or something.

However, a better approach might be to write your script the other way round and cycle through the points, selecting polygons that contain them and append just the polygon IDs to a delimited text field. You then have a table of all the crimes which has a field listing all the nearby properties and you have preserved all the crime data without any summary. You can then analyse this in all sorts of ways to understand the associations of crimes and properties by selecting all crimes associated with a single property and perhaps creating a pivot table.

share|improve this answer

Queries like this should, in my opinion, by done in a spatial database. For example, if you import both the address_buffers and the crimes into a SpatiaLite database, then, assuming the crime attrib table has a columns "crime_type" and "id" then a query such as:

SELECT crimes.crime_type, COUNT(crimes.id) 
FROM crimes JOIN address_buffers ON ST_Contains(address_buffers.geometry, crimes.geometry)
GROUP BY crime_type;

Will give you a total of all crimes of each type for ALL addresses together. (The query will return 10 rows) If you need 12K rows (all addresses) with 10 columns (the crime categories) then a main query with 10 subqueries is used:

SELECT a.address_id, ct1.count, ct2.count, ct3.count, ct4.count, ....
FROM address_buffers AS a,
   (SELECT COUNT(crimes.id) FROM
   crimes JOIN address_buffers ON ST_Contains(address_buffers.geometry, crimes.geometry)
   WHERE address_buffers.address_id = a.address_id AND crimes.crime_type="type 1") 
   AS ct1 ,
   (SELECT COUNT(crimes.id) FROM
   crimes JOIN address_buffers ON ST_Contains(address_buffers.geometry, crimes.geometry)
   WHERE address_buffers.address_id = a.address_id AND crimes.crime_type="type 2") 
   AS ct2 ,
   (SELECT COUNT(crimes.id) FROM
   crimes JOIN address_buffers ON ST_Contains(address_buffers.geometry, crimes.geometry)
   WHERE address_buffers.address_id = a.address_id AND crimes.crime_type="type 3") 
   AS ct3 ,
   .... (seven more subqueries)...

 GROUP BY a.address_id;

Note the use of the alias "a" for address_buffers which is used in each subquery to select out just one address, and do the sum for each crime type for that address separately. And the ct1, ct2, ct3... aliases refer to each subquery which filters out each category separately.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.