Before we go through the code example. Lets see the prerequisites.
(these are copy-paste from my other post --> sdo2gml)
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.
The following geotools jars shall be referenced to your project. (geotools version 13)
--> gt-xsd-filter
--> gt-jdbc-oracle
--> gt-data
--> gt-shapefile
So lets start coding.
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
(these are copy-paste from my other post --> sdo2gml)
GetOraConnection class CODE
packagegis.openfreegis.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; } }
Once we have this class we may create the class that does the job.
RsetToGeoJSON class CODE
/**
* A class to support the convertion of an JAVA oracle result set to a single GeoJson File.
* @author p_tsagkis
* 29/10/2019
*/
package gis.openfreegis.rdbms;
import com.vividsolutions.jts.geom.Geometry;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.OracleConnection;
import oracle.sql.STRUCT;
import org.geotools.data.oracle.sdo.GeometryConverter;
import org.geotools.geojson.geom.GeometryJSON;
public class RsetToGeoJSON {
private static String geoJsonStr;
//Constructor
public RsetToGeoJSON(ResultSet rset, Connection con, String geoJSONSRID) {
ArrayList<String> featsSts = new ArrayList<>();
int numOfColumns;
try {
numOfColumns = rset.getMetaData().getColumnCount();
while (rset.next()) {
ArrayList<HashMap<String, String>> attrs = new ArrayList<>();
HashMap<String, String> currAttrs = new HashMap<>();
Geometry jtsGeom = null;
for (int i=1;i<numOfColumns+1;i++){
String colName = rset.getMetaData().getColumnName(i);
String type = rset.getMetaData().getColumnTypeName(i);
String value = rset.getString(rset.getMetaData().getColumnName(i));
if (type.equals("MDSYS.SDO_GEOMETRY")){
GeometryConverter geomCon = new GeometryConverter((OracleConnection) con);
jtsGeom = geomCon.asGeometry((STRUCT) rset.getObject(i));
} else {
currAttrs.put(colName, value);
attrs.add(currAttrs);
}
}
String rowFeatureStrr = createFeatureObject(jtsGeom,attrs);
featsSts.add(rowFeatureStrr);
}
generateFinalGeoJSON(featsSts,geoJSONSRID);
} catch(SQLException ex) {
Logger.getLogger(RsetToGeoJSON.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* getter
* @return
*/
public String getGeoJsonStr(){
return geoJsonStr;
}
/**
* Save geoJsonStr to geojson file in supplied directory (local disk)
* @param filepath
*/
public void writeToGeoJsonFile(String filepath){
try (BufferedWriter bw = new BufferedWriter(new FileWriter(new File(filepath)))) {
bw.write(geoJsonStr);
bw.close();
} catch (FileNotFoundException ex) {
Logger.getLogger(RsetToGeoJSON.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(RsetToGeoJSON.class.getName()).log(Level.SEVERE, null, ex);
}
}
//******************Private methods this point forward ********************//
/**
* Put them all together.
* @param feats
* @params SRID getted from first geometry parsed
*/
private void generateFinalGeoJSON(ArrayList<String> feats, String srid){
StringBuilder finjson = new StringBuilder();
finjson.append("{");
finjson.append("\"type\": \"FeatureCollection\",");
finjson.append(" \"crs\": {");
finjson.append(" \"type\": \"name\",");
finjson.append(" \"properties\": {");
finjson.append(" \"name\": \"").append(srid).append("\"");
finjson.append(" }");
finjson.append(" }, ");
finjson.append("\"features\":[");
for (int i=0;i<feats.size();i++) {
if (i<feats.size()-1){
finjson.append(feats.get(i)).append(",");
} else {
finjson.append(feats.get(i)).append("]");
}
}
finjson.append("}");
//trim spaces and new lines
geoJsonStr = finjson.toString().replaceAll("[\\n\\t ]", "");
}
/**
* Construct the feature object.
* Put together geometry and attributes into a single feature
* @param geom (JGeometry)
* @param as (ArrayList<HashMap<String, String>>)
* @return
*/
private String createFeatureObject(Geometry geom, ArrayList<HashMap<String, String>> as) {
String propsStr = propsFromAttrs(as);
StringBuilder featjson = new StringBuilder();
featjson.append("{");
featjson.append("\"type\": \"Feature\",");
featjson.append("\"geometry\":");
featjson.append(geoJsonGeomFromJGeom(geom));
featjson.append(",");
featjson.append(propsStr);
featjson.append("}");
return featjson.toString();
}
/**
* Create the geojson representation string for the supplied jts geometry
* @Geometry geom Geometry JTS geometry
* @return String
*/
private String geoJsonGeomFromJGeom(Geometry geom){
String gJson = "";
try {
GeometryJSON gjson = new GeometryJSON();
StringWriter writer = new StringWriter();
gjson.write(geom, writer);
gJson = writer.toString();
} catch (IOException ex) {
Logger.getLogger(RsetToGeoJSON.class.getName()).log(Level.SEVERE, null, ex);
}
return gJson;
}
/**
* Pass a hashmap for attributes like key:value (column name: value) and get back the geojson representation
* @param as
* @return String
*/
private String propsFromAttrs( ArrayList<HashMap<String, String>> as){
StringBuilder porpertiesStr = new StringBuilder();
porpertiesStr.append("\"properties\":{");
for ( int i=0;i<as.size();i++){
System.out.println("i===="+i);
HashMap<String, String> curEntry = as.get(i);
porpertiesStr.append("\"");
porpertiesStr.append(curEntry.keySet().toArray()[i]);
porpertiesStr.append("\":\"");
porpertiesStr.append(curEntry.get((String) curEntry.keySet().toArray()[i]));
porpertiesStr.append("\"");
if (i<as.size()-1){
porpertiesStr.append(",");
} else {
porpertiesStr.append("}");
}
}
return porpertiesStr.toString();
}
}
And finally we need to call our new method passing a resultset, the connection and the SRID code
String dbip = "xxx.xx.x.xxx";
String dbport = "1521";
String dbname = "orcl";
String dbuser = "GEO";
String dbpass = "GEO";
String sqlquery = "select * from geotable";
try {
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());
}
ResultSet rset = null;
try {
System.out.println("start query execution ==== "+sqlquery);
rset = stmt.executeQuery (sqlquery);
} catch (SQLException e) {
System.out.println("ERROR:"+e.getMessage());
}
if( rset != null )
{
System.out.println("start geojson conversion");
//NOW TEST THE GEOJSON]
RsetToGeoJSON geoJsonConv = new RsetToGeoJSON(rset,con, "EPSG:2100");
String geoJson = geoJsonConv.getGeoJsonStr();
System.out.println("geoJson====="+geoJson);
geoJsonConv.writeToGeoJsonFile("D:\\work\\rsetgeom.geojson");
stmt.close();
rset.close();
con.close();
}
}
} 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);
}
