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