Xrhrft

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.

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).")')";

}

Rather than trying to hack your own slower method of dumping to WKT, use the much faster WKB dump.

• To Dump, use Oracle's method Get_WKB.


• To Load, use MySQL method ST_GeomFromWKB.

Though if you want dump to WKT, rather than writing that Oracle also provides Get_WKT