Monthly Archives: November 2011

Loading MySQL Spatial Data with JDBC and JTS WKBReader

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!