Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
EXPORTER
For many years now, I have had an in-database based solution to the exportation of shapefiles. It was this code that provided a base for the work I did on exporting shapefiles from the recently released GeoRaptor 3.0.
This exporter package uses GeoTools and JTS, with custom code to enable the exportation of any SQL statement containing an SDO_GEOMETRY to a file on a directory to which the user has write permissions.
The elegance of this approach is that it requires no external software (running on any other server than the database) for its execution. The solution is based on Java (1.4) and is installed into the JVM of the Oracle database. As long as the database is up and running, an export can occur.
The neat thing here is that shapefile exports can be scheduled using Oracle DBMS_SCHEDULER package to run as and when required (eg straight after a full refresh of a materialized view). (See example, later.)
The package also allows for the creation of a MapInfo TAB file “wrapper” over the shapefile for read-only access by MapInfo software.
The shapefile exporter can be coupled with software like ogr2ogr to convert to other formats (eg a true MapInfo file).
This package has been extended to include the creation and exporting of:
- xSV (any delimiter) files,
- Excel spreadsheets (uses the Java Excel API ).
Code exists for the exporting and creation of dBase files (uses xBaseJ – xBase Engine for Java ) but this is not currently in this package.
This package has a special installer as it has to install the associated jar files as well.
The header for the exporter is as follows:
CREATE OR REPLACE package Exporter AUTHID CURRENT_USER AS TYPE refcur_t IS REF Cursor; TYPE tablist_t IS TABLE OF user_tab_columns.TABLE_NAME%TYPE; -- shapeType constants for use with WriteShapeFile 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'; 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 c_WKT_Format CONSTANT varchar2(5) := 'WKT'; c_GML_Format CONSTANT varchar2(5) := 'GML'; c_GML3_Format CONSTANT varchar2(5) := 'GML'; -- Not currently supported, defaults to GML2 /* ========================================================== ** 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_DATETIMEFORMAT CONSTANT varchar2(30) := 'yyyy/MM/dd hh:mm:ss a'; c_DATETIMEFORMAT2 CONSTANT varchar2(20) := 'M/d/yy H:mm'; 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'; /** * Procedure that writes an ESRI shapefile from an existing refcursor * @param p_RefCursor - * @param p_output_dir - the directory to write output files to. * @param p_file_name - the file name of output files. * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString * @param p_precision - number of decimal places of ordinates * @param p_geometry_index - the column index of the geometry column. * @param p_commit_interval - When to write batch to disk * @param 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/ * @param p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3 * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF. * @throws Exception if anything goes wrong. **/ PROCEDURE WriteShapefile(p_RefCursor IN &&defaultSchema..EXPORTER.refcur_t, p_output_dir IN VarChar2, p_file_name IN VarChar2, p_shape_type IN VarChar2, p_precision IN NUMBER, p_geometry_index IN NUMBER, p_commit_interval IN NUMBER, p_prj_string IN VarChar2, p_geomFormat IN varchar2, p_recordIdentifier IN varchar2 ); /** * Procedure that writes an ESRI shapefile from a SQL SELECT statement (string) * @param p_sql - A SELECT Statement that include a geometry column. * @param p_output_dir - the directory to write output files to. * @param p_file_name - the file name of output files. * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString * @param p_precision - number of decimal places of ordinates * @param p_geometry_index - the column index of the geometry column. * @param p_commit_interval - When to write batch to disk * @param 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/ * @param p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3 * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF. * @throws Exception if anything goes wrong. **/ PROCEDURE WriteShapefile(p_sql IN VarChar2, p_output_dir IN VarChar2, p_file_name IN VarChar2, p_shape_type IN VarChar2, p_precision IN NUMBER, p_geometry_index IN NUMBER, p_commit_interval IN NUMBER, p_prj_string IN VarChar2, p_geomFormat IN varchar2, p_recordIdentifier IN varchar2 ); /** * Procedure that writes a MapInfo TAB from an existing refCursor * @param p_RefCursor - the result set, including a geometry column. * @param p_output_dir - the directory to write output files to. * @param p_file_name - the file name of output files. * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString * @param p_precision - number of decimal places of ordinates * @param p_geometry_index - the column index of the geometry column. * @param p_commit_interval - When to write batch to disk * @param p_coordsys - MapInfo CoordSys string for writing to TAB file parameter. * @param p_symbolisation - A MapInfo symbol string for styling all geometry objects in tab file. * @param p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3 * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF. * @throws Exception if anything goes wrong. **/ PROCEDURE WriteTabfile(p_RefCursor &&defaultSchema..EXPORTER.refcur_t, p_output_dir IN VarChar2, p_file_name IN VarChar2, p_shape_type IN VarChar2, p_precision IN NUMBER, p_geometry_index IN NUMBER, p_commit_interval IN NUMBER, p_coordsys IN VarChar2, p_symbolisation IN VarChar2, p_geomFormat IN varchar2, p_recordIdentifier IN varchar2 ); /** * Procedure that writes a MapInfo TAB from a SQL SELECT statement (string) * @param p_sql - A SELECT Statement that include a geometry column. * @param p_output_dir - the directory to write output files to. * @param p_file_name - the file name of output files. * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString * @param p_precision - number of decimal places of ordinates * @param p_geometry_index - the column index of the geometry column. * @param p_commit_interval - When to write batch to disk * @param p_coordsys - MapInfo CoordSys string for writing to TAB file parameter. * @param p_symbolisation - A MapInfo symbol string for styling all geometry objects in tab file. * @param p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3 * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF. * @throws Exception if anything goes wrong. **/ PROCEDURE WriteTabfile(p_sql IN VarChar2, p_output_dir IN VarChar2, p_file_name IN VarChar2, p_shape_type IN VarChar2, p_precision IN NUMBER, p_geometry_index IN NUMBER, p_commit_interval IN NUMBER, p_coordsys IN VarChar2, p_symbolisation IN VarChar2, p_geomFormat IN varchar2, p_recordIdentifier IN varchar2 ); /** * Procedure that writes a collection of tables with geometry columns to disk * @param p_tables - list of tables to export * @param p_output_dir - the directory to write output files to. * @param p_precision - number of decimal places of ordinates * @param p_commit_interval - When to write batch to disk * @param p_mi_coordsys - MapInfo CoordSys string for writing to TAB file parameter. * @param p_mi_style - A MapInfo symbol string for styling all geometry objects in tab file. * @param p_prj_string - An ESRI PRJ file's contents. * @param p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3 * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF. * @throws Exception if anything goes wrong. **/ PROCEDURE ExportTables(p_tables IN &&defaultSchema..EXPORTER.tablist_t, p_output_dir IN VarChar2, p_precision IN NUMBER, p_commit_interval IN NUMBER, p_mi_coordsys IN VarChar2 := NULL, p_mi_style IN VarChar2 := NULL, p_prj_string IN VarChar2 := NULL, p_geomFormat IN varchar2 := NULL, p_recordIdentifier IN varchar2 := NULL); /** * 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) * @param p_RefCursor - the result set, including a geometry column. * @param p_outputDirectory - the directory to write output files to. * @param p_fileName - the file name of output files. * @param p_FieldSeparator - the character between the values in the output file (could be a comma, or a pipe etc) * @param p_TextDelimiter - the character used to enclose text strings (especially where contain cSeparator) * @param p_DateFormat - Format for output dates * @param p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3 * @param p_precision - number of decimal places of ordinates * @author Simon Greener, The SpatialDB Advisor, May 2008 - Original &&defaultSchema..EXPORTER coding * @author Simon Greener, The SpatialDB ADvisor, October 2011 - Brought into dbutils from Exporter **/ PROCEDURE WriteDelimitedTextfile(p_RefCursor IN &&defaultSchema..EXPORTER.refcur_t, p_outputDirectory IN VarChar2, p_fileName IN VarChar2, p_FieldSeparator IN VarChar2, p_TextDelimiter IN VarChar2, p_DateFormat IN varchar2, p_geomFormat IN Varchar2, p_precision IN NUMBER); /** * Procedure that writes a SELECT stmt (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) * @param p_sql - A SELECT Statement that include a geometry column. * @param p_outputDirectory - the directory to write output files to. * @param p_fileName - the file name of output files. * @param p_FieldSeparator - the character between the values in the output file (could be a comma, or a pipe etc) * @param p_TextDelimiter - the character used to enclose text strings (especially where contain cSeparator) * @param p_DateFormat - Format for output dates * @param p_geomFormat - Format for non-SHP sdo_geometry eg WKT, GML, GML3 * @param p_precision - number of decimal places of ordinates * @author Simon Greener, The SpatialDB Advisor, May 2008 - Original Exporter coding * @author Simon Greener, The SpatialDB ADvisor, October 2011 - Brought into dbutils from Exporter **/ PROCEDURE WriteDelimitedTextfile(p_sql IN VarChar2, p_outputDirectory IN VarChar2, p_fileName IN VarChar2, p_FieldSeparator IN VarChar2, p_TextDelimiter IN VarChar2, p_DateFormat IN varchar2, p_geomFormat IN Varchar2, p_precision IN NUMBER); /** * writeExcelSpreadsheet * * Creates and writes an Excel 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. * * NOTE: Maximum size of an Excel spreadsheet cell is 32768 characters. * * @param p_resultSet - the result set, including a geometry column. * @param p_outputDirectory - the directory to write output files to. * @param p_fileName - the file name of output files. * @param p_sheetName - Name of base or first sheet. Prefix for all others. * @param p_stratification - Horizontal (H), Vertical (V) or None (N). * @param p_geomFormat - Text format for sdo_geometry columns eg WKT, GML, GML3 * @param p_dateFormat - Format for output dates * @param p_timeFormat - Format for output times * @param p_precision - Number of decimal places of coordinates * @history Simon Greener, The SpatialDB Advisor, October 2011, Original coding */ PROCEDURE writeExcelSpreadsheet(p_RefCursor IN &&defaultSchema..EXPORTER.refcur_t, p_outputDirectory IN VarChar2, p_fileName IN VarChar2, p_sheetName IN VarChar2, p_stratification IN VarChar2 DEFAULT &&defaultSchema..EXPORTER.c_HORIZONTAL_STRATIFICATION, p_geomFormat IN Varchar2 DEFAULT &&defaultSchema..EXPORTER.c_WKT_FORMAT, p_DateFormat IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_DATEFORMAT, p_TimeFormat IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_TIMEFORMAT, p_precision IN NUMBER DEFAULT 3); /** * writeExcelSpreadsheet * * @param p_sql - A SELECT Statement that include a geometry column. * @param p_outputDirectory - the directory to write output files to. * @param p_fileName - the file name of output files. * @param p_sheetName - Name of base or first sheet. Prefix for all others. * @param p_stratification - Horizontal (H), Vertical (V) or None (N). * @param p_geomFormat - Text format for sdo_geometry columns eg WKT, GML, GML3 * @param p_dateFormat - Format for output dates * @param p_timeFormat - Format for output times * @param p_precision - Number of decimal places of coordinates * @history Simon Greener, The SpatialDB Advisor, October 2011, Original coding */ PROCEDURE writeExcelSpreadsheet(p_sql IN VarChar2, p_outputDirectory IN VarChar2, p_fileName IN VarChar2, p_sheetName IN VarChar2, p_stratification IN VarChar2 DEFAULT &&defaultSchema..EXPORTER.c_HORIZONTAL_STRATIFICATION, p_geomFormat IN Varchar2 DEFAULT &&defaultSchema..EXPORTER.c_WKT_FORMAT, p_DateFormat IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_DATEFORMAT, p_TimeFormat IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_TIMEFORMAT, p_precision IN NUMBER DEFAULT 3); FUNCTION RunCommand( p_command IN varchar2 ) RETURN NUMBER Deterministic; END Exporter; / SHOW ERRORS
I am prepared to provide the code as a jar file (and rough installation instructions) for anyone who wants it on the basis that:
- The code is provide as-is-where-is and is used at the user’s responsibility to ensure that it functions correctly for the purpose to which it is put.
- There is no documentation other than planned documentation of the procedures in the package header.
- Source code is be provided in the downloadable zip file.
- Any improvements to the source code must be provided back to me.
- Any request for extension or improvement by myself (other than bug fixes) is done on a payment basis, except where I improve the code myself.
- Note: the code is for Oracle 10gR2 as it is compiled to Java 1.4 specification. It runs in Oracle 11g. Later versions may include Java 1.5 features that are not backwards compatible with 10gR2.
I hope this fires someone’s imagination.