Top 5 Recent Articles

ARTICLES CATEGORIES

Scheduling the Export of Spatial Data in Oracle to a Shapefile each night

While there is always a need for software like Safe Software’s Awesome FME to automate import and export tasks from your database, the ability to do so just using Oracle can also be enormously useful.

I have implemented the approach described below many times over the years, in different customer sites.

At one customer site, there was a requirement for the engineers to be able to access the latest version of the corporate spatial data stored in Oracle within their own dedicated desktop software. Some of that software could access Oracle directly, but some couldn’t. Additionally, the engineers needed to be able to take the data into the field where no connection to the database was possible.

Luckily their software could access shapefiles or MapInfo TAB files (even those which wrap a shapefile).

Additionally, spatial data within databases is often undergoing such editing that makes it difficult to enable direct access at any time of the day.

The obvious solution was to use Oracle Scheduled Tasks to export that critical engineering asset data each night to a shared drive. This approach provides security to end-users that they are working with the latest “published” version of the data (as different from the current version in the database that may still be undergoing editing). It also implements a level of robustness and reliability that is not possible when one is forced to use software external to the database and probably running on a completely different server.

Certainly the engineers and foresters that have benefited from these scheduled exports were happy with the idea!

But how do so it?

Well the Oracle Scheduler is easy enough to learn, but how can Oracle write formats that it does not directly support within the database?

(GeoJSON can be exported because most databases today support JSON and the ability to write/load data from disk.)

But Spatial to a Shapefile?

As I have said above one can use FME for this but not as a completely native Oracle process.

The solution I created uses my EXPORTER package which uses Java code (based on Java Topology Suite, GeoTools etc) to create the solution. The Java code is created, compiled and then loaded into the Oracle database JVM. The EXPORTER PL/SQL Package creates an interface which PL/SQL based processes can use. The EXPORTER Package supports ESRI Shapefiles, Wrapped TAB files, Excel Spreadsheets, Delimited Text Files (eg CSV) and soon GeoJSON.

Since I have been tidying up, improving, testing, and adding the GeoJSON functionality to the Java on which is based my EXPORTER code, I thought I would create an fully worked article on the exporting of sdo_geometry data to a shapefile via DBMS_SCHEDULER.

The EXPORTER package is available on my website. The new improved and expanded code will be re-leased soon as part of a whole package of tested software that will be bundled for purchase.

Here is an example of exporting data in a table called SEWER_PIT to a shapefile.

 DROP   TABLE Sewer_Pit  PURGE;
 TABLE SEWER_PIT dropped.
 .
 CREATE TABLE Sewer_Pit (
   AssetId          INTEGER,
   Pit_Label        varchar2(20),
   Pit_Lid_Material varchar2(10),
   Pit_Diameter     NUMBER,
   Pit_Depth        NUMBER,
   geom             mdsys.sdo_geometry
 );
 TABLE SEWER_PIT created.
 .
 SET FEEDBACK OFF
 INSERT INTO Sewer_Pit (AssetId, Pit_Label, Pit_Lid_Material, Pit_Diameter, Pit_Depth, geom)
       SELECT rownum AS AssetId,
              CHR(dbms_random.VALUE(65,90)) || to_char(round(dbms_random.VALUE(0,1000),0),'FM9999') AS Pit_Label,
              CASE WHEN round(dbms_random.VALUE(0,1),0) = 0 THEN 'Concrete' ELSE 'Plastic' END AS Pit_Lid_Material,
              CASE WHEN round(dbms_random.VALUE(0,1),0) = 0 THEN 900 ELSE 1200 END AS Pit_Diameter,
              ROUND(dbms_random.VALUE(0.5,15.0),1) AS Pit_Depth,
              mdsys.sdo_geometry(3001,
                                 28355,
                                 MDSYS.SDO_POINT_TYPE(
                                    ROUND(DBMS_RANDOM.VALUE( 300000, 500000),3),
                                    ROUND(DBMS_RANDOM.VALUE(5000000,5500000),3),
                                    ROUND(DBMS_RANDOM.VALUE(    -50,   1500),2)),
                                 NULL,NULL)
        FROM DUAL
      CONNECT BY LEVEL <= 500
 COMMIT
 SET FEEDBACK ON
 SET HEADING OFF
 .
 SELECT 'Inserted '||COUNT(*)||' records into Sewer_Pit' FROM Sewer_Pit
 Inserted 500 records INTO Sewer_Pit
 .
  1 ROWS selected
 .
 SET HEADING ON
 DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER('Sewer_Pit');
 1 ROWS deleted.
 .
 COMMIT;
 committed.
 .
 DECLARE
   v_shape mdsys.sdo_geometry;
 BEGIN
   SELECT SDO_AGGR_MBR(geom)
     INTO v_shape
     FROM Sewer_Pit;
   INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)
     VALUES (UPPER('Sewer_Pit'),
             'GEOM',
             MDSYS.SDO_DIM_ARRAY(
                   MDSYS.SDO_DIM_ELEMENT('X',V_SHAPE.SDO_ORDINATES(1),V_SHAPE.SDO_ORDINATES(4),0.05),
                   MDSYS.SDO_DIM_ELEMENT('Y',V_SHAPE.SDO_ORDINATES(2),V_SHAPE.SDO_ORDINATES(5),0.05),
                   MDSYS.SDO_DIM_ELEMENT('Z',V_SHAPE.SDO_ORDINATES(3),V_SHAPE.SDO_ORDINATES(6),0.05)),
             28355);
 END;
 /
 SHOW ERRORS
 anonymous block completed
 No Errors.
 .
 COMMIT;
 committed.
 .
 CREATE INDEX Sewer_Pit_GEOM ON Sewer_Pit(geom)
        INDEXTYPE IS mdsys.spatial_index
        parameters('sdo_indx_dims=2, layer_gtype=point');
 INDEX SEWER_PIT_GEOM created.
 .
 BEGIN
  -- Note use of Aliases to make column names > 10 characters Dbase compliant
  DBMS_SCHEDULER.CREATE_JOB (
    job_name         ='ASSET_DATA_EXPORT',
    job_type         ='PLSQL_BLOCK',
    job_action       ='
 DECLARE
 BEGIN
  EXPORTER.writeshapefile(
   p_sql             =''SELECT ASSETID,PIT_LABEL,PIT_LID_MATERIAL as Lid_Mtrl,PIT_DIAMETER as diameter,PIT_DEPTH,GEOM FROM PROJPOINT2D'',
   p_output_dir      =''C:\temp'',
   p_file_name       =''points_night_extract'',
   p_shape_type      =EXPORTER.c_Point,
   p_geometry_index  =6,
   p_precision       =3,
   p_commit_interval =100,
   p_prj_string      =''PROJCS["GDA94 / MGA zone 56",GEOGCS["GDA94",DATUM["D_GDA_1994",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",153],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",10000000],UNIT["Meter",1]]'',
   p_geomFormat      =EXPORTER.c_WKT_Format,
   p_recordIdentifier=''GID''
  );
 END;',
  start_date      =to_timestamp(trunc(sysdate+1))+5/24,
  repeat_interval ='freq=weekly; byday=mon,tue,wed,thu,fri; byhour=5; byminute=0; bysecond=0',
  comments        ='Job to export sewer_pits for use with Civil Software every working day at 5am.'
 );
 END;
 /
  SHOW ERRORS
 anonymous block completed
 No Errors.
 .
 -- Enable the job
 EXEC DBMS_SCHEDULER.ENABLE('ASSET_DATA_EXPORT');
 anonymous block completed
 .
 -- Show the job
 SELECT job_name, job_type, schedule_type, start_date, Repeat_interval, Enabled, Run_Count, job_action FROM USER_SCHEDULER_JOBS;
 .
 -- Results edited and split across two lines...
 --
 JOB_NAME           JOB_TYPE         SCHEDULE_TYPE START_DATE                             REPEAT_INTERVAL                                                         ENABLED  RUN_COUNT
 ------------------------------- ------------- -------------------------------------- ---------------------------------------------------------------------------- ------- ---------
 ASSET_DATA_EXPORT  PLSQL_BLOCK      CALENDAR      06-SEP-18 05.00.00.000000000 AM AUSTRA freq=weekly; byday=mon,tue,wed,thu,fri; byhour=5; byminute=0; bysecond=0 TRUE            0
 .
 JOB_ACTION
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 DECLARE 
 BEGIN
   EXPORTER.writeshapefile(
    p_sql             ='SELECT ASSETID,PIT_LABEL,PIT_LID_MATERIAL,PIT_DIAMETER,PIT_DEPTH,GEOM FROM PROJPOINT2D',
    p_output_dir      ='C:\temp',
    p_file_name       ='points_night_extract',
    p_shape_type      =EXPORTER.c_Point,
    p_geometry_index  =6,
    p_precision       =3,
    p_commit_interval =100,
    p_prj_string      ='PROJCS["GDA94 / MGA zone 56",GEOGCS["GDA94",DATUM["D_GDA_1994",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",153],PARAMETER["scale_factor",0.9996],PARAMETER["false_easting",500000],PARAMETER["false_northing",10000000],UNIT["Meter",1]]',
    p_geomFormat      =EXPORTER.c_WKT_Format,
    p_recordIdentifier='GID'
    );
 END;
 .
  1 ROWS selected
 .
 -- Drop the job
 EXECUTE DBMS_SCHEDULER.DROP_JOB (job_name => 'ASSET_DATA_EXPORT');
 anonymous block completed
 .
 -- Confirm job is gone.
 SELECT * FROM USER_SCHEDULER_JOBS
 no ROWS selected

Now this is of use to everybody working in a similar situation to that which caused this work to be done!