When it comes to open source spatial databases, PostGIS gets the lion’s share of attention, but MySQL has come a long way in its support of spatial features. Still, if you want to load spatial data from JDBC, the MySQL Connector/J JDBC libraries don’t provide Java classes for converting the data to spatial types, so you need to find another solution.
One approach is Geotools, which provides a datastore for loading features from MySQL. However, if you want something more lightweight, it’s fairly straightforward to load up a feature using the JTS WKBReader. That’s what I do for my Open Jump DB Query plugin.
Assuming your table is called “province”, and has a geometry column called “geom”, here’s how to query the database and load the resulting geometry:
// Do JDBC work to load driver, create connection, statement, etc.// Then....String query = "SELECT geom FROM province";ResultSet resultSet = statement.executeQuery(query);while(resultSet.next()) { //MySQL geometries are returned in JDBC as binary streams. The //stream will be null if the record has no geometry. InputStream inputStream = resultSet.getBinaryStream("geom"); Geometry geometry = getGeometryFromInputStream(inputStream); // do something with geometry...} |
Here’s the code that does the actual InputStream to Geometry conversion:
private Geometry getGeometryFromInputStream(InputStream inputStream) throws Exception { Geometry dbGeometry = null; if (inputStream != null) { //convert the stream to a byte[] array //so it can be passed to the WKBReader byte[] buffer = new byte[255]; int bytesRead = 0; ByteArrayOutputStream baos = new ByteArrayOutputStream(); while ((bytesRead = inputStream.read(buffer)) != -1) { baos.write(buffer, 0, bytesRead); } byte[] geometryAsBytes = baos.toByteArray(); if (geometryAsBytes.length < 5) { throw new Exception("Invalid geometry inputStream - less than five bytes"); } //first four bytes of the geometry are the SRID, //followed by the actual WKB. Determine the SRID //here byte[] sridBytes = new byte[4]; System.arraycopy(geometryAsBytes, 0, sridBytes, 0, 4); boolean bigEndian = (geometryAsBytes[4] == 0x00); int srid = 0; if (bigEndian) { for (int i = 0; i < sridBytes.length; i++) { srid = (srid << 8) + (sridBytes[i] & 0xff); } } else { for (int i = 0; i < sridBytes.length; i++) { srid += (sridBytes[i] & 0xff) << (8 * i); } } //use the JTS WKBReader for WKB parsing WKBReader wkbReader = new WKBReader(); //copy the byte array, removing the first four //SRID bytes byte[] wkb = new byte[geometryAsBytes.length - 4]; System.arraycopy(geometryAsBytes, 4, wkb, 0, wkb.length); dbGeometry = wkbReader.read(wkb); dbGeometry.setSRID(srid); } return dbGeometry; } |
And that’s it. Pretty easy thanks to JTS!
