Convert Oracle SDO_GEOMETRY ResultSet to GML (JAVA)

Today we will go through the oracle sdo_geometry datatype and its convertion to OGC GML type format.

Before we go through our code example lets see the prerequisites.
1) You need to download sdoutl.jar and sdoapi.jar. These 2 jars may be found in the Oracle Companion CD which may be freely downloaded from oracle. Else just google it and you may found some downloading links.
2) You need to download ojdbc6.jar which may be found here ojdbc6.jar
3) Once you have these three jars you may reference them to your project.

Now lets go through the hot stuff.

Fisrt we need to create a class to obtain a jdbc connection to oracle database.
class accepts 5 parameters (ip, port, dbname, user, pass) I shall give you an example at the end of this post. Lets go through the code of our first classs
GetOraConnection class CODE

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

package freeopengis.rdbms; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; /** * * @author pt */ public class GetOraConnection { public static Connection main (String ip, String port, String db, String user, String pass) throws InstantiationException, IllegalAccessException{ try { Class.forName ("oracle.jdbc.OracleDriver").newInstance(); } catch (ClassNotFoundException e) { System.out.println("Oracle JDBC Driver missing."); e.printStackTrace(); } System.out.println("Oracle JDBC Driver Found and Registered."); Connection connection = null; try { System.out.println("jdbc:oracle:thin:@"+ip+":"+port+":"+db); connection = DriverManager.getConnection( "jdbc:oracle:thin:@"+ip+":"+port+":"+db, user, pass); } catch (SQLException e) { System.out.println("Connection Failed!"); e.printStackTrace(); } if (connection != null) { System.out.println("Connection succeed!"); } else { System.out.println("Failed to create connection!"); } return connection; } }

  
Now we need to create one more class to accept the ResultSet and return its GML representation.
RsetToGml class CODE

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

package freeopengis.rdbms; import java.io.IOException; import java.sql.ResultSet; import java.sql.SQLException; import oracle.spatial.geometry.JGeometry; import oracle.spatial.util.GML2; import oracle.sql.STRUCT; /** * * @author pt */ public class RsetToGml { String outputStr = ""; public RsetToGml (ResultSet rset) throws SQLException, IOException { StringBuilder sb = new StringBuilder(); if( rset != null ){ int numOfColumns = rset.getMetaData().getColumnCount(); sb.append("<wfs:FeatureCollection xmlns:wfs=\"http://www.opengis.net/wfs\">"); int counter = 0; Boolean geomFound = false; while (rset.next()) { counter = counter + 1; //logger.info("parsing "+counter+" num"); sb.append("<gml:featureMember xmlns:gml=\"http://www.opengis.net/gml\">"); sb.append("<feature:features xmlns:feature=\"http://mapserver.gis.umn.edu/mapserver\" >"); for (int i=1;i<numOfColumns+1;i++){ String colName = rset.getMetaData().getColumnName(i); String type = rset.getMetaData().getColumnTypeName(i); //logger.info(type); if (type.equals("MDSYS.SDO_GEOMETRY")){ geomFound = true; STRUCT st = (oracle.sql.STRUCT) rset.getObject(i); JGeometry j_geom = JGeometry.load(st); //GML2 gml = new GML2(); String s = GML2.to_GMLGeometry(j_geom); //logger.info(s); sb.append("<feature:").append(colName).append(">"); sb.append(s); sb.append("</feature:").append(colName).append(">"); } else{ sb.append("<feature:").append(colName).append(">"); sb.append(rset.getString(rset.getMetaData().getColumnName(i))); sb.append("</feature:").append(colName).append(">"); } } sb.append("</feature:features>"); sb.append("</gml:featureMember>"); } sb.append("</wfs:FeatureCollection>"); if (geomFound == true){ outputStr = sb.toString(); sb.setLength(0); //flush it } else { outputStr = "ERROR: no geometry found."; } }} public String getGML (){ return outputStr; } }

Once we have these 2 classes in our project we may create a third class to call them and finally get the ResultSet back as GML. So I ll give you an example on how to use the claasses above and you may write your own method.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

//Declare the parameters to use in order to obtain a connection with the DB String dbip = "127.0.0.1"; String dbport = "1521"; String dbname = "orcl"; String dbuser = "geouser"; String dbpass = "geopass"; //we asume there is a CITIES geometry table within the geouser Schema. //You may write any SQL query as long as this outputs an SDO_GEOMETRY column String sqlquery = "SELECT ID,NAME,GEOM FROM CITIES"; String gmlStr = ""; try { //Get the Oracle connection Connection con = GetOraConnection.main(dbip,dbport, dbname, dbuser, dbpass); if (con !=null){ Statement stmt = null; try { stmt = con.createStatement (); } catch (SQLException e) { System.out.println("ERROR:"+e.getMessage()); } //execute your query and get back the result set ResultSet rset = null; try { rset = stmt.executeQuery (sqlquery); } catch (SQLException e) { System.out.println("ERROR:"+e.getMessage()); } if( rset != null ) { //convert the ResultSet into a GML string RsetToGml gml = new RsetToGml(rset); stmt.close(); rset.close(); con.close(); gmlStr = gml.getGML(); System.out.println(gmlStr); } } } catch (InstantiationException ex) { Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex); }