[ Top ] [ Modules ]
NAME
EXPORTER - This package exposes Java stored procedures that provide spatial data export.
DESCRIPTION
A package that allows for spatial data to be exported from within the database to a number of formats.
AUTHOR
Simon Greener
HISTORY
Simon Greener - January 2008 - Original coding.
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Variables ]
ATTRIBUTES
SOURCE
Type refcur_t Is Ref Cursor; Type tablist_t Is Table Of user_tab_columns.TABLE_NAME%Type; -- ========================================================== -- Excel Spreadssheet export -- ========================================================== -- --------- -- Constants -- ---------- -- 1. Stratification -- c_HORIZONTAL_STRATIFICATION CONSTANT varchar2(1) := 'H'; c_VERTICAL_STRATIFICATION CONSTANT varchar2(1) := 'V'; c_NO_STRATIFICATION CONSTANT varchar2(1) := 'N'; -- 2. Date/Time formats -- c_DATEFORMAT CONSTANT varchar2(20) := 'yyyyMMdd'; c_DATEFORMAT1 CONSTANT varchar2(20) := 'M/d/yy'; c_DATEFORMAT2 CONSTANT varchar2(20) := 'd-MMM-yy'; c_DATEFORMAT3 CONSTANT varchar2(20) := 'd-MMM'; c_DATEFORMAT4 CONSTANT varchar2(20) := 'MMM-yy'; c_TIMEFORMAT CONSTANT varchar2(20) := 'h:mm a'; c_TIMEFORMAT1 CONSTANT varchar2(20) := 'h:mm:ss a'; c_TIMEFORMAT2 CONSTANT varchar2(20) := 'H:mm'; c_TIMEFORMAT3 CONSTANT varchar2(20) := 'H:mm:ss'; c_TIMEFORMAT4 CONSTANT varchar2(20) := 'mm:ss'; c_TIMEFORMAT5 CONSTANT varchar2(20) := 'H:mm:ss'; c_TIMEFORMAT6 CONSTANT varchar2(20) := 'H:mm:ss'; -- 3. Shapefile -- c_Point CONSTANT varchar2(20) := 'point'; c_Point_Z CONSTANT varchar2(20) := 'pointz'; c_Point_M CONSTANT varchar2(20) := 'pointm'; c_LineString CONSTANT varchar2(20) := 'linestring'; c_LineString_Z CONSTANT varchar2(20) := 'linestringz'; c_LineString_M CONSTANT varchar2(20) := 'linestringm'; c_Polygon CONSTANT varchar2(20) := 'polygon'; c_Polygon_Z CONSTANT varchar2(20) := 'polygonz'; c_Polygon_M CONSTANT varchar2(20) := 'polygonm'; c_Multi_Point CONSTANT varchar2(20) := 'multipoint'; c_Multi_Point_Z CONSTANT varchar2(20) := 'multipointz'; c_Multi_Point_M CONSTANT varchar2(20) := 'multipointm'; c_Multi_LineString CONSTANT varchar2(20) := 'multilinestring'; c_Multi_LineString_Z CONSTANT varchar2(20) := 'multilinestringz'; c_Multi_LineString_M CONSTANT varchar2(20) := 'multilinestringm'; c_Multi_Polygon CONSTANT varchar2(20) := 'multipolygon'; c_Multi_Polygon_Z CONSTANT varchar2(20) := 'multipolygonz'; c_Multi_Polygon_M CONSTANT varchar2(20) := 'multipolygonm'; -- 4. Polygon Ring Ordering -- c_Ring_Oracle CONSTANT varchar2(20) := 'ORACLE'; c_Ring_Inverse CONSTANT varchar2(20) := 'INVERSE'; c_Ring_Clockwise CONSTANT varchar2(20) := 'CLOCKWISE'; c_Ring_AntiClockwise CONSTANT varchar2(20) := 'ANTICLOCKWISE'; -- 5. DBASE file type choice -- c_DBASEIII CONSTANT varchar2(20) := 'DBASEIII'; c_DBASEIII_WITH_MEMO CONSTANT varchar2(20) := 'DBASEIII_WITH_MEMO'; c_DBASEIV CONSTANT varchar2(20) := 'DBASEIV'; c_DBASEIV_WITH_MEMO CONSTANT varchar2(20) := 'DBASEIV_WITH_MEMO'; c_FOXPRO_WITH_MEMO CONSTANT varchar2(20) := 'FOXPRO_WITH_MEMO'; -- 6. Shapefile and MapInfo Tab File ID NAMES -- c_mapinfo_pk CONSTANT varchar2(8) := 'MI_PRINX'; -- For use when recordset or table has only a geometry c_shapefile_pk CONSTANT varchar2(3) := 'GID'; -- For use when recordset or table has only a geometry -- 7. Supported SDO_GEOMETRY as TEXT formats. -- c_SDOGeometry CONSTANT varchar2(20) := 'SDO_GEOMETRY'; c_STGeometry CONSTANT varchar2(20) := 'ST_GEOMETRY'; c_KML2 CONSTANT varchar2(20) := 'KML2'; c_GML2 CONSTANT varchar2(20) := 'GML2'; c_GML3 CONSTANT varchar2(20) := 'GML3'; c_KML CONSTANT varchar2(20) := 'KML'; c_WKT CONSTANT varchar2(20) := 'WKT'; c_GEOJSON CONSTANT varchar2(20) := 'GeoJSON'; -- 8. XML Flavours for handling attributes of KML files .... -- c_OGR CONSTANT varchar2(20) := 'OGR'; c_FME CONSTANT varchar2(20) := 'FME'; c_GML CONSTANT varchar2(20) := 'GML'; -- 9. CharSet names -- c_UTF8 CONSTANT varchar2(128):= 'UTF-8';
[ Top ] [ EXPORTER ] [ Functions ]
NAME
ExportTables -- Procedure that writes a collection of tables with geometry columns to disk
SYNOPSIS
ARGUMENTS
p_tables - list of tables to export
p_output_dir - the directory to write output files to.
p_digits_of_precision - number of decimal places of ordinates
p_commit - When to write batch to disk
p_mi_coordsys - MapInfo CoordSys string for writing to TAB file parameter.
p_mi_style - A MapInfo symbol string for styling all geometry objects in tab file.
p_prj_string - An ESRI PRJ file's contents.
p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3
DESCRIPTION
NOTES
Throws Exception if anything goes wrong.
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
RunCommand -- Method that allows an Oracle stored procedure to execute an external program (eg ogr2ogr) from within the database.
SYNOPSIS
ARGUMENTS
RETURNS
Error code: 0 if OK, otherwise error code.
DESCRIPTION
This function allows an Oracle stored procedure to execute an external program from within the database.
An example might be the ability to execute ogr2ogr to convert a shapefile written by the WriteShapefile procedure
to another spatial format.
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteGeoJson - Writes result of SQL Select to a GeoJson file.
SYNOPSIS
ARGUMENTS
RETURNS
DESCRIPTION
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteGMLFile - Writes result of SQL Select to a GML file.
SYNOPSIS
ARGUMENTS
RETURNS
DESCRIPTION
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteKMLFile -- Exports SQL Select refCursor to KML file.
SYNOPSIS
ARGUMENTS
RETURNS
DESCRIPTION
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteShapefile -- Procedure that writes an ESRI shapefile from an existing refcursor
SYNOPSIS
ARGUMENTS
p_RefCursor - Open Oracle ref cursor.
p_output_dir - the directory to write output files to.
p_file_name - the file name of output files.
p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
p_geometry_name - the name of the geometry column.
p_ring_orientation - Ring orientation to be applied to Polygon exports.
p_dbase_type - This exporter supports DBASEIII/DBASEIV With Memo.
Memo useful for exporting varchar2/clobs > 255 charaters.
p_geometry_format - Format for non-SHP sdo_geometry eg WKT, GML, GML3
p_prj_string - An ESRI PRJ file's contents.
PRJ writing. To have the shapefile writer create a correct PRJ file,
supply the contents of an existing PRJ file to the p_prj_string parameter.
If you do not have a valid PRJ file/string visit http://www.spatialreference.org/
p_digits_of_precision - number of decimal places of ordinates
p_commit - When to write batch to disk
DESCRIPTION
NOTES
Throws Exception if anything goes wrong.
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteShapefile -- Procedure that writes an ESRI shapefile from a SQL SELECT statement (string)
SYNOPSIS
ARGUMENTS
p_sql - A SELECT Statement that include a geometry column.
p_output_dir - the directory to write output files to.
p_file_name - the file name of output files.
p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
p_geometry_name - the name of the geometry column.
p_ring_orientation - Ring orientation to be applied to Polygon exports.
p_dbase_type - This exporter supports DBASEIII/DBASEIV With Memo.
Memo useful for exporting varchar2/clobs > 255 charaters.
p_geometry_format - Format for non-SHP sdo_geometry eg WKT, GML, GML3
p_prj_string - An ESRI PRJ file's contents.
PRJ writing. To have the shapefile writer create a correct PRJ file,
supply the contents of an existing PRJ file to the p_prj_string parameter.
If you do not have a valid PRJ file/string visit http://www.spatialreference.org/
p_digits_of_precision - number of decimal places of ordinates
p_commit - When to write batch to disk
DESCRIPTION
NOTES
Throws Exception if anything goes wrong.
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
writeSpreadsheet - Writes data in result set to spreadsheet.
SYNOPSIS
ARGUMENTS
p_resultSet - the result set, including a geometry column.
p_outputDirectory - the directory to write output files to.
p_fileName - the file name of output files.
p_sheetName - Name of base or first sheet. Prefix for all others.
p_stratification - Horizontal (H), Vertical (V) or None (N).
p_geomFormat - Text format for sdo_geometry columns eg WKT, GML, GML3
p_dateFormat - Format for output dates
p_timeFormat - Format for output times
p_digits_of_precision - Number of decimal places of coordinates
DESCRIPTION
Creates and writes an Excel XLS format spreadsheet from the passed in resultSet.
Overflow of resultSet across Sheets is controlled by _stratification.
If number of rows in _resultSet is > MAX_ROWS (65535) and _stratification
is N (NONE) or V (VERTICAL) then the resultSet processing will only output MAX_ROWS
in the first sheet. No more sheets will be created.
If _stratification is H (HORIZONTAL) a new sheet is created for the next MAX_ROWS (65535).
If the resultSet contains > MAX_COLS (255) and _stratification is set to V (VERTICAL) then
the first 255 columns will be in the first sheet, the next 255 in the second sheet etc up
to the maxiumum number of rows that can be output in a SELECT statement.
If > MAX_COLS exist and _stratification is H or N then only 255 columns will be output in
the first sheet: if > MAX_ROWS also exists then overflow is controlled by _stratification = H or N.
NOTES
Does not write any modern XML format spreadsheets.
Maximum size of an Excel spreadsheet cell is 32768 characters.
AUTHOR
Simon Greener
HISTORY
Simon Greener - October 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
writeSpreadsheet - Executes SQL statement and writes resultset to an Excel spreadsheet.
SYNOPSIS
ARGUMENTS
p_sql - A SELECT Statement that include a geometry column.
p_outputDirectory - the directory to write output files to.
p_fileName - the file name of output files.
p_sheetName - Name of base or first sheet. Prefix for all others.
p_stratification - Horizontal (H), Vertical (V) or None (N).
p_geomFormat - Text format for sdo_geometry columns eg WKT, GML, GML3 ...
p_dateFormat - Format for output dates
p_timeFormat - Format for output times
p_digits_of_precision - Number of decimal places of coordinates
DESCRIPTION
Creates and writes an Excel XLS format spreadsheet from the passed in resultSet.
Overflow of resultSet across Sheets is controlled by _stratification.
If number of rows in _resultSet is > MAX_ROWS (65535) and _stratification
is N (NONE) or V (VERTICAL) then the resultSet processing will only output MAX_ROWS
in the first sheet. No more sheets will be created.
If _stratification is H (HORIZONTAL) a new sheet is created for the next MAX_ROWS (65535).
If the resultSet contains > MAX_COLS (255) and _stratification is set to V (VERTICAL) then
the first 255 columns will be in the first sheet, the next 255 in the second sheet etc up
to the maxiumum number of rows that can be output in a SELECT statement.
If > MAX_COLS exist and _stratification is H or N then only 255 columns will be output in
the first sheet: if > MAX_ROWS also exists then overflow is controlled by _stratification = H or N.
NOTES
Does not write any modern XML format spreadsheets.
Maximum size of an Excel spreadsheet cell is 32768 characters.
AUTHOR
Simon Greener
HISTORY
Simon Greener - October 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteTabFile -- Procedure that writes a MapInfo TAB from an existing refCursor
SYNOPSIS
ARGUMENTS
p_RefCursor - the result set, including a geometry column.
p_output_dir - the directory to write output files to.
p_file_name - the file name of output files.
p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
p_geometry_name - The name of the sdo_geometry column to export.
p_ring_orientation - Ring orientation to be applied to Polygon exports.
p_dbase_type - This exporter supports DBASEIII/DBASEIV With Memo.
Memo useful for exporting varchar2/clobs > 255 charaters.
p_geometry_format - Format for non-SHP sdo_geometry eg WKT, GML, GML3
p_coordsys - MapInfo CoordSys string for writing to TAB file parameter.
p_symbolisation - A MapInfo symbol string for styling all geometry objects in tab file.
p_digits_of_precision - number of decimal places of ordinates
p_commit - When to write batch to disk
DESCRIPTION
NOTES
Throws Exception if anything goes wrong.
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteTabFile -- Procedure that writes a MapInfo TAB from a SQL SELECT statement (string)
SYNOPSIS
ARGUMENTS
p_sql - A SELECT Statement that include a geometry column.
p_output_dir - the directory to write output files to.
p_file_name - the file name of output files.
p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
p_ring_orientation - Ring orientation to be applied to Polygon exports.
p_dbase_type - This exporter supports DBASEIII/DBASEIV With Memo.
Memo useful for exporting varchar2/clobs > 255 charaters.
p_geometry_format - Format for non-SHP sdo_geometry eg WKT, GML, GML3
p_coordsys - MapInfo CoordSys string for writing to TAB file parameter.
p_symbolisation - A MapInfo symbol string for styling all geometry objects in tab file.
p_digits_of_precision - number of decimal places of ordinates
p_commit - When to write batch to disk
DESCRIPTION
NOTES
THrows Exception if anything goes wrong.
AUTHOR
Simon Greener
HISTORY
Simon Greener - September 2011, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
[ Top ] [ EXPORTER ] [ Functions ]
NAME
WriteTextFile -- Writes result set to a delimited text file.
SYNOPSIS
DESCRIPTION
Procedure that writes a result set (including one or more sdo_geometry objects - as a delimited text file eg csv.
Supports all Oracle types except LONG, LONG RAW, BLOB, VARRAY and STRUCT (non SDO_GEOMETRY)
ARGUMENTS
p_RefCursor - The result set, including a geometry column.
p_outputDirectory - The directory to write output files to.
p_fileName - The file name of output files.
p_FieldSeparator - The character between the values in the output file (could be a comma, or a pipe etc)
Default if NULL is a comma ','
p_TextDelimiter - The character used to enclose text strings (especially where contain cSeparator)
Default if NULL is a double quote '''
p_DateFormat - Format for output dates
DEFAULT of NULL is 'yyyy/MM/dd hh:mm:ss a'
p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3
Default if NULL is WKT
p_charset - CharSet of file being written
Default if NULL is US-ASCII
p_digits_of_precision - Number of decimal places of ordinates
Default if NULL is 3
AUTHOR
Simon Greener
HISTORY
Simon Greener - May 2008, Original Coding
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener