Tableau: Convert Oracle Spatial Data into Tableau Format


I had requirement to shapes we had in Oracle Spatial data( which we used for MapInfo) in Tableau. Since tableau doesnt support Oracle spatial type columns, I had to extract that data in Tableau readable format.

Here are the steps to convert oracle spatial data into format useable by Tableau.
  1. Run the Oracle package ven_OraSpatial_2_TabData in your oracle environment.
  2. Now run the below "ConvertDataFormat" code to extract the data into Tableau format
  3. Now use extracted data in tableau ( refer to my blog - Tableau Maps - Custom shapes / polygon - Basics)
You can also download the code from this link -  VEN_ORASPATIAL_2_TABDATA.zip

Oracle package ven_OraSpatial_2_TabData
SPEC
CREATE OR REPLACE PACKAGE VSUBR.ven_OraSpatial_2_TabData as

----- TYPE 
TYPE rec_LatLong IS RECORD( Polygon_ID NUMBER,
                            LAT       NUMBER,
                            LON       NUMBER
                          );
TYPE tab_LatLong IS TABLE OF rec_LatLong INDEX BY BINARY_INTEGER; 

 
FUNCTION extract_LatLong_From_Geomentry( p_geoLoc SDO_GEOMETRY)
  RETURN ven_OraSpatial_2_TabData.tab_LatLong;

END ven_OraSpatial_2_TabData;
/
BODY
CREATE OR REPLACE PACKAGE BODY VSUBR.ven_OraSpatial_2_TabData as 

FUNCTION extract_LatLong_From_Geomentry( p_geoLoc SDO_GEOMETRY)
  RETURN ven_OraSpatial_2_TabData.tab_LatLong IS

  arr_LatLong   ven_OraSpatial_2_TabData.tab_LatLong;
  idx           NUMBER;
  
  l_geoClob     CLOB;
  l_chunk_size  NUMBER:=4000; -- keep it way less than l_str.. cos there are some contatenation logic
  l_clob_len    NUMBER;
  l_str         VARCHAR2(32767);
  l_start_pos   NUMBER:=1;
  l_start_end   NUMBER;
  l_polygon_id  NUMBER:=0;
     
  l_lat_lon     VARCHAR2(4000);
  l_lat         NUMBER;
  l_lon         NUMBER;
  l_start_lat   NUMBER;
  l_start_lon   NUMBER;

  ---- These are from my checks ( to make sure my assumptions are correct) and for debugging
  l_num         NUMBER;
  l_trace       VARCHAR2(10);
  l_my_exception EXCEPTION;
BEGIN
  ---- Sample GeoLoc will be like this
    -- POLYGON ((144.68613516 -37.89121599, 144.68314896 -37.89260001,144.687717 -37.89048096, 144.68613516 -37.89121599))
  ---- My Assumptions
    -- please note that the starting co-ordinates(144.68613516 -37.89121599) will be( or should be) the same as the last one. only then the polygon will be complete.
    -- All co-ordinates starts with (only) "POLYGON" OR "MUTLIPOLYGON" 
     
  IF p_geoLoc IS NOT NULL THEN
     
      SELECT p_geoLoc.Get_WKT()
        INTO l_geoClob  
        FROM DUAL;
        
      l_clob_len := dbms_lob.getlength(l_geoClob); 
     -- dbms_output.put_line('len='||l_clob_len);
  END IF;
  l_trace :='0.1';
  
  FOR cx IN 0..( FLOOR(l_clob_len/l_chunk_size))
  LOOP 
        l_trace :='0.2';
        -- in case the CHUNK has cut the string into half.. we need to join that, so taht we dont lose anything.
        l_str := l_str || DBMS_LOB.SUBSTR( l_geoClob, l_chunk_size, (l_chunk_size*cx)+1 );  
                
        ---- clean it
        l_str := REPLACE( l_str, CHR(10));
        l_str := REPLACE( l_str, CHR(13));     
        l_str := REPLACE( l_str, CHR(9));
        
        
        
        ---- if not it means it we cut in the middle of a str and we have to join with the remaining
        WHILE (INSTR( l_str, ',') > 0 OR INSTR( l_str, '))') > 0 )
        LOOP
            l_trace :='0.3';        
            ---- checks to make sure my assumptions are correct
            l_num := REGEXP_INSTR( l_str, '((MULTI)?POLYGON (\()+|(,)?( )?(\()+)', 1, 1, 0, 'i');
            IF l_num = 1 THEN   
               l_str := SUBSTR( l_str, REGEXP_INSTR( l_str, '((MULTI)?POLYGON (\()+|(,)?( )?(\()+)', 1, 1, 1, 'i'));
            END IF;   
            
            l_trace :='0.36';     
            l_lat_lon := REGEXP_SUBSTR(l_str, '[^,)]+', 1, 1, 'i');
            --dbms_output.put_line( l_lat_lon||'/'||l_str);
               
            ---- clean it
            l_lat_lon := LTRIM( RTRIM( l_lat_lon));
            
            ---- Remove the above Lat_long from l_str.. gets to the point after any 
            l_str := LTRIM( SUBSTR( l_str, REGEXP_INSTR(l_str, '[,)]+', 1, 1, 1, 'i')+1));
            
            l_trace :='0.37';
            ---- separate out lat and longi 
            l_lat     := REGEXP_SUBSTR( l_lat_lon, '[^ ]+', 1, 2, 'i'); --NOT of space
            l_trace :='0.38';
            l_lon     := REGEXP_SUBSTR( l_lat_lon, '[^ ]+', 1, 1, 'i'); --NOT of space
            
            l_trace :='0.4';
            ---- if null raise error         
            IF l_lat IS NULL OR l_lon IS NULL THEN
                        
               dbms_output.put_line('Lat Long values not proper..'||'/'||l_str);
               dbms_output.put_line('ERROR4:Lat Long values not proper..'||'/'||l_lat_lon);
               RAISE l_my_exception;
            END IF; 
            
            l_trace :='0.5';
            ---- work out if the polygon eneded or starting. 
            IF l_start_lat IS NULL OR l_start_lat IS NULL THEN
                        
               l_start_lat  :=  l_lat;
               l_start_lon  :=  l_lon;
               l_polygon_id := NVL( l_polygon_id, 0) + 1;
            ELSIF l_start_lat = l_lat AND l_start_lon = l_lon THEN -- means a new ploygon is going to start

               l_start_lat  :=  NULL;
               l_start_lon  :=  NULL;    
            END IF;   
            
            l_trace :='0.6';
            idx := arr_LatLong.COUNT+1;
                                 
            arr_LatLong( idx).Polygon_ID := l_polygon_id;
            arr_LatLong( idx).lat        := l_lat;         
            arr_LatLong( idx).lon        := l_lon;
        END LOOP;
  END LOOP;

  IF l_start_lat IS NOT NULL OR l_start_lon Is NOT NULL THEN
  
      dbms_output.put_line('Polygon not complete..'||'/'||l_str);
      dbms_output.put_line('ERROR4:Polygon not complete..'||'/'||l_polygon_id);
      RAISE l_my_exception;
  ENd IF;

  --l_trace :='0.7';
  IF ( dbms_lob.isopen( l_geoClob) = 1 ) THEN
       dbms_lob.close( l_geoClob);
  END IF; 
  
  RETURN  arr_LatLong;
EXCEPTION 
  WHEN OTHERS THEN
      dbms_output.put_line( 'ERROR1:'||l_trace||'/'||l_lat_lon||'/'|| SQLERRM);
      IF ( dbms_lob.isopen( l_geoClob) = 1 ) THEN
           dbms_lob.close( l_geoClob);
      END IF; 
      RAISE;               
END;
END ven_OraSpatial_2_TabData;
/
"ConvertDataFormat" code
BEGIN 
 arr_tmp_LatLong    ven_OraSpatial_2_TabData.tab_LatLong;
 l_lastPointID      NUMBER:=0;
 l_prev_polygonID   NUMBER:=0;
BEGIN
  for cx in (select a.*
              from my_shape_table a
         )
  loop
     BEGIN
         arr_tmp_LatLong := ven_OraSpatial_2_TabData.extract_LatLong_From_Geomentry( cx.geoloc);
         
         --dbms_output.put_line( 'tot_count:'||arr_tmp_LatLong.COUNT);
         for i in 1..arr_tmp_LatLong.COUNT
         LOOP
            
           IF arr_tmp_LatLong(i).polygon_id !=  l_prev_polygonID THEN  
              l_lastPointID := i;  
           END IF;
           
            INSERT INTO ( SHAPE_NAME, polygon_id, point_id, geo_lat, geo_long)
              VALUES( cx.SHAPE_NAME,  arr_tmp_LatLong(i).polygon_id, (i-l_lastPointID), arr_tmp_LatLong(i).lat, arr_tmp_LatLong(i).lon);
           
            l_prev_polygonID := arr_tmp_LatLong(i).polygon_id;  
            --dbms_output.put_line(arr_tmp_LatLong(i).polygon_id||'/'||arr_tmp_LatLong(i).lat||'/'||arr_tmp_LatLong(i).lon);
         ENd LOOP;
         commit;
      EXCEPTION
        when others then 
            dbms_output.put_line( 'error'); 
            RAISE;
      END;    
  end loop;
END;
/

Comments

derrickb52 said…
Why not use sdo_util.getvertices or sdo_util.to_wktgeometry_varchar?
likitha said…
This comment has been removed by the author.
seoprofessional said…
Thanks for sharing the great blog, I think now we have Tableau online training India where you can learn complete tableau within few weeks
jeya sofia said…
Thank you for the useful information. I got a clear idea after going through your site.Share more updates.
Learn Tableau Online
Tableau Classes Online

Popular posts from this blog

Tableau - Accessing Tableau's DB

react-bootstrap-table | header column alignment fix

Django 1.8 | email as username