トップ＞ C# ＞ Npgsql(C#でPostgreSQLを使う)

Npgsql(C#でPostgreSQLを使う)

Npgsql入門

NpgsqlはC#でPostgreSQLを使用する.Netオープンソースライブラリである。少し、触れてみた限りでは使い方は予想以上に簡単であった。

今回は、ダウンロードした Npgsql.dll を実行ファイルと同じディレクトリにコピーした。

今回のプログラム開発では、カレントディレクトリを c:\gis として、 c:\gis\src にアプリケーションプログラムのソースファイル、c:\gis\bin に実行ファイルを置く。

まず、PostgreSQLデータベースとの接続テストのみを行う。当然、**** のところには、実際のパスワードを記述する。

ここでは、using System.Data は不要であるが、実用レベルのプログラムでは必要となる。

// sql01.cs
// c:\gis>csc /out:bin\sql01.exe /r:bin\Npgsql.dll src\sql01.cs

using System;
//using System.Data;
using Npgsql;

public class NpgsqlTest {
  public static void Main(String[] args) {
    NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=****;Database=osm20150611;");
    conn.Open();
    NpgsqlCommand command = new NpgsqlCommand("select version()", conn);
    String serverversion;
    try {
      serverversion = (String)command.ExecuteScalar();
      Console.WriteLine("PostgreSQL server version: {0}", serverversion);
    } finally {
      conn.Close();
    }
  }
}

コンパイル・実行結果を下に示す。

c:\gis>csc /out:bin\sql01.exe /r:bin\Npgsql.dll src\sql01.cs
Microsoft (R) Visual C# Compiler version 12.0.31101.0
for C# 5
Copyright (C) Microsoft Corporation. All rights reserved.


c:\gis>sql01
PostgreSQL server version: PostgreSQL 9.4.1, compiled by Visual C++ build 1800,
64-bit

もう一つ、ネット検索で見つかったプログラムを試した。（当然、Database、User ID、 Password、およびテーブル名は変更した）

// sql02.cs
// csc /out:bin\sql02.exe /r:bin\Npgsql.dll src\sql02.cs

using System;
//using System.Data;
using Npgsql;

public class NpgsqlTest {
  public static void Main(String[] args) {
    NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=****;Database=osm20150611;");
    conn.Open();
    NpgsqlCommand command = new NpgsqlCommand("SELECT COUNT(*) FROM planet_osm_point", conn);

    // Execute the query and obtain the value of the first column of the first row
    Int64 count = (Int64)command.ExecuteScalar();

    Console.Write("{0}\n", count);
    conn.Close();
  }
}

実行結果を下に示す。データベース osm20150611 のテーブル planet_osm_point のレコード件数は 824893 という結果が得られた。

c:\gis>sql02
824893

テーブルの列数を求める

テーブルplanet_osm_pointの列の数を求めるには、次のクエリを用いる。

SELECT count(*) FROM information_schema.columns WHERE table_name = 'planet_osm_point'

テーブルに列を追加する

テーブルplanet_osm_pointに新しい列 shortname (text型)を追加するには、次のクエリを実行する。

　var cmd = "ALTER TABLE planet_osm_point ADD shortname text;";
  var cmdAdd = new NpgsqlCommand(strAdd, conn);
  cmdAdd.ExecuteNonQuery();

位置は指定できない。末尾に追加されていることを確認した。

データベースを作成する

通常の用途では、データベースの作成、削除は頻繁に実行するわけではない。このため、自作地図システムでは、データベースの作成、削除は pgAdmin III で行ってきた。

今回、Just In Time で狭い範囲に対して OSMデータをダウンロードして、それを PostgreSQL+PostGIS データベースにインポートして、Mapnikを使ってタイル画像を作成することを考える。データベースは予め、手作業で作っておいてもよいが、ワンクリックで全ての操作を行うために、データベースの作成や削除も Npgsql を使ってプログラムで行うことを試みたい。

PostgreSQLをインストールすると、データベース pgsql が必ず作られている。データベースを作成するときには、このデータベース pgsql に接続する。

下にテストプログラムを示す。データベースの作成(CREATE DATABASE)およびコメントの追加(COMMENT ON DATABASE データベース名 IS)、データベースの削除(DROP DATABASE)を確認した。

後半では、作成したデータベース test01 に接続し、SQL文「CREATE EXTENSION postgis;」を実行した。これはタイル画像作成では PostGIS拡張を使うため、その準備命令である。

このようにして、データベースを作成してから osm2pgsql.exe を実行して OSMデータ(XML)をインポートする。

// npgsql01.cs
// c:\gis>csc /r:bin\Npgsql.dll sys\src\misc\npgsql01.cs

using System;
using Npgsql;

public class NpgsqlTest {

  public static void Main(String[] args) {
    NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=****;Database=postgres;");
    conn.Open();
    try {
      NpgsqlCommand command = new NpgsqlCommand("DROP DATABASE IF EXISTS test01", conn);
      command.ExecuteNonQuery();
      command = new NpgsqlCommand("CREATE DATABASE test01", conn);
      command.ExecuteNonQuery();
      string comment = "'Test Database " + DateTime.Now.ToString("yyyy.MM.dd HH:mm:ss") + "'";
      command = new NpgsqlCommand("COMMENT ON DATABASE test01 IS " + comment, conn);
      command.ExecuteNonQuery();
    } finally {
      conn.Close();
    }

    conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=****;Database=test01;");
    conn.Open();
    try {
      NpgsqlCommand command = new NpgsqlCommand("CREATE EXTENSION postgis;", conn);
      command.ExecuteNonQuery();
    } finally {
      conn.Close();
    }
  }
}

テーブルからカラムの値を読み込む

select文の事例(抜粋)を下に示す。このプログラムはタブレット(Windows)で動かすときとノートPCで動かすときがある。

ノートPC(Windows)は仮想マシンでubuntuを動かしている(IPドレス192.168.56.2)。 PostgreSQLはこのゲストOS上で動いている。

この sqlコマンドはホストOS(Windows)からゲストOS(ubuntu)に送り込まれ、どこで実行される。

この sql文には含まれないが、もし、ファイルが含まれる場合には、 Windows上のファイルではなく、ubuntu上のファイルでなければならない。

        string sql = "select ST_X(ST_Centroid(ST_Transform(way,4326))) as lon, "
                   +        "ST_Y(ST_Centroid(ST_Transform(way,4326))) as lat, name "
                   + "from my_osmdata where name like '%" + word + "%' limit 1000; ";
        NpgsqlConnection conn = new NpgsqlConnection("Server="
                + (GIS.PC == "tb" ? "127.0.0.1" : "192.168.56.2") 
                + ";Port=5432;User Id=postgres;Password=****;Database=osm;");
        conn.Open();
        var list = new List<Node>();
        try {
            NpgsqlCommand cmd = new NpgsqlCommand(sql, conn);
            var dr = cmd.ExecuteReader();
            while (dr.Read()) {
                Node node = new Node();
                node.lon = dr["lon"] != DBNull.Value ? (double)dr["lon"] : 0.0;
                node.lat = dr["lat"] != DBNull.Value ? (double)dr["lat"] : 0.0;
                node.name = (string)dr["name"];
                list.Add(node);
            }
        } catch (Exception ex) {
            Console.WriteLine(ex.ToString());
        }

上の例で、dr["lon"] を文字列として取り出したいときは dr["lon"].ToString() とする。もちろん ((double)dr["lon"]).ToString() でも誤りではない。

データベースを更新する

実際のメソッド全体を載せたので行数が多くなったが、骨子はブルーで示した。ここではトランザクションを使っている。 command.ExecuteNonQuery()で update文が実行されるが、結果がデータベースに反映されるのは、transaction.Commit() を実行したときである。 command.ExecuteNonQuery()でエラーが起きたときは、その更新は transaction.Rollback() で取り消される。

下のプログラムでは日本地図領域を小さい区画に分割して、繰り返し処理を行っている。 transaction.Commit()は区画ごとに行っているため、エラーが起きたときはその区画の処理が取り消されるだけで、すでに Commitした区画の更新処理は取り消されない。

もし、エラーの頻発が予想される場合には、どこまで実行したかを記録して置き、そこから再開するように配慮すべきであるが、このプログラムでのエラーは稀なため、その配慮はしていない。ただし、複数回実行しても問題はないようになっている。

一度、updateクエリでタイムアウトエラーが起きた。小区画でも、東京、大阪などでは実行時間が大きくなる。そのため、CommandTimeout = 0 を追加した。これは、実行時間無制限を意味し、タイムアウトエラーは起きなくなる。

一般に、巨大な update処理全体を一つのトランザクションにするのは避けるべきである。そうしないと、一時的に使用されるディスクスペースが巨大となり、実行時間も長くなる。

ただし、このプログラムの場合は、トランザクション・サイズの都合で小区画に分割したのではない。このupdateクエリの実行時間はレコード数Nの二乗のオーダーになる。このupdate時間短縮のために分割している。

クエリ実行時ではなく、コネクション・タイムアウトも起こりうる。何年か前に経験した。対策方法は、再度経験したときに、追記する。

    public static void DetectDuplicationThread(Object obj) {
        Stopwatch sw = Stopwatch.StartNew();
        int zoom = (int)obj;
        int count = 0;
        int NN = (int)Math.Pow(2, zoom);
        for (int x = 0; x < NN; x++) {
            bool fPrn = true;  
            for (int y = 0; y < NN; y++) {
                if (!Zone.IsJapan(zoom,x,y)) {
                    continue;   // 日本地図領域外
                }
                if (fPrn) {
                    Monitor.WriteLine("DetectDup x=" + x);
                    fPrn = false;
                }
                double lon1 = Zone.X2Lon(x, zoom);
                double lat1 = Zone.Y2Lat(y+1, zoom);
                double lon2 = Zone.X2Lon(x+1, zoom);
                double lat2 = Zone.Y2Lat(y, zoom);
                var bbox = "Box2D(ST_Transform(ST_GeomFromText('LINESTRING("
                         + string.Format("{0} {1}, {2} {3}", lon1, lat1, lon2, lat2)
                         + ")', 4326), 900913)) ";
                var sql = "update my_osmdata small set tags = "
                        + "(case when small.tags is null then '' else small.tags end) || hstore('dup','yes') "
                        + "from my_osmdata big "
                        + "where (small.way && " + bbox + ") and (big.way && " + bbox + ") "
                        + "and small.tags->'dup' is null and big.way_area > small.way_area "
                        + "and small.key = big.key and small.val = big.val "
                        + "and big.name is not null and my_match(big.name, small.name, "
                        + "case when small.key='amenity' then small.val else null end) "
                        + "and ST_Within(small.way, big.way);";
                var ConnectionString = "Server=192.168.56.2;Port=5432;"
                           + "User Id=postgres;Password=****;Database=osm;";
                var connString = new NpgsqlConnectionStringBuilder(ConnectionString) { CommandTimeout = 0 };
                using (var conn = new NpgsqlConnection(connString)) {
                    conn.Open();
                    using (var transaction = conn.BeginTransaction())  {
                        var command = new NpgsqlCommand(sql, conn);
                        try {
                            command.ExecuteNonQuery();
                            transaction.Commit();
                        } catch (NpgsqlException) {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
                count++;
            }
        }
        sw.Stop();
        Monitor.WriteLine("重複検出 回数: " + count + ", 時間: " + sw.Elapsed.ToString());
    }

複数行を一括でインサートする

マイOSM地図では下に示すカラムからなるテーブル my_osmdata に現在約200万レコードを登録している。

CREATE TABLE my_osmdata (
  osm_id text, way_area real, key text, val text, name text,
  tags hstore, z_order int, way geometry(Geometry,3857)
)

１レコードずつインサートする場合、パラメータを使うのが一番楽である。

  var cmd = new NpgsqlCommand("insert into my_osmdata(id, name) values (:osm_id, :name)", con);
  cmd.Parameters.Add(new NpgsqlParameter("osm_id", DbType.String) { Value = "abc" });
  cmd.Parameters.Add(new NpgsqlParameter("name", DbType.String) { Value = "Test" });
  cmd.ExecuteNonQuery();

しかし、１レコードずつの登録では時間がかかりすぎる。一度に複数レコードを登録するには、次のようにする。この場合もパラメータを使う方法があるかも知れないが、ちょっと思いつかなかった。

INSERT INTO my_osmdata (osm_id, name) VALUES
('n123', 'abc'),
('w456', 'def'),
     :
('r555', 'xyz');

そのため、今回はパラメータを使わずに次のようにした。 1000レコード毎に一括登録するようにした。当然、最後は端数レコードの登録となる。

geometry型もhstore型も文字列形式であるが、それぞれ、 '...'::geometry、'...'::hstore のようにデータ型を明記する必要がある。

文字列の中に '(一重引用符)を含む場合には、エスケープ文字 ' を付ける必要がある。更に頭に e を付けて、 e'...' の形にする必要がある。

以上のように、パラメータ型とは異なる配慮が必要となる。

 string insert = "insert into my_osmdata(osm_id, way, way_area, key, val, name, tags, z_order) values ";
 StringBuilder sb = new StringBuilder();
 sb.Append(insert);
 int cntRecord = 0;
 
 while (...) {
   // レコード算出（省略）
   if (cntRecord > 0) sb.Append(", ");
   sb.Append("(" + osm_id + ", ");
   sb.Append("'" + way + "'::geometry, ");
   sb.Append(way_area + ", ");
   sb.Append("'" + key + "', ");
   sb.Append("e'" + val.Replace("'","''") + "', ");
   if (name == null) sb.Append("null, ");
   else sb.Append("e'" + name.Replace("'","''") + "', ");
   sb.Append("'" + tags + "'::hstore, ");
   if (z_order == DBNull.Value) sb.Append("null) ");
   else sb.Append(z_order + ") ");

   if (++cntRecord%1000 == 0) {
      sb.Append(";");
      var sql = sb.ToString();
      var cmdIns = new NpgsqlCommand(sql, conn_insert);
      cmdIns.ExecuteNonQuery();
      sb = new StringBuilder();
      sb.Append(insert);
      cntRecord = 0;
   } 
 }
 if (cntRecord > 0) {
    sb.Append(";");
    var cmdIns = new NpgsqlCommand(sb.ToString(), conn_insert);
    cmdIns.ExecuteNonQuery();
 }

参考文献

[1] COMMENT
[2] 【PostgreSQL】Npgsqlでの大量レコード更新の高速化
[3] PostGIS/NetTopologySuite型のプラグイン
[4] 型のマッピング Npgsql
[5] C#（Npgsql）でPostgresqlに大量データを追加
[6] PostgreSQL のテーブルにデータを登録 (INSERT) する複数の方法