1 Reply Latest reply on Jan 9, 2019 3:42 PM by Kent Marten

    Unable to import custom geocoding file for European cities not recognized by Tableau

    Nikos Fournarakis

      Hi all and happy new year!

       

      I came across the following issue and I would really appreciate some help.

       

      I am working on map visualization (please see screenshot below), where I am trying to show Sales from stores (red) vs orders from the online store (blue). Some of the stores are located in small towns (<15k population) that are not automatically recognized by Tableau.

       

      In my dataset, I only have information for Country and City, State is not included.

       

      According to the following thread (Map Locations Not Recognized - Tableau ), even though State does not exist as a field in my data, the text file should include State in the hierarchy, in order to be aligned with the Tableau built-in geographical hierarchy:

      - Country (Name)

      - State/Province

      - City

       

      Below an example of the csv file I tried to upload (the complete file is attached). I have intentionally left the State field empty, since this information is not available:

       

       

      Country (Name)State/ProvinceCityLatitudeLongitude
      United KingdomSouth Normanton53.10821.3407
      United KingdomLondonderry54.99667.3086
      United KingdomKent51.27870.5217
      United KingdomGreenhithe51.4490.2846
      United KingdomCheshire53.23262.6103

       

      I got the following error message:

       

      I removed the hierarchy of Country-City in my workbook, removed the field State from the csv file, but still got the same error. If I don't remove the hierarchy, and exclude State from the csv file, then I get an error that State is missing. I guess this means that if you go for hierarchies, Tableau expects you to include the full hierarchy in the csv file.

       

      Does anyone have any idea what I could do in such a case?

       

      All I am want to do is upload the coordinates of only the cities that are not recognized by Tableau, by no chance do I want to extend the existing role and create a new role for cities, including the ones that are recognized (which is the case in most of the threads I have found in Tableau forums so far).

       

      I hope the explanation is clear, if not, please let me know!

       

      Thanks in advance!

       

      Kind regards,

       

      Nikos

       

      Denis Windisch : I have written a comment under this thread (Custom geocoding - >600 ambiguous cities ). It seems that you have found the solution to this problem , but unfortunately I don't seem to understand exactly how you did it. Any feedback would be more than welcome

        • 1. Re: Unable to import custom geocoding file for European cities not recognized by Tableau
          Kent Marten

          Hi Nikos,

           

          For your case, you will want to extend the existing role of City in the Tableau geocoding database. To do this, you will need to format your table like this:

          You cannot leave State/Province blank.

           

          *

           

          A workflow for determining the State/Provinces for your cities would be this:

           

          1. Install QGIS - the open source GIS tool - with which you can perform some spatial analysis to determine the correct State/Province
          2. Using QGIS - add delimited text layer using your .csv file
          3. Go to gadm.org and download administrative shapefiles for Germany, France, and the UK.
          4. Using QGIS - use the merge vector layers tool to union the state/province shapefiles for Germany, France, and UK.
          5. Using QGIS - run Join Attributes by Location; joining the attributes from the state/province merged shapefile to the points of unrecognized cities
          6. Using QGIS - update the attribute table using the Field Calculator
          7. Export the table as .csv

           

          I've attached the merged shapefile, but also the final result based on the data you shared. You will still need to cross the finish line but updating the table to be in the correct format (using the prescribed field names). Note, the data for the UK unrecognized locations did not plot in a location that intersected the UK admin.

           

          1 of 1 people found this helpful