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.
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.
Contributors