4 Replies Latest reply on Jan 17, 2019 8:04 AM by Don Wise

    Join Spatial file and csv file

    Jamal Awal
    Jamal Awal Jan 16, 2019 2:17 PM

      Hey!

       

      I have an issue with joining original data (imported as a csv file) and a shapefile (GeoJSON):

       

      1. Chicago Crime Data from 2012-2017 (each row represents a crime commited)

      2. Chicago community area shapefile data

       

      I wish to conduct an analysis on community area level, and therefore link each individual crime to its resprective community area by utilizing the map functionalties of Tableau.

      The original dataset contains a column with information about the specific community in which a crime has been committed but in number format. Each community area  (77 in total) has a corresponding number associated with it. E.g. Community Area "Douglas" has a corresponding code of "35".

      I hoped that I could use the Tableaus join function since both datasets contain the numbers associated with the specific community area, and some how "lookup" the Community area name. Sinces names are more convenient than the numbers. But I'm not able join them at all.

       

      Snapshot of GeoJSON file (column 5 & 6):

       

      Snapshot of Crime data (column 12):

      As you can see, the Spatial file works perfectly by itsself.

      So im quite uncertain about why I can't join the two datasets.

       

       

      I have uploaded my Tableau workbook.

      And I should probably mention that I just started using Tableau in the past few weeks.

      Any help/tips/tricks/advice is very much appreciated!

      • 80 Views
      • Categories: Data Sources & Connections, Maps & Geocoding
      • Tags:
        • 1. Re: Join Spatial file and csv file
          Jim Dehner
          Jim Dehner Ambassador Jan 16, 2019 2:17 PM (in response to Jamal Awal)

          first welcome to Tableau

          see the article by Sarah Battersby on spatial joins - it covers yor case

           

           

          Dual Axis Mapping - Many Ways

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Join Spatial file and csv file
            Jamal Awal
            Jamal Awal Jan 17, 2019 5:34 AM (in response to Jim Dehner)

            Hi Jim,

             

            Thanks for the quick reply!

            It helped me with joining the two different files, but it is still quite unclear to me how I can link the number of a given community area with the respective community area name. Do I need to rename all the community areas manually? Or is there a way for Tableau to recognize the community area number as community area name?

            And if renaming everything is the only option, how do I then make Tableau recognize the geographic format of the variable, which allows me to utilize the GeoJSON file, so I can map the crime data in the respective community areas?

             

            Thanks again!

            • 3. Re: Join Spatial file and csv file
              Jim Dehner
              Jim Dehner Ambassador Jan 17, 2019 5:42 AM (in response to Jamal Awal)

              I can't see the data after the Join - so don't know how you connected them - see Sarah's memo starting about 2/3rds of the way down under the heading below

               

              The 2018.1 and beyond easy dual axis method:

               

              it is there that she is placing points on shapes with a dual axis map

               

              Jim

              • 4. Re: Join Spatial file and csv file
                Don Wise
                Don Wise Jan 17, 2019 8:04 AM (in response to Jamal Awal)

                Hi Jamal,

                If you were to hover your mouse cursor over the red exclamation point in the Data Pane Window in Tableau Desktop, you'll see a message that describes the error. Like so:

                Screen Shot 2019-01-17 at 7.48.13 AM.png

                It says the issue is your data is structured as a STRING (ABC/TEXT) on one side of the JOIN from your Chicago Crime Data table and as a WHOLE NUMBER (1,2,3,4,5, etc.) in the other table (.shp file).  Tableau cannot join on different data types. 

                 

                In this case, I changed the STRING to be recast as an Integer (Whole Number), so that it would recognize the JOIN between the two columns.  I used [Area Numbe] field. 

                 

                You had a good start and were on the right track though...please see below screenshots for context as to how to correct the issue.

                Screen Shot 2019-01-17 at 7.52.42 AM.png

                Screen Shot 2019-01-17 at 7.52.46 AM.png

                Your data will be INNER-JOINED thereafter.  Hope it helps!  Thx, Don