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
             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);

         return dbGeometry;

And that’s it.  Pretty easy thanks to JTS!

8 thoughts on “Loading MySQL Spatial Data with JDBC and JTS WKBReader

  1. Dharma

    In the following line

    srid = (value << + (sridBytes[i] & 0xff);

    Can you please tell me what is “value”?..
    I guess there is a compile time error here. Please help me on this

  2. lreeder Post author

    WordPress was converting some of the java syntax to emoticons. The value should have been:

    srid = (value << 8) + (sridBytes[i] & 0xff); Fixed in the post above. Thanks for the heads-up! -Larry

  3. Dharma

    I’m sorry but still the value of the variable “value” is not initialized with anything .. By value do you mean the parameter “inputstream”?..

  4. lreeder Post author

    You’re right.

    “value” should be “srid”

    This came from a bad cut and paste. I fixed and compiled my example this time, so hopefully it finally works for you as well.

  5. Brian

    There’s no reason to make an entire copy of the byte[] to trim the first four bytes off it… WKBReader has an option that takes an input stream, which could be a ByteArrayInputStream wrapped around the byte[] then advanced by four.

  6. Pingback: Reading Geometry (spatial data) from MySql | siddharth's space

  7. fb

    Thanks a lot! I’ve been struggling with the srid (i. e. trying to parse
    the byte[] directly with WKBReader –> no reasonable output…).
    You really saved my day!


Leave a Reply

Your email address will not be published. Required fields are marked *