Development Tips

Logi Tutorial: Integrating Google Maps using SQL Tables

By David Abraham
Share on LinkedIn Tweet about this on Twitter Share on Facebook

Integrating Google Maps into your BI application can provide valuable geographic data within an analytics context. For Logi developers, the Logi Info Google Map element allows them to include the geo-mapping features of the Google Maps web service in their reports and provides an opportunity to combine it with their own information to produce hybrid maps, sometimes called “map mashups”.

Here, we explain how to use the Logi Info Google Map Element to easily render a data-driven interactive geographic map.

>> Related: Mastering Plugins – 3 Ways to Extend Your Analytics <<

Geographic locations can be shown in three ways:

  • A point: A geographic Map Marker is placed on the map to identify a specific location. Map Markers can use the default icon or a custom image, gauge, or even a chart. Optionally, the map can be configured to display a Map Marker Info window, which shows additional location-specific data when the marker is clicked.
  • A line: Google Map polylines can be plotted from data to show a route. Polyline color, width, and transparency levels are all configurable and can be set from data values.
  • A polygon: Logi Info can work with GIS boundary data to produce region overlays for states, counties, cities, school districts, and other areas using Google Map Polygons. Like the Map Marker, regions can be clicked to display a pop-up information window with detail data. Polygons are used to layer something like a heatmap on top of a map.

Locations are plotted from sets of latitude and longitude points. Longitude and latitude information typically come from:

  • GPX or KML files: These are GIS industry-standard XML data files. Special data layers are provided in Logi Info to read these files.
  • Shapefiles: Popular geospatial vector format for GIS data. Free and inexpensive software tools (which we’ll discuss in a later section) are available for converting shapefiles into GPX or KML data files.
  • SQL Data Layer: Geographic data can also be retrieved from SQL database tables using DataLayer.SQL element in Logi Studio. This gives developers the ability to define and run a query against a SQL-compliant data source.

In this post, we will discuss using a SQL data layer to accelerate the rendering of Google Maps on a report.

Advantage of using a SQL Data Layer to provide Geolocation Information:

Including latitude and longitude positioning data in a SQL database saves both time and cost in rendering Google Maps on your report. Logi offers Geocode column elements which accepts an address and attempts to return latitude and longitude.

However, the use of Geocode Column elements to provide that data involves additional “trips” to the web service and, depending on the service broker’s agreement, that could mean additional transaction fees. Also, Google imposes a geocoding limit of 2,500/day (using free service) or 100,000/day limit (Premiere API license), and enterprise users may have other contractual limits. Refer to Google’s Geocoding Limits.

SQL Data Layer Column Information

The SQL Data Layer requires a:

  • Name column: Decide how you want to bound your data—for example, by state, zip code, or country. This will be your first column.
  • rdCoordinates column: Formatted as Lat, Long, 0 for each polygon point, separated by a space in between them. The Latitude and Longitude point for each location is easily accessible online.

The steps for configuring the information include:

  1. Create a SQL Data Layer and connect it to database you loaded the data into
  2. Open the Default report (or create a new one)
  3. Add a Google Map to the body.
  4. Add a Google Map Polygons layer (or a line, or point, if you’d like)
  5. Add a polygon layer to it (or a line, or a point if you’d like.)
  6. Add a SQL Data Layer under the polygons and set the connection to select back the Name and rdCoordinates columns.

The Logi Info Google Map Family of Elements allows you to connect to the Google Maps web service, plot data on the amp, add interactive map controls, geocode address data, and use an info window. For more information on how to make the most of Logi Info’s suite of maps to enhance your report, check out our well-supported Developer Network Documentation.


Originally published September 19, 2017

About the Author

David Abraham is a Solutions Engineer at Logi Analytics. Before joining Logi, David was a cybersecurity analyst.