ST_Geometry SQLiteとFileGeodatabase


やはり速度的にはまだまだなのか?
どう使うか検討は必要そう

下記記事の続き

SQLite ST_Geometry [ ArcGIS 10.2]

FGDB SQLite
252 433
20 214
16 209
12 203
22 202
21 229
23 203
16 220
17 205
27 184
15 156
10 145
9 162
10 203
15 187
13 163
13 207
13 197
17 214

一発目は全レコード廻しの結果、残りは空間条件付

コードは下記

SQLite側ロジック

public static void Test1(string filePath)
  {
    using (SQLiteConnection sqlCon = new System.Data.SQLite.SQLiteConnection("Data Source=" + filePath))
    using (SQLiteCommand cmd = sqlCon.CreateCommand())
    using (DataSet ds = new System.Data.DataSet())
    {
      sqlCon.Open();

      const string EXT_QUERY = "SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');";
      cmd.CommandText = EXT_QUERY;
      cmd.ExecuteNonQuery();

      cmd.CommandText = "SELECT * FROM JPN71";

      SQLiteDataAdapter dataAdapter = new System.Data.SQLite.SQLiteDataAdapter();
      dataAdapter.SelectCommand = cmd;

      dataAdapter.Fill(ds);
      DataTable dt = ds.Tables[0];

      DataColumn oidCol = dt.Columns["OBJECTID"];

      using (dataAdapter)
      using (dt)
      {
        bool isFirstTime = true;
        foreach (DataRow row in dt.Rows)
        {
          if (isFirstTime)
          {
            isFirstTime = false;
            Console.Write(Environment.NewLine + row[oidCol].ToString());
          }
          else
            Console.Write(",{0}", row[oidCol]);

        }//end row

        Console.Write(Environment.NewLine);
      }//using dt

    }//end connection
  }//end method

  public static void Test2(string filePath, double minX, double minY, double maxX, double maxY)
  {

    //sde.st_geomfromtext('polygon ((10.02 20.01, 11.92 35.64, 25.02 34.15,19.15 33.94, 10.02 20.01))', 4326)
    string rectGeom = string.Format("st_geomfromtext('polygon (({0} {1}, {2} {3}, {4} {5}, {6} {7}, {0} {1}))',  300001)",
      minX, minY,
      minX, maxY,
      maxX, maxY,
      maxX, minY);

    using (SQLiteConnection sqlCon = new System.Data.SQLite.SQLiteConnection("Data Source=" + filePath))
    using (SQLiteCommand cmd = sqlCon.CreateCommand())
    using (DataSet ds = new System.Data.DataSet())
    {
      sqlCon.Open();

      const string EXT_QUERY = "SELECT load_extension('stgeometry_sqlite.dll','SDE_SQL_funcs_init');";
      cmd.CommandText = EXT_QUERY;
      cmd.ExecuteNonQuery();

      cmd.CommandText = string.Format(@"
SELECT
 OBJECTID

FROM
 JPN71 TAB
WHERE
 st_intersects({0},  TAB.Shape) = 1", rectGeom);

      SQLiteDataAdapter dataAdapter = new System.Data.SQLite.SQLiteDataAdapter();
      dataAdapter.SelectCommand = cmd;

      dataAdapter.Fill(ds);
      DataTable dt = ds.Tables[0];

      DataColumn oidCol = dt.Columns["OBJECTID"];

      using (dataAdapter)
      using (dt)
      {
        bool isFirstTime = true;
        foreach (DataRow row in dt.Rows)
        {
          if (isFirstTime)
          {
            isFirstTime = false;
            Console.Write(Environment.NewLine + row[oidCol].ToString());
          }
          else
            Console.Write(",{0}", row[oidCol]);

        }//end row

        Console.Write(Environment.NewLine);
      }//using dt

    }//end connection

SQLite側呼び出し

      string test = "130.945242511000060,31.955611141000077|130.781871140000020,33.235353548000035|132.470041976000060,34.596781641000064|133.041841775000080,33.562096290000056|134.130984249000110,33.970524718000036|136.036983579000090,35.032438631000048|137.017211806000090,36.284952476000058|139.358868126000060,35.930981172000031|137.942982910000070,35.440867059000027|140.039582173000100,37.782523379000054|140.883667591000060,39.770208395000054|141.754981570000040,39.715751271000045|142.136181436000020,43.446064246000049|143.334238158000060,43.772806988000070|143.089181101000010,42.847035885000025|139.413325250000070,37.292409265000060|140.202953544000020,36.393866724000077|134.621098363000100,35.304724249000060|133.314127393000040,34.678467327000078";

      const string path = @"C:\Data\ST_GEOM.sqlite";

      DateTime now = DateTime.Now;
      SqliteTest.Test1(path);

      string msg = string.Format("条件なし:{0} ミリ秒", (DateTime.Now - now).TotalMilliseconds);
      Console.WriteLine(msg);

      string[] pts = test.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
      foreach (string ptXY in pts)
      {
        string[] pt = ptXY.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
        if (pt.Length != 2)
          continue;

        double x = double.Parse(pt[0]);
        double y = double.Parse(pt[1]);

        now = DateTime.Now;
        SqliteTest.Test2(path, x, y, x + 3, y + 3);

        msg = string.Format("{1},{2}:{0} ミリ秒", (DateTime.Now - now).TotalMilliseconds, x, y);
        Console.WriteLine(msg);
      }//end loop

FGDB側

  public static void Test1(string fgdbPath, string fcName)
  {
    using (ComReleaser com = new ComReleaser())
    {
      Type t = Type.GetTypeFromProgID("esriDataSourcesGDB.FileGDBWorkspaceFactory");
      IWorkspaceFactory wsf = (IWorkspaceFactory)Activator.CreateInstance(t);
      com.ManageLifetime(wsf);

      IFeatureWorkspace fw = (IFeatureWorkspace)wsf.OpenFromFile(fgdbPath, 0);

      IFeatureClass fc = fw.OpenFeatureClass(fcName);
      com.ManageLifetime(fc);

      IFeatureCursor cursor = fc.Search(null, true);
      com.ManageLifetime(cursor);

      IFeature feature = null;

      bool isFirstTime = true;
      while ((feature = cursor.NextFeature()) != null)
      {
        if (isFirstTime)
        {
          Console.Write(Environment.NewLine + feature.OID);
          isFirstTime = false;
        }
        else
          Console.Write(",{0}",feature.OID);
      }//end loop

      Console.Write(Environment.NewLine);
    }//end com
  }//end method

  public static void Test2(string fgdbPath, string fcName, IEnvelope env)
  {
    using (ComReleaser com = new ComReleaser())
    {
      Type t = Type.GetTypeFromProgID("esriDataSourcesGDB.FileGDBWorkspaceFactory");
      IWorkspaceFactory wsf = (IWorkspaceFactory)Activator.CreateInstance(t);
      com.ManageLifetime(wsf);

      IFeatureWorkspace fw = (IFeatureWorkspace)wsf.OpenFromFile(fgdbPath, 0);

      IFeatureClass fc = fw.OpenFeatureClass(fcName);
      com.ManageLifetime(fc);

      ISpatialFilter spFilter = new SpatialFilterClass();
      com.ManageLifetime(spFilter);

      spFilter.GeometryField = fc.ShapeFieldName;
      spFilter.Geometry = env;
      spFilter.SpatialRel = esriSpatialRelEnum.esriSpatialRelIntersects;

      spFilter.AddField(fc.OIDFieldName);
      spFilter.AddField(fc.ShapeFieldName);

      IFeatureCursor cursor = fc.Search(spFilter, true);
      com.ManageLifetime(cursor);

      IFeature feature = null;

      bool isFirstTime = true;
      while ((feature = cursor.NextFeature()) != null)
      {
        if (isFirstTime)
        {
          Console.Write(Environment.NewLine + feature.OID);
          isFirstTime = false;
        }
        else
          Console.Write(",{0}", feature.OID);
      }//end loop

      Console.Write(Environment.NewLine);
    }//end com
  }//end method

FGDB側呼び出し

      string test = "130.945242511000060,31.955611141000077|130.781871140000020,33.235353548000035|132.470041976000060,34.596781641000064|133.041841775000080,33.562096290000056|134.130984249000110,33.970524718000036|136.036983579000090,35.032438631000048|137.017211806000090,36.284952476000058|139.358868126000060,35.930981172000031|137.942982910000070,35.440867059000027|140.039582173000100,37.782523379000054|140.883667591000060,39.770208395000054|141.754981570000040,39.715751271000045|142.136181436000020,43.446064246000049|143.334238158000060,43.772806988000070|143.089181101000010,42.847035885000025|139.413325250000070,37.292409265000060|140.202953544000020,36.393866724000077|134.621098363000100,35.304724249000060|133.314127393000040,34.678467327000078";
      string fgdbPath = @"C:\japan_ver71\FGDB.gdb";
      string fcName = @"JPN71";

      DateTime now = DateTime.Now;
      FgdbTest.Test1(fgdbPath, fcName);

      string msg = string.Format("条件なし:{0} ミリ秒", (DateTime.Now - now).TotalMilliseconds);
      Console.WriteLine(msg);

      using (ComReleaser com = new ComReleaser())
      {
        string[] pts = test.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
        foreach (string ptXY in pts)
        {
          string[] pt = ptXY.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
          if (pt.Length != 2)
            continue;

          double x = double.Parse(pt[0]);
          double y = double.Parse(pt[1]);

          IEnvelope env = new EnvelopeClass();
          env.PutCoords(x, y, x + 3, y + 3);
          now = DateTime.Now;

          FgdbTest.Test2(fgdbPath, fcName, env);

          msg = string.Format("{1},{2}:{0} ミリ秒", (DateTime.Now - now).TotalMilliseconds, x,y);
          Console.WriteLine(msg);
        }//end loop
      }//end com
カテゴリー: 開発, 設計 タグ: , パーマリンク