Mamad Purbo

Using PostGIS Spatial Object for MySQL Spatial Raw Binary Data

Although MySQL version 5.0.x and above has spatial data support, the latest JDBC connector (as of 2007/08) is lacking the spatial object model to simplify spatial data manipulation on Java clients. Fortunately the good folks at JPOX project provide a clue (through here) as to how to convert the binary as it is retrieved from MySQL to PostGIS object. Browsing through the JPOX-Spatial plugin source code proves that it is actually quite simple to use PostGIS library to process MySQL binary data.
It turns out that MySQL binary data is nothing but standard WKB preceded by 4 bytes of SRID convertible to integer by checking whether it is a big endian or little endian as specified in the 5th byte. Here's the neat code that does the trick compiled from various places in JPOX-Spatial source code:

import org.postgis.Geometry;
import org.postgis.binary.BinaryParser;

static final BinaryParser POSTGIS_WKB_PARSER = new BinaryParser();

public static Geometry mySqlBinaryToPostgisGeometry(byte [] mySqlBin)
throws Exception {

int SRID_LENGTH = 4;

// extracting WKB from MySQL spatial binary format
byte[] wkb = new byte[mySqlBin.length - SRID_LENGTH];
System.arraycopy(mySqlBin, SRID_LENGTH, wkb, 0, wkb.length);

// extracting SRID from MySQL spatial binary format
int srid = 0;

if (mySqlBin[SRID_LENGTH] == 0) {
srid = mySqlBin[0] << 24 |
(mySqlBin[1] & 0xff) << 16 |
(mySqlBin[2] & 0xff) << 8 |
(mySqlBin[3] & 0xff);
} else if (mySqlBin[SRID_LENGTH] == 1) {
srid = mySqlBin[3] << 24 |
(mySqlBin[2] & 0xff) << 16 |
(mySqlBin[1] & 0xff) << 8 |
(mySqlBin[0] & 0xff);
} else {
throw new Exception("Unknown endian:" + mySqlBin[SRID_LENGTH]);
}

// parsing WKB using PostGIS' binary parser to generate PostGIS' geometry
Geometry ret = POSTGIS_WKB_PARSER.parse(wkb);
ret.setSrid(srid);
return ret;
}