In this post we look at extracting and mapping data from the CliFlo database, with some unanticipated coordinate system detours.  

Here at GIS Central we recently had a frustrating problem arise with some data (Ed. not an uncommon thing by any means, almost a daily occurrence).  Postgrad Joseph has been interested in identifying heat islands around Christchurch related to different demographic data. Climate data are collected at stations around the city (and the country, for that matter), but they are too sparse to get a fully extensive map of surface temperature,s so heat islands are typically derived using satellite imagery (we’ll hear more about how this is done in another post).  With this in mind, we dug into imagery available from the Earth Explorer website, and settled on a relatively cloudless Landsat 8 image from 19 February 2021:

(See here for details on how to use this site.)

We’ve got to be a bit careful about time here before going too far.  Landsat, being a global resource, uses Greenwich Mean Time for its clock.  The image metadata tell us that it was acquired on 19 Feb 2021 at 22:13 GMT.  Given time differences, that equates to 20 Feb 2021 at 11:13 NZ time.  This becomes more important later on.

A big upside of using Landsat data is that they are free – the other side of that coin is that the resolution is 30 m; fairly coarse, but still usable.  Deriving the land surface temperatures is a quite involved process making use of several of the Landsat data bands but in the end, one gets a nice map showing the distribution of land surface temperatures – it was a warmish day:

Credit: Joseph Azer

Having done the hard yards to get here, Joseph was then wanting to check his results against some “on the ground” data, as it were, using ground temperature measurements, so we next went in search of temperature data from meteorological stations around the city.

NIWA is one of the main organisations that collects data like these, and since it is a publicly funded CRI we have access to their data via the CliFlo national climate database, available through the library (Databases > Alphabetically > C > Cliflo):

Once here and logged in, you can search their database with different criteria:

First, we have to set the datatypes – since we were interesting in ground temperatures, we clicked “Select datatype(s)” > Daily and Hourly Observations > Temperature and Humidity > Screen obs and then ticked “Hly AirT” (Hourly air temperature).

Next we had to choose some stations in the next section.  A helpful pop up gives us some options depending on how much we know – we went with the Region > Christchurch option to cast our net widely:

Clicking “Get Station List” and another page lets you select stations and the “Add Selected Stations”

Next, we can be specific about the data and time in section 3 – the settings below should give us hourly readings over the course of the whole day for the day the image was captured:

Finally, we can set the output options for our query – the main one being the “File download option”.  We want something we can easily work with:

Most of these will work, apart from HTML and Word; we chose Excel file as it will be easier to read.  Clicking “Send Query” got us a downloaded file which I then opened in Excel:

So here we’ve got station names, some identifiers and most importantly, x- and y-coordinates (Longitude and Latitude respectively) plus a Height and some other details.  Note that the coordinates are in decimal degrees (“dec.deg”)

Here comes an important question: what coordinate system are the Lat/Long values in?  There are several we could choose from.  The file says NZGD49 (New Zealand Geodetic Datum 1949) at the bottom so we’ll go with that.  Below the station details we get the hourly readings for each station:

We’ll use these later on but first, let’s get these points on the map.  to map the points I first need to separate out the station details from the hourly readings so those records were copied to a new sheet:

Not quite ready yet – I must ensure that the first line has what will be the attribute names and that those names have no spaces, or don’t begin with a number, or have any odd characters – here’s the revamped first few lines:

That should play nicely with Pro now.  Saving the sheet, adding it to a map and previewing the table is my next step:

Right-clicking on the table name in Contents, I can map the points using the Display XY Data option:

Here I’ve set the X Field to Longitude, the Y Field to Latitude and the Coordinate System to NZGD49.  Clicking OK gets me some points on the map:

Hurrah!  I’m done!  Well, not quite.   As a next step, we’ll need to attach the temperatures closest to 11:13 from the hourly observations to each point and then compare them against the modelled land surface temperatures from Joseph’s heat island map.  I won’t go into detail on that here, suffice it say I could have done it in the spreadsheet before I mapped the points, or I could create a new sheet with the temperature values listed by station number and then use a table join to bring them together, or (and there’s almost always another or with GIS) I could have used Extract Values to Points.

But before we do, we should really do a bit of quality control – just to make sure the points are where they should be (also known as ground truthing).  I’ll pick on one point in particular, NIWA’s own met station at their Kyle Street offices as I’ve got a pretty good idea about where the point should be; immediately I see a problem:

The blue marker is where the station has been mapped.  The purple mark is roughly where it should be.  A bit of ground truthing shows that it’s not on busy Riccarton Road:

Zooming in to a satellite image of the NIWA campus you can roughly see where the station is:

And here is it from ground level:

So what’s up here?  The mapped point is clearly not in the right place (good thing I checked!).

There are a few possibilities but the major suspect is the coordinate system.  Are the points in fact in NZGD49?  Possibly not, but which other ones are possible?  The two main alternatives would be NZGD2000, an NZ-specific geographic (3D) coordinate system, and WGS84, the global system used by GPS.  That’s a bit of a trick answer because, for most practical purposes, these two are the same (that was one of the reasons for creating NZDG2000, so it would play more nicely with GPS than NZGD49 does).  We may be able to fix this by changing the coordinate system of the mapped points and see if they end up in the right place.  A quick way to check them is to just remap the points using Display XY Data with the coordinate system set to WGS84 (or, one could use Project).  When done, I got this location for the station:

Now that’s a bit more like it!

So, where have we gotten to with this little exercise?  One, there’s now some uncertainty for me around the coordinates coming out of CliFlo – as best as I could tell, the metadata told us one thing but the points on the map told us something different.  Are they all in WGS84?  Or just this one?  Is it that the coordinates themselves have low levels of accuracy?  I would want to do some spot checking, especially critical stations (well, they are all critical, really) to make sure.  Joseph’s comparing these measurements to his imagery-derived 30 m resolution temperature values – this particular location was over 160 m away from the actual, potentially 5 to 6 grid cells away, so this is an important step.  To be fair, NIWA do provide a bit more information on the coordinates:

Our Kyle Street station gets on “H” meaning that it should be in NZGD49 and many of the stations are as well, but not all.

Secondly, this helps to remind us to not blindly accept the outputs of tools: always double-check your outputs to make sure they’re correct.  Here are a few quick spot checks, first from Diamond Harbour – NZGD49 in red this time, WGS84 in blue:

Zooming in – sure looks like a met station at the WGS84 point to me:

And here’s one in Akaroa:

Up close:

The WGS84 points aren’t exactly in the right place (i.e. within a metre or two) but are much closer than the NZGD49 points.  I have to say that I haven’t looked at all the points; some were clearly more accurate in WGS84 while others were less certain – my uncertainty about the coordinate system has not been fully assuaged.  There’s a good case to be made that all the points in the database be standardised into the same coordinate system (preferably NZGD2000 or, my personal favourite, NZTM).  Sounds like a good project for a summer intern!

In Joseph’s case, the results turned out pretty well overall once we had made this correction.  I’ll want to follow up with NIWA to be sure about the coordinate system in the future.  After all, location is important!  (But you knew I would say that.)