Visualize coordinates and IPs

Visualizing data on a map can provide powerful insights into the geographical distribution of your users, traffic, or events. Whether you're tracking login locations, identifying regional performance issues, or analyzing market reach, map charts turn raw coordinates or IP addresses into an intuitive visual display.

Creating a map chart

You can create a map visualization in Dashboards using the Map chart type. This chart requires two key pieces of information:

• A set of coordinates - either latitude/longitude or an IP address.
• A single numerical value to plot for each location - the number of requests, average latency, etc.

Using drag & drop

The quickest way to create a map is with the Drag & drop query builder.

1. Select the Map chart type.
2. In the Coordinates section, drag and drop either:
   • Two numerical fields with Latitude and Longitude.
   • A field with IP Address field, both IPv4 and IPv6 are supported.
3. In the Value field, drag and drop the metric you want to visualize.
4. Click Run query.

Using SQL expression with IP geolocation

For more advanced control, you can use a SQL expression to perform GeoIP lookups yourself. This is especially useful when your IP data needs parsing or is stored within a larger JSON object.

Under the hood, we use a an IP dictionary. You can access it with the dictGetOrNull function.

How it works

The dictGetOrNull function retrieves geographic information based on an IP address. Here's the basic structure:

dictGetOrNull(
  'default.ip_trie',
  'latitude',
  '8.8.4.4'::IPv4
)

This function takes three arguments: 1. The dictionary name: 'default.ip_trie'. 2. The attribute you want to retrieve, you can use 'latitude', 'longitude', 'city', 'country_code', 'timezone'). 3. The IP address, converted to a supported format - either IPv4, IPv6, or FixedString(16).

Preparing your IP data

The IP dictionary accepts IPv4, IPv6, or FixedString(16) types. Your log data might store IPs as strings, so you'll need to convert them first. Use these functions to parse and convert your IP data:

• toIPv4OrNull(ip_string): Converts a string to an IPv4 address.
• toIPv6OrNull(ip_string): Converts a string to an IPv6 address.
• IPv6StringToNumOrNull(ip_string): Converts a string to a FixedString(16) representation, which works for both IPv4 and IPv6. This is often the most convenient option.

Example: Charting user logins by city

Let's say your logs contain a client_ip field. The following query counts the number of logins from each city and plots them on a map.

SELECT
  -- Extract latitude and longitude from the IP
  dictGetOrNull('default.ip_trie', 'latitude', ip::IPv4) AS latitude,
  dictGetOrNull('default.ip_trie', 'longitude', ip::IPv4) AS longitude,
  -- Count the number of events for the value
  countMerge(events_count) AS value,
  -- Get the city name for the tooltip
  any(dictGetOrNull('default.ip_trie', 'city', ip::IPv4)) AS city
FROM (
  SELECT
    -- Convert the IP string to a format the dictionary can use
    toIPv4OrNull(request_ip) AS ip
  FROM {{source}}
  WHERE dt BETWEEN {{start_time}} AND {{end_time}}
    AND ip IS NOT NULL
)
GROUP BY latitude, longitude
HAVING value > 0

When you run this query with the Map chart type selected, it will display a world map with circles representing the concentration of logins in different locations.

Need help?

Please let us know at hello@betterstack.com. We're happy to help! 🙏