Displaying data from SQL Database when you select features

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

Patrick Fingler
Patrick Fingler
Technical Marketing Specialist
March 22, 2019

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


Categories:
Archive
Open: 2019
2019
August 16, 2019

Displaying SQL Server Data in Geocortex Workflow [Geocortex Tech Tip]

August 14, 2019

Customer Spotlight: Geocortex Essentials

August 8, 2019

City of Austin: Spreading floodplain awareness with Geocortex

August 1, 2019

Geocortex Essentials 4.12: Improved integration with ArcGIS® Online

July 25, 2019

Introduction to Geocortex Printing: Learn to build stunning print templates

July 17, 2019

Updates to the Geocortex Product Life Cycle

July 8, 2019

Extending Web AppBuilder for ArcGIS® with Geocortex Essentials [Webinar]

July 4, 2019

City of Bellingham: Using Geocortex to take the stress out of water shut-off

June 27, 2019

Geocortex at the 2019 Esri User Conference

June 14, 2019

Customer Spotlight: Geocortex Workflow

May 17, 2019

Important Notice - Outage Issues & New Support Cases

May 17, 2019

How to build a summary report with aggregations of feature data [Geocortex Tech Tip]

May 16, 2019

Technology Q&A: Geocortex Mobile

May 10, 2019

How to create print templates for Web AppBuilder for ArcGIS®[Geocortex Tech Tip]

May 8, 2019

Dive into Geocortex Printing [Webinar]

May 3, 2019

Using Geocortex Workflow and Geocortex Reporting to produce a multi-layer report [Geocortex Tech Tip]

May 1, 2019

Market-Leading GIS Software and Services Companies Join to Form VertiGIS

May 1, 2019

Introducing VertiGIS

April 18, 2019

Integrating Workflows into Geocortex Mobile [Geocortex Tech Tip]

April 17, 2019

Customer Spotlight: Geocortex Reporting

April 12, 2019

Using Geocortex Go to preview app configurations [Geocortex Tech Tip]

April 10, 2019

How to Address Complex Form Navigation Using Geocortex Workflow

April 5, 2019

Building native, offline IOS, Android and Windows apps with Geocortex Mobile Viewer [Geocortex Tech Tip]

April 3, 2019

Streamlining mobile operations with Geocortex Mobile Viewer [Webinar]

March 29, 2019

A better way to run geoprocessing tasks in Web AppBuilder for ArcGIS [Geocortex Tech Tip]

March 28, 2019

Extend the reach and capabilities of your applications with integrations [eBook]

March 22, 2019

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

March 20, 2019

How Geocortex users are enriching the communities they serve

March 15, 2019

How to Configure a Geocortex Essentials layer report with Geocortex Reporting 5 [Geocortex Tech Tip]

March 14, 2019

Region of Waterloo: How GIS & Geocortex technology helped meet local Source Protection Plan requirements

March 6, 2019

Integrating Business Intelligence and Data Sources with Geocortex Essentials [Webinar]

March 1, 2019

How to quickly add attachments to new features when editing [Geocortex Tech Tip]

February 22, 2019

Generating a report with sub reports from external data sources [Geocortex Tech Tip]

February 20, 2019

Geocortex Essentials 4.11: Saving Time & Increasing Efficiency

February 15, 2019

Accessing Samples and Building your First Workflow [Geocortex Tech Tip]

February 12, 2019

Open Beta of Geocortex Printing 5 Has Begun

February 8, 2019

Different Ways of Creating Selections of Features in Geocortex Viewer for HTML 5 [Geocortex Tech Tip]

February 6, 2019

4 Best Practices to Follow When Using Geocortex Workflow 5

February 1, 2019

Creating a Report that Displays a Screenshot of the Map [Geocortex Tech Tip]

January 28, 2019

Diving Into Geocortex Reporting 5 [Geocortex Tech Tip]

January 24, 2019

How Geocortex is Advancing Water Management Solutions

January 16, 2019

Creating Custom User Experiences with Geocortex Workflow 5 [Webinar]

January 11, 2019

Showing the search results using the item picker in Geocortex Workflow 5 [Geocortex Tech Tip]

January 9, 2019

City of Troy: Using GIS and asset management technologies to manage legislative requirements

January 4, 2019

Using scripting to put attachments in reports in Geocortex Reporting 5 [Geocortex Tech Tip]

Open: 2018
2018
December 21, 2018

Integrating Pictometry, Bing and other 3rd party maps within your Geocortex applications

December 19, 2018

Recapping the Texas Geocortex Regional User Group

December 14, 2018

How to use Geocortex Workflow 5 to populate the Attribute Table in Web AppBuilder for ArcGIS [Geocortex Tech Tip]

December 12, 2018

Finding yourself: Using geolocation in mobile and web applications [Webinar]

December 7, 2018

Enabling real-time user-to-user map collaboration within Geocortex Essentials

December 5, 2018

Reflecting on 2018

November 30, 2018

Creating mailing labels with reports with Geocortex Reporting 5 [Geocortex Tech Tip]

November 29, 2018

Alberta Energy Regulator: Supporting safe and responsible energy resource operations

November 26, 2018

Important update for ArcGIS and Transport Layer Security (TLS) Protocol Support!

November 23, 2018

Using fine-grained security to control access to layers, features, attributes and application functionality [Geocortex Tech Tip]

November 21, 2018

Enhancing Web AppBuilder for ArcGIS® with Geocortex Reporting [Webinar]

November 16, 2018

Using Geocortex Workflow 5 to automatically display a form for editing layer attributes [Geocortex Tech Tip]

November 15, 2018

Geocortex Achieves Esri’s “Release Ready Specialty” Designation

November 14, 2018

Celebrating GIS Day with York Regional Police: How GIS Data Has Mitigated Risk & Increased Efficiency in Crime Prevention

November 9, 2018

How to access a SQL database from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip]

November 2, 2018

How to send an email from a workflow using Geocortex Workflow 5 [Geocortex Tech Tip]

October 26, 2018

How to add and configure charts inside reports with Geocortex Reporting 5 [Geocortex Tech Tip]

October 24, 2018

Geocortex Water Webinar Series

October 19, 2018

Integrating Geocortex Essentials with ArcGIS Online and ArcGIS Enterprise portal [Geocortex Tech Tip]

October 17, 2018

Cross-Platform Development with Xamarin [Webinar]

October 12, 2018

City of Fort Collins: Dynamic flood maps for public awareness, and flood insurance rate saving

October 2, 2018

Technology Q&A: Geocortex Workflow 5 “Behind the Firewall”

June 27, 2018

Geocortex Essentials 4.10 is here!

June 12, 2018

Geocortex and the GDPR

June 6, 2018

Geocortex at the 2018 Esri User Conference

May 15, 2018

Technology Q&A: The evolution of Geocortex and Web AppBuilder for ArcGIS

April 25, 2018

How to manage data collected from Geocortex Workflow 5 forms [Geocortex Tech Tip]

April 18, 2018

Delivering accessible mapping applications for everyone [Geocortex Tech Tip]

April 11, 2018

How to search for data in a non-spatial database [Geocortex Tech Tip]

April 5, 2018

Configuring Geocortex Analytics to monitor a new Portal for ArcGIS instance [Geocortex Tech Tip]

March 27, 2018

Getting started with forms in Geocortex Workflow 5 [Geocortex Tech Tip]

March 21, 2018

Using Geocortex Workflow with Web AppBuilder for ArcGIS [Geocortex Tech Tip]

March 15, 2018

Understanding tool usage in your GIS applications [Geocortex Tech Tip]

March 7, 2018

Running Geocortex Essentials workflows from an identify operation [Geocortex Tech Tip]

March 2, 2018

GIS Health Assessment: A new way to think about your system

February 28, 2018

Using the in-app help system in Geocortex Workflow 5 [Geocortex Tech Tip]

February 21, 2018

How to configure a personalized dashboard in Geocortex Analytics [Geocortex Tech Tip]

January 25, 2018

GIS is shifting to SaaS, and it’s a win for everyone


Categories

Popular Tags
Accessibility Active Operating Picture ArcGIS ArcGIS Online Customer Story Data Models Energy Esri esri user conference Geocortex Geocortex 5-Series Geocortex Analytics Geocortex Essentials geocortex integrations geocortex mobile Geocortex Mobile Viewer geocortex printing Geocortex Printing 5 Geocortex Reporting Geocortex Reporting 5 geocortex tech tip Geocortex Tech Tips Geocortex Viewer for HTML5 Geocortex Web Viewer Geocortex Workflow Geocortex Workflow 5 GIS GIS Day GIS Health gis integrations Integrations mobile gis Oil & Gas Pipelines Portal for ArcGIS press release SaaS Tech Tip Tech Tips Transportation vertigis Water Industry Web AppBuilder Web GIS Xamarin

Contributors