[QGIS-ZA-user] aggregate crime attribute data from SAPS Station boundaries to Wards

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[QGIS-ZA-user] aggregate crime attribute data from SAPS Station boundaries to Wards

QGIS-za-user mailing list
Dear QGis SA,

I would like to know if there is a QGis tool that can assist with the following task.  I want to transfer crime attribute data from SAPS Station boundaries to Ward boundaries, assuming that crime is evenly distributed within the SAPS Station boundary.

Previously I have done this task in Arc View using a script called DataPart.avx which was written by Jianwei Dou in 2002.  The below simplified sketch illustrates what I am trying to do in Qgis.  The SAPS Station shape has the data attributes and the Ward shape is the overlay or boundary shape. The output that I need is an attribute table that has calculated the % intersection of the SAPS Station by the ward boundaries and aggregated the crime attributes to a Ward table based on the % intersection.  In the below sketch the ward boundaries intersect the SAPS Station boundary by 50% in both cases.  As there were 100 crimes in the SAPS Station boundary the output table lists Wards 1 and 2 with 50 crimes in each.

image.png
Any advise most welcome.

Regards
Brian O'Leary




_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user
Reply | Threaded
Open this post in threaded view
|

Re: aggregate crime attribute data from SAPS Station boundaries to Wards

QGIS-za-user mailing list
Hi Brian

This should be fairly easy to do in QGIS without using any special plugins if you don’t mind doing a few steps. Can you share the datasets you want to use with me and I will see if I can come up with an easy workflow for you?

Regards

Tim

On 02 Oct 2018, at 10:57, Brian O'Leary via qgis-za-user <[hidden email]> wrote:

Dear QGis SA,

I would like to know if there is a QGis tool that can assist with the following task.  I want to transfer crime attribute data from SAPS Station boundaries to Ward boundaries, assuming that crime is evenly distributed within the SAPS Station boundary.

Previously I have done this task in Arc View using a script called DataPart.avx which was written by Jianwei Dou in 2002.  The below simplified sketch illustrates what I am trying to do in Qgis.  The SAPS Station shape has the data attributes and the Ward shape is the overlay or boundary shape. The output that I need is an attribute table that has calculated the % intersection of the SAPS Station by the ward boundaries and aggregated the crime attributes to a Ward table based on the % intersection.  In the below sketch the ward boundaries intersect the SAPS Station boundary by 50% in both cases.  As there were 100 crimes in the SAPS Station boundary the output table lists Wards 1 and 2 with 50 crimes in each.

<image.png>
Any advise most welcome.

Regards
Brian O'Leary



_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user








Tim Sutton

Co-founder: Kartoza
Ex Project chair: QGIS.org

Visit http://kartoza.com to find out about open source:

Desktop GIS programming services
Geospatial web development
GIS Training
Consulting Services

Skype: timlinux 
IRC: timlinux on #qgis at freenode.net


_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user
Reply | Threaded
Open this post in threaded view
|

Re: aggregate crime attribute data from SAPS Station boundaries to Wards

QGIS-za-user mailing list

Hi,

I would do it in PostGIS as a first choice by finding the appropriate function.

In QGIS I guess one needs to use the intersection tool and then load the result into a spreadsheet and do a pivot table per ward

 

Regards

 

Arthur Taute | Chief Technical Principal
SMEC South Africa
230 Albertus Street, La Montagne, Pretoria, South Africa, 0184
T +27 (12) 481 3821 | F +27 (12) 803 7943 | M +27 (83) 274 8885
[hidden email] |
www.smec.com | LinkedIn
____________________________________________________
SMEC SNOWY MOUNTAINS ENGINEERING CORPORATION

Local People. Global Experience.

Disclaimer: The information contained in this e-mail and any attachment file is confidential. It is intended for the addressee, and may not be used, reproduced, disclosed or distributed without SMEC's permission. SMEC accepts no liability for loss or damage (whether caused by negligence or not) resulting from the use of any attached files.

Please consider the environment before printing this email.

 

From: qgis-za-user [mailto:[hidden email]] On Behalf Of Tim Sutton via qgis-za-user
Sent: 02 October 2018 01:38 PM
To: South Africa QGIS user group <[hidden email]>
Cc: Tim Sutton <[hidden email]>
Subject: Re: [QGIS-ZA-user] aggregate crime attribute data from SAPS Station boundaries to Wards

 

Hi Brian

 

This should be fairly easy to do in QGIS without using any special plugins if you don’t mind doing a few steps. Can you share the datasets you want to use with me and I will see if I can come up with an easy workflow for you?

 

Regards

 

Tim



On 02 Oct 2018, at 10:57, Brian O'Leary via qgis-za-user <[hidden email]> wrote:

 

Dear QGis SA,

 

I would like to know if there is a QGis tool that can assist with the following task.  I want to transfer crime attribute data from SAPS Station boundaries to Ward boundaries, assuming that crime is evenly distributed within the SAPS Station boundary.

 

Previously I have done this task in Arc View using a script called DataPart.avx which was written by Jianwei Dou in 2002.  The below simplified sketch illustrates what I am trying to do in Qgis.  The SAPS Station shape has the data attributes and the Ward shape is the overlay or boundary shape. The output that I need is an attribute table that has calculated the % intersection of the SAPS Station by the ward boundaries and aggregated the crime attributes to a Ward table based on the % intersection.  In the below sketch the ward boundaries intersect the SAPS Station boundary by 50% in both cases.  As there were 100 crimes in the SAPS Station boundary the output table lists Wards 1 and 2 with 50 crimes in each.

 

<image.png>

Any advise most welcome.

 

Regards

Brian O'Leary

 

 

 

_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

 

 





 

Tim Sutton

 

Co-founder: Kartoza

Ex Project chair: QGIS.org

 

Visit http://kartoza.com to find out about open source:

 

Desktop GIS programming services

Geospatial web development

GIS Training

Consulting Services

 

Skype: timlinux 

IRC: timlinux on #qgis at freenode.net

 


_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user
Reply | Threaded
Open this post in threaded view
|

Re: aggregate crime attribute data from SAPS Station boundaries to Wards

QGIS-za-user mailing list
In reply to this post by QGIS-za-user mailing list
Hi Brian

Please share a few overlapping station and ward features with the list.

thanks

Gavin

On 02 Oct 2018, at 10:57, Brian O'Leary via qgis-za-user <[hidden email]> wrote:

Dear QGis SA,

I would like to know if there is a QGis tool that can assist with the following task.  I want to transfer crime attribute data from SAPS Station boundaries to Ward boundaries, assuming that crime is evenly distributed within the SAPS Station boundary.

Previously I have done this task in Arc View using a script called DataPart.avx which was written by Jianwei Dou in 2002.  The below simplified sketch illustrates what I am trying to do in Qgis.  The SAPS Station shape has the data attributes and the Ward shape is the overlay or boundary shape. The output that I need is an attribute table that has calculated the % intersection of the SAPS Station by the ward boundaries and aggregated the crime attributes to a Ward table based on the % intersection.  In the below sketch the ward boundaries intersect the SAPS Station boundary by 50% in both cases.  As there were 100 crimes in the SAPS Station boundary the output table lists Wards 1 and 2 with 50 crimes in each.

<image.png>
Any advise most welcome.

Regards
Brian O'Leary



_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user


_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user
Reply | Threaded
Open this post in threaded view
|

Re: aggregate crime attribute data from SAPS Station boundaries to Wards

QGIS-za-user mailing list
Hi Gavin,

attached are the Durban police station boundaries with murder attributes for the last 10 years.  Also attached are the Ward boundaries.  Any advise on how to produce an output table with murder by ward would be much appreciated.

Thanks
Brian

On Wed, Oct 3, 2018 at 8:53 AM Gavin Fleming via qgis-za-user <[hidden email]> wrote:
Hi Brian

Please share a few overlapping station and ward features with the list.

thanks

Gavin

On 02 Oct 2018, at 10:57, Brian O'Leary via qgis-za-user <[hidden email]> wrote:

Dear QGis SA,

I would like to know if there is a QGis tool that can assist with the following task.  I want to transfer crime attribute data from SAPS Station boundaries to Ward boundaries, assuming that crime is evenly distributed within the SAPS Station boundary.

Previously I have done this task in Arc View using a script called DataPart.avx which was written by Jianwei Dou in 2002.  The below simplified sketch illustrates what I am trying to do in Qgis.  The SAPS Station shape has the data attributes and the Ward shape is the overlay or boundary shape. The output that I need is an attribute table that has calculated the % intersection of the SAPS Station by the ward boundaries and aggregated the crime attributes to a Ward table based on the % intersection.  In the below sketch the ward boundaries intersect the SAPS Station boundary by 50% in both cases.  As there were 100 crimes in the SAPS Station boundary the output table lists Wards 1 and 2 with 50 crimes in each.

<image.png>
Any advise most welcome.

Regards
Brian O'Leary



_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

eThekwini Police Bounds 2018_Murder.zip (493K) Download Attachment
eTh_Wards2016_Consult_Zones.zip (1M) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: aggregate crime attribute data from SAPS Station boundaries to Wards

QGIS-za-user mailing list
Hi Brian

You need a proxy variable to predict what the proportional contribution from each ward would have been to get to the total murder rate for the station area.

One way will be to use socio-econ profiling, but you do run into certain biases which may become problematic when you need to explain your method to your users of the output. Else it is just going to be a straight proportional redistribution to wards based on total population per ward if you assume the probability is the same across the police station area.

Best rgds
Gerbrand Mans
>>> Brian O'Leary via qgis-za-user <[hidden email]> 03/10/2018 12:26 >>>
Hi Gavin,

attached are the Durban police station boundaries with murder attributes for the last 10 years. Also attached are the Ward boundaries. Any advise on how to produce an output table with murder by ward would be much appreciated.

Thanks
Brian

On Wed, Oct 3, 2018 at 8:53 AM Gavin Fleming via qgis-za-user <[hidden email]> wrote:
Hi Brian

Please share a few overlapping station and ward features with the list.

thanks

Gavin

On 02 Oct 2018, at 10:57, Brian O'Leary via qgis-za-user <[hidden email]> wrote:

Dear QGis SA,

I would like to know if there is a QGis tool that can assist with the following task. I want to transfer crime attribute data from SAPS Station boundaries to Ward boundaries, assuming that crime is evenly distributed within the SAPS Station boundary.

Previously I have done this task in Arc View using a script called DataPart.avx which was written by Jianwei Dou in 2002. The below simplified sketch illustrates what I am trying to do in Qgis. The SAPS Station shape has the data attributes and the Ward shape is the overlay or boundary shape. The output that I need is an attribute table that has calculated the % intersection of the SAPS Station by the ward boundaries and aggregated the crime attributes to a Ward table based on the % intersection. In the below sketch the ward boundaries intersect the SAPS Station boundary by 50% in both cases. As there were 100 crimes in the SAPS Station boundary the output table lists Wards 1 and 2 with 50 crimes in each.

<image.png>
Any advise most welcome.

Regards
Brian O'Leary



_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user
Reply | Threaded
Open this post in threaded view
|

Re: aggregate crime attribute data from SAPS Station boundaries to Wards

QGIS-za-user mailing list

Hello Brian,

What Gerbrand says is absolutely correct, but taking your assumption that crimes occur the same over the total area of the ward you would need to work out the crimes/km2 or so first. From there on it is a pretty straight forward exercise with QGIS. That involves a few steps however:

  1. Add the two layers to your map
  2. Set your project projection to Zangi:Zangi:CPNO31 (and area units to km2, if that's what you want)
  3. Then use the vector function "Union" on the two layers to create a new polygon layer which essentially is a combination of attributes on the two
  4. The you will notice that there are a few polygons that are not "unioned" as they don't overlap (e.g. policing areas outside your wards): make the new layer editable and delete those. You should then have 367 polygons with "unioned" attributes (I noticed that there are a few small ones inside the wards/policing areas due to not-adjoining polygons..these should be sorted out first!).
  5. Then use the field calculator to create new columns for each year category by taking the "number of murders" / $area to get the murders per km2 (scary!!)
  6. Then use the Vector Analysis Tool "Statistics by categories" (you are on QGIS 3?) to do a statistic on each of the new year columns using the ward ID or number (or both) as Field with categories where you would get min, max, mean, etc. but the mean is a pretty good assumption in murders/km2 as it is a ratio.
  7. Then you join the Union table with your results for each year and save the layer (using selections) to a new table with only the columns you want. You could convert the murders/km2 back to the total murders in the area (inverse) beforehand.
  8. "Bob's your Uncle"

Hope his helps!

Immo


On 03/10/2018 14:20, Gerbrand Mans via qgis-za-user wrote:
Hi Brian

You need a proxy variable to predict what the proportional contribution from each ward would have been to get to the total murder rate for the station area.

One way will be to use socio-econ profiling, but you do run into certain biases which may become problematic when you need to explain your method to your users of the output. Else it is just going to be a straight proportional redistribution to wards based on total population per ward if you assume the probability is the same across the police station area.

Best rgds
Gerbrand Mans
>>> Brian O'Leary via qgis-za-user [hidden email] 03/10/2018 12:26 >>>
Hi Gavin,

attached are the Durban police station boundaries with murder attributes for the last 10 years. Also attached are the Ward boundaries. Any advise on how to produce an output table with murder by ward would be much appreciated.

Thanks
Brian

On Wed, Oct 3, 2018 at 8:53 AM Gavin Fleming via qgis-za-user <[hidden email]> wrote:
Hi Brian

Please share a few overlapping station and ward features with the list.

thanks

Gavin

On 02 Oct 2018, at 10:57, Brian O'Leary via qgis-za-user <[hidden email]> wrote:

Dear QGis SA,

I would like to know if there is a QGis tool that can assist with the following task. I want to transfer crime attribute data from SAPS Station boundaries to Ward boundaries, assuming that crime is evenly distributed within the SAPS Station boundary.

Previously I have done this task in Arc View using a script called DataPart.avx which was written by Jianwei Dou in 2002. The below simplified sketch illustrates what I am trying to do in Qgis. The SAPS Station shape has the data attributes and the Ward shape is the overlay or boundary shape. The output that I need is an attribute table that has calculated the % intersection of the SAPS Station by the ward boundaries and aggregated the crime attributes to a Ward table based on the % intersection. In the below sketch the ward boundaries intersect the SAPS Station boundary by 50% in both cases. As there were 100 crimes in the SAPS Station boundary the output table lists Wards 1 and 2 with 50 crimes in each.

<image.png>
Any advise most welcome.

Regards
Brian O'Leary



_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user


_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user

--

Immo F. Blecher
Street:
56 Saffraan Ave., Stellenbosch, 7613, South Africa
Postal: PO Box 74, Lynedoch, 7603, South Africa
Cell: +27 83 272 6791 | Fax: +27 86 736 3302 Email: [hidden email] | [hidden email] | [hidden email]
Internet: http://www.blecher.co.za/

Contact me: [hidden email] immoblecher

Please consider your environmental responsibility. Before printing this e-mail message, ask yourself whether you really need a hard copy.

IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.


_______________________________________________
qgis-za-user mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/qgis-za-user