3

I am trying to join attributes from 2 shapefiles. 1 shapefile is a polygon layer indicating police precincts and the second is a point layer with crime statistics for each police station. The police station and the police precinct are the same. The police station layer that contains the crime statistics has roughly 40 points per police station. See attached police station image

enter image description here.

I want to join this layers table to the police precinct layer (See attached precinct layer image)

enter image description here

The trouble I am having with this, is that when i use the Join by location tool, it only takes the first 17 records from the police stations layer and appends the data to the existing 17 police precincts. I need it to append all 40 crimes points to the precincts layer without creating 40 polygons that lie ontop of oneanother. Just 1 polygon with multiple crime records. Does anyone know how to do this in QGIS?

Improve this question

2 Answers 2

Reset to default
2

If I understand this correctly, you don't need a spatial join. Use Project > Project Properties > Relations. Use 'Precincts' as the parent and 'Stations' as the child. Use "Station" and "COMPNT_NM" as the join fields.

Edit: If you then use the identify features tool and click on a precinct, you will get a form pop up with all the attributes for the precinct plus a list of all the associated crimes. See the documentation for further details

Improve this answer
4
  • Thanks for your comment and suggestion. The reason that I never considered a relate is that I would like to/need to be able to display the information contained in the "Police Stations" layer spatially rather than just viewing it in table format, So as to be able to visually compare "police precinct" and the crime in each of them. I hope this maybe provides some further clarification regarding this. Unless I am completely missing the point of a join/relate here.
    – Stevedup
    Mar 22, 2018 at 11:43
  • Is this just for visual representation then? You can style all the crimes according to the precinct they are associated with. If I've still got this wrong, can you provide an example of what you want to do with the data... Eg run some sort of query or spatial statistics?
    – firefly-orange
    Mar 22, 2018 at 11:54
  • So the idea with this data is two-fold. Firstly just to have a visual representation of the data and secondly the end goal is to take the crime statistics and to analyze them in conjunction with population demographics to produce some meaningful statistics for each police precinct or any other administrative polygons. To achieve this, the approach will be by assigning the statistics to a common feature e.g. Electrical connection points to the main grid, each building has this, and then doing the same with census data. This would then be the basis of that analysis and density comparisons
    – Stevedup
    Mar 22, 2018 at 12:31
  • I think a relation is the way to go. I've edited my answer to include some details on what to do with it once you've set the relation up
    – firefly-orange
    Mar 22, 2018 at 13:28
0

Another approach would be a Virtual Layer.

From the menu, go to Layer | Add Layer | Add/Edit Virtual Layer and [Import] your layers.

Assuming polygon layer name is precinct and point layer is station, following query will create a new virtual layer (polygon) :

SELECT precinct.*, 
       station.Station,
       station.Type,
       station.Crime_ca_1,
       station.2005-2006
FROM precinct CROSS JOIN station 
ON st_intersects(precinct.geometry, station.geometry) = 1

In the above example I had stopped at the field 2005-2006 (as station.2005-2006) but please add all required fields following comma(, ).

After checking the result please save this layer as new Shapefile.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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