3 Replies Latest reply on Oct 2, 2018 8:33 PM by David Taylor

    Translating X Y State Plane coordinates, using R

    David Taylor

      Help! I have X Y coordinates, but Tableau can't map them!

      I work with a lot of municipalities who commonly use X Y coordinates, mapped on a State Plane system, instead of traditional Lat/Long, to identify specific points on a map where accidents or crimes or artwork placements occurred. Tableau does a great job of mapping Lat/Long data, but Tableau cannot map X Y data without some sort of translation. Commonly, those customers ask their GIS departments to translate, then that translated data is visualized in Tableau.

       

      But there is an alternative: this article provides a technique for approximate conversion of X Y State Plane geographic data to lat/long data. You need a few rows of translation points – locations that have the correct lat/long for the X Y you’re dealing with. Choose points around the perimeter of your geography to maximize accuracy. Here are ten records within the city of Seattle. There are only five locations here, each with a “version” of A and B. We’ll use the two different versions to test our results in Tableau. (You’ll see.)

       

      We will use a statistical package called R to figure out what our calculations need to look like, but once you’ve completed these steps once, you should never need to use R again – the calculations will continue to work unless your data changes radically. (Switching from Texas data to Zimbabwe data, for example.)

       

      Step by step

      Version,Latitude,Longitude,X,Y

      A,47.73345,-122.2934,1281140,270948

      A,47.62256,-122.3215,1273444,230639

      A,47.49986,-122.24226,1292164,185522

      A,47.52196,-122.38945,1255950,194281

      A,47.6576,-122.4064,1252772,243834

      B,47.73345,-122.2934,1281140,270948

      B,47.62256,-122.3215,1273444,230639

      B,47.49986,-122.24226,1292164,185522

      B,47.52196,-122.38945,1255950,194281

      B,47.6576,-122.4064,1252772,243834

       

      1. Copy/paste the above data into notepad, and save as “c:\temp\llxy.csv”
      2. Download and install R. I used R for Windows, version 3.5.0.
      3. In R: llxy<-read.csv('c:\\temp\\llxy.csv', header=TRUE)
      4. In R: myLong<-lm('Longitude~X+Y',data=llxy)
      5. In R: myLat<-lm('Latitude~X+Y',data=llxy)
      6. In R: print(myLong, digits=10)
      7. In R: print(myLat, digits=10)
      8. These steps will produce two multiple linear regression formulas for predicting Lat/Long based on Y/X. You now need to replicate those in Tableau. Start by Opening Tableau and connecting to llxy.csv as a text data source. All subsequent steps are in Tableau, not R.
      9. Create two calculations, myLat and myLong, as shown. Below I have displayed the R output alongside the Tableau calculation. Hopefully it’s apparent which terms get articulated in which places. Pay special attention to R’s negative numbers. The “iif” wrapper isn’t required, but it will allow us to easily compare the “true” lat/long points to our estimated points in Step 19.
      10. Change the Geographic Type of myLat to Latitude.
      11. Change the Geographic Type of myLong to Longitude.
      12. Change the default aggregation type of myLat to Average.
      13. Change the default aggregation type of myLong to Average.
      14. On a fresh worksheet, double-click myLat.
      15. Double-click myLong.
      16. From the Analysis menu, deselect “Aggregate Measures”.
      17. Drag “Version” from Dimensions to the Color shelf.
      18. From the “Map Layers” menu, turn on “Streets and Highways”.
      19. Zoom in on one of your dots. Once you zoom in close enough, you will see dots of two colors indicating both the A and B (true and estimated) version of the same location. You can evaluate whether or not the approximation is accurate enough for your needs. This worked great for my data set that was relatively compact: all points were within the 84 square miles of the city of Seattle. It uses statistics, not projection geometry, so its approximation will be less accurate for larger geographies. On the plus side, it’s very simple.
      20. Repeat steps above using your own X Y and Lat/Long data! Good luck!

      How accurate is the approximation?

      "It depends." For me, it was plenty accurate: the points were comparable to being at different ends of the same table, versus being on a different building or a different street. Most Tableau maps are viewed only at a high level, and would seldom zoom in far enough notice. But your use case may vary. I suspect this will generally be good enough for small geographies like a city or county, but definitely not good enough for larger geographies like an entire state. True GIS experts can create even better calculations, involving ARCTAN and other geographic calculations, with even greater accuracy.

      Why not use SCRIPT_REAL?

      Tableau’s SCRIPT_REAL function would allow Tableau to call out to R (or Python) dynamically to make these translations, or perhaps even much better translations. That is certainly a great technique, too! However, for most of my municipal customers facing this challenge, this exercise needs to be done only once, and the calculations will never change. Therefore, calling out to R potentially thousands of times thereafter introduces a lot of unnecessary processing.