Map visualization

The map visualizations show the results of query on a geographic map. The query result must include the appropriate geographic data:

  • Chloropleth: Geographic localities, such as countries or states, are colored according to the aggregate values of each key column. The query must return geographic locations by name.

  • Marker: A marker is placed at a set of coordinates on the map. The query result must return latitude and longitude pairs.

Chloropleth

This example of a chloropleth visualization shows revenue per country. The color of each country provides a quick visual indicator of its revenue along a color scale from yellow to blue.

Example map chloropleth visualization

To create a chloropleth visualization:

  1. In the SQL editor, run a query.

  2. Click Add Visualization.

  3. From the drop-down, select Map (Chloropleth).

The following query is the source for the visualization. It queries tables in the samples.tpch dataset and aggregates the revenue for each country by name:

SELECT
    initcap(n_name) AS `Nation`,
    SUM(l_extendedprice * (1 - l_discount) * (length(n_name)/100)) AS revenue
FROM
    `samples`.`tpch`.`customer`,
    `samples`.`tpch`.`orders`,
    `samples`.`tpch`.`lineitem`,
    `samples`.`tpch`.`supplier`,
    `samples`.`tpch`.`nation`,
    `samples`.`tpch`.`region`
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND l_suppkey = s_suppkey
    AND c_nationkey = s_nationkey
    AND s_nationkey = n_nationkey
    AND n_regionkey = r_regionkey
GROUP BY
    INITCAP(n_name)
ORDER BY
    revenue DESC;

This query uses the samples.tpch database and you can try it in your workspace with no set-up required.

The visualization uses the following options:

  • Map: Countries

  • Key Column: Nation

  • Target Field: Short name

  • Values column: Revenue

  • Clustering Mode: Equidistant

  • Steps: 10

The next sections explain the options for chloropleth visualizations.

General

The following settings are required:

  • Map: Which geographic map to use. One of Countries, USA, or Japan/Prefectures. The results in the key column must appear in the map you choose.

  • Key Column: The column containing the locations for the map.

  • Target Field: The format of values in the key column. One of Short name, Full name, ISO code (2 letters), ISO code (3 letters), ISO code (numeric):

    Format

    Value

    Abbreviated name

    U.S.A, Brazil

    Short name

    United States, Brazil

    Full name

    United States, Brazil

    ISO code (2 letters)

    US, BR

    ISO code (3 letters)

    USA, BRA

    ISO code (numeric)

    840, 076

    If a key column value doesn’t fit one of the target field formats, no data is shown for that locality. In that case, transform your data either in the table or using a subquery.

  • Value Column: Aggregates the result per key column.

Colors

  • Clustering mode: How data is segmented into steps along the color gradient.

    • Equidistant (default): Results are segmented into the number of segments you specify in the Steps field, and each segment’s range is the same size.

    • Quantile: Results are segmented into a number of segments less than or equal to the value you specify in the Steps field, where each segment contains the same number of results, regardless of the segment’s numeric range.

    • K-mean: Results are segmented into the number of segments you specify in the Steps field, and the results in each segment are clustered near a common mean.

  • Steps: The number of colors to use inclusive of Min Color and Max Color in the map and the legend. If Clustering Mode is Quantile, there may be fewer steps than you specify here.

  • Min Color: The color for the lowest value range.

  • Max Color: The color for the highest value range.

  • No Value Color: The color for locations with no result in the query. Often, this color is outside of the gradient range between Min Color and Max Color.

  • Background Color: The color for the map background.

  • Border Color: The color for borders between locations.

Format

  • Value Format: Optionally override the automatically detected format for the value column.

  • Value Placeholder: An optional value to display for locations with no results. Defaults to N/A.

  • Show Legend: Uncheck to disable the legend.

  • Legend Position: Where in the visualization to display the legend.

  • Legend Text Alignment: How to align text in the legend.

  • Show Tooltip: Whether to display a tooltip when you hover your cursor over a location.

  • Tooltip Template: An optional value to override the template used for tooltips.

  • Show Popup: Whether to display a popup when you click a location.

  • Popup Template: An optional value to override the template used for popups.

Bounds

To show only a portion of the map in the visualization, you can configure coordinates for the four corners of the area to show. You can set the coordinates manually, zoom in or out, or use the rectangular selector.

Marker

This example of a marker map visualization shows the locations of fires in San Francisco that caused the most property damage:

Example marker map visualization

The query for a marker map visualization returns a key column and a longititude latitude per key.

To create the example visualization:

  1. As a workspace admin, create the table from a CSV file that is included in Sample datasets by running the following query in the SQL editor:

    CREATE TABLE IF NOT EXISTS default.sf_fire_incidents
    USING com.databricks.spark.csv
    OPTIONS (PATH 'dbfs:/databricks-datasets/learning-spark-v2/sf-fire/sf-fire-incidents.csv',
             HEADER "true",
             INFERSCHEMA "true");
    
  2. As any user who can access the new table, run the following query in the SQL editor.

    Before running the query, uncheck the Limit 1000 checkbox. The query specifies a limit of 2000.

     SELECT location,
       `Estimated Property Loss`,
       coordinates[0]  AS LAT,
       coordinates[1] AS LONG
     FROM (SELECT location,
            `Estimated Property Loss`,
             split(btrim(location, '() '), ', ') AS coordinates
           FROM default.sf_fire_incidents
           ORDER BY `Estimated Property Loss` DESC,
                    location)
     LIMIT 2000;
    
  3. Click + Add Visualization, then select Map (Markers).

The visualization uses the following options:

  • Latitude Column: LAT

    • Longitude Column: LONG

    • Show popup: True

    • Map Tiles: OpenStreetMap

    • Cluster Markers: true

The next sections explain the options for marker map visualizations.

General

  • Latitude Column: Latitude portion of the coordinate pair.

  • Longitude Column: Longitude portion of the coordinate pair.

  • Group By The column to use for grouping, if different from the original query.

Format

  • Show tooltip: If selected, a tooltip displays when you hover the mouse over a marker in the visualization.

  • Tooltip template: Override the default tooltip template using a format you specify.

  • Show popup: If selected, a popup displays when you click a marker in the visualization.

  • Popup template: Override the default popup template using a format you specify.

Style

  • Map Tiles: Override the default map tiles with one of the included sets.

  • Cluster Markers: If selected, markers that are near to each other are clustered into a single marker that indicates the number of markers in the cluster.

  • Override default style: If selected, you can override the marker shape, icon, color, background color, and border color.

Edit a visualization

To edit a visualization, click its tab on the tab bar, then click the Edit Visualization button beneath the visualization.

Temporarily hide or show only a series

To hide a series in a visualization, click the series in the legend. To show the series again, click it again in the legend.

To show only a single series, double-click the series in the legend. To show other series, click each one.