Displaying data from SQL Database when you select features [Geocortex Tech Tip]

When it comes to data, not everything you need to interact with lives in a spatial database. With Geocortex Essentials, you can pull this non-spatial data into your application and work with it on the map. Our technology quietly queries outside databases and displays relational data seamlessly alongside spatial data. No duplication or synchronization of data is required either; all you need is a relational database with data that contains a shared key or attribute.

In this week’s Geocortex Tech Tip, we focus on SQL databases specifically, and how to quickly access and display data within your Geocortex Viewers via the data linking technology in Geocortex Essentials.

Watch on YouTube.

Video Transcript

“Hi everyone, my name is Patrick Fingler. I work on our technical marketing team, and in this tech tip video I’m going to show you how you can display data from SQL databases when selecting a feature on the map using our data linking technology.

In this Tech Tip video, I’m going to show you how you can display data from SQL databases inside of your Geocortex Viewers when selecting features on the map.

This is done through the data linking technology that ships with Geocortex Essentials.

Here you can see that I’m in Geocortex Essentials Manager and currently I am on the map tab.

I’ve just got a single layer that I’ve added, I’ve got two layers, one is my Open Street Map base map and my Fire Hydrants layer.

This fire hydrants layer has a variety of attributes on it. One particular one being the facility ID, this is essentially the fire hydrant ID and I happen to have an SQL database table which highlights the inspections performed on these fire hydrants.

It’s a non-spatial data table living in SQL server and I happen to want to join that to my fire hydrants.

Now, the key thing here is you know you have a common ID between the spatial feature that you want to select, and the non-spatial table that you want to join to.

Here I’m going to show you how you can accomplish this.

So, the first thing that we’re going to want to do is navigate to the data connections tab and this is where we’re going to add a data connection to our non-spatial database.

I can see that I can give it a display name. I’m just going to call it the name of my server that I’m connecting to and we’ve got a variety of different data providers available.

We can see that we’ve got ODBC providers but, in this example, because I’m connecting to an SQL server database, I’m going to select an SQL client and if you’re interested to learn more about all the different data connections, all of this lives on our Documentation Center.

You can just navigate to here, there’s a data connections tab as well as a data link tab and that will have additional information on where you can kind of learn how to connect to other database providers.

In this example, I’m going to select an SQL Client, click next and I’m going to enter in my server name and in this particular scenario, I’m going to use SQL server authentication and I’m going to enter in my password and test it.

You can see its successful and I can now click next.

You can see that these are all the instances inside of this SQL Server database and I’m after my production incidents, so I’ll click OK and click finish.

I’m pretty happy with that, so I’m going to save my site.

The next step in the workflow here is to navigate to the mapping tab.

We’re going to configure a data link on the layer that we want to tie this to.

I’ve got my fire hydrants layer, if I edit the layer here, you’ll see there’s a variety of other configuration options and I’m after the data link tab.

I’m going to a data link, give it a name, so I’ll call this “inspections” and here you can see I’m selecting my data connection that I just created.

Now, this is where I can select the actual data or the table in my particular production instance that I want to join this to. I’m going to scroll down – there’s all sorts of stuff in here, but I’m after this hydrant inspection stable, and these are the attributes within my hydrant inspection table. I might want to select all of them, but you can also pick and choose which ones you want to show and I’ll click next.

So this is where we actually perform the join. I’m going to do a one to many join.

I’ve got one hydrant asset which has many inspections, so I’m going to add a condition, this table column is the attributes of my actual of my table and I’m going to join it to my facility ID on my hydrant asset. This is my feature field. I could give it a default value, let’s quickly find add a default value to use.

Let’s go to here, find my fire hydrants, it’s going to be where one equals one. I will get the facility ID and just return the first 5 features.

If we go back here, I’ll just enter that in as my default value. You don’t need a default value but that’s what I’m going to enter there.

I’ll just call this facility ID and click “OK”, and then I will click finish.

You can see now I’ve configured my data link. We can actually quickly test this by opening up the sites rest endpoint here and scrolling down and selecting data links now.

Here is my inspection data link that I just configured, and I can click link, this is where I can test it.

You can see I’ve got a default value that I just entered in, if I click link, we can see that this facility ID has these two inspections that have been performed and are being retrieved from SQL Server.

Now once you have a data link, you can actually view this information in a variety of different areas in Essentials.

I’ll just quickly show how you can do this in a chart. I’m just going to add a chart and I’ll call this “Hydrant Inspections”.

I’m going to create a pie chart and I’ll just do single feature chart and click finish. I’m just going to edit this chart, add a single series and I’ll just say, let’s create a pie chart on the inspectors.

Let’s see if I can find inspectors, I don’t see inspectors in the list here. That’s because I’m after data link. That’s the key thing here, is you can see there is a data source here. I can select field, that the field is on a hydrant asset itself and I’m after the data links.

Here is my inspection data link, if I scroll down here, there’s the inspector. So now we’re creating a chart based on our data link. I’ll just give it a color palette, apply my changes, click save site.

Now when I refresh my viewer here, this is the viewer that I’ve created, it’s the same viewer as if you were to navigate to the viewer section of Essentials Manager.

If I select the hydrant here, click the additional details, you’ll see here I’ve got my hydrant inspections chart that I just configured. I can click no chart if I wanted to or I can select that chart.

You can see here that we’ve got three inspections that are being returned from our SQL Server database. I’ve got two inspectors that were performed by County fire station and one that’s just been done by station 4.

We can confirm that if I navigated to here, this is the one done by station 4. This is the end and the next two have been performed by County Fire and County Fire.

And again, I could reduce what attributes I actually want to display here when performing the data link itself.

I hope that was informative.

That’s essentially how you can connect to non-spatial databases and pull that information from those non-spatial databases and tie them to a spatial feature using the data linking technology within Geocortex Essentials.

Bye for now!

If you’re interested in learning more about our data linking capabilities, check out our Integrations eBook.

Download eBook