3 Replies Latest reply on Sep 13, 2013 2:38 PM by Allan Walker

    Creating custom geocoded filled maps from multiple data sources

    Eric Tobias

      I have three sets of source data that I need to blend in Tableau. One is a shapefile containing custom geographic elements (1 million rows), such as census districts (for example), the other is demographic data broken down by the census district (one row per census district), and the third is sales data (multiple rows per census district, total of 10,000 rows).

       

      I'd like to see about two different combinations of this source data.

       

      1. A Tableau workbook/dashboard that combines the census district that is coloured by sales.
      2. Another Tableau workbook/dashboard that combines the census district, colours it by one of the demographics (say population), and overlays a pie chart with product sales in the census district.

       

      Number 1 should be possible, I believe, but I can't find any workable approaches. One comes close, but with my datasets it results in over 100 million rows of data and can't "fill" the geographic entities.. It's just lots and lots of points. This would get even worse if I tried to do a multi-table join with the demographic data.

       

      Note, I don't necessarily recommend the visualization approach used for number 2, but it highlights the more complex type of work I'm trying to do.

       

      I should note that I've already converted the shapefile to a tab-delimited file that can draw proper filled maps on its own. My main problem is when I try to blend these on the geographic element ID from the secondary data source. Tableau doesn't seem to like multiple data connections and blending on the polygons generated from the shapefile conversion.

       

      Is there a way to import the converted shapefile so it just becomes part of the Tableau mapping capability? Then could I use dual marks on the map? If not, is there some other combination that will get this done?

        • 1. Re: Creating custom geocoded filled maps from multiple data sources
          Allan Walker

          Eric,

           

          Ask for the file from the Tableau Mapping website to get the TDE for the census blocks, or you can get the shapefile from Tiger.

           

          Then in ESRI or QGIS merge the shapefiles.  Make sure they are both in WGS84 EPSG:4326.  Ensure the merge has a unique ID.  Then prepare the files for Tableau.  I use ETWizards polygon to point, but you can also use shapetotab.

           

          Import the shapefiles into PostgresSQL/PostGIS.  Left Outer Join your sales table, Left Outer Join your demographic table to the feature table of the merged shapefile as a custom SQL argument. 

           

          Best Regards,

           

          Allan

          1 of 1 people found this helpful
          • 2. Re: Creating custom geocoded filled maps from multiple data sources
            Eric Tobias

            Thanks Allen. The census blocks was only an example of what I'm trying to do. I'm actually going for Canadian FSA shapes. I have the shapefile already.

             

            I'm not sure I understand the information you provided. You say to "merge" the shapefiles, but I only have one shapefile. One shapefile, one Excel file with demographic data, and one Excel file with sales data. I don't have any shapefiles to merge in QGIS. I've transformed the one shapefile to a Tableau suitable format that properly draws filled polygons, so if this is true, would my only remaining step be the left joins you described?

             

            There are a few "magic" steps in what you're describing, which I can likely figure out, but are you aware of any articles that describe the process in more detail? It would save me some time in a time-critical exercise.

             

            Again, thank you for the input. I read a number of your articles in this search, so I see you contribute a lot to the community. A big thank you for that!

            • 3. Re: Creating custom geocoded filled maps from multiple data sources
              Allan Walker

              Eric, sorry - I assumed USA: Census Tracts and Combined Statistical Based Areas.  So you avoid that complication.

               

              So if you have the shapefile transformed ready for Tableau, import the shapefile into Postgres SQL using PGAdmin III or better, the DBF loader.  The DBF file is the feature file part of the shapefile.  It contains the header row that you'll need a key from to left outer the demo & sales data.  Basically, if you have the Census Tract ID in all three, bingo.

               

              Then with custom SQL, pull all three in, and then you could do a colorswapper parameter on the color shelf to give you a choropleth for all your measures.

              1 of 1 people found this helpful