整理一下 shape file 轉 geo sqlite 的作法

1、Shapefile to geojson
ogr2ogr -f GEOJson a.json a.shp

2、去除 Geojson 的小數點 (如果是 97 去除小數點可以減少資料量，不過轉成 geo sqlite 沒影響大小)
Code : geojson_remove_decimal.php
  ini_set('memory_limit',-1);
  $message = "";
  if($argc!=2)
  {
    echo $message;
    exit();
  }

  $data = file_get_contents($argv[1]);
  $data = preg_replace("/\[\ (\d*)\.(\d*),\ (\d*)\.(\d*)\ \]/","[ \\1, \\3 ]",$data);
  file_put_contents($argv[1],$data);

使用方式: php geojson_remove_decimal.php a.json 

3、如果 a.json 是 big5 要修正 許、蓋、功 的問題，然後還要轉成 UTF-8
Code：geojson_big5toutf8.php
 ini_set('memory_limit',-1);
  mb_http_output("UTF-8");
  mb_internal_encoding('UTF-8');
  mb_regex_encoding("UTF-8");
  function big5toutf8($str)
  {
    return mb_convert_encoding($str, 'UTF-8','BIG5');
  }

  $message = "";
  if($argc!=2)
  {
    echo $message;
    exit();
  }

  $data = file_get_contents($argv[1]);

  $data = preg_replace('/([\x81-\xFE]\\\\)\\\\/','\1',$data);

  $data = big5toutf8($data);


  file_put_contents($argv[1],$data);
  echo "\nBIG5 -> UTF8 OK...\n";

使用方式：php geojson_big5toutf8.php a.json

4、將GeoJson 轉成 GeoSQLite
ogr2ogr -f SQLite a.db a.json -a_srs EPSG:3826 -s_srs EPSG:3826 -t_srs EPSG:3826  -dsco SPATIALITE=YES

5、將 GeoSQLite 加入 c_x、c_y、left、right、top、bottom 等中心點、四角坐標的 index
Code：sqlite_make_index.php
  ini_set('memory_limit',-1);
  function word_appear_times($find_word,$input)
  {
    //找一個字串在另一個字串出現的次數
    $found_times=0;
    $len = strlen($find_word);
    for($i=0,$max_i=strlen($input)-$len;$i<=$max_i;$i++)
    {
      if(substr($input,$i,$len)==$find_word)
      {
        $found_times++;
      }
    }
    return $found_times;
  }
  function PDO_execSQL_SAFE($pdo,$SQL,$data_arr)
  {    
    //找有幾個問號
    $questions = word_appear_times('?',$SQL);
    $max_i=count($data_arr);        
    if($questions!=$max_i)
    {
      echo "查詢條件無法匹配...:{$SQL} 
      <br>Questions:{$questions}
      <br>Arrays   :{$max_i}";
      exit();
    }    
    $q = $pdo->prepare($SQL);    
    for($i=0;$i<$max_i;$i++)
    {
      $q->bindParam(($i+1), $data_arr[$i]);
    }            
    $q->execute() or die("查詢失敗:...".print_r($pdo->errorInfo(),true));    
  } 
  function mainname($fname){
    $pathinfo=my_pathinfo($fname);   
    return $pathinfo['filename'];           
  }
  function my_pathinfo($path)
  {  
    $dirname = '';
    $basename = '';
    $extension = '';
    $filename = '';  
    $pos = strrpos($path, '/');   
    if($pos !== false) {
     $dirname = substr($path, 0, strrpos($path, '/'));
     $basename = substr($path, strrpos($path, '/') + 1);
    } else {
     $basename = $path;
    } 
    $ext = strrchr($path, '.'); 
    if($ext !== false) {
     $extension = substr($ext, 1);
    }
    $filename = $basename;
    $pos = strrpos($basename, '.');
    if($pos !== false) {
     $filename = substr($basename, 0, $pos); 
    }
    return array (
     'dirname' => $dirname,
     'basename' => $basename,
     'extension' => $extension,
     'filename' => $filename
    );
  }
  function PDO_selectSQL_SAFE($pdo,$SQL,$data_arr)
  {    
    //找有幾個問號
    $questions = word_appear_times('?',$SQL);
    $max_i=count($data_arr);        
    if($questions!=$max_i)
    {
      echo "查詢條件無法匹配...:{$SQL} 
      <br>Questions:{$questions}
      <br>Arrays   :{$max_i}";
      exit();
    }    
    $q = $pdo->prepare($SQL,
        array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false));    
    for($i=0;$i<$max_i;$i++)
    {
      $q->bindParam(($i+1), $data_arr[$i]);
    }            
    $q->execute() or die("查詢失敗:...".print_r($pdo->errorInfo(),true));
    $ra = $q->fetchAll(PDO::FETCH_ASSOC);                                       
    return $ra;
  }  
  $message = "";
  if($argc!=2)
  {
    echo $message;
    exit();
  }
  $PDO = new PDO("sqlite:{$argv[1]}");
  //$PDO->query("SELECT load_extension('/var/www/sqlite3_ext/mod_spatialite.so');"); //要重編 pdo 麻煩，算了
  
  $tableName = mainname($argv[1]);
  
  $SQL = "PRAGMA table_info(`{$tableName}`)";
  $ra = PDO_selectSQL_SAFE($PDO,$SQL,ARRAY());
  $exists_tables = ARRAY();
  foreach($ra as $k=>$v)
  {
    array_push($exists_tables,$v['name']);
  }
  $append_columns = ARRAY('c_x','c_y','left','right','top','bottom');
  
  foreach($append_columns as $k=>$v)
  {
    $SQL = "ALTER TABLE \"{$tableName}\" ADD COLUMN \"{$v}\" INTEGER NULL";
    echo "{$SQL}\n";
    if(!in_array($v,$exists_tables))
    {  
          
      PDO_execSQL_SAFE($PDO,$SQL,ARRAY());
    }
  }
  
  $db = new SQLite3($argv[1]);
  $db->loadExtension('mod_spatialite.so');
    
  $SQL = "UPDATE `{$tableName}` SET 
            `c_x` = X(PointOnSurface(`GEOMETRY`)),
            `c_y` = Y(PointOnSurface(`GEOMETRY`)),
            `left` = ST_MinX(`GEOMETRY`),
            `right` = ST_MaxX(`GEOMETRY`),
            `top` = ST_MinY(`GEOMETRY`),
            `bottom` = ST_MaxY(`GEOMETRY`)
         ";
  echo "{$SQL}\n";  
  $db->exec($SQL);
  
  $SQL = "UPDATE `{$tableName}` SET 
            `c_x` = X(Centroid(`GEOMETRY`)),
            `c_y` = Y(Centroid(`GEOMETRY`))
          WHERE
            `c_x` IS NULL
         ";
  echo "{$SQL}\n";  
  $db->exec($SQL);  
  
  //$SQL = "SELECT * FROM `{$tableName}` LIMIT 10";
  //$ra = PDO_selectSQL_SAFE($PDO,$SQL,ARRAY());
  //print_r($ra);
  
  $SQL = "CREATE INDEX \"main\".\"c_x\" ON \"{$tableName}\" (\"c_x\" ASC);";
  echo "{$SQL}\n";
  PDO_execSQL_SAFE($PDO,$SQL,ARRAY());
  
  $SQL = "CREATE INDEX \"main\".\"c_y\" ON \"{$tableName}\" (\"c_y\" ASC);";
  echo "{$SQL}\n";
  PDO_execSQL_SAFE($PDO,$SQL,ARRAY());
  
  $SQL = "CREATE INDEX \"main\".\"left\" ON \"{$tableName}\" (\"left\" ASC);";
  echo "{$SQL}\n";
  PDO_execSQL_SAFE($PDO,$SQL,ARRAY());
  
  $SQL = "CREATE INDEX \"main\".\"right\" ON \"{$tableName}\" (\"right\" ASC);";
  echo "{$SQL}\n";
  PDO_execSQL_SAFE($PDO,$SQL,ARRAY());
  
  $SQL = "CREATE INDEX \"main\".\"top\" ON \"{$tableName}\" (\"top\" ASC);";
  echo "{$SQL}\n";
  PDO_execSQL_SAFE($PDO,$SQL,ARRAY());
  
  $SQL = "CREATE INDEX \"main\".\"bottom\" ON \"{$tableName}\" (\"bottom\" ASC);";
  echo "{$SQL}\n";
  PDO_execSQL_SAFE($PDO,$SQL,ARRAY());
  
  echo "Done...\n";
  
使用方法：php sqlite_make_index.php a.db

MSSQL:
ALTER TABLE [dbo].[GEOMETRY_TOWN_2020]
ADD [c_x] float NULL ,
[c_y] float NULL ,
[minx] float NULL ,
[maxx] float NULL ,
[miny] float NULL ,
[maxy] float NULL 


update GEOMETRY_COUNTY_2020
set 
c_x = ogr_geometry.STPointOnSurface().STX,
c_y = ogr_geometry.STPointOnSurface().STY,
minx= ogr_geometry.STEnvelope().STPointN(1).STX,
maxx= ogr_geometry.STEnvelope().STPointN(3).STX,
miny= ogr_geometry.STEnvelope().STPointN(1).STY,
maxy= ogr_geometry.STEnvelope().STPointN(3).STY