0

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.

2 Answers 2

0

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

Well-Known Binary (WKB)

Rather than trying to hack your own slower method of dumping to Well-known text (WKT), use the much faster and native WKB functionality.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.