What are "best-practices" for organizing location/geographic data so that it plays nice with map layers?

[Steve_Guidi]

Hello!  I’m experimenting with map layers for rendering data in geographical regions and I’m finding some limitations that I can’t seem to work around.  Perhaps this is caused by my data schema, or my understanding of how-to best use map_layer_name in a dimension, or both.

My view has dimensions for country, state, city, and postal code, but the dimensional data may contain location information for any country, and any of that country’s states/provinces, cities, etc…  The dimensional data is denormalized into the fact table, but that may change as we add more location-related attributes.

It seems like many of the existing map layer files are region-specific.  That said, is there a strategy to load a correct map layer file given context of the country or region being queried, perhaps by inspecting a filter or query column?  Ideally, I’d like to support a world-view, but that only seems possible if I find world map layer file.  I’m also curious if modifying my schema will make a world-view presentation much more feasible.

[Dawid]

I don’t think we can use Liquid there, though I never tried. When I had to deal with international geographical location I would do one of the two things:

1. Create normalised levels of geographical data in the Data Model, using LAU (https://ec.europa.eu/eurostat/web/nuts/local-administrative-units)
2. Then I would create specific dimensions for specific countries’ fields and map them to specific layers

Other approach

1. The Data Model already has a country specific fields like:
   1. country
   2. spain_community
   3. spain_catalunya_comarca
   4. spain_province
   5. spain_neighbourhood
   6. uk_district
2. Then in Looker it’s easier because each field is already separated, no need for IFs

However, I recognise the lack of flexibility with either approach if you need all the levels for many  countries. 

If I had to do it I would start merging maps in QGIS, unfortunately files would get big, and probably too big at some point but without the ability to use Liquid in the map_layer_name parameter, or some kind of native ability to deal with geographical data, we would reach some kind of a bottleneck. 

Going further than liquid, this would be awesome:

dimension: lau_1 {

  sql: ${TABLE}.lau_1 ;;

  map_layer_name: lau_1_{{ country._value }}

}

[Steve_Guidi]

Thank you for the feedback, @Dawid!  I’ve confirmed that the Liquid syntax features doesn’t apply to the map_layer_name attribute and I so I opted to redefine each dimension per desired map layer.

  dimension: postal_code {
    group_label: "Geography"
    type:  string
    sql:  ${TABLE}."POSTAL_CODE" ;;
  }

  dimension: postal_code_CA {
    group_label: "Geography: Maps - Postal Code"
    label: "Postal Code (CA)"
    map_layer_name: canada_fsa
    type:  string
    sql:  ${TABLE}."POSTAL_CODE" ;;
  }

  dimension: postal_code_US {
    group_label: "Geography: Maps - Postal Code"
    label: "Zip Code (US)"
    map_layer_name: us_zipcode_tabulation_areas
    type:  string
    sql:  ${TABLE}."POSTAL_CODE" ;;
  }

I used the group_label field to remove clutter from the UI.  In this case, users may select a column from the “maps” group if they want visualization, which behaves almost like a parameter.  The no-map column is an optimization for when no visualization is required and thus no call is made to a paid API service.

A work-in-progress, but it should be usable for the time being!