Skip to content
June 22, 2010 / olaf

Processing and Oracle

I knew that it is (http://processing.org/) very easy to experiment with visualizations in Processing, but I just recently learned just how easy it is to extend its functionality.
In the Geo-area GML is widespread known as Geography Markup Language (http://en.wikipedia.org/wiki/Geography_Markup_Language). GML can be very complex and some consider GML as far too much. It tries to model the whole world and everything in it. I recently learned about the other GML : Graffity Markup Language and the gml-to-dxf processing example (link), does not seem to work anymore, sorry)  As I think that plain geography in the Oracle Spatial databases (boundaries) are a bit boring I always try to invent ways to insert funky geometries for testcases.
This is where I tried to combine processing, the GML-to-dxf example and Oracle Spatial SDO-api. Very, very simple ! Just drop jars sdoapi.jar ojdbc6.jar classes12.jar on the processing ide, add following code and run !
In header section


import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OracleDriver;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;
boolean bIsOraRecording = false;
String m_host = "DB-HOSTNAME";
String m_port = "1521";
String m_sid = "ORCL";
String m_user = "SCOTT";
String m_password = "TIGER";
String url = "jdbc:oracle:thin:@" + m_host + ":" + m_port + ":" + m_sid;

On-screen instructions :

String instructions = "Current GML file: " + gmlFilename + "\n";
instructions += "Click mouse for next GML file in data folder.\n";
instructions += "Press 'd' to export DXF file to sketch folder.\n";
instructions += "Press 'o' to export to Oracle database: " + url + ".\n";
instructions += "Press 'c' to export CSV file(s) to sketch folder.";
text(instructions, 100,height-100);

In Keypressed :


case 'O':
case 'o':
bIsOraRecording = true;
break;

New procedure connect() :


OracleConnection connect() {
OracleConnection conn = null;
try {
DriverManager.registerDriver(new OracleDriver());
conn = (OracleConnection)DriverManager.getConnection(url, m_user, m_password);
}
catch(Exception e) {
e.printStackTrace();
}
return conn;
}

And the procedure to insert geometries:


void putGeomInOracle() {
boolean _3d = false;
float ORA_MULTPLY = 10000.0;
Object[] lineArray = new Object[data.size()];

for (int s=0; s<data.size(); s++){
GMLPoint pts[] = (GMLPoint[]) data.elementAt(s);
double ordArray[] = new double[pts.length * ((_3d) ? 3 : 2)];

int multiplyFactor = (_3d) ? 3 : 2 ;
for (int p=0; p<(pts.length); p++){
GMLPoint pt = pts[p];
ordArray[p*multiplyFactor] = map(pt.x, 0, 1, minX, maxX);//pt.x * ORA_MULTPLY;
ordArray[(p*multiplyFactor) + 1] = map(pt.y, 0, 1, minY, maxY);//pt.y * ORA_MULTPLY;
if (_3d) {
ordArray[(p*multiplyFactor) + 2] = (bIsTag3D) ? map(pt.z, 0, 1, minZ, maxZ) : 0.0;
}
}
lineArray[s] = ordArray;
}
JGeometry gml = JGeometry.createLinearMultiLineString(lineArray,
(_3d) ? 3:2, 90112);

OracleConnection conn = connect();
try{
PreparedStatement ps = conn.prepareStatement("insert into t2 (geom) values (?)");
// convert JGeometry instance to DB STRUCT
STRUCT obj = JGeometry.store(gml, conn);
ps.setObject(1, obj);
ps.execute();
ps.close();
conn.close();
} catch (SQLException de) {
System.out.println(de);
}
}

Of course many improvements can be made but the point here is to create
a very simple proof-of-concept.
As a bonus I used this example to test GeoRaptor (https://sourceforge.net/projects/georaptor/support) (Spatial plugin fro SQL Developer) to create,
index and view Oracle Spatial tables.

PS. I am still learning to format the code sections.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: