1 2 Previous Next 15 Replies Latest reply on Nov 19, 2018 9:59 AM by Don Wise

    Joining CSV and Spatial Files

    Christine Crews

      Hi Michael Gillespie,

       

      We are having trouble joining a spatial file and CSV file on Tableau. The spatial file has the city of Chicago community points. We are trying to plot the data points in the city of Chicago spatial file with the "Dropoff Centroid Long" data points (latitude and longitude) located in the CSV file ("NORTH SIDE") We are receiving errors from Tbleau when we try to join the datasets. We tried to use "Dropoff Community" located in the CSV file and "Area Number" located in the KML (spatial) file, since they share commonalities, as the primary keys to connect each file, but it does not work. I have attached the CSV file and the Spatial file separately below so hopefully we can play with the joins.

       

      Any feedback or suggestions would be so appreciated! Thank you so much in advance!

       

      Best,

      Christie

        • 1. Re: Joining CSV and Spatial Files
          Don Wise

          Hi Christine,

          So I'm able to view the .xslx file but the .shp file provided won't open unless you have the other related files that support the .shp file; usually, .dbf, .shx, etc.  Whomever has supplied the .shp file should also have provided the related/supporting files in order for Tableau to recognize the .shp appropriately.  Otherwise, you'll get an error that looks like this:

           

          An error occurred while communicating with the data source.

           

          Unable to connect to the file "Y:\Downloads\geo_export_8821fae9-ffbf-43e6-a124-bdd001ea51b9.shp". Check that the file exists and that you have sufficient access privileges for it.

          There was an error reading the Shapefile: "Y:\Downloads\geo_export_8821fae9-ffbf-43e6-a124-bdd001ea51b9.shp".

           

           

          Check to ensure that "Y:\Downloads\geo_export_8821fae9-ffbf-43e6-a124-bdd001ea51b9..shx" and "Y:\Downloads\geo_export_8821fae9-ffbf-43e6-a124-bdd001ea51b9...dbf" are all included in the same directory.

          The table "[TableauTemp].[geo_export_8821fae9-ffbf-43e6-a124-bdd001ea51b9.shp]" does not exist.

           

          Thx, Don

          • 2. Re: Joining CSV and Spatial Files
            Christine Crews

            Hi Don,

            I have other files that were provided (I didn't realize they would be needed, my apologies), but as I'm replying to you I do not see an option to add them. Will I need to make an entirely new post?

            Thanks! Christie

            • 3. Re: Joining CSV and Spatial Files
              Don Wise

              Hi Christine,

              Try clicking on Advanced editor and add an attachment and then you should be able to add them or edit your original post and add them...

              Screen Shot 2018-11-14 at 1.39.18 PM.png

              • 4. Re: Joining CSV and Spatial Files
                Christine Crews

                I attached the .shx, .dbf., and .prj to the original post for you to see.

                • 5. Re: Joining CSV and Spatial Files
                  Don Wise

                  Hi Christine,

                  OK, please see attached 2018.3 workbook. Not sure about your results here due to how the data is joined. Because there's not much in the POINTS (.xlsx) file other than Lat/Lon, I was left with doing a workaround of creating an OUTERJOIN using a 1:0 relationship between the .shp GEOMETRY file and the POINTS file.  Ideally, the POINTS file would also be formatted as a .shp file (with all of the supporting files), then one could potentially use Tableau's newest 'Intersect' feature between the two files.  Tableau would recognize that the join is spatial between both files and then appropriately recognize the lat/lon points into the polygons.

                   

                  Also, I noticed that the Pickup and Drop Off points are exactly the same?  Something for you to look at...hopefully it helps?  If yes, please mark as helpful or if actually correct as correct.  Thx!  Don

                   

                  With the supporting .shx and .dbf and related files, Tableau is able to recognize and use the GEOMETRY of the .shp file:

                  Screen Shot 2018-11-14 at 1.49.12 PM.png

                  And it will display something similar to this, where it recognizes that there are POLYGON layers available to use:

                  Screen Shot 2018-11-14 at 2.00.43 PM.png

                  To do the JOIN, selected Create Join Calculation and simply type in '0' on one side and then a '1' on the other side, then select OUTER JOIN:

                  Screen Shot 2018-11-14 at 2.04.49 PM.png

                  Screen Shot 2018-11-14 at 2.05.05 PM.png

                  Slide GEOMETRY to the Marks Card and the polygons will display like so:

                  Screen Shot 2018-11-14 at 2.07.31 PM.png

                  Add another instance of Latitude to ROWS next to the first one and second map will display.  Remove the GEOMETRY pill and replaced with Lat/Lon of the Pickup locations (Latitude P and Longitude P):

                  Screen Shot 2018-11-14 at 2.09.58 PM.png

                  Right-click on the Latitude Pill and select Dual-Axis:

                  Screen Shot 2018-11-14 at 2.10.04 PM.png

                  You'll have a layered map view that looks like this:

                  Screen Shot 2018-11-14 at 2.15.10 PM.png

                  In duplicating the map and then changing Pickup Locations to Drop-Off Locations, is when I noticed the POINT values were the same:

                  Screen Shot 2018-11-14 at 2.15.17 PM.png

                  • 6. Re: Joining CSV and Spatial Files
                    Michael Gillespie

                    There is no "Dropoff Area" column in the CSV file.

                     

                    In the .shp file, the Area column is a Decimal, and the Area Num 1 and Area Number columns are Text.  The "Pickup Area" column in the CSV is Numeric.

                     

                    I'll keep playing, but should there be a Dropoff column in the CSV?

                    • 7. Re: Joining CSV and Spatial Files
                      Don Wise

                      Hi Michael,

                      This is what I'm seeing when I open the MS-Excel file...

                      Screen Shot 2018-11-14 at 2.43.30 PM.png

                      • 8. Re: Joining CSV and Spatial Files
                        Michael Gillespie

                        I hate how the commenting system shows replies: that was for Christine!  There is a column called "Pickup Community Area".  Christine said she wanted to use "Dropoff Community Area" but that column does not exist.  There are Pickup & Dropoff Lat/Longs & Centroids (as in your screenshot) but nothing to join on for Dropoff locations, only for Pickups.

                         

                        You beat me to the solution.  Very nice!

                        • 9. Re: Joining CSV and Spatial Files
                          Don Wise

                          Hi Michael,

                          I know...it's frustrating some times; i didn't see that she'd named you until after I'd posted everything up.  Sorry if I intruded! D

                          • 10. Re: Joining CSV and Spatial Files
                            Michael Gillespie

                            not at all!  Glad you got it figured out quickly.

                             

                            Michael Gillespie

                            • 11. Re: Joining CSV and Spatial Files
                              Christine Crews

                              My apologies! The Drop off community area is the exact same as the pick up area. As I was cleaning the data I decided I only needed one column since they are the same and I named the wrong one in my initial approach.

                              • 12. Re: Joining CSV and Spatial Files
                                Christine Crews

                                Hi Don Wise,

                                 

                                We appreciate your help so much and we would love to move forward with this packaged workbook, but we are having trouble opening the file. When I try to open the file, I get an error message that it is an incompatible document. I took time to reply to your post and let you know because I wanted to see if any professors/TAs were able to help open the document before I troubled you. Do you have any suggestions for how I can open this file? Thank you so much for all your help! This is truly amazing!

                                 

                                Thanks,

                                Christie

                                • 13. Re: Joining CSV and Spatial Files
                                  Don Wise

                                  Hi Christie,

                                  What version of Tableau are you using? I'm on 2018.3. Thx! Don

                                  • 14. Re: Joining CSV and Spatial Files
                                    Christine Crews

                                    I am using 2018.2, does that affect my ability to open the document?

                                     

                                    Thanks!
                                    Christie

                                    1 2 Previous Next