將空間數據從 Oracle 轉換為 MySQL

這是這個問題的後續：Problem getting zips in raius via MySQL

我在 Oracle 表中有一些空間內容。

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.........

如何將此幾何轉換為 MySQL 幾何類型？如果有人可以給我基礎知識，我可以編寫一個腳本來做到這一點。

我花了整整 8 小時的時間閱讀無聊的 Oracle 和 MySQL 文件來弄清楚，但這裡是……在 PHP 中……

（作為 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).")')";
}

引用自：https://dba.stackexchange.com/questions/172428