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.
- Run the Oracle package ven_OraSpatial_2_TabData in your oracle environment.
- Now run the below "ConvertDataFormat" code to extract the data into Tableau format
- Now use extracted data in tableau ( refer to my blog - Tableau Maps - Custom shapes / polygon - Basics)
Oracle package ven_OraSpatial_2_TabData
SPEC
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
Learn Tableau Online
Tableau Classes Online