How to use Geokettle to load shapefiles into oracle spatial

Question

I'm looking for an example how to load a shapefile from the file system into an oracle spatial database.

What I have so far is:

• I load the shapefile with the 'Shapefile File input' step
• I use the calculator step to transform the WKT from the shapefile into a Geometry
• I use the Table output to write the data into the database.

I get an error from the Table output step which says 'Error setting value #6 [GEOM Geometry] on prepared statement (Geometry), java.sql.SQLException: invalid datatype

The target column is of type SDO_GEOMETRY.

Thanks D3

Answer 1

Although this example does not have geokettle I'm working backward from oracle spatial and perhaps the document has relevant information.

D Loading ESRI Shapefiles into Spatial

The Java Shapefile converter transforms an ESRI Shapefile into an Oracle Database table for use with Oracle Spatial and Locator. The Shapefile converter uses the Oracle Spatial Java-based Shapefile AdapterShapefileJGeom and SampleShapefileToJGeomFeature classes to load a Shapefile directly into a database table, with the Oracle-equivalent .dbf data types for the attribute columns and the SDO_GEOMETRY data type for the geometry column.

To load a Shapefile into the database, use the converter as described in this appendix. (You can also use the Adapter class to create your own applications and interfaces that transform Shapefiles to SDO_GEOMETRY or JGeometry data types; however, doing this is beyond the scope of this manual. For information about Shapefile-related classes, see Oracle Spatial Java API Reference).

To use the Shapefile converter, you must have the following:

The following Oracle utilities and JDBC libraries: ojdbc14.jar or ojdbc5.jar, sdoutl.jar, and sdoapi.jar

One or more ESRI Shapefiles (.shp, .shx, .and .dbf files) to be converted

D.1 Usage of the Shapefile Converter

The following is the syntax for the Shapefile converter. (Enter the command all on a single line.)

java -cp [ORACLE_HOME]/jdbc/lib/ojdbc5.jar:[ORACLE_HOME]/md/jlib/sdoutl.jar:[ORACLE_HOME]/md/jlib/sdoapi.jar oracle.spatial.util.SampleShapefileToJGeomFeature -h db_host -p db_port -s db_sid -u db_username -d db_password -t db_table -f shapefile_name [-i table_id_column_name][-r srid][-g db_geometry_column][-x max_x,min_x][-y max_y,min_y][-o tolerance] Parameters

-h: Host machine with an existing Oracle database

-p: Port on the host machine (for example, 1521)

-s: SID (database name) on the host machine

-u: Database user

-d: Password for the database use

-t: Table name for the converted Shapefile

-f: File name of an input Shapefile (without extension)

-i: Column name for unique numeric ID, if required

-r: Valid Oracle SRID for coordinate system; use 0 if unknown

-g: Preferred SDO_GEOMETRY column name

-x: Bounds for the X dimension; use -180,180 if unknown

-y: Bounds for the Y dimension; use -90,90 if unknown

-o: Load tolerance fields (x and y) in metadata; if not specified, tolerance fields are 0.05

-a: Append Shapefile data to an existing table

-n: Start ID for column specified in -i parameter

-c: Commit interval; by default, commits occur every 1000 conversions and at the end

-v: Println interval; by default, a display every 10 conversions

D.2 Examples of the Shapefile Converter

The following examples show the use of the Shapefile converter to transform a file named shapes to a table named shapes containing an SDO_GEOMETRY column named geom. The SRID for the Longitude/Latitude (WGS 84) coordinate system is used (8307).

Linux Example

setenv clpath $ORACLE_HOME/jdbc/lib/ojdbc5.jar:$ORACLE_HOME/md/jlib/sdoutl.jar:$ORACLE_HOME/md/jlib/sdoapi.jar java -cp $clpath oracle.spatial.util.SampleShapefileToJGeomFeature -h gis01 -p 1521 -s orcl -u scott -d -t states -f states -r 8307 -g geom Windows Example

java -classpath %ORACLE_HOME%\jdbc\lib\ojdbc5.jar;%ORACLE_HOME%\md\jlib\sdoutl.jar;%ORACLE_HOME%\md\jlib\sdoapi.jar oracle.spatial.util.SampleShapefileToJGeomFeature -h gis01 -p 1521 -s orcl -u scott -d -t states -f states -r 8307 -g geom

Answer 2

To load a shapefile into an oracle database just a 'Shapefile input' and an 'Table output' is required, Geokettle handles everything internally

In the shapefile input You define the shapefile and in the output You map the geometry of the shape file (GeoKettle identifies the geometry for You) to the corresponding db-column.

To see an example You may copy the following XML and paste it into the graphical area of a transformation.

<?xml version="1.0" encoding="UTF-8"?>
<transformation-steps>
 <steps>
  <step>
    <name>Shapefile File Input</name>
    <type>GISFileInput</type>
    <description/>
    <distribute>Y</distribute>
    <copies>1</copies>
         <partitioning>
           <method>none</method>
           <schema_name/>
           </partitioning>
    <filename>D:\GIS\data\Admin Deutschland\DEU_adm3.shp</filename>
    <isfilenameinfield>N</isfilenameinfield>
    <filenamefield/>
    <limit>0</limit>
    <add_rownr>N</add_rownr>
    <field_rownr/>
    <gis_file_charset/>
    <passing_through_fields>N</passing_through_fields>
    <accept_stepname/>
     <cluster_schema/>
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
      <xloc>186</xloc>
      <yloc>263</yloc>
      <draw>Y</draw>
      </GUI>
    </step>

  <step>
    <name>Table output</name>
    <type>TableOutput</type>
    <description/>
    <distribute>Y</distribute>
    <copies>1</copies>
         <partitioning>
           <method>none</method>
           <schema_name/>
           </partitioning>
    <connection>local oracle</connection>
    <schema/>
    <table>SHAPES</table>
    <commit>1000</commit>
    <truncate>N</truncate>
    <ignore_errors>N</ignore_errors>
    <use_batch>Y</use_batch>
    <specify_fields>N</specify_fields>
    <partitioning_enabled>N</partitioning_enabled>
    <partitioning_field/>
    <partitioning_daily>N</partitioning_daily>
    <partitioning_monthly>Y</partitioning_monthly>
    <tablename_in_field>N</tablename_in_field>
    <tablename_field/>
    <tablename_in_table>Y</tablename_in_table>
    <return_keys>N</return_keys>
    <return_field/>
    <fields>
        <field>
          <column_name>GEOM</column_name>
          <stream_name>the_geom</stream_name>
        </field>
        <field>
          <column_name>ID</column_name>
          <stream_name>ID_3</stream_name>
        </field>
    </fields>
     <cluster_schema/>
 <remotesteps>   <input>   </input>   <output>   </output> </remotesteps>    <GUI>
      <xloc>383</xloc>
      <yloc>261</yloc>
      <draw>Y</draw>
      </GUI>
    </step>

    </steps>
<order>
  <hop> <from>Shapefile File Input</from><to>Table output</to><enabled>Y</enabled> </hop>
  </order>
  <notepads>
   </notepads>
 </transformation-steps>