But My GIS Doesn't Like That

05 Apr 2017

Make Me A Map

Oft in my day job I get requests to map data contained in spreadsheets. I must say I prefer ‘seeing’ my (now not) spreadsheets in a GIS Software environment - TableView. So I quickly want to move any data I get to that place. The path to such and eventually the cartographic output is rarely straight forward, springing from the fact that:

• Most data cells are merged to make the spreadsheet look good.
• Column (field) names are rarely database friendly. (Remember the Shapefile/ DBF >10 characters limit?)
• Something is bound to happen when exporting the data to an intermediate format - CSV - for manipulation. (Data cells formatting.)
• Not forgetting the unnecessary decimal places at times.

The map requester often wonder why I’m taking so long to have their maps ready. In the meantime I have to deal with a plethora of issues as I wrangle the data:

• Discovering that some field names chosen by the client are reserved words in “My GIS” - [ size, ESRI File Geodatabase ]
• The tabular data can’t be joined to the spatial component straight-on because:
  • A one-to-one relationship doesn’t exist cleanly because the supplied data contains spatial duplicates but unique attribute data. (What to retain?)
  • The supposed common (join) field of the source data doesn’t conform to the format of my reference spatial dataset.
  • Some entries of the common field from the master data, contain data that does not fall within the domain of the spatial - e.g a street address such as 101 Crane Street, where evidently street numbers are only 1 - 60. (and 01,10 and 11 exist in the database - in case you’re thinking about a transcribing error.)

Frustration from the Client is understandable. The integrity of their data has come under scrutiny by the Let-The-Data-Speak dictate of data processing and cleaning. Unfortunately they have to wait a bit longer for their map. The decision on how to deal with the questionable data lies with them. I simply transform, as best as I can, to a work of map art.

Database Again Please

… entry level Data Scientists earn $80-$100k per year. The average US Data Scientist makes $118K. Some Senior Data Scientists make between $200,000 to $300,000 per year…

~ datascienceweekly.org, Jan 2017

Working with and in geodatabases makes me somewhat feel like I’m getting closer to being a data scientist. So as I resolved for year 2017 - more SQL (…and Spatial SQL) and databases.

Importing spreadsheets to an ESRI’s Geodatabase has major advantages but isn’t straightforward either. The huge plus being able to preserve field names from source and do data check things. Good luck importing a spreadsheet without first tweaking a field or two to get out of the way ‘Failed To Import’ errors.

Comma Separated Values (CSV) always wins but, beware of formatting within the spreadsheet. If wrongly formatted…Garbage In! But once the import is neatly done, I can comfortably clean data for errors and check integrity better in My GIS environment. As a bonus I get to learn SQL too. Cant’ resist that pretty window!

#postscript

The above are just a few matters highlighting what goes behind the scenes in transforming a spreadsheet to a map. The object to show the non GIS user it takes more than a few clicks to come up with a good map.

Compassing It

11 Feb 2017

Still Looking ?

GIS Specialist

I am in the habit of Googling “GIS” job openings, first in my province of residence and then nationally. CareerJet and Gumtree are my go-to places. CareerJet is particularly good in that it aggregates posts from several job sites. Job Insecurity? ~ far from it! I like to know how my industry is doing, job wise, and also just to check how employable and relevant I still am, a check against having decanted skills and a way of identifying areas of personal improvement. During this insightful ‘pass-time’ activity I couldn’t help notice one particular job that persistently appeared in my searches, albeit evidently being re-posted , for more than 1 year!

Well, as usual you may ~ Cut To The Chase.

Yardsticking

For the particular job above, I felt I was comfortable enough with the requisite skills to dive in. In some areas I wasn’t 100% confident but was sure I could learn on the job in no time (if the prospective employer had such time. But looking at 1 year of advertising? ~ the solution they had in place was good enough for now and could afford to wait before migrating it. Anyway…). I hit upon the idea of preparing myself as if I was to take such a job. As a spin off I decided to aggregate ten “GIS” jobs within South Africa I thought were cool and get a sense of what was commonly being sought.

Add to it, during the beginning of the year I had come across a great post on what an Entrepreneur was and that an individual should be one at their workplace. This was going to be my aim in 2017. I was off to a good start with How Do I Get Started In Data Science from which I had also taken a hint on doing ‘job analysis’. So this year I would list skills I wanted to develop and improve on and work on these through the year. My quick list;

• Improve on my SQL chops . . .SpatialSQL.
• Improve on JavaScript . . . for WebMapping
• Learn Python . . .for scripting.
• Dig a little deeper into PostGRES (..and PostGIS) Admin
• Play more with an online web GeoServer on Digital Ocean or OpenShift.

So I’ll see how that compared with what the market required.

Compassing It

GIS Jobs are variedly titled! For the same tasks and job description you find different banners in use. During this ‘pseudo’ job hunt I quickly discovered that GIS Developers were in demand. I ignored the curiosity to find out more and focus on what I was sure was comfortable with. Just avoiding the small voice in the head saying ‘You must learn software engineering, have some software development experience, learn Java, master algorithm design….’.STOP! The search string for all jobs was simply “GIS”.

I chose the following job postings for the exercise.

1. GIS Specialist
2. GIS Engineer
3. GIS Fascilitator/Manager
4. Head GIS and Survey
5. Senior GIS Specialist
6. Senior GIS Analyst
7. GIS Business Analyst
8. GIS Specialist
9. Economic Development and GIS Data Quality Researcher
10. Business Analyst (GIS)

A Play On Words

To complete the exercise, I aggregated all the text used in the above job posts (skippping stuff like email your CV to..blah blah). The aim was to have a sense of responsibilities given and skills sought.

I plugged the combined text from the ten job posts and plugged them into Word ItOut for a word cloud. Why? The thing just looks nice doesn’t it? Seriously. As an aside I figured the larger sized words mean that they appears more often ~ viz is what potential employees are looking for in candidates. After the first run I found some not so useful words appearing often. I pre-processed the combined text - removing words like - South Africa, Salary, City,…I finally ended up with:

My text from job posts had 1051 words. With a minimum frequency of 5 words, 95 words were displayed on the word cloud.

All Words Count

For overkill I also created a 10 word ‘summary’

10 Word Cloud

Last Word

Interestingly, the technical terms are obviated by words like team, development, support e.t.c. SQL doesn’t feature as I guessed it would. Development and experience are dominant. Employers must be looking for persons with systems/ procedures setup skills (Skills is mentioned significantly too!). These positions are likely non entry level kind, requiring experience. For now I’ll stick to my quick list above for skills development and give less weight to results from this exercise.

#postscript

• This surely is not the best way to do a trend analysis of job requirements. I figure something that crawls the web and scraps job sites by South Africa domain names is the way to go. The method used here is rudimentary and creates a cool word cloud graphic to paste on some wall.
• Results from this exercise didn’t produce the Wow! Effect one would get from a visualisation. It wasn’t what I expected at all. The black box spewed something off what I expected. To see that SQL didn’t make it to the word cloud? Where is database?

A Relevant Tweet - City of CT Alerts

14 Jan 2017

The Spatio-bias

Granted, after a time, one tends to want to explore the place they’re at in greater detail, just to soothe the curiosity itch. For me the preceding sentence is interpreted - “one tends to want to map the area around them”. Data flowing off @cityofctalerts (City of CT Alerts) was a great candidate for such an ‘itch-scratcher’.

The thought of an CCT Alerts App that had some geofence trigger of sorts kept strolling the helm of my mind. How citizens of the city could benefit such an App;

CCT Alerts Tweet: Electric Fault in Wynberg. The Department is attending.

App User’s Response: Let me drive to the nearest restaurant - can’t really be home and cooking at the moment.

Pardon me, I’m not an App developer(yet). So off to . . . Mapping!

Well, in following the format of this blog posts - TL;DR a.k.a - Cut To The Chase !

Baking The Base

I’ve had my fair share of trouble with shapefiles, from corrupted files to hard-to-read field names. So I have since started experimenting with other spatial data formats - spatialite became the playground for this exercise.

I read up on the cool format here and followed clear instructions on how to start off with and create a spatialite database from within QGIS.

Building Blocks

Without repeating the excellent tutorial from the QGIS Documentation Site. Here’s what my parameters where:

• Layer name: alert_points
• Geometry name: geom
• CRS: EPSG:4326 - WGS84
• Attributes:

I chose to start with these three fields and see how everything evolved in the course of the project.

Before I toiled with my points data layer any further. I opened the database using QGIS’ DB Manger. Lo and behold!

I had not made those many tables (or are they called relations?). It must have been some ‘behind-the-scenes’ operation. How the SQLite DB gets to function. I ogled the SQLite version 3.7.17, the current version is 3.10.2 (2016-01-20). I was using using QGIS 2.8.2 -Wien (Portable Version).

To add to the fun I downloaded spatialite_gui_4.3.0a-win-amd64.7z from here. Extracted with WinRar and the eye-candy…

This was just overkill. QGIS is quite capable of handling everything just fine.

Data Collation

Next was the collation of data from twitter. The mention of road and suburb names in the tweets was assurance most of the tweets could be geolocated (well… geotagging in retrospect). I chose to start on 1 January 2016. The plan being to gather the tweet stream for the whole year. I would work these on a per-month basis; February tweets collated and geocoded in March and March tweets collated and geocoded in April and so forth.

Scrapping The Tweet

I thought about data scrapers, even looked at Web Scrapper but, somehow I couldn’t get ahead. I went for ‘hand-scrapping’ the tweet data from Tweettunnel which changed during the course of the year (2016) to Omnicity. This data was input to an MS Excel spreadsheet for cleaning albeit with some serious Excel chops.

I would copy data for a specific day, paste in MS Excel, Find and Replace recurrent text with blanks, then delete the blank rows. Used hints on deleting blank columns and another clever tip on filtering odd and even rows. The tweet text was properly formatted to correspond with the time.

One of the magic formulas was

=MOD(ROW(A2),2)=0

Precious Time

A typical tweet captured 08:17 read

The electrical fault in Fresneay affecting Top Rd & surr has been resolved as of 10:10.

The webtime reflected on the tweet, 08:17 is apparently 2 hours behind the tweet posting time. Adjustment had to be made for the shift. Formula adopted from here.

=Row A + Time(HH,MM,SS)

Some tweets were reports of the end of an event (like the one above). After aggregating response times, 2 hours was found to be the average life time of an event. Thus events/ incidences whose end times was reported, were projected to have started two hours earlier.

Tweet Record

Importing a ‘flat file’ table into a database is easy and having a text file for an intermediate format works wonders. I formatted my spreadsheet thus:

Some tweets were repeated, especially on incident resolution. These were deleted off the record to avoid duplication. I also noticed that the majority of the tweets were traffic related.

Where Was That ?

The intention of the exercise was to map the tweets and animate them in time. The idea of a geocoder to automate the geocoding occurred to me but, seeing the format of the tweets, that was going to be a mission. It appeared tweets were made from a desk after reports were sent through to or an observation was made at some ‘command centre’.

Next up was importing my ‘spreadsheet record’ into the SQLite database - via an intermediate csv file.

Representing time was no mean task since “SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values “(source). So I went for text storage, knowing CartoDB would be able to handle it.

For kicks and to horn my SQL Skills I updated categories field from within the SQL Windows of DB Manager within QGIS. Now this I enjoyed, kinda answered my question why IT person cum GIS guys insist on Spatial SQL!

####Getting Reference Data To geocode the tweets, geographic data is required. Fortunately the City of Cape Town has a Open Data Portal. I searched for road data and got me - Road_centrelines.zip. The site has great metadata I must say. I also got the suburbs data - Official_planning_suburbs.zip

To add to the fun, I loaded the shapefile data into my spatialite database via QGIS’s DB Manager. A trivial process really but, my roads wouldn’t show in QGIS! This was resolved after some ‘GIYF’, turned out to be some Spatial Index thing.

Tweets To Places

Next was mapping the tweets. With the database of tweets, roads and suburbs loaded in QGIS. The task was to locate event points on the map. To relate the record of tweets and event locations, I chose a primary key field of sequential integer numbers. (I good choice? I self queried. A co-worker at another place had retorted “you don’t use sequential numbers for unique identifiers” - I dismissed the thought and continued ).

Here’s the procedure I followed for geolocation:

• Interpret the tweet to get a sense of where the place would be.
• Using the Suburbs and Road Centrelines (labelled), identify where the spot could be on the ground.
• Zoom to the candidate place within QGGIS and with the help of a satellite imagery backdrop, created a point feature.
• Update the alert_point Incident_no field with the corresponding Incident_no in the tweets_collation table.

Spoiler Alert: I kept up with this until I got to 91 points. At this point I decided I just didn’t have time to geocode a year’s worth of tweets. I would still however take the sample points to a place where the project could be considered successfully competed. So I continued…

Attributes to Points

Next up was merging the tweets record and the points geocoded in the sqlite database.

First instinct was to search QGIS’s Processing Toolbox for attributes and sure enough there was a join attributes table tool. Filling in the blanks was trivial.

Catch: To illustrate a point and get the graphic for this blog post representative, I had selected the corresponding record in the points layer. So, when I ran the join tool, only one record, the selected one came up for a result! I had to re-run the tool with no active selections. Additionally the join tool creates a temporary shapefile (if you leave the defaults on) after the join and disappointingly with shapefiles- long field names get chopped. To escape this, specify the output to sqlite file.

I wanted to have my resultant join point data in my SpatiaLite database so I attempted a Save As operation to the database and it didn’t work. So using DB Manager, I imported the Joined layer , Renaming it cct_geolocated_tweets in the process and voila. The joined data with all the field names intact.

I did additional data cleaning, deleting empty fields in my working layer. Table editing of spatilite tables in QGIS didn’t work so I tried again using SQL in DB Manager.

ALTER TABLE cct_geocoded_tweets
DROP COLUMN category;

Still no success. I kept getting a ‘Syntax Error’ message. I would try things in Spatialite GUI. Still after several chops in SQL nothing worked…I kept getting the same Syntax error. It was time to ask for help. My suspicion of no edit support in QGIS was confirmed and I got the solution elsewhere.

Tweets To Eye Candy

The fun part of the project was to animate the tweets in CartoDB. Since I had previous experience with this.I re-read my own blog post on the correct way to properly format time so that CartoDB plays nice with the data.

Timing The Visual

Next was to export the data into CartoDB to start with the visualisation. As I started I figured the following attributes about the data would be required to make the visualisation work.

SpatiaLite stores the geo data as geometry and Carto won’t ingest sqlite data. The go to format was again CSV. In QGIS the data was updated for Lat, Lon fields. A matter of adding Latitude and Longitude fields via a scripted tool in the Processing Toolbox.

Within Spatialite_GUI, I exported the tweets relation to csv for manipulation in Carto.

In Carto I imported the CSV.

Carto provides a simplistic yet powerful ‘work benches’ for the spatial data. Auto magically, Carto assigns data types to fields after interpreting the data it contains.

Next, I wanted to have time fields setup for eventual mapping. For fun - I’ld do the data manipulation in Carto.

I aimed at creating a new field, event_time, similarly formatted like day (with date data type). This would be a compound of (day) + (true_event_start). A second field event_duration = (true_event_end) - (true_event_start). So within Carto’s SQL window, I created event_time

ALTER TABLE cct_geocoded_tweets
ADD event_time date;

ALTER TABLE cct_geocoded_tweets
ADD event_duration interval;

Now to populate the time fields:

UPDATE cct_geocoded_tweets SET event_time = day + true_event_start;

The above didn’t start without some research. Time manipulation in databases is not straight forward. From the syntax highlighting in Carto, I figured day was a reserved word, so I changed my field from day to event_day to avoid potential problems. I had to change event duration to interval inorder to make the sql time math work okay.

ALTER TABLE cct_geocoded_tweets
ADD event_day date;
UPDATE cct_geocoded_tweets SET event_day=day;

Detour: After discovering that time manipulation with SQL is much more complex than meets the eye. I decided to revert to familiar ground - choosing to format the times fields within a spreadsheet environment. And after several intermediate text manipulations, I ended up with event_time formatted thus YYYY-MM-DDTHH:MM:SSZ and event_duration, thus HH:MM. I then imported the new csv file into Carto.

The Visualisation

After inspecting the imported csv file in DATA VIEW view, I switched to MAP VIEW to see how things would look. Carto requested I define the geometry fields - an easy task for a properly formatted dataset.

On switching to MAP VIEW Carto hints that 11 interesting maps were possible with the data! How easy can mapping be?! I chose Torque on event_time ANIMATED. I started the Wizard to see what fine tuning I could do, basing on parameters I had used in my previous exercise. I finally settled for the following:

The Chase

Having flickering points only is not sufficient for other insights, such as looking what the event was about. For that I made another map - where one can probe the attributes. Hover a point to see entire tweet text and Click to see extra information like event duration.

#postscript

• A great follow-up exercise would be to automate the scrapping of the @cityofctalerts (City of CT Alerts) twitter stream. This would quickly build a tweets database for subsequent geocoding.

• Semi-automate the geocoding of the tweets by creating a roads and place gazetteer of sorts. This would quicken the geo-coding processing. Grouping tweets, thus narrowing the search area for geocoding.

• Most likely Transport for CapeTown has something going already but, this exercise’s approach fosters ‘srap-your-own-data-out-there’.

• Google Maps tells of traffic congestions and related road incidents so will also likely have great data horde as relates to traffic information.

Spatial Doodle - On The Everytime Sensor

18 Dec 2016

My Spatial Doodle

A QGIS dump of track data recorded for 102 days, (Start 2014/08/17; Finish 2014/12/04). 143 GPX Files, total of 375 165 points logged, 250 051 weekday points. With gaps in the data too.

Well, we don’t always have time to read: ..so cut to THE CHASE.

Tracing Where

With smartphones, we’re carrying sensors with us all the time and the GPS (Receiver!) one, tickles a ‘geo-itch’. The exercise of tracking oneself is not new, a great case in point being The Drawing of Our Lives by Belasco and New. Some TL;DR [1], [2].

For a while I had OSM Tracker for Android installed on my phone. So when the idea of tracking my to-work and from-work ‘spatial doodle’ occured, I defaulted to it. (I’ve tried alternative phone-gps-loggers but OSM Tracker came tops.). I started logging the moment I embarked on the ‘taxi/ van’ to work and would stop when I disembarked. The logging frequency was 2 seconds since the taxi moved quite fast and I wanted to leave open the chance of pin-pointing places the taxi stopped to pick-up passengers on any given day and possibly some other whatever ‘time analytics’. I would repeat the procedure after work. Weekend recorded data is also included in this exercise.

Serving Time…GPS Time

Yes! Serving and saving time. I discovered the hard way that GPX data doesn’t play very nice! It’s read super easy by QGIS, but try to export it to shapefile to fiddle with the points and oblivion sucks in the precious Time field, formatted thus 2014-08-17T07:13:26. The intention was to merge the 143 GPX files to have one data file to play with.

There are alternative ways to deal with the GPX data and preserve the Time, like gpx2spatialite. With a Spatialite file (database) better SQL operations can be utilised to delve more insight. But the ‘tinkering’ with Python, again, is too much on I-feel-lazy weekends. So I looked on for alternative solutions.

Borrowing Servers

• Merging The GPS Data

  GIYF may seem like an insult when received from someone on social media or such forums, but should be a great maxim to adopt - GIMF. My intention was to compare/ visualise my commute to and from work so I needed a ‘stack of times’ to compare, preferrably in one file. I discovered a tool to merge GPX files online.

  Curtesy of GOTOES, Utilities for Strava and Garmin Connect, I got to use this tool to merge my GPX files and I ended up with a ~ 70MB GPX file.

• Make The Dots Play

  I now believe every ‘geospatial’ person should toil with CartoDB at least once. It’s incredibly powerful but trivially simple. The memory of various tweeter and miscellanea visualisations using CartoDB drew me to it and was sure it could handle time.

  Like magic CartoDB ingested the GPX! Yielding both the tracks and points data, preserving the ‘dear’ time attribute. Just days before I embarked on my exercise, CartoDB had increased storage space of the free plan from 50MB to 250MB! This was super convenient with the 70MB data I had.

  There were lots of fields with null values and these I deleted within CartoDB in DATA VIEW mode. Simple as ‘Delete this column’. I was interested in comparing/ visualising travel time, so had to ignore days (dates). I created a time_a field to contain only the time variable - hour, minute, second. That’s easy in CartoDB - In DATAVIEW, clicking on the fields gives various options, one of which is to create a New Column.

  I initially made the time_a field of type string. I was conversant with extracting parts of text from a field so went for that option. Created several intermediate fields applying variants of

    UPDATE spatial_doodle_points SET dummy_time = left(dummy_time_a,8);
  

  until I got HH:MM:SS. I got a clue from gis.stackexchange to finally properly format the time field

    UPDATE spatial_doodle_points SET time_a = to_timestamp(dummy_time, 'HH24:MI:SS');
  

  I eventually ended up with a field with values such as -1-01-01THH:MM:SSZ. The consistent auto appended day was fine with me as they made all my data points to fall one one day. I was interested in visualising the time.

  Things get interesting when one switches to the Maps View. CartoDB runs a courtesy analysis on the data and gives an inviting “We discovered X interesting maps!” Clicking on SHOW gives suggestions of visualisations on TIME fields.Now how simple can any visualisation app/ service get?

  Selecting the VISUALIZE tab takes one to the mapping interface.

  Select Wizards and there’s an option to have Torque visualisations. I finally settled for the parameters as shown after several iterations.

Here Goes

Insights

From the visualisations I could make the following deductions:

• The data visualised is inclusive of weekend doodles.
• I didn’t take the same taxi consistently every morning (06:25 - 07:45) or the taxi was not consistent in its arrival(departure) time. This is evident from the way the dots ‘depart’ from the boarding place.
• There is general consistency in the route the taxi(s) took in the morning. The contrary is true for transport used from work.
• To an extent, traffic flow can be deduced from the ‘to-work’ dots. After a certain point, the dots are evidently ‘faster’.
• On the ‘from-work’ component (post 16:00), there is a larger variation in routes used by the taxi(s). A clear evidence of rat racing as the main routes are avoided.
• There is a section of the main road to work that has a dedicated bus-lane. This however cannot be easily seen from the point data. Track data reveals this better.
• An even more interesting visualisation would have been a race-to-work by day. Say 5 day data, symbolised by day to see the variation between days. Currently CartoDB Torque visualisations accomodate only one dataset.

#postscript

There is on an active repo on GitHub, a CartoDB dockerfile. A goldmine for anyone who wants to explore CartoDB in all it’s glory in one’s palm.

Anything For A Map - Part 0

10 Dec 2016

On The Heels of The Pioneer Corps

Mapping the footprints of the men who faced the thicket, built drifts across rivers, played the debut soccer and rugby matches in a country.

Background

I am enthused by maps and more so good cartography. I will scrounge any document just to map the two or more places mentioned there. Perchance while working with an old topomap of Zimbabwe, I bumped into small crosses that represented features of historic significance. Of special intrigue were locations identified as Fort and Laager (pictured above). As I Google-dug some more on these, I came across an article, Pioneer Forts in Rhodesia 1890-1897. My interest grew as I read a gripping account by Horste.(The Historic Publication) He chronicles how in 1890, the Pioneer Column’s escort of British South Africa Company’s Police progressed into present day Zimbabwe. I had read and heard about this in my history classes, that was then, I am all maps now!

BUSY ?:~ Cut To The Chase.***

Why?

• Well, I’m am oft looking for something to map (and the narrative by Hoste is too good to waste!)
• To have a ‘cartographic-perspective’ of how the historic route impacted the development of existing roads and towns.
• Get a ‘feel’ of the topography the column faced.
• Tinkering!

Get There Fast

1. Vector Data Preparation

2. Raster Data Preparation

3. Map Creation

4. Map Serving

5. Challenges/ Observations

6. Results

My conception of the map was a route, points and relief map that stood out, with a subtle backdrop of existing roads and features. I had bumped into carto works with tilemill, in particular Toner. I had to ‘broad-read’ on various carto projects. For starters, I had an undated, georeferenced 1:250 000 topography map of Zimbabwe(Rhodesia). I opened a blank QGIS project, which had the following CRS settings. This was the basis of other work to follow:

1. Vector Data Preparation

Key Points

• (route_points.shp): The first task was to create spatial data for key points. Sources of information was the topo map and the narrative by horste. Editing in QGIS is pretty straight forward. This route_points.shp shapefile would contain three fields: name, type, notes, arrival. These fields I came up with after several passes of the narrative. Name would be used to label the point, type to style (in Tilemill) , notes for additional information on the point and arrival to capture the date the Pioneer Corps arrived at the ‘point’.

Route Plotting

• (route_track.shp): Route plotting logic was to join the points with a line. To refine the route, I took a hint from the narrative - avoid the hills and follow watersheds.

Route Buffer

• (route_50km_buffer.shp): Within QGIS Processing Toolbar, the next operation was to buffer an area around the route. I would use this extent to map the route and generally clip other data for the project. I decided on a buffer radius of 50km. A guy named Selous had ‘pre-scanned’ the road ahead and gave indication as to where the column should march through. Additionally, I intended to use terrain data with 30m spatial resolution and 50km would give me ~1700 pixels on either side of the route. This should provide good ‘terrain-context’.

2. Raster Data Preparation

I am a fan of relief maps. I could just spend hours just panning a good representation of terrain so this stage would give me great pleasure! For this section I intended to follow these tutorials - Working with terrain data and Using TileMill’s raster-colorizer. I however ended up emulating this in QGIS. Which gave excellent results!

Relief Map

• QGIS handles raster data easily. I was going to use the SRTM Version 3, 30m DEM data set. The project area (route_50km_buffer.shp) was covered by 14 elevation data tiles. In QGIS I merged the tiles to form one dem tif. [Toolbar] – Raster – Miscellaneous – Merge, to come up with (route_dem_raw.tif)

• I clipped the merged elevation model with (route_50km_buffer.shp), this resulted in a ‘along-route’ tif viz (route_dem_50km_buf.tif).

• In line with this tutorial, I was to create a hillshade first. My machine is setup with OSGeo4W so running gdal commands was supposed to be a breeze. so I ran

  gdaldem hillshade -co compress=lzw route_dem_50km_buf.tif route_dem_hillshade.tif
  

• A relief map was next. Firstly I styled (route_dem_50km_buf.tif) in QGIS: . It turned out the 15 equal interval classes and a spectral colour map gave an unsatisfactory relief map. Pleasantly, QGIS permits the export and import of colour ramps. So I got busy editing the 15 interval ramp I had generated - a painstaking task, but I prevailed. So with the colour_ramp.txt, which I had also checked in QGIS, I ran

  gdaldem color-relief route_dem_50km_buf.tif colour_ramp.txt route_dem_colour_relief.tif
  

• Next up was slope shading which is a two way procedure. First

  gdaldem slope route_dem_50km_buf.tif route_dem_slope.tif
  

• Then secondly, the actual slope shade

  gdaldem route_dem_colour_relief.tif -co compress=lzw route_dem_slope.tif slope-ramp.txt route_dem_slopeshade.tif
  

  As explained here, make sure to use the slope.txt file so that the “color-relief command can use it to display white where there is a slope of 0° (i.e. flat) and display black where there is a 90° cliff (with angles in-between being various shades of grey).”

Just for fun, I loaded the three elevation data files (tifs) created above into QGIS and styled them with the transparencies indicated in the Tilemill tutorial, viz colour_relief: 10% transparent, hillshade: 60%,slopeshade: 40%. The result was astounding!

3. Map Creation

Osm Toner

To provide cartographic contrast and temporal context I wanted a subtle background and osm toner seemed the best choice. The procedure is outlined succinctly here.

• First , I got a subset of OpenStreetMap data relevant to my project from BBBike.org from which one can clip data to custom boundaries. (Aside: I only discovered this site while hacking this project, indeed giyf! )

• Next was loading the OSM data into a PostGIS database. I already had a Postgres Installation. So in PgAdmin III, I created a database and named it osm. After that the actual loading of the data had to be done. More information on running osm2pgsql can be found here. For this I downloaded cygwin-package.zip and extracted to a folder. After saving my custom OSM data (pioneer_route2.osm.pbf) to the same folder as osm2pgsql.exe. I ran the command and got error: After some Googling and amends, another error After this, I had to download default.style from the web. Re-running the command again, now with the style available…another error: , I tried to address that with some Postgis chops. . That also did not give solace. I opened the file in Notepad++ and commented out line 155 to 160 of default.style in the text editor. I figured this wasn’t that important for my intended use - map styling. .

  Tilemill

• Running config.py : In accordance with the tutorial, I edited the python file accordingly and successfully ran make.py. My project was automatically created. Unfortunately several things went wrong with this setup - among them, having to download a ~300MB file (I have access to very limited bandwidth).

• Since I had some exposure to Tilemill, I had an idea of how stuff was structured so decided to take the ‘brute-force’ route to having a Toner-Template setup. I resorted to modifying the DC-Streets project (which is auto-setup with an installation of TileMill):

  • I prepared my custom osm data (previously loaded into PostGRES ) to match that on the DC-Streets project. The (shapefile) schema had to be exact - viz field for field so that styling and labelling would be smooth.

  • Now what remained was to tweak the major CartoCSS colour styles using gray scales only. For this I simply edited the highway.mss and used ColorBrewer to more accurately pick greys that would represent the roads and color-hex to decode the ‘hashed’ alpha-numeric characters.

More Styling in Tilemill

• Creating the terrain map

  • Using the raster layers I had prepared in QGIS in Stage 2. above, I loaded them in TileMill and styled them as I had in QGIS. Amazeballs! This would serve as the base layer for the other data except at higher zoom levels viz > 13. (I concluded the relief display range from playing with the visualisation in TileMill).

• Adding detail - The Route and Stop Points

  • Adding the Column route was straight forward as I had styling in highway.mss to emulate.

  • With the Stop points I had a little reading of the manual (aka Google) to do as the *open-streets-dc templates did not include any points styled. I wanted some fancy symbolisation. Through trial and error I got satisfactory results eventually for the various zoom levels.

  • Rivers and streams were essentially lines and polygons and I had the template as a starting point.

• Labelling

  • My Intention was emphasise history and ‘background’ the present. So I made my labels for the column landmarks coloured. Luckily the place names in open-streets-dc were already black and white. So I didn’t have to touch those.

  • A serious challenge was to avoid label conflicts and also at the same time display landmarks and labels at the same time. Again reference to the manual saved the day. Used text-dx: and text-dy: extensively in the CartoCSS.

Exporting The Mapwork

This stage was the most exciting for me - who doesn’t want to see the fruit of their labour?! TileMill gives you a preview of what the export will be like in the WYSIWYG manner so there’s no crossing of fingers to what the output will be like, just a demand for patience as the export runs.

After two unsuccessfully runs I had to explicitly define the RCS for all the layers in my project.(those were set to Auto-Detect). I defined my export parameters - MBTiles, Bounds, Centre - saw the software estimated 8 hours to completion of the run. Luckily it was night so I went for some shut-eye.

Come morning there was my 237MB MBTiles file!

I fired up QGIS to take a look. I recalled QGIS supported these. The result was not so impressive - had a grainy feel to it. TileMill had rendered it smoothly so I knew it was not my data. I remembered a Portable MBTiles viewer. I couldn’t quite recall the name to I consulted my GitHub account (anything I think might be of interest to me I put on the Watch list ) - there it was TileStream Portable.

I loaded my MBTiles per instruction and my pannable map ….

So one can actually give a friend, client a pannable map on a USB Stick loaded with the MBTiles being served by the TileStream Portable. (*I’ve read on the inter-web companied serving imagery as MBTiles *)

4. Map Serving

*I will not go into detail describing step-by-step this part of my exercise as it detracts from the intention of this blog post. Summary will only be given since with Stage 3 above, the Mapping exercise would be complete.

How-to serve the resultant map was largely inspired by the following blog post which I emulated closely - Setting up a Cloud MBTiles Server with Benchmarks (You can also find the screencast in my Github Repo).

Leaflet was used as the Mapping Client. I had had a historic stint with The Bootleaf Template and I started there - with a clone of that repository. I spend considerable time in SublimeText 3 editing the template to suit my needs.

5. Challenges/ Observations

Some points to note in the representation of wok done here:

• I had to deal with data of varying spatial scales - Topo map, Elevation data, Openstreetmap data, etc.

• For some stretch of the route between explicitly mentioned stop points, the narrative by horste was not sufficient to accurately approximate the route the pioneer corps took.

• There is error inherent in the the use of Laagers plotted from the topo map since no temporal information was given. These could well have been apart historically.

• Laagers are a temporary feature hence their representation must be interpreted as being virtual, temporally obsolete from point to point. (The corps were moving forward and ‘broke laager’ as they progressed!)

• Notice that after Fort Victoria, the track becomes dual. According to Horste “We now made two parallel roads, about fifty yards apart, as it had been decided to have a double line of wagons, instead of the long cumbersome single line that we had had up to then.”

• Gave water bodies prominence (represented in colour) - little might have changed since 1890 just as is the case with terrain. Not withstanding the construction of dams since then.

6. Results

I have served all the data relating to this project to a GitHub repository.

You can link directly to the resultant map here - The Pioneer Column Map

#postscript

• I traversed many ‘technology domains’ in this exercise and it was fun. That has helped me know a comfortable bit of everything. Linux Server administration! Database Server Management - remember my PostGIS instance?
• After my experience with TileMill on this project, I decided to move to (Mapbox) Studio. No need to remain in ‘legacy mode’ - I am a geohipster!
• TileMill may be ‘old’ and out of active development but it does pack a punch! Additionally you don’t have to ‘connect’ to MapBox to use it like its successors - MapBox Studio and MapBox Studio Classic.