Sunday, September 29, 2013

Tableau : Convert ESRI shapes into Tableau Format



Requirement is to convert data in ESRI Map shapes into Tableau format.

Tools Required
  1. Quantum GIS - http://www.qgis.org/en/site/
  2. Python 
  3. Download the below Python Code - https://github.com/venkat-vs-id/python
  4. import csv
    import re
    import sys
    
    #def convertESRI2TableauFormat( p_inputfile, p_outputfile):
    
    l_outfileName = 'c:/ESRI_suburb_tab_format.csv'  # make changes here..
    l_ESRIFileName ='c:/ESRI_suburb.csv'             # make changes here..
    
    rdx=0
    csv.field_size_limit(sys.maxsize)
    outfile = open( l_outfileName, 'w', newline='')
    csvWriter = csv.writer( outfile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    
    with open( l_ESRIFileName, 'r') as csvfile:
    
     csvR = csv.reader( csvfile, delimiter=',', quotechar='"')
     for row in csvR:
        rdx += 1           
        ci=0
        rowValues = []
        rowValues.append(row[1]);# ssc_code
        rowValues.append(row[2]);# ssc_name
        rowValues.append(row[4]);# state_code
        rowValues.append(row[5]);# confidence
        rowValues.append(row[6].strip());# AREA_SQKM
        rowValues.append('');# place holder for lat
        rowValues.append('');# place holder for long
        rowValues.append('');# place holder for polygon
        rowValues.append('');# place holder for point
          
        if ( rdx==1): # first row add header col names
    
           rowValues[5]=('Latitude');# 
           rowValues[6]=('Longitude');#
           rowValues[7]=('Polygon_ID');#
           rowValues[8]=('Point_ID');#
           csvWriter.writerow( rowValues);
        else:        
           for col in row:
    
               ci += 1
               LatLonStrArr=[]
    
               if (ci==1): # first col has the polygon values.
    
                   polygonArr = []
                   polygonArr = re.findall('(\((?:-?\d+\.\d+\s-?\d+\.\d+,?)+\))', col)
                   polyx =0
                         
                   for px in polygonArr:
    
                       polyx += 1
                       LatLonStrArr = []
                       LatLonStrArr = re.findall('(-?\d+\.\d+\s-?\d+\.\d+)', px)
    
                       if( len( LatLonStrArr) != 0 ):
    
                           pointx  = 0
                           pointxx = 0
                           for lx in LatLonStrArr:
    
                               pointx += 1
                               resArr = re.findall( '(-?\d+\.\d+)', lx)
                               #print('>>>> lx='+lx + "/"+ str(len(resArr)))
                               rowValues[5] = resArr[1] # lat
                               rowValues[6] = resArr[0] #long
                               rowValues[7] = polyx
    
                               if( (pointx ==1) | (pointx == len(LatLonStrArr))| (len(LatLonStrArr)<=10)| (pointx % 10 == 0) ):
                                  pointxx += 1
                                  rowValues[8] = pointxx
                                  csvWriter.writerow( rowValues);
       
        #print('ROW========================='+ str(rdx))
        #for rx in rowValues:
        #   print('val=' + str(rx))
        
        #if (rdx >=100):
           #break
                
    csvfile.close()
    outfile.close()
    
For the sake of this exercise I downloaded the census data from ABS website ( free registration required).

Step to convert ESRI data into Tableau Format

  1. Download your ESRI data. and it will look like this 
  2. Open Quantum GIS
  3. From the top menu -> new project
  4. From the top menu -> Layer -> "Add Vector Layer" ( in this select the .shp file downloaded in step 1) 
  5. Now you will be able to see the From maps and Shapes from the <filename>.shp file
  6. Right-click and select "save as"
  7. Make sure you save as with the Layer Option as GEOMETRY=AS_WKT 
  8. Now you have exported the ESRI  shp file into a CSV
  9. Use the Python code from above, and make the changes to the 2 variables 
    1. l_outfileName
    2. l_ESRIFileName
  10. Run the above py code- you will get the CSV file readable by Tableau
  11. If you dont know how to use that CSV in Tableau - Look into this Blog - Tableau Maps - Custom shapes / polygon - Basics


10 comments:

Jura said...

Hi Venkat,

I follow your step by step but the outfile generated a empty csv file.

Jura said...

Thank you for sharing this kind of tool with us...

Congratulation

Venkatachalam Veerabagu Subramanian said...

@Jura,
Q1) R u getting an empty file from python script Or from the GIS tol(step-7)?

Q2) Please give me more details to help u.

tanooj said...

Hi Venkatachalam Veerabagu Subramanian ,Thank for you great support,i am looking for the same solution , but i am facing problem with python script.Its throwing some errors while executing.
Please let me out of this scenario,also i can attach you qis generated csv file

Venkatachalam Veerabagu Subramanian said...

Sorry, i was away from my blog for a while. Let me know if you still need help.

david dou said...

The code you posted on the github page doesn't work and is different from the one you have on your webpage. when using your script this error is consistently displayed.
“OverflowError: Python int too large to convert to C long”

Lalita said...

Hi Venkat,

This seems like a cool tool, But I get error whenever I try to run the code. Would appreciate if you could help.

Venkatachalam Veerabagu Subramanian said...

Im been a while and i dont have a tableau instance with me to test it.. anyway if you tell me what kind of error you get, I will see what I can do for you.

Leslie Lim said...

I read your blog.I thought it was great.. Hope you have a great day. God bless.

Camille
www.imarksweb.org

likitha said...

I like your blog, I read this blog please update more content on hacking,
Tableau Online Training