Oracle to MySQL – Converting Spatial Data

geometryMySQLoraclespatialtype conversion

This is a followup to this question: Problem getting zips in raius via MySQL

I have some spatial stuff in an Oracle table.

MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-71.88455,42.27993,-71.88467,42.27977,-71.88271,42.27823,-71.88269,42.2780.........

How can I convert this geometry to a MySQL geometry type? I can write a script to do it if someone can give me the basics.

Best Answer

I took an entire 8 hour shift of reading boring Oracle and MySQL docs to figure it out but here it is.. in PHP..

(as a Github Gist)

/**
 * Convert an Oracle SYS.SDO_GEOMETRY definition (perhaps extracted from an insert 
 * statement) to a MySQL Geometry column. This function only handles Polygons and 
 * Multipolygons.
 * 
 * Example:
 * $definition = "MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-75.01703,41.79308,-75.02978,41.7941,-75.02735,41.772,-75.02716,41.77193,-75.02697,41.77187,-75.01354,41.79051,-75.01337,41.79061,-75.0132,41.79072,-75.00949,41.79234,-75.00946,41.79254,-75.00943,41.79274,-75.00518,41.7943,-74.9995,41.79178,-74.99365,41.79788,-74.99383,41.79798,-74.99876,41.80318,-74.9988,41.80337,-74.99883,41.80349,-75.00425,41.80479,-75.00431,41.80459,-75.01239,41.79415,-75.01245,41.79412,-75.01688,41.79345,-75.01696,41.79327,-75.01703,41.79308))";
 * $sql = "INSERT INTO mytable (geom) VALUES (".OraclePolygonToMysql($definition).")";
 */
function OraclePolygonToMysql($definition){
    // Get the geometry type, our data should only be polygons and multipolygons
    $SDO_GTYPE = substr($definition, strpos($definition, "(") + 3, 2);
    switch($SDO_GTYPE){
        case "03": $type = "POLYGON"; break;
        case "07": $type = "MULTIPOLYGON"; break;
        default: die("hol up i thought we only had polygons in this bitch");
    }
    // MDSYS.SDO_ELEM_INFO_ARRAY contains info about how to split up the coordinates..
    // So the info array is some dumb ass 1-indexed triplet model where we only need every third index..
    // https://docs.oracle.com/cd/B12037_01/appdev.101/b10826/sdo_objrelschema.htm#i1006226
    $start = strpos($definition, "MDSYS.SDO_ELEM_INFO_ARRAY")+strlen("MDSYS.SDO_ELEM_INFO_ARRAY(");
    $infoArray = explode(",", substr($definition, $start, strpos($definition, ")", $start) - $start));
    // MDSYS.SDO_ORDINATE_ARRAY contains the actual ordinates that need to be grouped properly for 
    // MySQL to understand them
    $start = strpos($definition, "MDSYS.SDO_ORDINATE_ARRAY") + strlen("MDSYS.SDO_ORDINATE_ARRAY");
    $ordsArray = explode(",",trim(substr($definition, $start), " ()")); 
    // Get the starting index fro each group
    $groupStarts = array();
    while(count($infoArray) > 0){
        $startIndex = intval(array_shift($infoArray)) - 1; // compensate for the dumb ass 1-index
        array_shift($infoArray); array_shift($infoArray); // we don't need these... i don't think
        $groupStarts[] = $startIndex;
    }
    // Get the ending index for each group
    $groups = array();
    for($i=0; $i<count($groupStarts); $i++){
        $start = $groupStarts[$i];
        $end = isset($groupStarts[$i+1]) ? $groupStarts[$i+1] - 1 : count($ordsArray)-1;
        $groups[] = array("first"=>$start, "len"=>$end-$start+1);
    }
    // Group the indexes into their own arrays
    for($i=0; $i<count($groups); $i++) 
        $groups[$i]['group'] = array_slice($ordsArray, $groups[$i]['first'], $groups[$i]['len']);
    // Create the string from the groups array
    $chunks = array();
    foreach($groups as $group){
        $chunked = array_chunk($group['group'], 2);
        foreach($chunked as $k=>$chnk) $chunked[$k] = implode(" ", $chnk);
        $c = "(".implode(",", $chunked).")";
        $chunks[] = $type === "MULTIPOLYGON" ? "($c)" : $c;
    }
    return "GeomFromText('$type(".implode(",",$chunks).")')";
}