Go to content Go to navigation and search

Home

Free Software Download

Download Code, Packages and Installers

Free Software Articles and Documentation


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

EXPORTER Package Documentation

Wednesday July 20 2011 at 14:05

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:

  1. CREATE OR REPLACE
  2. package Exporter
  3. AUTHID CURRENT_USER
  4. AS
  5.   TYPE refcur_t IS REF Cursor;
  6.   TYPE tablist_t IS TABLE OF user_tab_columns.TABLE_NAME%TYPE;
  7.   -- shapeType constants for use with WriteShapeFile
  8.   c_Point              CONSTANT varchar2(20) := 'point';
  9.   c_Point_Z            CONSTANT varchar2(20) := 'pointz';
  10.   c_Point_M            CONSTANT varchar2(20) := 'pointm';
  11.   c_LineString         CONSTANT varchar2(20) := 'linestring';
  12.   c_LineString_Z       CONSTANT varchar2(20) := 'linestringz';
  13.   c_LineString_M       CONSTANT varchar2(20) := 'linestringm';
  14.   c_Polygon            CONSTANT varchar2(20) := 'polygon';
  15.   c_Polygon_Z          CONSTANT varchar2(20) := 'polygonz';
  16.   c_Polygon_M          CONSTANT varchar2(20) := 'polygonm';
  17.   c_Multi_Point        CONSTANT varchar2(20) := 'multipoint';
  18.   c_Multi_Point_Z      CONSTANT varchar2(20) := 'multipointz';
  19.   c_Multi_Point_M      CONSTANT varchar2(20) := 'multipointm';
  20.   c_Multi_LineString   CONSTANT varchar2(20) := 'multilinestring';
  21.   c_Multi_LineString_Z CONSTANT varchar2(20) := 'multilinestringz';
  22.   c_Multi_LineString_M CONSTANT varchar2(20) := 'multilinestringm';
  23.   c_Multi_Polygon      CONSTANT varchar2(20) := 'multipolygon';
  24.   c_Multi_Polygon_Z    CONSTANT varchar2(20) := 'multipolygonz';
  25.   c_Multi_Polygon_M    CONSTANT varchar2(20) := 'multipolygonm';
  26.   c_mapinfo_pk         CONSTANT varchar2(8)  := 'MI_PRINX'; -- For use when recordset or table has only a geometry
  27.   c_shapefile_pk       CONSTANT varchar2(3)  := 'GID';      -- For use when recordset or table has only a geometry
  28.   c_WKT_Format         CONSTANT varchar2(5)  := 'WKT';
  29.   c_GML_Format         CONSTANT varchar2(5)  := 'GML';
  30.   c_GML3_Format        CONSTANT varchar2(5)  := 'GML';  -- Not currently supported, defaults to GML2
  31.   /* ==========================================================
  32.   ** Excel Spreadssheet export
  33.   ** ========================================================== */
  34.   /* ---------
  35.   ** Constants
  36.   * ----------
  37.   * 1. Stratification
  38.   */
  39.   c_HORIZONTAL_STRATIFICATION CONSTANT varchar2(1)  := 'H';
  40.   c_VERTICAL_STRATIFICATION   CONSTANT varchar2(1)  := 'V';
  41.   c_NO_STRATIFICATION         CONSTANT varchar2(1)  := 'N';
  42.   /* 2. Date/Time formats
  43.   */
  44.   c_DATETIMEFORMAT            CONSTANT varchar2(30) := 'yyyy/MM/dd hh:mm:ss a';
  45.   c_DATETIMEFORMAT2           CONSTANT varchar2(20) := 'M/d/yy H:mm';
  46.   c_DATEFORMAT                CONSTANT varchar2(20) := 'yyyyMMdd';
  47.   c_DATEFORMAT1               CONSTANT varchar2(20) := 'M/d/yy';
  48.   c_DATEFORMAT2               CONSTANT varchar2(20) := 'd-MMM-yy';
  49.   c_DATEFORMAT3               CONSTANT varchar2(20) := 'd-MMM';
  50.   c_DATEFORMAT4               CONSTANT varchar2(20) := 'MMM-yy';
  51.   c_TIMEFORMAT                CONSTANT varchar2(20) := 'h:mm a';
  52.   c_TIMEFORMAT1               CONSTANT varchar2(20) := 'h:mm:ss a';
  53.   c_TIMEFORMAT2               CONSTANT varchar2(20) := 'H:mm';
  54.   c_TIMEFORMAT3               CONSTANT varchar2(20) := 'H:mm:ss';
  55.   c_TIMEFORMAT4               CONSTANT varchar2(20) := 'mm:ss';
  56.   c_TIMEFORMAT5               CONSTANT varchar2(20) := 'H:mm:ss';
  57.   c_TIMEFORMAT6               CONSTANT varchar2(20) := 'H:mm:ss';
  58.   /**
  59.    * Procedure that writes an ESRI shapefile from an existing refcursor
  60.    * @param p_RefCursor  -
  61.    * @param p_output_dir - the directory to write output files to.
  62.    * @param p_file_name  - the file name of output files.
  63.    * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
  64.    * @param p_precision  - number of decimal places of ordinates
  65.    * @param p_geometry_index  - the column index of the geometry column.
  66.    * @param p_commit_interval - When to write batch to disk  
  67.    * @param p_prj_string - An ESRI PRJ file's contents.
  68.    *                       PRJ writing. To have the shapefile writer create a correct PRJ file,
  69.    *                       supply the contents of an existing PRJ file to the p_prj_string parameter.
  70.    *                       If you do not have a valid PRJ file/string visit http://www.spatialreference.org/
  71.    * @param p_geomFormat       - Format for non-SHP sdo_geometry eg WKT, GML, GML3
  72.    * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF.
  73.    * @throws Exception if anything goes wrong.
  74.    **/
  75.   PROCEDURE WriteShapefile(p_RefCursor        IN &&defaultSchema..EXPORTER.refcur_t,
  76.                            p_output_dir       IN VarChar2,
  77.                            p_file_name        IN VarChar2,
  78.                            p_shape_type       IN VarChar2,
  79.                            p_precision        IN NUMBER,
  80.                            p_geometry_index   IN NUMBER,
  81.                            p_commit_interval  IN NUMBER,
  82.                            p_prj_string       IN VarChar2,
  83.                            p_geomFormat       IN varchar2,
  84.                            p_recordIdentifier IN varchar2 );
  85.   /**
  86.    * Procedure that writes an ESRI shapefile from a SQL SELECT statement (string)
  87.    * @param p_sql        - A SELECT Statement that include a geometry column.
  88.    * @param p_output_dir - the directory to write output files to.
  89.    * @param p_file_name  - the file name of output files.
  90.    * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
  91.    * @param p_precision  - number of decimal places of ordinates
  92.    * @param p_geometry_index  - the column index of the geometry column.
  93.    * @param p_commit_interval - When to write batch to disk  
  94.    * @param p_prj_string - An ESRI PRJ file's contents.
  95.    *                       PRJ writing. To have the shapefile writer create a correct PRJ file,
  96.    *                       supply the contents of an existing PRJ file to the p_prj_string parameter.
  97.    *                       If you do not have a valid PRJ file/string visit http://www.spatialreference.org/
  98.    * @param p_geomFormat       - Format for non-SHP sdo_geometry eg WKT, GML, GML3
  99.    * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF.
  100.    * @throws Exception if anything goes wrong.
  101.    **/
  102.   PROCEDURE WriteShapefile(p_sql              IN VarChar2,
  103.                            p_output_dir       IN VarChar2,
  104.                            p_file_name        IN VarChar2,
  105.                            p_shape_type       IN VarChar2,
  106.                            p_precision        IN NUMBER,
  107.                            p_geometry_index   IN NUMBER,
  108.                            p_commit_interval  IN NUMBER,
  109.                            p_prj_string       IN VarChar2,
  110.                            p_geomFormat       IN varchar2,
  111.                            p_recordIdentifier IN varchar2 );
  112.   /**
  113.    * Procedure that writes a MapInfo TAB from an existing refCursor
  114.    * @param p_RefCursor  - the result set, including a geometry column.
  115.    * @param p_output_dir - the directory to write output files to.
  116.    * @param p_file_name  - the file name of output files.
  117.    * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
  118.    * @param p_precision  - number of decimal places of ordinates
  119.    * @param p_geometry_index   - the column index of the geometry column.
  120.    * @param p_commit_interval  - When to write batch to disk  
  121.    * @param p_coordsys         - MapInfo CoordSys string for writing to TAB file parameter.
  122.    * @param p_symbolisation    - A MapInfo symbol string for styling all geometry objects in tab file.
  123.    * @param p_geomFormat       - Format for non-SHP sdo_geometry eg WKT, GML, GML3
  124.    * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF.
  125.    * @throws Exception if anything goes wrong.
  126.    **/
  127.   PROCEDURE WriteTabfile(p_RefCursor        &&defaultSchema..EXPORTER.refcur_t,
  128.                          p_output_dir       IN VarChar2,
  129.                          p_file_name        IN VarChar2,
  130.                          p_shape_type       IN VarChar2,
  131.                          p_precision        IN NUMBER,
  132.                          p_geometry_index   IN NUMBER,
  133.                          p_commit_interval  IN NUMBER,
  134.                          p_coordsys         IN VarChar2,
  135.                          p_symbolisation    IN VarChar2,
  136.                          p_geomFormat       IN varchar2,
  137.                          p_recordIdentifier IN varchar2 );
  138.   /**
  139.    * Procedure that writes a MapInfo TAB from a SQL SELECT statement (string)
  140.    * @param p_sql        - A SELECT Statement that include a geometry column.
  141.    * @param p_output_dir - the directory to write output files to.
  142.    * @param p_file_name  - the file name of output files.
  143.    * @param p_shape_type - the type of shapefile eg polygon etc. See constants eg &&defaultSchema..EXPORTER.c_LineString
  144.    * @param p_precision  - number of decimal places of ordinates
  145.    * @param p_geometry_index   - the column index of the geometry column.
  146.    * @param p_commit_interval  - When to write batch to disk  
  147.    * @param p_coordsys         - MapInfo CoordSys string for writing to TAB file parameter.
  148.    * @param p_symbolisation    - A MapInfo symbol string for styling all geometry objects in tab file.
  149.    * @param p_geomFormat       - Format for non-SHP sdo_geometry eg WKT, GML, GML3
  150.    * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF.
  151.    * @throws Exception if anything goes wrong.
  152.    **/
  153.   PROCEDURE WriteTabfile(p_sql              IN VarChar2,
  154.                          p_output_dir       IN VarChar2,
  155.                          p_file_name        IN VarChar2,
  156.                          p_shape_type       IN VarChar2,
  157.                          p_precision        IN NUMBER,
  158.                          p_geometry_index   IN NUMBER,
  159.                          p_commit_interval  IN NUMBER,
  160.                          p_coordsys         IN VarChar2,
  161.                          p_symbolisation    IN VarChar2,
  162.                          p_geomFormat       IN varchar2,
  163.                          p_recordIdentifier IN varchar2 );
  164.   /**
  165.    * Procedure that writes a collection of tables with geometry columns to disk
  166.    * @param p_tables     - list of tables to export
  167.    * @param p_output_dir - the directory to write output files to.
  168.    * @param p_precision  - number of decimal places of ordinates
  169.    * @param p_commit_interval  - When to write batch to disk  
  170.    * @param p_mi_coordsys      - MapInfo CoordSys string for writing to TAB file parameter.
  171.    * @param p_mi_style         - A MapInfo symbol string for styling all geometry objects in tab file.
  172.    * @param p_prj_string       - An ESRI PRJ file's contents.
  173.    * @param p_geomFormat       - Format for non-SHP sdo_geometry eg WKT, GML, GML3
  174.    * @param p_recordIdentifier - When DBF has no attributes in SELECT, name of unique primary key written to DBF.
  175.    * @throws Exception if anything goes wrong.
  176.    **/
  177.   PROCEDURE ExportTables(p_tables           IN &&defaultSchema..EXPORTER.tablist_t,
  178.                          p_output_dir       IN VarChar2,
  179.                          p_precision        IN NUMBER,
  180.                          p_commit_interval  IN NUMBER,
  181.                          p_mi_coordsys      IN VarChar2 := NULL,
  182.                          p_mi_style         IN VarChar2 := NULL,
  183.                          p_prj_string       IN VarChar2 := NULL,
  184.                          p_geomFormat       IN varchar2 := NULL,
  185.                          p_recordIdentifier IN varchar2 := NULL);
  186.   /**
  187.    * Procedure that writes a result set (including one or more sdo_geometry objects - as a delimited text file eg csv.
  188.    * Supports all Oracle types except LONG, LONG RAW, BLOB, VARRAY and STRUCT (non SDO_GEOMETRY)
  189.    * @param p_RefCursor       - the result set, including a geometry column.
  190.    * @param p_outputDirectory - the directory to write output files to.
  191.    * @param p_fileName        - the file name of output files.
  192.    * @param p_FieldSeparator - the character between the values in the output file (could be a comma, or a pipe etc)
  193.    * @param p_TextDelimiter  - the character used to enclose text strings (especially where contain cSeparator)
  194.    * @param p_DateFormat     - Format for output dates
  195.    * @param p_geomFormat      - Format for non-SHP sdo_geometry eg WKT, GML, GML3
  196.    * @param p_precision       - number of decimal places of ordinates
  197.    * @author Simon Greener, The SpatialDB Advisor, May 2008 - Original &&defaultSchema..EXPORTER coding
  198.    * @author Simon Greener, The SpatialDB ADvisor, October 2011 - Brought into dbutils from Exporter
  199.   **/
  200.   PROCEDURE WriteDelimitedTextfile(p_RefCursor        IN &&defaultSchema..EXPORTER.refcur_t,
  201.                                    p_outputDirectory  IN VarChar2,
  202.                                    p_fileName         IN VarChar2,
  203.                                    p_FieldSeparator   IN VarChar2,
  204.                                    p_TextDelimiter    IN VarChar2,
  205.                                    p_DateFormat       IN varchar2,
  206.                                    p_geomFormat       IN Varchar2,
  207.                                    p_precision        IN NUMBER);
  208.   /**
  209.    * Procedure that writes a SELECT stmt (including one or more sdo_geometry objects) as a delimited text file eg csv.
  210.    * Supports all Oracle types except LONG, LONG RAW, BLOB, VARRAY and STRUCT (non SDO_GEOMETRY)
  211.    * @param p_sql             - A SELECT Statement that include a geometry column.
  212.    * @param p_outputDirectory - the directory to write output files to.
  213.    * @param p_fileName        - the file name of output files.
  214.    * @param p_FieldSeparator  - the character between the values in the output file (could be a comma, or a pipe etc)
  215.    * @param p_TextDelimiter   - the character used to enclose text strings (especially where contain cSeparator)
  216.    * @param p_DateFormat      - Format for output dates
  217.    * @param p_geomFormat      - Format for non-SHP sdo_geometry eg WKT, GML, GML3
  218.    * @param p_precision       - number of decimal places of ordinates
  219.    * @author Simon Greener, The SpatialDB Advisor, May 2008 - Original Exporter coding
  220.    * @author Simon Greener, The SpatialDB ADvisor, October 2011 - Brought into dbutils from Exporter
  221.   **/
  222.   PROCEDURE WriteDelimitedTextfile(p_sql              IN VarChar2,
  223.                                    p_outputDirectory  IN VarChar2,
  224.                                    p_fileName         IN VarChar2,
  225.                                    p_FieldSeparator   IN VarChar2,
  226.                                    p_TextDelimiter    IN VarChar2,
  227.                                    p_DateFormat       IN varchar2,
  228.                                    p_geomFormat       IN Varchar2,
  229.                                    p_precision        IN NUMBER);
  230.     /**
  231.      * writeExcelSpreadsheet
  232.      *
  233.      * Creates and writes an Excel spreadsheet from the passed in resultSet
  234.      * Overflow of resultSet across Sheets is controlled by _stratification.
  235.      * If number of rows in _resultSet is > MAX_ROWS (65535) and _stratification
  236.      * is N (NONE) or V (VERTICAL) then the resultSet processing will only output MAX_ROWS
  237.      * in the first sheet. No more sheets will be created.
  238.      * If _stratification is H (HORIZONTAL) a new sheet is created for the next MAX_ROWS (65535).
  239.      * If the resultSet contains > MAX_COLS (255) and _stratification is set to V (VERTICAL) then
  240.      * the first 255 columns will be in the first sheet, the next 255 in the second sheet etc up
  241.      * to the maxiumum number of rows that can be output in a SELECT statement. If > MAX_COLS exist
  242.      * and _stratification is H or N then only 255 columns will be output in the first sheet: if
  243.      * > MAX_ROWS also exists then overflow is controlled by _stratification = H or N.
  244.      *
  245.      * NOTE: Maximum size of an Excel spreadsheet cell is 32768 characters.
  246.      *
  247.      * @param p_resultSet       - the result set, including a geometry column.
  248.      * @param p_outputDirectory - the directory to write output files to.
  249.      * @param p_fileName        - the file name of output files.
  250.      * @param p_sheetName       - Name of base or first sheet. Prefix for all others.
  251.      * @param p_stratification  - Horizontal (H), Vertical (V) or None (N).
  252.      * @param p_geomFormat      - Text format for sdo_geometry columns eg WKT, GML, GML3
  253.      * @param p_dateFormat      - Format for output dates
  254.      * @param p_timeFormat      - Format for output times
  255.      * @param p_precision       - Number of decimal places of coordinates
  256.      * @history Simon Greener, The SpatialDB Advisor, October 2011, Original coding
  257.      */
  258.     PROCEDURE writeExcelSpreadsheet(p_RefCursor       IN &&defaultSchema..EXPORTER.refcur_t,
  259.                                     p_outputDirectory IN VarChar2,
  260.                                     p_fileName        IN VarChar2,
  261.                                     p_sheetName       IN VarChar2,
  262.                                     p_stratification  IN VarChar2 DEFAULT &&defaultSchema..EXPORTER.c_HORIZONTAL_STRATIFICATION,
  263.                                     p_geomFormat      IN Varchar2 DEFAULT &&defaultSchema..EXPORTER.c_WKT_FORMAT,
  264.                                     p_DateFormat      IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_DATEFORMAT,
  265.                                     p_TimeFormat      IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_TIMEFORMAT,                                    
  266.                                     p_precision       IN NUMBER   DEFAULT 3);
  267.     /**
  268.      * writeExcelSpreadsheet
  269.      *
  270.      * @param p_sql             - A SELECT Statement that include a geometry column.
  271.      * @param p_outputDirectory - the directory to write output files to.
  272.      * @param p_fileName        - the file name of output files.
  273.      * @param p_sheetName       - Name of base or first sheet. Prefix for all others.
  274.      * @param p_stratification  - Horizontal (H), Vertical (V) or None (N).
  275.      * @param p_geomFormat      - Text format for sdo_geometry columns eg WKT, GML, GML3
  276.      * @param p_dateFormat      - Format for output dates
  277.      * @param p_timeFormat      - Format for output times
  278.      * @param p_precision       - Number of decimal places of coordinates
  279.      * @history Simon Greener, The SpatialDB Advisor, October 2011, Original coding
  280.      */
  281.     PROCEDURE writeExcelSpreadsheet(p_sql             IN VarChar2,
  282.                                     p_outputDirectory IN VarChar2,
  283.                                     p_fileName        IN VarChar2,
  284.                                     p_sheetName       IN VarChar2,
  285.                                     p_stratification  IN VarChar2 DEFAULT &&defaultSchema..EXPORTER.c_HORIZONTAL_STRATIFICATION,
  286.                                     p_geomFormat      IN Varchar2 DEFAULT &&defaultSchema..EXPORTER.c_WKT_FORMAT,
  287.                                     p_DateFormat      IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_DATEFORMAT,
  288.                                     p_TimeFormat      IN varchar2 DEFAULT &&defaultSchema..EXPORTER.c_TIMEFORMAT,                                    
  289.                                     p_precision       IN NUMBER   DEFAULT 3);
  290.   FUNCTION RunCommand( p_command IN varchar2 )
  291.     RETURN NUMBER
  292.            Deterministic;
  293. END Exporter;
  294. /
  295. 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:

1. 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.
2. There is no documentation other than planned documentation of the procedures in the package header.
3. Source code is be provided in the downloadable zip file.
4. Any improvements to the source code must be provided back to me.
5. 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.
6. 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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [3]

Hi,

Requirement: Need to convert an oracle table which has varchar2 and number columns with data to a shape file(.shp).

Could u please send me the required package to convert the oracle table to a .shp file?

Thanks – Raj

— Raj · 1 May 2012, 06:39 · #

Hi Simon,

I’m trying to use the loadShapefile function inside the EXPORTER package, but I got no success. The weird is that after the function execution, I got no error message, but the table is not created and the shapefile is not imported. I know that this function calls Oracle SampleShapefileToJGeomFeature class, and I don’t know if any additional installation step is required. Could you give any clue?

Regards,
Luis

— Luis · 31 October 2012, 04:49 · #

Luis,

Are you running 11gR2?

Contact me directly.

regards
Simon

— Simon Greener · 31 October 2012, 20:07 · #
META HTTP-EQUIV=