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!