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

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

Matt Vaillant
Matt Vaillant
Digital Marketing Lead
April 11, 2018

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

Maps allow you to visualize data in meaningful ways and expose patterns that can’t be seen anywhere else. One of the challenges, though, is that your most important business data typically lives in another system or database. This can become even more challenging when it’s data stored outside your geodatabase.

In this Geocortex Tech Tip, Drew Millen shows you how to search for data in a non-spatial database (such as Oracle or SQL), find the spatial relationship, and display it on a map.

Watch on YouTube

Video Transcription

“Hi everybody, I’m Drew with Latitude and in this Tech Tip we’re going to look at searching for non-spatial data. That’s data stored in Oracle or SQL Server… somewhere that’s not in your geodatabase. We’re going to look for that, find the spatial relationship, and display it on a map, so let’s see how we do that with Geocortex.

What we’re looking at here is a very basic Geocortex viewer application that’s been configured with a single layer called “Land Use”. This contains polygons of different types of land uses and what I’m interested in is this “Arts and Recreation” land use polygon, which contains park information for Los Angeles County. I also have a database table – in this case, an Excel spreadsheet of trees. Now notice that I‘ve got records of all the different types of trees that exist, but I don’t have location information for these. In other words, this is a non-spatial database table. This could live in Oracle or SQL Server, but for the sake of this demonstration it’s just an Excel table.

We’ve got a facility that tells us which park this tree belongs to, but we still don’t have its “XY” location on the map. What I want to find out is where I can find certain trees in my county, so, what parks do I have to visit to discover certain types of trees.

Now in this application, I’ve got a data link between my parks layer, or my land use layer, and the tree database. So, if I have a park selected, and I view the additional details for [the park]; I can see the spatial details associated with that park and I can also see the trees that are within that park, but I’m not quite there. What I want to find out is which parks contain which trees… and remember, my trees don’t have “XY” locations.

How do I solve this? Well, I’ve already set it up so that we can do a search against this Excel table. So, if I do a search for the word “macadamia”, for example, I will find search results from that Excel table, but I still don’t have the location on the map where these macadamia nut trees exist. I need to create a “join” between these search results and a spatial layer on the map to find the underlying spatial feature. In other words, the park that the trees live within.

What I can do is come back to Geocortex Essentials Manager where I’ve configured this application. And to connect to this Excel spreadsheet, I’ve established a data connection. You can see the connection string that we’ve used here simply points to the spreadsheet. If you’re connecting to Oracle or SQL Server, there’s different syntax that you would use for your connection string, but the same idea exists.

Now that we have that data connection, we can set up what we call a “Search Table.” And a search table gives us a select clause: in other words, which fields are we interested in returning from that table when the user issues a search. In this case, we want the user to be able to search on the common name of the tree (like my example when I typed in the keyword “macadamia”) and find all the attributes from the LA Parks trees in this database. So that search is set up.

I’ve also got the land-use layer in my site configured with a datalink. This datalink means that the layer is joined to this data connection, so that every time I click on a park on the map, I see the associated records from my Excel spreadsheet. Recall, however, that I want to do the reverse. So, our current datalink makes sure that every time I select a park on the map I’m grabbing the trees and joining it on the facility column. Notice that facility column is the name of the column that we’re using in the spreadsheet to represent the park that the tree exists within.

There’s this section down at the bottom, here, that allows me to add a search, so that’s the reverse of what we’re currently doing, and it allows me to use one of the searches that I’ve configured to find these features from the land use layer that match my search criteria from my datalink.

I’m going to give this a display name. We’ll just use “Park Trees Search,” and the search table that I’m searching on is the only one that we’ve configured in our site earlier, so it’s this Park Trees Search table. And then the field that we want to join is called “Facility,” and that maps to the name of the land use polygon. So that’s where we get our many-to-one relationship from. I’ll go ahead and save the site with those configuration changes and then refresh our viewer.

Now I’m going to issue a search for the word “macadamia” like I did before, and I’ll find the same four results from my Excel spreadsheet. But now when I drill into a result, we can see the facility that it belongs to. It exists in two different parks: there’s “Runyon Canyon Park” and “Runyon Canyon Park – MRCA”. If I click on one of those it’s going to take me to the park where I can discover these macadamia trees.

Hopefully this quick Tech Tip has shown you how you can configure a non-spatial data source to be searchable inside your viewer and still return spatial results. Thanks for watching!”

Explore more Geocortex Essentials functionality in the Discovery Center.

Discover Geocortex


Categories:
Archive
Open: 2019
2019
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 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