1 2 3 Previous Next 95 Replies Latest reply on May 2, 2016 12:13 PM by Luke Stanke

    ESRI Shapefile importer: R

    Mary Solbrig

      I've created a bit of R code for importing ERSI shapefiles into Tableau Desktop, attached.

       

      Requirements:
      R and RStudio, which are free and easy to download and install.

       

      It does require altering 2 lines of code: You must enter what you want the file to be called, and the path of the directory where you want the file to be written. Step-by-step instructions are attached.

       

      Advantage this has over other importing methods? I think it is more user friendly, the required downloads (R and RStudio) are easy to manage.


      Places to go from here:

       

      • Currently it only works for polygon-type shapefiles. I will update it to work for other types as well.
      • I could look into making it export into a file type that could be used as a background map, but not without making it harder to use.
      • Originally I exported the Data and the coordinates as separate files. It now exports as a single file. Do people have preferences about that?
        • 1. Re: ESRI Shapefile importer: R
          allan.walker.0

          Mary,

           

          Here's my experience FWIW:

           

          1) Anything that pre joins data to the shape would be welcome.  It's the #1 problem i seem to see on this forum.

          2) Re-project to EPSG:4326 for Tableau maps, assumes a PRJ file in the SHP schema.

          3) Simplify vertices for those limited by 32-bit

          4) Support for inner ring polygon

           

          Shapetotab already does 2+3 but not polyline, and I'm unsure about 4).

           

          If this can be called directly from R as a CF (Tableau integrates with R) as a run-time routine: that would be awesome; perhaps you could parameterize the path and the export name.

           

          Best Regards,

           

          Allan

          • 2. Re: Re: ESRI Shapefile importer: R
            Mary Solbrig

            The attached code now contains 3 functions: TabShapeRLines, TabShapeRPoints, TabShapeRPolygons. Each function deals only with the shapefile type indicates by its name.
            For example, the following workbook was created using the data from this link: Read and write ESRI Shapefiles with R | NCEAS

             

            Workbook: Shapefile Import

             

            Is this the type of polylines shapefile you meant?

             

            In response to common issues:
            1) This exports as a single file, so I believe this resolves the issue?
            2) Yes, that would be useful. I'll look into that.

            3) An interesting problem. I'll look into what sorts of algorithms exist in R already, but otherwise I might have other priorities...

            4) I would have to use custom geocoding to accomplish this, at which point it would lose the user-friendly benefits that this provides. I could just delete inner polygons from the data, but otherwise I think that would be unsupported.

            5) Personally, I don't see the benefit of being able to call these functions from within Tableau. The R-integration feature is good for sending data from Tableau to R, and then manipulating it in R. This is not what is happening here; all that is happening is exporting data from R to Tableau. If instead you wanted to manipulate the shapefiles based on data from Tableau (for instance, create a cartogram, which is my next project), then it would make sense. For this, I don't think it does. However, if you want to anyways, copy the code from the TabShapeR.R file into a file called Rserv.cfg, put some 'execute' statements before each library call and function definition, and place it in the same file as Rserve.dll.

             

            I'll see about implementing the modifying projections thing, and then reply again with better instructions. If you have any other feedback, let me know.

            2 of 2 people found this helpful
            • 3. Re: Re: ESRI Shapefile importer: R
              allan.walker.0

              Mary,

               

              This is outstanding work and I am grateful for your contribution, especially the polyline work.

               

              Best Regards,

               

              Allan

              • 4. Re: ESRI Shapefile importer: R
                Mansi Parekh

                Hi Mary,

                 

                Thanks a lot for describing the process and sharing the R codes.

                 

                I am currently working on showing the Wards (or precinct) Map for Wisconsin. I used your R codes to get the  latitude -longitudes in csv format from the shapefiles for polygon.

                 

                I had initially used the 2002 Wards data and your code worked perfectly. But when I tried replicating it for the new 2012's Wards data, I am facing problem with latitude longitudes. They aren't coming correctly. I am not sure whether there is some problem with the data or something needs to be changed in your R code (I am not much familiar with R programming so don't know where to edit).

                Can you please help me a little on this?

                 

                I have attached 2012 Wards data here which is downloaded from the link:Census 2010 Data

                 

                Thanks,

                Mansi

                • 5. Re: ESRI Shapefile importer: R
                  Mary Solbrig

                  Thanks for trying the code! I'm glad it worked for the one file.

                   

                  Looking at the data in the Wards file, it looks like the map is projected. This means that the coordinates that my code ends up spitting out are not latitude and longitude, but projected latitude and longitude. I'll work on having the next iteration also adjust for projection, which should resolve this issue.

                  I'm a bit surprised that similar data from the same department was given with different projections, but I guess they could be compiled by different people and such. Normally there is a text file that comes with the shapefile that explains exactly what the file contains, who made it, how the data was collected, etc, and what projection is used. Is that somewhere else on the site and I just missed it? Without that file though, one way to check whether the file will make sense before importing the csv into Tableau is to type, "bbox(ShapeFile)" into the R console. This will tell you the bounding box of the shapefile imported. In this case, the bounding box was 294822.8, 773288.3, 225108.6, 734398.1. These don't look like standard latitude/longitude coordinates at all, so you know that the current code will fail to produce anything that Tableau can read.

                  • 6. Re: Re: ESRI Shapefile importer: R
                    Mansi Parekh

                    Thanks Mary for looking into this file. You are correct, these doesn't look like standard latitude/longitude at all and I agree that there should be a file with the information about the data which would guide you to change the R code.

                     

                    But unfortunately, even I am unaware of its location. I just clicked Wards at Bottom right to download this data..

                    Wards.JPG.jpg

                    Are you aware of any other source from where we can get the latest (2010) Wards data with correct latitude/longitude which Tableau can read?

                    • 7. Re: ESRI Shapefile importer: R
                      Sergey Vershinin

                      You can always get a trial copy of ArcGIS Desktop, and use it to re-project your shapefile.

                       

                      I just did it with a shapefile of municipal wards I got form Ottawa's open data portal.

                       

                      Once you get ArcGIS installed, run ArcMap, cancel the "New Map" dialog and do the following:


                      1. Go to "Geoprocessing" in the menu

                      2. Select "Arc toolbox"

                      3. Go to "Data Management Tools/Projections and Transformations/Project"

                      4. Select your shapefile (note that you may need to "connect" to a local folder on your machine)

                      5. Specify the output location (by default it will add it to the default.gdb)

                      6. Specify the desired projection (search for "NAD 1983")

                      7. Run it (it takes a bit of time and a pop-up will come-up in the bottom right corner of the screen when it's done

                       

                      If you ended-up with the newly projected data frame in the default.gdb, you can get it out to a regular shapefile by adding the newly projected data to a blank map, and then exporting it to a file by right-clicking the data layer.

                       

                      Let me know if you have any problems with the above instructions and I'll do what I can to help.

                      • 8. Re: ESRI Shapefile importer: R
                        allan.walker.0

                        Or you can just use the "save as" dialogue box in QGIS.

                         

                        Best Regards,

                         

                        Allan

                        • 9. Re: ESRI Shapefile importer: R
                          Mary Solbrig

                          I have updated it to deal with projected data. I have attached the Wards data, exported into Tableau in a normal lat/long format, although you probably don't need that...

                           

                          It now functions a bit more slowly (it's using a different R package), and now takes 3 parameters instead of just 2:

                           

                          Functions: TabShapeRPolygons, TabShapeRPoints, TabShapeRLines

                          Parameters: Path, ImportName, ExportName

                          Path: Directory with .shp, .shx, .dbf, and .prj if applicatble. Also where .csv will be written.

                          InportName: Apparently I can't spell? Name of import file, without .shp/.shx/ etc. Example: "Wards_111312"

                          ExportName: What you would like the .csv to be called. Example: "Wards" will be written as "Wards.csv"

                          1 of 1 people found this helpful
                          • 10. Re: ESRI Shapefile importer: R
                            allan.walker.0

                            Nice work, Mary!

                             

                            Now, let's see some amazing examples using this script!

                             

                            Best Regards,

                             

                            Allan

                            • 11. Re: ESRI Shapefile importer: R
                              Dustin Smith

                              This is seriously cool stuff.

                               

                              Hey The specified item was not found. - if ESRI Shapfiles & R scripts are your thing, you should check this out.:)

                              • 12. Re: Re: ESRI Shapefile importer: R
                                allan.walker.0

                                Hi Mary,

                                 

                                So I just can't get this to work.  I've downloaded all the necessary packages (you might want to add the package dependencies into your instructions).  I tried using the latest version.

                                 

                                I have a polyline SHP that I need: please find attached.  If I can get the CSV's back in response, that would be great.

                                I've pre-joined the Seattle Street Name dB with the addresses, and also with the traffic counts.  I can of course style this as a background layer, but I'd like to do the analysis in Tableau.  It's all projected in EPSG:4326, so no problem there.

                                 

                                Best Regards,

                                 

                                Allan

                                • 13. Re: Re: Re: ESRI Shapefile importer: R
                                  Mary Solbrig

                                  You are right, I forgot to include the rgdal package

                                   

                                  I was able to get this to work though as soon and I added that library. It took a while, I would guess about 2 min, but worked. See attached .csv in .zip. This can be plotted by:

                                  1. Select Line in plot type drop down from Marks card

                                  2. Drag Line ID to Detail shelf

                                  3. Drag PlotOrder to Path

                                  4. Deselect Analysis > Aggregate Measures

                                  5. Double click Latitude

                                  6. Double click Longitude

                                   

                                  And this is definitely going to be much faster in 64 bit.

                                   

                                  I've written up some instructions that should make this more clear, I just want to run them by some people before posting them. Some reasons I can think of off hand it might have failed:

                                   

                                  1. If using a Windows machine, make sure the path uses / and not \. Windows paths are odd.

                                  2. Make sure that the file name does not have .shp at the end

                                  The exact code I used to run this:

                                  directory <- "C:/Users/msolbrig/Desktop/Map Sources/Seattlestreetnetwork"

                                  name <- "Seattlestreetnetwork"

                                  endName <- "Output"

                                  TabShapeRLines(directory,name,endName)

                                   

                                  If this doesn't work, if you could send any error messages that appeared in R then I'll see if I can figure out what the issue is. Also, is this a Linux machine? I don't think it should work differently on Linux, except the formatting of the path.

                                  • 14. Re: ESRI Shapefile importer: R
                                    allan.walker.0

                                    This is an example of what Mary's tool can do:

                                     

                                    Seattle Traffic Counts, 2007

                                     

                                    Seattle.jpg

                                    1 2 3 Previous Next