ODX’s grant have us the resources we needed to build our data management solution.

To learn more about ODX’s programs and services, visit cdmn.ca

ThinkData is an open data startup that allows developers, businesses, and citizens to access high value data in standard useable formats.

Reach out to us for more information.

Who is ThinkData Works was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Canadian Government is the largest buyer of local goods and services with estimated $13B spent in the 2016–2017 fiscal year only.

Federal agencies are required to disclose contracts over $10K issued on behalf of the Government of Canada (more about contracting policy). Here at Namara we have put together all historical reports published individually by the 47 largest departments and agencies, starting as early as 2004. Our team has standardized, refined, and consolidated disparate records to map them to an ideal schema in an aggregated and easily queryable data set. It has removed duplicate entries, extracted various attributes from the comments (e.g. solicitation procedure, former public servant, etc.), and adjusted contract values where specified.

Click on the API Info tab to learn more about all available columns and their corresponding types.

Since most of the agencies publish their amendments as individual records, calculating any aggregates would result in overblown numbers. Hence, we have collapsed all of the relevant records to the same original contract, eliminating duplicate amendments and updating respective contract values. All of the contracts were also mapped to 23 parent agencies and assigned an economic category and a subcategory as per the Object Codes classification. For the purposes of this analysis we are only going to focus on the data set with the collapsed contracts and limit our scope to 2016–2017 FY.

First things first, let’s head to Namara and apply corresponding filters on the Contract Date Earliest:

This should result in some 55k contracts. Then click on the Download tab, Prepare filtered data set button, and finally on the Download button on the popup:

Voilà, you have a filtered version of this data set exported as a CSV file, and it’s ready to be imported to any of your favourite tools! In this post we’ll be using Tableau — the champion of data visualization and explorative analytics that has become an industry standard among journalists, analysts, financial experts and pretty much anyone data savvy.

First, you will need to make sure that you have a valid Tableau account for either a desktop or a web app. You can try it for free or use a free version with publicly hosted data sets. We will proceed with the desktop app here.

To load the file, click on Connect -> Text File, and provide the path to a target CSV. And then click on Update now, if prompted:

You will see that Tableau has parsed the data set and has automatically assigned data types to every column. At this point you can change it, as well as rename and hide columns, and create new ones using existing. You are all set for a deep dive!

Let’s start with a high-level overview and see what agencies are procuring the most, both in terms of volume and dollar amount. Let’s create a new sheet “Parent Agencies” and drag Parent Agency dimension to Rows shelf and Number of Records, Current Contract Value and Amendment Value to Columns shelf. This will render three horizontal bar charts. And if you click on the Number of Records label under the first section, the data points will be sorted based on the overall volume. We can see that the top procuring agencies are pretty much aligned in terms of number of contracts and overall contract value, and that the largest spenders are: National Defence, Public Safety, Public Works, Fisheries and Oceans, and Environment and Climate Change.

If you want to limit the parent agencies, to, say, top 5 by number of contracts, click on a drop down on the Parent Agency pill, select Filter…, switch to the Top tab, and pick 5 by Number of Records.

What is interesting is how Public Works has quite a large dollar amount in amendments compared to other top spending departments. Let’s create a new sheet called “Projected vs Actual” and look into individual contracts for Public Works. To make it easier to spot the contracts that were amended by a lot, we will plot original contract values against the current ones.

To do this drag Current Contract Value to the Columns shelf, click on the pill drop down, and select Dimension. Drag Original Contract Value to the Rows shelf, click on the pill drop down, and select Dimension. Now drag Original Contract Value to the Columns shelf as well, and click on the drop down and select Dual Axis. Right-click on one of the horizontal axes and select Synchronize Axis to make sure that we have a perfect 45 degree line. Click on the Measure Names shelf -> Edit Colors…, in case if you want to change the default color of this line (I have it set to grey). Right-click on the plot ->

Trend Lines -> Show Trend Lines… and pick SUM(Original Contract Value) to extrapolate this trend line beyond the min and max. On the shelf Marks pick SUM(Original Contract Value) and update its opacity to 0%. Change the plot type to Circle for Current Contract Value. Let’s update the way tick values are showing up. To do this right-click on the top horizontal axis and select Format…. In Scale -> Numbers choose Currency (custom) and set decimal places to 0, and units to Millions. Repeat the same procedure for the bottom axis. Much better!

To limit data points to Public Works contracts only, drag Parent Agency to Filters shelf, and select “Public Works and Government Services Canada”. Alternatively you can select all parent agencies or those you are currently analyzing. To show the filter, click on Parent Agency pill on the Filters shelf and tick Show Filter.

We are pretty much set. Let’s add more information to every data point. To do this, drag Vendor Name, Economic Descriptor, Original and Current Contract Values to the tooltipicon on the Marks shelf. For both dollar values set their type to “Attribute”. If the attributes are not showing up when you hover over a data point, then double click on the tooltip icon on the Marks shelf and edit the template accordingly.

Last step is to color-code the contracts based on an economic category specified for procured goods/services. Move Economic Category to the color icon on the Marks shelf. Done!

Now all of the contracts that appear below the dotted line have amendments that have increased the overall contract value. Points above it ended up costing less then originally reported, and for those on the 45 degree line, the original and current contract values are the same. So now when analyzing certain contracts, you can easily get the information about the vendor, contract value and the type of service just by hovering over the point of interest. You should have a graph that looks similar to this:

You can also access the full row that corresponds to a certain point by clicking on the point of interest -> View Data icon and switching to the Full Data tab.

By simply exploring the plot, you cannot see a specific culprit, because the majority of service-related contracts ended up having slightly higher cost.

Now let’s take a look at the data from a different angle. Let’s determine what the top vendors are both volume- and cost-wise. For this we will be using a treemap. Drag Vendor Name to the text icon, Current Contract Value to the color icon, and Number of Records to the size icon — all on the Marks shelf. This should render a pretty crowded treemap with quite a few vendors. To de-clutter it, we will set a minimum number of contracts to 20: click on a drop down on SUM(Number of Records) pill and set the lower value for the range to 20. Much better! You can adjust it, while exploring how the pattern changes across different departments: feel free to add a filter based on Parent Agency, just like we did before. To provide more information, add Number of Records and Current Contract Value to the label.

If you want to see a breakdown by economic category, just add it to the color and Current Contract Value to the size:

These are obviously just starting points, and this data set has so much more to reveal. So definitely make sure to explore it firsthand!

Here are some great resources for learning how to use Tableau efficiently:

• training videos
• Forums
• Blog
• and solutions gallery

Also check out our other data sets that cover federal expenditures: Canadian and US procurement. And feel free to reach out, if you have any questions.

Happy analyzing!

Namara is the data management platform that governments, banks, and some of the world’s largest companies rely on. We designed Namara to be the most secure and collaborative way to access, manage, and integrate the data that powers your business. Sign up free today.

Connect with us on the app, or contact us here to get answers to any questions.

Tutorial: How to Analyze Namara Data Using Tableau was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

Mapbox is a powerful open source platform that allows users to generate maps displaying exactly what they want, and none of what they don’t. It’s easy, powerful, and robust — it reminds us of Namara that way. If you do any geospatial data visualization, you probably already know and love Mapbox.

What you might not know is how easy it is to use Mapbox to visualize your Namara data.

STEP 1: Get the Data

Geospatial data is your friend here, so start by finding yourself a data set that includes latitude and longitude. We’ll take a pretty basic data set for simplicity’s sake in the example: Farmers’ Markets in Ontario, Canada. I’ll use Namara to give me only the columns I want — the market name, latitude, and longitude. From there, I’ll select ‘prepare the filtered data set’ in the Download tab. This generates the data set limited to only the selected columns. Namara will show a notification when the data is prepared and ready for download.

STEP 2: Think Inside the Box

Once the data set is downloaded, head over to Mapbox. Once you’re there, create an account (or sign in if you’re already a user) and from the Dashboard, click Studio near the top right. Just below that, you’ll see Styles, Tilesets, and Datasets. You’ll actually work in reverse order — click Datasets first and upload the data set from Namara. Once complete, you have the opportunity to edit the data set if necessary, but since we filtered it earlier, our data is showing exactly the way we want. We can get straight to the next step by clicking ‘Export to tileset’ from the right side menu.

STEP 3: Getting on the Map

Clicking on the Styles tab beside Tilesets and Datasets, you’ll get the option to ‘Create a style.’ Here’s an opportunity to get creative. There are many, many styling options, and you can personalize your map however you like. For the purposes of this demo we’ll stay with something pretty basic. I’ve chosen the first theme, Streets. The first thing you should do is centre and zoom the map on a location that suits the data you’re presenting. That way you’ll see what you’re working with as soon as it’s imported.

Next, import the tileset containing your data. Click ‘Add layer’ in the upper-left corner. The drawer that opens has a section for Source which says ‘No tileset, click to select’ — find the tileset you created in step 2, then click ‘Create layer.’ Your data should be visible on the map! Click ‘Publish’ and you’re ready for the next step.

STEP 4: Going Places

Now that you’ve got a map with data points represented on it, it’s time to use Mapbox GL to put it to use. Mapbox has very clear documentation for this step. Basically, include the necessary JS and CSS in your HTML <head>. Then copy the following code block into the <body>:

<div id=’map’ style=’width: 400px; height: 300px;’></div>

<script>

mapboxgl.accessToken = ‘[Your API Key]’;

var map = new mapboxgl.Map({

container: ‘map’,

style: ‘mapbox://styles/mapbox/streets-v9’

});

</script>

On the Styles page in Mapbox, each style you’ve created has a ‘Share & use’ button. Selecting that will bring up a dialog box with Share or Use at the top. Under use, you’ll find your access token and a unique style URL — replace those with the values in the example code above, and your map should be right in front of you.

Wrapping Up

As you can see, it’s easy to get data showing on a map — but Mapbox’s abilities reach much farther than simple plotting. Their documentation explores everything in minute detail, and their tutorials are great for getting a handle on specific manipulations and tricks. Watch for another post from us soon demonstrating how to get Namara data on Mapbox through our API.

Namara’s capabilities are broad, too. We integrate easily with Tableau, Salesforce, and almost anything in between. Dive into our blog posts, or reach out to find out how Namara can make your life easier.

Tutorial: How to Use Namara Data in Mapbox was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

What’s really behind this post is the exciting news that Namara API now supports OData Version 4.0!

Since each data set is offered as it’s own OData Service, you can use the query capabilities of any OData-compliant client to view external data in real time. Though this post will focus on getting up and running with Salesforce, you can use the service endpoints mentioned below for comparable .NET or SAP services.

STEP 1: Retrieve your Namara API key

After logging into your Namara Account, click on your user icon in the sidebar (bottom left of the window) and select Account Settings. Then click the API Key tab.

You will need to create an API key if you have not already done so. Otherwise, retrieve the one that you have already created and paste this somewhere where it can be accessed. You will need to use it a few times.

STEP 2: Find the data set you would like to add

Navigate to the data set of interest. In the top bar, click on the API Info tab, to view integration information. Copy the contents of the API Endpoint field into somewhere where you can access it later.

STEP 3: Adding a new external data source to Salesforce

On your Salesforce Lightning dashboard, access “Setup” in the top toolbar.

Either enter “External Data Source” in the Quickfind bar, or navigate Data > External Data Sources in the sidebar under the “Administration” header. Then click the “New External Data Source” button.

In the form, fill out the following fields:

• External Data Source: give this a name to refer to the data set that you are adding a view for.
• Type: Salesforce Connect: OData 4.0 (Please note that 2.0 is not supported by Namara)
• URL: paste the API Endpoint url that you copied from before, then add “OData.svc/” to the end, so that it looks like this:

HTTPS://API.NAMARA.IO/V0/DATA_SETS/:DATA_SET_ID/DATA/:VERSION/ODATA.SVC/

• Identity Type: Per User
• Authentication Protocol: Password Authentication
• Administration Username: Set this to whatever you want. This field will not be used for verification and will be ignored.
• Administration Password: Paste your API Key here.

The form should look like this:

Now click “Save”. You will be taken to a screen to review the details. If you are satisfied, click “Validate and Sync”.

Salesforce will access a specific metadata endpoint for this data set and model the external data as Salesforce objects. If successful, you will be shown a table of all External objects found. Only the table named “Data” is necessary for viewing. After selecting that, click “Sync”.

If you go back to the Setup sidebar and view your “External Data Objects”, you should see one called Data. DataSets and Versions only store metadata and aren’t required for viewing. You can edit this object to enable reporting. I recommend changing the label to something more descriptive like “HamiltonSchool”, but do not change Object Name.

Click “Save” when you are finished.

STEP 4: Grant user permissions for viewing

In the sidebar, navigate to “Profiles” and find the profiles of whatever user will be accessing this data. Click “Edit” for the profile and ensure that External Object Permissions are set for this external source.

Then under Enabled External Data Source Access, click “Edit” and grant access for your external data source.

Next you need to store the access credentials for this data set so that the API Key can be used on subsequent access calls. To do this, click on your user icon in the Top Right corner and then click “Settings”. You will be taken to another page with a Sidebar & Quickfind text field. Click on the tab labelled “Authentication Settings For External Systems” and then click “New”.

Fill out the form with access credentials for a user.

STEP 5: Create a tab

In your Sidebar Quickfind, search for “Tabs”, then create a new Custom Object tab. Select the External Object by the label that you added in Step 3 and add an icon style. Click “Next”. Set visibility for Tabs however you would like.

STEP 6: Create a view

Navigate to the tab that you created in Step 5. When you click “Go!” you’ll notice that these aren’t the fields you are looking for.

NOTE: We do not give API-level access to a single row in a data set, so accessing Display URL will give you an error.

Create new view

You will have to create a new View and use $filters and $select statements in order to pull out the data. Start by clicking the “Create New View” button.

Create whatever query you would like on the data. Here, I am trying to view all high schools with enrolment over 500.

When selecting fields to display, ignore _row_id — it’s just a unique key we use so that the OData adapter can tell records apart.

Since there are a maximum 15 fields for a view, it’s more manageable to create several meaningful views rather than a single one that attempts to read the entire data set.

Now when you click “Go!” you will see the fields from your view, and be able to step through the paginated results.

There you have it! I apologize for switching between the old Salesforce interface and the new Lightning dashboard, but I was informed that the new lightning dashboard did not support filter views (yet).

Finished project

Please get in touch if you’ve done something neat using Namara & OData. We would love to hear about it!

Tutorial: How to view Namara Data on Your Salesforce Dashboard was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

A little more than a year ago, we published an update to let Namara users know that the Namara API supported OData Version 4.0.

At the time, each data set was offered as it’s own OData Service and could be queried by any OData-compliant platform.

Since then, we’ve revisited our integration with Salesforce to make it more customizable.

Today, we are pleased to announce that we have improved the way that users can view Namara data in real time on their compatible .NET, SAP or Salesforce-based services. We have utilized our own platform’s Project functionality to allow users to quickly load in more data sets without having to perform extra authorization steps.

As an example, we will walk through the Namara integration on Salesforce.

STEP 1: Retrieve your Namara API key

After logging into your Namara account, click on your user icon in the sidebar (bottom left of the window) and select Account Settings. Then select the API Key tab.

You will need to create an API key if you have not already done so.

Have an open document available and paste your API key in there. You will need to access it a few times as well as keep track of a few other values, so keep it on hand.

STEP 2: Create a new project or use an existing project

For this example, I’m going to create a fresh project and add some new data to it. Below, I have created a sample project that I will use for this walkthrough.

If you already have a project that you would like to view, feel free to use that instead.

STEP 3: Put some data in your project

For this example, I’m going to use the Heritage Properties data set published in the Hamilton, Ontario open data portal. I start by browsing open data in the Marketplace, and searching “Heritage Properties Ontario”. I can quickly add these to a project by expanding the data set preview and selecting my new project.

When viewing your project, your url should match a pattern like this:

https://app.namara.io/#/organizations/<organization_id>/projects/<project_id>/list?...

Extract the values for organization_id and project_id from the url and paste them into the document where you have pasted your API key. You will need these later.

Data set result page with project drop-down

STEP 4: Turn on OData for my data sets

To start out, I want to make sure that I know the current versions for both of my data sets. I can find this out by clicking on the data set card to get to the table view, then clicking on the API Info tab.

I will make note that my data sets are at versions “en-0” and “en-1” respectively. Because Salesforce requires a static set of properties to view OData sources, we must pin our OData adapter to a specific version of a data set. (refer to our API Docs for more information about data set versioning.)

Now we need to set up our OData meta attributes that Salesforce will use to build external objects. Navigate back to the Project view and click on the cogwheel icon for each data set to edit the Project Item settings.

Once this is set up, you can quickly check to make sure that your data is being registered by the Namara OData adapter. Using the IDs that I’ve saved from earlier, I can view in a browser:

http://api.namara.io/v0/organizations/<organization_id>/projects/<project_id>/OData.svc

At this point, I should be able to see an XML document detailing the data that I’ve activated for viewing.

We can see from the metadata manifest that it’s registering both Hamilton and Brampton houses as entities and is picking up their properties. It’s also included our activated Project Items as metadata for the Project OData service.

STEP 5: Adding Namara as an external data source

On your Salesforce Lightning dashboard, access “Setup” in the top toolbar.

After arriving at the settings page, either enter “External Data Source” in the Quickfind bar, or navigate to Data > External Data Sources under the “Administration” header.

Click “New External Data Source”, and complete the form for your project.

Here, I have called the data source “Namara Heritage Homes”. I have selected OData 4.0 as my connector type (note that OData 2.0 is not supported by Namara).

The url that I provide is the same as the metadata manifest URL that we built back in step 3. Adjust the other parameter settings to match what is shown here.

Since we are authenticating by API key, we set authentication by User level. The username that you choose does not matter but the password field must be your Namara API key.

Once you have completed this form, click “Save”. You will see the “Validate and “Sync” button on a successful save, but will be unable to complete this action at this time.

STEP 6: Authenticating user for external data source

Go to your personal settings and add a new Authentication for the external system.

In the sidebar, navigate My Personal Information > Authentication Settings for External Systems. Click on the “new” button and set up the authentication for the new Namara data source that you’ve created.

Search for the user and set up their credentials using the API key. Please note that this assumes that you are the user and you are using your own API key. This can be set up on behalf of another user who is a member of your Namara Project. It can even be set up using their Project API key. (To learn more about how Namara lets you collaborate on data projects, check out our Features Pages.)

Once you’ve finished authorizing this user, save the form. Navigate back to our External Data Source by clicking the link provided.

STEP 7: Creating external objects using the data set

On this page, select “Validate and Sync”. After a moment, Salesforce will have received and read the metadata document. We are now provided with Data Sets that we can view as Salesforce External Objects.

Now select the Data Sets that you would like to view and click “Sync”.

If I go to my external objects view I should see that these have been added.

STEP 8: View data on my dashboard

Go back to the first settings/setup panel that we were on. Search “tabs” in the quickfind bar or navigate to User Interface > Tabs.

Create two new custom objects tabs for our two house data sets.

Now when we go back to our dashboard, we can see two new tabs, “BramptonHomes” and “Hamilton Homes”. We will create a new view for our HamiltonHomes data set. In the settings for one of the tabs, under the settings cog, create a new list view.

We will call this view “All Houses”. Initially, it’s not going to be a very interesting view.

We are going to edit our list view to show data that we care about. Under the gear cog, select the “Select Fields To Display” option. This should give us a small window allowing us to view specific fields for this list. I would recommend selecting all except for Display URL, External ID and __row_id. For the sake of a nice table, we are just going to select a few fields.

Which gives us our final table view.

I can scroll to see more, add any filters and sorting options, and use any other Salesforce functionality.

Want to see more data?

If I were to add additional Heritage Home data sets to my project, I would only have to activate those for OData inside of the project, and then re-synchronize the data source to pick up the new external objects.

ThinkData Works proudly works with governments to help them release high-value data, and their data management platform Namara connects users to every open data portal in North America.

Tutorial: Using Namara Projects to View External Data on Your Salesforce Dashboard was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

Namara is a platform for accessing external data using a standardized format and familiar API.

Our first iteration of this involved our Data API — an endpoint for viewing, aggregating, filtering, and exporting data sets.

Since then, both the platform and the data on it have changed.

We value the notion that data is not meant to live in isolation, and that new insights come from the freedom to mix data sets together. If we wanted the Namara catalog to offer these same insights, then we needed to enable users to start blending data sets together on their terms.

We are happy to announce a new platform feature which we call NiQL (Namara.io Query Language — think ‘nickel,’ not ‘nyquil’).

This is a SQL-like query language that will allow users to browse the Namara catalog as though they were submitting queries to a SQL database. Like our Data API, users query with their Namara API keys or organization tokens to filter, aggregate, and export data. NiQL makes all of these features possible on joined data sets.

Creating NiQL was important for us. Our company was built on the idea that data should be easily accessible — that accessibility needs to reach as far as possible in order for our users to get the most out of our platform. The NiQL Query API supports a marked-up SQL syntax, so database users should feel right at home. Like the Data API, the Query API supports CSV, JSON, and GeoJSON, so users can operate on results in their preferred format. The Query API features support standard SQL operations as well as expose a wealth of PostGIS geospatial functionality.

The ability to not just access but manipulate data using a query that’s both familiar and formidable opens up new possibilities for our users, and means our data is as flexible in our app as it is outside of it.

Please check out our documentation on the Query API to:

• view how to get up and running with the Namara API;
• learn how to access your API keys and other access tokens, as well as use them to make requests;
• view the NiQL Query API functionality or see how to interact with our previous Data API; and
• discover the expressiveness and functionality of NiQL;

The public Namara API will enforce some limitations on query features in terms of number of subselects, number of permissible joins, and limit result maximums per query. Please get in touch with us if you are interested in advanced querying capabilities. We will be rolling this feature out to the whole catalog over time. If you receive an error about a certain data set that does not yet support the Query API functionality, please get in touch so that we can activate it for you.

Interested in adding support for any features? Having issues connecting? Finding bugs? Don’t be afraid to reach out to us, here on our contact page or with the live chat inside of Namara.

Why We Created Our Own Query Language was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Data

Police departments are starting to launch open data portals (Toronto, Fort Lauderdale, South Bend, and most recently, Minneapolis, to name a few…). This is excellent news. Not only does it open a window to a historically opaque part of the criminal justice system, it also has the potential to provide both the government and an engaged citizenry with the information they need to start solving real problems facing real people.

Take the traffic accident report published on Québec’s provincial open data portal. The report provides detailed information on the day the accident occurred, what kind of road it was on, who was involved (cyclists, pedestrians, etc), how many were injured and how severely, etc. What it doesn’t provide are the coordinates at which the accident happened.

The Problem

This is one of those situations where data visualization is important. I can easily query the data to see how many of the accidents happened on days when it was raining, or how many accidents happened between midnight and 4:00am, but until I can plot the data on a map, I’m only seeing part of the picture.

Anyone with a bit of time can geocode physical addresses, but in the case of the accident report the physical address that’s provided isn’t a route, street name, unit, city, and postal code. In fact, the address information doesn’t initially make a whole lot of sense.

There’s a field for the civic address of the closest landmark, but it’s 62% empty. Since these are traffic accidents, there’s always a road name, but they’re frequently recorded as (and I’m translating here) “close to the Burger King on Demasse st”, or something similar. Next to the route title, there’s a column named tp_reprr_accdn which contains the values 0, 1, and 2.

None of this helps me out if I want to plot these accidents on a map.

The Solution: Refinement and Geocoding with Unity

Metadata is critical.

The SAAQ (Société de l’assurance automobile du Québec) maintains very good information to go along with the accident report, but unless I want to have a pdf explaining what I’m looking at while I’m checking out the data itself, I need to find a way to plug the information from the metadata into the data set itself.

From looking at the metadata (and a few years of French immersion) I can tell that the column cd_etat_surfc signifies road surface quality. The values in this column are the numbers 11–20 and 99. Using the extract node on Unity, I can look for those numbers in the original data set and change them to their corresponding description in the output data (“14” becomes “Sand/Gravel”, etc). Since these values are used internally at SAAQ, I’m confident that every time the data updates they’ll use the same set of values, which means I won’t have to worry about validating these descriptions every time the data set updates.

Looking through the metadata, I also learn that the column named tp_reprr_accdn relates to the type of reference point on the road. “0” means “not specified”, “2” means “other”, and “1” means intersection.

This is important information for me to have. I didn’t want to geocode based on a street name alone, because I couldn’t be sure where the pin drop would go. But knowing which accidents took place at intersections means that I can geocode based on <route name 1> & <route name 2>, <municipality>, <province>.

To do this, I have to perform a number of different transformations on unity.

Here’s the simple breakdown of the transformations I’ve added:

• The if_then_else node looks for the value “1”, signifying intersection, in the landmark (tp_reprr_accdn) column.
• When it finds a “1” it pulls the values from route 1 (rue_accdn) and route 2 (accdn_pres_de). I’ve concatenated these two values and separated them by an ampersand, which is how they’ll be geocoded.
• These, in turn, get concatenated with the municipality name (mrc) and a static node with the value “Quebec”, because I want to make sure I limit my results to the right place. This gives me the string <route name 1> & <route name 2>, <municipality>, <province>.
• Since I already know that not all of the data is ideally structured (recall the “close to the Burger King on Demasse st”), I’ll toggle the geocoding node to disallow partial matches. This will limit the number of successes I get, but it will ensure that the matches on the output data are accurate.
• That output is then plugged into the geocoding node
• The latitude and longitude are pulled from the geocoding node into a to_geo_point node which formats the latitude and longitude based on our preferred WKT format.
• The to_geo_point node output connects to the geometry column

After I’ve made these and a few other transformations, I get a data set that looks like this.

By asking a bit more from the data, we can start pulling out points that can help us start understanding accident trends. While the success rate for this kind of geocoding is currently limited by the quality of the data, it’s through seeing the data presented this way that we can start to see the value of plotting it on a map.

A note regarding Unity screenshots: Unity is currently an internal feature and the GUI is in prototype. There are UX and UI components that are in the design phase and cannot be displayed.

Click here to view a sample of the refined data set. For clarity, I’ve limited the columns and translated all French in the data set to English. For more Unity case studies, or to try it out for yourself, please don’t hesitate to get in touch.

Unity Case Study: Geocoding Traffic Accidents was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Data

The City of Toronto has some excellent open data. Not only that, they’re constantly iterating on their portal and their strategy. This kind of agility is absolutely necessary if we want open data to succeed in the coming years. It’s critical to get the data out the door, sure, but governments at every level also need to be thinking about how they’re going to bridge the divide between data producers and data consumers, how they’ll optimize their data to make it business compatible, and how they’ll plan for the future.

As with any open data, though, there are a few data sets on Toronto’s portal that leave something to be desired. Take this data set, for example:

The Problem

Despite proudly calling Toronto my home, I am not (yet) able to conjure up the geospatial coordinates of each of the city’s wards from memory. Lucky for me, the city publishes a data set on their portal that gives me, among other things, Toronto ward name and coordinates.

The Solution: Joining and Enriching with Unity

Using Unity, I can join these two data sets on the one value they have in common, ward in one, and lcode_name in the other. In Unity, the graph looks like this:

Since ward numbers 1–9 in the second graph are preceded by an unnecessary “0”, I transform the entire column into integers before matching it to the first graph. Then, using the Namara node, I specify the data set id of other graph I want (“basement flooding by ward”) and then specify each of the values I want to pull from the one data set into the other. After I run it, I can see the data set plotted on a map.

Now I can query the data in a way that makes a bit more sense, and I can immediately click through to see which wards have a higher rate of basement flooding.

Once I was looking at the data set, though, I started wondering about the other factors that might go into a high incident rate for flooding. Looking through Toronto’s open data portal, I found a data set that shows watermain breaks in Toronto

By layering that data set on top of the other one, I can start to see if there’s a correlation between the two.

Now I’ve got something that can potentially help the city understand areas in need of an infrastructure overhaul. If I’m a homeowner, I can use this data to predict the likelihood of my basement flooding. If I’m a guy selling flood insurance… well you get the picture.

A note regarding Unity screenshots: Unity is currently an internal feature and the GUI is in prototype. There are UX and UI components that are in the design phase and cannot be displayed.

For more Unity case studies, or to try it out for yourself, please don’t hesitate to get in touch.

Unity Case Study: Basement Flooding & Watermain Breaks was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

The Data

Address points are one of the most critically important data sets that any city can release on their open data portals. They provide an invaluable geospatial base layer to virtually any app.

A quick search for “Address Points” on Namara tells me that there are over 700 address point data sets available on the platform. These range in size from the Kodiak Island Borough in Alaska (7764 rows) to New York City (942,321 rows), and while some of these data sets are statewide/provincial (way to go, Utah!) and a number of them are by county, the vast majority of them are by city. Since they’re all released in different places, collected by different agencies, and published in different ways, it’s hard to find any two among the 700 that look the same.

The Problem

Since addresses are usually maintained at the municipal level, that means they’re usually released at the municipal level.

This is fine if you want to build an app that doesn’t extend past your city limits, but if you’ve set your mind on something bigger, you’re going to have to get your hands dirty.

Take these three address point data sets from the Waterloo, Toronto, and Ottawa open data portals:

A quick look at each tells me that there’s a lot of variation in what’s offered in a standard address point data set. Ultimately, though, it boils down to an address and a coordinate.

The Solution: Aggregating in Unity

To merge these data sets in Unity, I need to define a schema, my ideal output.

The above contains the requisite address information, the metadata from the source (so I can keep track of when the data sets have updated), and geometry, so I can plot the aggregate on a map.

After I’ve defined the schema, I need to bring the graphs into Unity and map their attributes to the schema that I’ve already set up.

The Ottawa address data set maps pretty cleanly to the ideal schema. Despite lacking ward information and some differences in naming conventions (addrnum on theirs, street_number on mine, etc), the only thing I need to actually add is a static node specifying that for this graph the default value in city should be “Ottawa”.

The Toronto Address graph is also relatively straightforward. Since I prefer my street_name and ward_name uppercased, I’ve added that transformation. Looking at the Toronto data set, I can see that a lot of the values in fcode_des are marked as unknown. Since neither of the other data sets acknowledge unknown values I added a replace node to look for instances of the word “Unknown” and replace them with null.

Finally, since the original graph’s ward_name provides ward name and number in the same column, I added two extract nodes and plugged in a regular expression that pulls out the name and number separately. It took two minutes, and it means that later I’ll be able to query the data better.

Waterloo’s address point data set doesn’t have a column that maps cleanly to street_number, but I can see that the data in civic_addr is always recorded with a space between the street number and the street name, so I split the value on the first space and plugged that value into street_number. I want to make sure I’m only pulling numbers, though, so I add in a “to_integer” node to make sure I’m only grabbing what I want.

The rest of the graph lines up well, with the exception of ward. In the original data set, Waterloo only provides the ward number as a data point. I could easily map that and leave ward_name blank, but I would prefer to have both values in my final data set.

Since Waterloo has pretty great open data, I decided to see if they released a ward map data set. Turns out they do.

Using the Namara node on Unity, I can point towards the ward data set on Namara, match on ward_no, and output the ward name (which on the data set is called ward_2014).

So by matching the values in one data set with the corresponding value in a second data set, I can add a totally new attribute to my final aggregate.

Once the data sets have all been mapped to the schema, I can kick off a Unity run and then upload the data to Namara, where it will update automatically.

ThinkData Works proudly works with governments to help them release high-value data, and their data management platform Namara connects users to every open data portal in North America (and a lot of public data, too).

Unity Case Study: Aggregating Address Points was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.

Making smart business decisions is dependent on a company’s ability to use its data effectively.

While internal company data has a lot to offer, in a world of complex connections with a fast-growing ocean of accessible data, it is naïve to think that useful and competitive information can be harvested without benefiting from external data sources. As more and more data becomes available, businesses are developing a new way of thinking:

Every piece of the puzzle is needed in order to see the big picture.

Matching these external sources to internal data becomes an entity resolution and data linkage question, and it’s one that businesses in every industry are struggling to answer. At ThinkData, we help businesses find and leverage new sources of data and match them to their own.

For example, one of our banking clients is interested in ingesting new streams of import/export data that we provide on Namara. By linking this external import/export data to their internal client data, the bank will be able to uncover new client trade activities that were otherwise unknown, identify true wallet size, investigate trade trends, monitor growth, map clients, and ultimately gain insight into a previously opaque part of their business.

Every one of these advantages is dependent on linking the bank’s internal client data to the external data feed provided by ThinkData.

UK Companies Data on Namara

To link this valuable export/import data feed to the bank’s internal client data, a third external data source is needed. This third data feed is an extremely useful public repository of all businesses in UK which can be linked to external trade data feeds. This connection provides us with the key we need to connect to the bank’s internal data. This data set was harvested and organized by ThinkData and can be accessed on Namara.

The UK companies data set has about 4 million rows of company registration information in 55 columns including basic information such as company names and addresses. Since some of the companies appear in more than one row with similar information, the data set needs to be deduplicated. After omitting the repetitive information, we can then assign a unique ID to each company in this data set. This unique ID can be used later in connecting to the import/export data.

Global Exports Data Feed

Let’s consider a scenario in which we want to use the export data feed, filtered on UK based companies, and connect this data to the UK companies information on Namara. First, let’s take a look at this data set. A sample of this data feed contains various trade information in 20 columns, such as:

1. Date
2. Exporter information (name, ID, address)
3. Consignee information (name, ID, address)
4. Information on exported goods (weight, quantity, value, etc.)
5. etc.

Note that not all of the columns are populated for every record. This plays an important role in choosing the columns we want to use for the matching exercise.

Results: Connecting Global Export Data to UK Companies Data

Since both the global export data and UK companies data are missing information in some columns, we decided to use the company name field in the UK companies data and the consignee name (representing UK companies involved in the deal) field in the export data feed to make the connection.

Since these two columns are always populated in both data sets, the first attempt in connecting these data sets was based on matching these unstructured text fields. An unstructured text field could contain anything and is not necessarily cleanly recorded company names.

The first step is performing the required preprocessing steps to make these data sets ready for the matching pipeline. We designed and performed a fuzzy matching algorithm which includes the following steps:

1. An indexing algorithm is used to extract a good subset of similar fields. This helps with the performance of the algorithm and saves time by choosing pairs from two data sets that are more likely to be a match.
2. The candidate pairs are then processed by the text-matching algorithm. Our approach breaks down the names and takes into account many different scenarios in which a specific name can appear. This algorithm computes a score of match for each record in the global export data set to its closest match in the UK companies data.
3. The generated scores reflect the probability of the match which then is used to extract the most likely matches.
4. The final result would be the global export data set with an extra column which has the unique ID of the corresponding company in the UK companies data set.

The following image shows 20 randomly selected records with the corresponding match score. We can see that the score appropriately reflects the level of confidence in match and the matching algorithm has a good tolerance for small typos and variations.

Based on this ID, the two data sets are then linked and ingested to Namara. The resulting data set can then be used by the banking client to merge this information with their own internal client data.

This process adds new transaction level data to the bank’s internal data, providing them with competitive and valuable information on their current and prospective clients — information that was not available before linking to external trade and company data sets on Namara. It helps them understand not only who’s linked to trade and where they are, but what they’re importing/exporting, and how much.

Data-Driven Value

For modern companies, data is the key to unlocking major opportunities, better understanding your market, and developing advanced analytics. As we move toward AI and ML-driven tech, the quality of insight will be reflected in the quality of the data that is being used. By enriching internal company data with external feeds of data, fast-moving businesses are establishing a pipeline that can deliver them actionable information on demand, giving them the time and resources needed to grow their business.

Namara is a turnkey data refinery that helps you get more out of data. Let us know how we can help.

Linking Trade Data to Company Information was originally published in ThinkData Works on Medium, where people are continuing the conversation by highlighting and responding to this story.