Mercator GeoSystems Ltd - SDO2SHP ================================= http://www.sdo2shp.com/ Release 1.3.0 (November 2023) ============================== Background ========== Oracle Spatial users will no doubt be familiar with the "shp2sdo" tool supplied by Oracle that can be used to convert ESRI Shapefiles (.shp) into Oracle data files suitable for use by the Oracle SQL*Loader and hence into the Oracle Database. One tool that is missing though is the opposite of this - a tool to extract Oracle Spatial data and convert it to an ESRI Shapefile. This is often required for many reasons including visualising the data, moving the data to another database, backing up spatial data, as an intermediate step to converting to another format, for transmission of the data to other organisations.... This list is endless! Until now this could typically be carried out in one of three, less than perfect, ways : 1) Expensive data conversions tools that do far more than required and cost more to buy, maintain and install. 2) Open Source tools that require building or compiling which is inappropriate in some situations 3) Building code to do it yourself. Often none of these are ideal. The "sdo2shp" tools does one thing - converts Oracle Spatial data to an ESRI Shapefile format in one go. There are no intermediate formats required, no building or other compiling and no need to code anything yourself. The tool is a "command line" tool so is ideal for integration into automated scripts and batch files. Installation Notes ================== 1. Windows ---------- We recommend you use the installer packager provided to install SDO2SHP. This ensures all the correct DLLs, dependencies etc are all setup correctly. If required sdo2shp.exe can be moved out of the installtion directory and copied elsewhere. For use in batch files etc we we recommend that the installation directory is added to the system PATH variable. The installation script does not do this for you. 2. Linux -------- For Linux all we supply is the sdo2shp binary. You will need to obtain the Oracle Instant client and install it yourself. Supported Environments & Installation notes =========================================== Currently the software is available on these platforms : Windows (64 bit) Linux (x86_64 and ARM) MacOS (Intel chips or Apple Silicon with Rosetta) On some Windows based systems you may need to install the Visual C++ runtime from here before the tool will run successfully : http://go.microsoft.com/fwlink/?linkid=65127&clcid=0x409 For Linux, we recommend the use of the Instant Client 19.21 release available here : https://www.oracle.com/database/technologies/instant-client/downloads.html The "basic" version is required (as opposed to the "basic lite"). Usage Notes =========== The tool is a command line tool and will need to be run via a Command Prompt in Windows or a Terminal (or shell) in Linux. The tool can be run in two modes, interactive mode in which appropriate parameters are collected from the user via an interactive process and then executed. Secondly the command line mode which offers more flexibility and the option to include SDO2SHP in batch files etc. To run in interactive mode simply run SDO2SHP with no arguments. Running the tool a /help argument displays the usage notes : SDO2SHP. 1.3.0 Copyright (c) 2023 Mercator GeoSystems Ltd Usage notes : sdo2shp [options] Where [options] are : -g --geom : geometry column -t --table : table -s --schema : table owner / schema -q --query : sql -o --output : output shapefile name or directory if table is '*' -p --proj : output a .prj file -x --timings : output timings -d --datefmt : format string to use when exporting DATE and TIMESTAMP fields default is "dd/mm/yyyy hh:mi:ss" -i --ignore : ignore unsupported geometry errors e.g sdo2shp scott/tiger@orcl -t cities -o cities sdo2shp scott/tiger@orcl -q "select cityname,geom from cities" -g geom -o city sdo2shp scott/tiger@orcl -t "*" -o /tmp/ -p -i sdo2shp scott/tiger@orcl -t cities -d "dd/mm/yyyy hh:mi:ss" Connection Strings ------------------ Note that the must be a valid Oracle connection string, appropriate for the Oracle client software you are using and any TNSNAMEs.ORA configuration. For example, a full Oracle Client install or any other installation with a TNSNAMES.ORA can use a TNS alias like so : sdo2shp scott/tiger@orcl [options] Whilst an 'out of the box' Instant Client will require syntax such as : sdo2shp scott/tiger@//server:1521/sid [options] Options ------- The simplest usage is to simply specify the -t (or --table) option. The specified table will be converted to Shapefile format with the name the same as the table. For example : sdo2shp scott/tiger@orcl -t cities The tool will attempt to derive the geometry column via the SDO metadata views for you but if you like you can specify the geometry column explictly using the -g (or --geom) option : sdo2shp scott/tiger@orcl -t cities -g geom If you wish to specify a different output file name use the -o (or --output) option (note : do not supply a .shp extension) : sdo2shp scott/tiger@orcl -t cities -g geom -o exported_cities If you wish to export every table in a schema that has geometry (and metadata) then you can specify "*" as a table name. The geometry column will be derived automatically from the Oracle Spatial metadata views and the output Shapefile will be named after the table. In this context the -o can be used to specify an output directory. For example : sdo2shp scott/tiger@orcl -t "*" -o /tmp/ Alternatively an ad-hoc query can be used instead of a table name via the -q (or --query) option. Note that you will need to enclose the query in double quotes. Using the -q option means that you MUST supply the geometry column (using -g or --geom) and output shapefile name (using -o or --output). For example : sdo2shp scott/tiger@orcl -q "select cityname,geom from cities" -g geom -o my_cities By default a projection file (.prj) will not be created for each shapefile that is created. To create one pass the -p (or --proj) parameter. The output .prj file will only be created if the Oracle Spatial geometry has an associated SRID. For example : sdo2shp scott/tiger@orcl -t cities -p To display timings for each exported shapefile (useful for performance testing and other diagnostic purposes) pass the -x (or --timings) option and the start and end time for each shape will be displayed. sdo2shp scott/tiger@orcl -t "*" -o /tmp/ -x Note that the level of detail for logging must be BASIC or "above" for you the timings to be displayed (see below). If any of the Oracle columns are DATE or TIMESTAMP columns then by default they will be written as the 'date' type with the YYYYMMMDD format. If this is unnaceptable then you can specify that they be converted to a string before storing in the appropriate attribute column of the shapefile. This is performed by converting them to text using a standard format string using the -d (or --datefmt) option. sdo2shp scott/tiger@orcl -t cities -d "dd-mm-yyyy (hh:mi)" Not all types of SDO geometry can be exported to a Shape file. For example a compound line string has no equivalent in a shape file. Normally an error will be displayed if any of these unsupported types are encountered and the export stops. To ignore these errors and continue the export use the -i (or --ignore) option. In the output shape file a NULL geometry will be written instead. sdo2shp scott/tiger@orcl -t cities -i Console output ============== There are four levels of detail available for the output from sdo2shp. SILENT : Totally silent. No errors or other information will be displayed at all. ERROR : Only errors are displayed. No diagnostic or timing information (if enabled) will be displayed. BASIC : The default value. Basic output is displayed sufficient to show any errors or success. DETAILED : Detailed output will be displayed showing information on each step in the export process including every column value for every row. The level of detail is set via the SDO2SHP_LOG_LEVEL environment variable. On Windows set this using : C:\> set SDO2SHP_LOG_LEVEL=BASIC To set on Linux : $ export SDO2SHP_LOG_LEVEL=BASIC Known issues ============ 1. On some Linux systems a bug with the standard C++ library implementation can cause "Segmentation Faults" when accessing Oracle databases. If this occurs try setting the following environment variable : $ export GLIBCXX_FORCE_NEW=1 or $ export GLIBCPP_FORCE_NEW=1 See this Oracle forum post for more detail : http://forums.oracle.com/forums/thread.jspa?messageID=1444301 2. On some windows systems it seems necessary to set a log level environment, as opposed to optional. In this case set the level to BASIC, like so : C:\> set SDO2SHP_LOG_LEVEL=BASIC Change Log ========== 1.2.2 => 1.3.0 -------------- Product relaunched and code modernised and adapted for ARM use 1.2.1 => 1.2.2 -------------- Added -r option to check for inner-rings inside polygons and hence create multi-patch shape file 1.2.0 => 1.2.1 -------------- Fix to bug when exporting NUMBER(1) column in Oracle 9.2 1.1.1 => 1.2.0 -------------- Updated to use Oracle 11.1 libraries. 1.1.0 => 1.1.1 -------------- Fix to handling of large integers. 1.0.4 => 1.1.0 -------------- Addition of new interactive mode. Distribution of SDO2SHP via new Windows installer. 1.0.3 => 1.0.4 -------------- Fix to bug where non-point geometry containing a SDO_POINT member, but with NULL vertices was exported as a point. Integration of Oracle OCCI fix re: bug #6142534 and NULL vertex values. 1.0.2 => 1.0.3 -------------- Addition of support for dBase date type. (YYYYMMDD) Change of handling for zero scale numbers to Integers. 1.0.1 => 1.0.2 -------------- Improved support for additional GTYPES with measures and 3D / 4D types. 1.0.0 => 1.0.1 -------------- Better handling of non-geographic types, such as date etc. 0.9.1 => 1.0.0 -------------- Change of version number to indicate post-beta release. 0.9.0 => 0.9.1 -------------- Addition of -i (or --ignore) flag to ignore unsupported ETYPE and INTERPRETATION values. Note : this change has caused the previous -i flag (for timings) to be changed to -x 0.8.9 => 0.9.0 -------------- Beta period extended until 15th March 2007. 0.8.8 => 0.8.9 -------------- Fixed bug re exporting of NUMBER data with no specified scale or precision. 0.8.7 => 0.8.8 -------------- Fixed bug re: reading of non-NULL SDO_POINT data with NULL X & Y attributes. Fixed bug re: reading of NULL date fields. Fixed bug re: writing of certain large and negative floating point numbers 0.8.6 => 0.8.7 -------------- Fixed bug where metadata wasa being checked even if geometry column was being supplied via -g. 0.8.5 => 0.8.6 -------------- Changes to memory allocation / deallocation code to attempt to solve memory freeing problems. 0.8.4 => 0.8.5 -------------- Fixed bug relating to queries using geometry columns not called GEOM. 0.8.3 => 0.8.4 -------------- Fixed bug introduced in 0.8.3 re: executing query. 0.8.2 => 0.8.3 -------------- Improved error reporting when connecting and executing extract query. 0.8.1 => 0.8.2 -------------- Addition of code to support evaluation copies of the software. Tidying up of usage instructions. Addition of message to display expiration date of beta & eval software. General code tidy up. 0.8.0 => 0.8.1 -------------- Change to build environment to make distribution on Windows easier. Improved error handling for failed connections. 0.7.1 => 0.8.0 -------------- Addition of DATE and TIMESTAMP support.