Inside IBM i’s New Geospatial Functions For Db2
October 24, 2022 Alex Woodie
One of the more interesting elements of the recent IBM i Technology Refreshes is the addition of Watson-based geospatial functions to the Db2 for i relational database. According to IBM, the new capabilities will make it simpler and easier to incorporate advanced geospatial functions into IBM i applications.
As its name implies, Db2 for i’s new geospatial functions allow people to compute data that has a geographical aspect to it. In this case, the geographical data takes the form of latitude and longitude readings on the Earth, and the functions provide ways to manipulate those points (as well as lines and polygons derived from the lat/long data) for some purpose.
All database functions start with data, and the new geospatial functions in Db2 for i are no different. While IBM hasn’t introduced a new geospatial data type for Db2 for i with this release, the new geospatial functions are supporting a new distinct type of data – in this case, a user-defined geospatial data type, says Scott Forstie, IBM’s business architect for Db2 for i.
“A user-defined type is a distinct type, and you can create your own,” Forstie says. “It’s different than when we added something like Boolean, which to me is a new base type. It’s actually a data type in your column or on your parameters and your host variables.” (Forstie was mum on whether geospatial would become a full-blown base type in the future.)
To use the new Watson geospatial functions, users need to have lat/long data stored in a database. If users don’t have lat/long data, there are various services available on the Web that allow them to convert things like mailing addresses into lat/long data sets. Once the geospatial data is available in a table, then the full array of about 50 geospatial functions stored in QSYS2 can now be put to use.
IBM has grouped the geospatial functions into six groups. There are constructor functions, which allow users to create different geometries – including points, lines, and polygons – based on lat/long data. Other database functions can be called to describe a property of those geometries, such as the minimum and maximum values contained in the table.
The full list of 49 Watson geospatial functions added to Db2 for i with IBM i 7.5 TR1 and 7.4 TR7.
There are hash functions available, as well as functions for comparing different geometrics. Users can construct new geometries from intersections and unions of existing geometries with another group. They can also convert one geometry type into another type. Some of these are scalar functions that return a specific value, while others are table functions that return a table.
Once you have geospatial data loaded into your database, then you are ready to start analyzing it with some of the geospatial functions. One of the most popular uses will be geofencing, according to Sarah Mackenzie, a senior software engineer with IBM.
“Let’s say you have trucks out there doing deliveries,” she says. “You want to let the customer know when the truck is 10 kilometers away so that you can make sure that person is home. You can say, okay what’s the distance between that person’s home and where the truck is currently located? And if that distance is less than 10 kilometers, let’s send them a text message.”
If your customers give the okay to track their location, such as via a mobile phone, then a geofence could be set up to trigger whenever a customer moves into a given area. “If your customers allow them to know where you are, you can say…okay, if the customer is close to the store, I’m going to push them an advertisement,” Mackenzie says.
Geospatial applications can also be historical in nature. For example, an insurance company can use a record of tornado strikes to determine whether to write policies in a given area and how to price the premiums, Mackenzie says.
Sighting a new retail outlet is another potential function. Armed with a data set that describes foot traffic and vehicular traffic in a given area, you could know whether to install a drive-thru for a new restaurant.
“If this is in the middle of a downtown area, foot traffic is more important. I don’t need a drive through,” says Alison Butterill, IBM i product manager. “But if I’m in the middle of a commercial area where people are driving to work, the drive-thru becomes critical because nobody’s going there on foot.”
“Those are the sorts of questions you can ask,” Mackenzie says. “All kinds of interesting things like that.”
It is worth noting that the Watson geospatial functions aren’t relegated to flat-earth geographical projections, which tend to introduce some unwanted skew into geospatial analysis, particularly over long distances.
“There are no projections, so it’s not based on a flat map at all,” Mackenzie says. “It is totally based on the spherical globe of the Earth. So you have projection-less calculations, which are more accurate than what you would have with the flat-map projections.”
This isn’t the first time that IBM i shops have access to Watson’s geospatial capabilities. In fact, they have been accessing them via Web services for the past five years or so, Forstie says. However, there is a big difference between calling out to a remote Watson service from an IBM i application waiting for a reply and having that Watson service running right in the IBM i server.
“Five years ago, you saw Allison, myself, Sarah, and others talk about the pathway and the roadway to get to Watson, and it was about interacting with Web services, being able to add Watson into your IT infrastructure, and it was great,” Forstie says. “At the time it was great and it served the purpose.
“But this is very different to me,” the database guru continues. “Adding integrated value into the operating system is something that we strive to do always. The client owns the IBM i. They own the database. Let’s just let them do more with it to have that great client experience. And with this advancement of being able to have a pathway, to have an inner source project where we are partnering with IBM Watson to add content to Db2 for i, is what this is about.”
The journey to adding Watson’s geospatial functions to Db2 for i began when Forstie and his team wondered what their colleagues in the Watson division could add to the database. Since IBM i shops had been finding some success with the Watson geospatial features, they started there, but there will be additional Watson functions added to the database over time.
“It’s starting with geospatial functions,” Forstie says, “but there’s likely to be other additions in this regard because we’ve got this channel open now. We’re demonstrating success both to the IBM corporation but also to our clients.”
The Watson geospatial functions will be available December 2 with a database PTF update. For more information, check out this IBM support page on geospatial analytics.
RELATED STORIES
IBM Bolsters Availability, Backup with Latest TRs
