{"id":2922,"date":"2021-04-01T20:42:53","date_gmt":"2021-04-01T07:42:53","guid":{"rendered":"http:\/\/blogs.lincoln.ac.nz\/gis\/?p=2922"},"modified":"2023-05-07T03:07:23","modified_gmt":"2023-05-07T03:07:23","slug":"deprived-data","status":"publish","type":"post","link":"https:\/\/blogs.lincoln.ac.nz\/gis\/deprived-data\/","title":{"rendered":"Deprived Data"},"content":{"rendered":"

Some quick data amalgamation tasks are tackled with spatial joins, Near and some table joins.<\/em><\/p>\n

Over the years we’ve spent time talking about the smoking reduction research I’ve been involved with. (Ed. Who’s this we, kemosabe<\/a>?<\/em>)\u00a0 It’s had me doing geocoding<\/a>\u00a0(and this<\/a>),\u00a0Network Analysis<\/a>, a lot of Python scripting<\/a> and lots of little data amalgamation jobs here and there.\u00a0 As we look to finish up some recent work, I found I needed to add some data from a range of sources to my existing database and thought it might be useful to cover some of that here.\u00a0 Along the way, we get to see some of the more useful and fundamental vector spatial analysis tools.<\/p>\n

First off, I’ve got a layer of points containing the location and attributes of 5,131 tobacco retailers across New Zealand circa 2018.\u00a0 Regular readers (Ed. Both of them.<\/em>) may recall that we’ve lately been looking at modelling the effect of reducing the number of retailers on accessibility to tobacco, and for some of the statistical analysis we need to know some of the details around levels of economic deprivation, whether a retailer is in an urban or a rural setting as well as distances to the nearest other retailer and secondary school.<\/p>\n

We start with my layer of retailers:<\/p>\n

\"\"<\/a><\/p>\n

In the interests of keeping things simple, I’ve stripped down my attribute table to just the basics:<\/p>\n

\"\"<\/a><\/p>\n

UniqueID2018 can be used to link each feature to the full dataset if needed.<\/p>\n

Data-wise, I can calculate the nearest distance to other retailers using a tool, so I’ve got what I need for that.\u00a0 For the level of economic deprivation, I’m going to need to extra data.\u00a0 Otago Uni is the source for the New Zealand Deprivation Index<\/a>\u00a0(DPI)\u00a0so that’s my first stop.\u00a0 We’re focused on 2018 data (or thereabouts) and here’s what’s available from their website:<\/p>\n

\"\"<\/a><\/p>\n

Lots to choose from but I don’t see anything in spatial format though I do see plenty of Excel and Text files.\u00a0 On close inspection there are different versions to consider – I could look at “Statistical Area 1” data or “Meshblock” data or “Statistical Area 2” data.\u00a0 What the heck’s all that about?\u00a0 With a bit of digging, Otago links their DPI to the regular censuses, which are themselves linked to areas of differing sizes.\u00a0 Meshblocks are what used be the finest scale of data from the census that could be released publicly.\u00a0 Since the 2013 census, Stats New Zealand has been promoting the use of “Statistical Areas” for mapping census data.\u00a0 SA1 is the finest resolution scale aiming to have between 100 and 200 residents and no more than 500.\u00a0 SA2 aims for 1,000 – 4,000 residents and are larger in size than SA1 areas.\u00a0 \u00a0Compared to previous censuses, SA1 is roughly the meshblock scale while SA2 is more like census area units.\u00a0 These are nested areas meaning that the two different scales share the same boundaries where they are close.<\/p>\n

Anyway, the decision was made that we wanted to know the DPI and urban\/rural values at the SA1 level but also needed to know which SA2 area each retailer was in, so going back to the data above, I downloaded the NZDep2018 SA1 Excel spreadsheet- here’s what it looks like:<\/p>\n

\"\"<\/a><\/p>\n

Decoding: in the spreadsheet there are 29,889 rows (SA1 areas).\u00a0 Each SA1 area has a numerical code to uniquely identify it (that will be more important later).\u00a0 The NZDep2018 column has the deprivation index value, which ranges from 1 (least deprived) to 10 (most deprived).\u00a0 The blanks are for SA1s that have no one usually resident in them.\u00a0 NZDep2018_Score is the raw value Otago used to define the DPI index value and URPopnSA1_2018 is the usually resident population for that SA1.\u00a0 For continuity, information on the SA2 area that each SA1 is nested within is provided as a code and a name.<\/p>\n

So, having this as a spreadsheet is handy, but what do I do with it spatially?\u00a0 Very happily, all those SA1 areas have been mapped and are freely available from Stats NZ<\/a>:<\/p>\n

\"\"<\/a><\/p>\n

This layer is downloadable as a shapefile or in a geodatabase so after a bit of downloading and unzipping, this layer goes on my map:<\/p>\n

\"\"<\/a><\/p>\n

Getting there – but while I’ve got the SA1s on the map, the DPI data is still sitting in the Excel spreadsheet, so I need a way to link them.\u00a0 Table Joins<\/a> to the rescue!\u00a0 Notice anything common between the SA1 attribute table and the DPI spreadsheet?\u00a0 They both contain the SA1 code though are called different things.\u00a0 Using the values, I can join the data in the spreadsheet to the spatial layer and get the benefit of both. I do this by adding the spreadsheet to the map (not essentially, actually) and then right-clicking on the spatial layer and looking for Joins and Relates > Add Join and setting up the link:<\/p>\n

\"\"<\/a><\/p>\n

When OK is clicked, the spreadsheet rows are joined to their corresponding SA1 record:<\/p>\n

\"\"<\/a><\/p>\n

Nice – so in one fell swoop I’ve added the DPI data, the usually resident population and along for the ride came the SA2 details (which we needed anyway – happy coincidence (Ed. but was it really<\/strong> a coincidence?<\/em>).\u00a0 Since table joins like this are temporary, it’s a good idea to make this permanent by exporting this layer to a new layer (right-click on layer name > Data > Export Features).<\/p>\n

Next we need some rural\/urban data.\u00a0 Happily, the good people at Stats NZ have already done the hard yards and that one, too, and the data are right there in the DataFinder, only a download away:<\/p>\n

\"\"<\/a><\/p>\n

With these on the map, we’re just about ready to rumble.<\/p>\n

Next up are the distance data.\u00a0 Recall we need two distances for each retailer: distance to closest other retailer and distance to nearest secondary school.\u00a0 The Near <\/a>tool comes in handy for these as a way to calculate the straight-line distance between vector features.\u00a0 We’ll first do the distance to the other retailers.<\/p>\n

The way this tool works is that it adds two new attributes to an existing layer (unlike most tools which create a new output layer).\u00a0 Here it is setup:<\/p>\n

\"\"<\/a><\/p>\n

Notice that I’m using the same layer as the Input and<\/em> the Near Features – we would normally use this tool to find the distance to some other layer, but here I’m interested in the other features in the same layer.\u00a0 The two new fields that will be added to my will be NEAR_FID that holds the object ID of the other retailer and NEAR_DIST that will be the straight-line distance to that other retailer.\u00a0 Here’s my table after I run the tool:<\/p>\n

\"\"<\/a><\/p>\n

For the school distances, I’ve got a slight problem.\u00a0 If I rerun the Near tool again with schools as the Near Features, it will simply overwrite the NEAR_FID and NEAR_DIST attributes already there but I need to have both.\u00a0 Not to worry, I’ve got another option with this kind of proximity analysis – Generate Near Table<\/a>.\u00a0 This does the same thing, but the output is a standalone table rather than an attribute in my spatial layer.\u00a0 \u00a0The output looks like this:<\/p>\n

\"\"<\/a><\/p>\n

Notice anything useful here?\u00a0 The IN_FID is the ID from my retailers layer – I can use this to join this table to my spatial layer (these joins are super<\/em> useful).\u00a0 To avoid any conflicts with attribute names, before I do the join I add a new attribute called School_Dist and use Calculate Field to copy the NEAR_DIST value into it.\u00a0 All right Mr DeMille,\u00a0<\/a>I’m ready for my close up<\/a>.\u00a0 After the join and an export, I’ve got both distances in my retailer layer.<\/p>\n

Now, how do I add the DPI and urban\/rural data to the retailers?\u00a0 I’ve got all the data I need but they are spread across three layers of two different types, points and polygons.\u00a0 How do I bring them all together in my point layer of retailers?<\/p>\n

I certainly hope that all those ERST202 and 606 students are shouting the same thing at their screens: spatial joins<\/a>, one the simplest but most powerful of vector spatial analysis tools.\u00a0 With a spatial join, the polygon attributes that a point sits within are transferred into the point’s attribute table, thus bringing everything together.\u00a0 I’ve got three options: Identity<\/a>, Intersect <\/a>and Union<\/a>.\u00a0 Union won’t work here because it only works on polygons.\u00a0 Both Identity and Intersect will work but I’m going with Intersect because I can join both of my polygon layers to the points in one step, rather than two that would be needed with Identity.\u00a0 So here’s Intersect,\u00a0 primed and ready to go:<\/p>\n

\"\"<\/a><\/p>\n

I’m specifying that my output will be Point though it should do that automatically (given the choice it will produce an output in the simplest type, here, points).\u00a0 Et voila!\u00a0 All the data together in one point layer.<\/p>\n

\"\"<\/a><\/p>\n

Nice!\u00a0 (I’ve turned off some of the attributes to make it easier to see the critical ones.)\u00a0 Last job is to use the Table to Excel <\/a>tool to export this to something my Otago colleagues are happy to work with:<\/p>\n

\"\"<\/a><\/p>\n

Like many GIS analyses, all I’ve got to show for my efforts is a spreadsheet, but hopefully in this post you’ve gotten to see some of the blood and guts that goes into outputs like this.\u00a0 I thought it might be worthwhile to go through this because there was a nice variety of (vector) tools needed to get what we needed: proximity analysis with Near and Generate Near Table, table joins, and spatial joins.\u00a0 All up, with downloading data from Stats and the running of tools, this might have taken me about, oh, maybe and hour and a half?\u00a0 There was a bit of thought that went into sequencing and matching the right data for the job, but it gives my colleagues something to work on and I can now sit back and relax…but not for long.\u00a0 With GIS you’re never idle for long.\u00a0 Really must get on to finishing that darned Python script<\/a>…<\/p>\n

C<\/p>\n","protected":false},"excerpt":{"rendered":"

Some quick data amalgamation tasks are tackled with spatial joins, Near and some table joins. Over the years we’ve spent time talking about the smoking reduction research I’ve been involved with. (Ed. Who’s this we, kemosabe?)\u00a0 It’s had me doing geocoding\u00a0(and this),\u00a0Network Analysis, a lot of Python scripting and lots of little data amalgamation jobs […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2922","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/2922","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/comments?post=2922"}],"version-history":[{"count":1,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/2922\/revisions"}],"predecessor-version":[{"id":4087,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/2922\/revisions\/4087"}],"wp:attachment":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/media?parent=2922"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/categories?post=2922"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/tags?post=2922"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}