SQLServerで地図データ（緯度経度）を検索

先日FaceBookでDB上の地図座標を検索したいという話題がでていたので、SQLServerでやり方を調べてみました。
実現したいのは「現在地の座標から500m（任意の範囲）内の登録地点を検索する」となります。

SQLServer2008から空間データ用にgeometry型とgeography型がサポートされるようになりました。
geometry型は平面上の座標データ（一般的なxyグラフ）を扱います。
geography型は楕円体上の座標データを扱うので緯度経度データはこちらを利用することになると思います。

まずはサンプルテーブルを作成します。

CREATE TABLE T_TEST
(
 id int IDENTITY(1,1) NOT NULL,
 name nvarchar(50) ,
 address nvarchar(50) ,
 geo geography
)

次にサンプルデータを挿入します。今回は弊社近辺の銀行の所在地にしてみました。

INSERT INTO T_TEST(name,address,geo)
     VALUES('りそな銀行明石支店',
'明石市本町1丁目2-26',
geography::STGeomFromText('POINT(134.991151 34.647505)',4326))
INSERT INTO T_TEST(name,address,geo)
     VALUES('みずほ銀行明石支店',
'明石市大明石町1丁目5-1',
geography::STGeomFromText('POINT(134.993259 34.64798)',4326))
INSERT INTO T_TEST(name,address,geo)
     VALUES('三井住友銀行明石支店',
'明石市大明石町1丁目5-4',
geography::STGeomFromText('POINT(134.993299 34.647755)',4326))
INSERT INTO T_TEST(name,address,geo)
     VALUES('東京三菱UFJ銀行明石支店',
'明石市本町1丁目1-34',
geography::STGeomFromText('POINT(134.993251 34.647223)',4326))
INSERT INTO T_TEST(name,address,geo)
     VALUES('百十四銀行明石支店',
'明石市本町2丁目1-26',
geography::STGeomFromText('POINT(134.989622 34.647607)',4326))

geography型へのデータの挿入は「geography::STGeomFromText('POINT([経度] [緯度])',4326)」となります。（「経度」と「緯度」の間は半角スペースです。間違えるとエラーになります。）

------------------------------------------
追記
------------------------------------------
geography型へのデータの挿入は「geography::POINT([緯度],[経度],SRID)」でも可能です。
こちらのほうが直接的でわかりやすいかと思います。
------------------------------------------
追記終わり
------------------------------------------

SQLServerでのgeography型データは.NET 共通言語ランタイム (CLR) のデータ型として実装されているのでgeography::STGeomFromTextメソッドで座標を変換しています。「4326」はSRID (spatial reference ID) になります。（詳しく調べていないのですが固定値として指定しています。）


これで検索する準備ができました。

登録されているデータで任意の位置から特定範囲（この場合は円内）に含まれる地点を検索するには任意の位置座標とデータの位置座標の距離を求めることによって検索することができます。

距離（単位はメートルです）はgeography型のSTDistanceメソッドで求めることができます。

次のようにクエリを発行すると任意の座標から各データの位置データまでの距離が計算されます。

--任意の位置座標を代入する変数
declare @g geography

--任意の位置座標データとしてJR明石駅の位置座標を使用しています。
set @g=geography::STGeomFromText('POINT(134.993164 34.649096)',4326)

select name,address,@g.STDistance(geo) as distance from T_TEST

上記のクエリに以下の条件を加えると任意の範囲に含まれるデータを検索できます。

--200m以内にある地点を検索
where @g.STDistance(geo)<200

以上の内容をストアドもしくはユーザー定義関数としてSQLServerに登録して呼び出せるようにしておけば任意の位置データと検索したい範囲の距離を与えて検索することができるようになります。

Node.jsからローカルのSQLServerに接続

最近はNode.jsとDB接続に関して色々調べています。
Node.jsと組み合わせるDBはNosql系のDBが多いですが、今回はSQLServerでやってみました。

MS純正のNode用ドライバは以前から提供されているのですが、日本語での情報がほとんどないため結構苦労しました。

今回はHyper-V上のWindows7にSQLServer2012ExpressとNode.jsをインストールしてみました。

環境：
・Windows7 Pro SP1 32bit
・SQLServer 2012 Express x86版
・Node.js v0.8.25 32bit

その他
・Express 3.3.0 + ejs

また node-sqlserverのコンパイルに必要なのが
・Visual C++ 2010 Express
・Python 2.7

となります。


導入手順

１．Node.jsのダウンロードとインストールをします。今回は0.8系の最新版を選択しました。
　　（最新安定版の10.0系ではnode-sqlserverがコンパイルできませんでした。）

２．Visual C++ 2010 ExpressとSQLServer2012Expressをダウンロードして、インストールします。
　　SQLServerはネットワーク接続できるようにファイアウォール等の設定をしています。

３．Python2.7をダウンロードしてインストールします。

４．SQLServerに「test」データベースを作成し、「T_M商品」テーブルを作成します。
　　内容は下の図を参照してください。データも適当に入れておきます。
　　

５．C:\に「node」フォルダを作成し、この中にアプリケーションを配置するようにします。

６．Node.jsコマンドプロンプトを起動してからC:\nodeフォルダに移動します。

７．コマンドプロンプトでc:\node> npm install -g express と入力してExpressをインストールします。

８．c:\node> express -e test　と入力してtestフォルダにアプリケーションの雛形を作成します。
　　testフォルダには以下のように雛形が作成されます。
　　

９．package.jsonファイルをエディタで以下のように編集します。

--------------------------------------------------------　
{
  "name": "application-name",
  "version": "0.0.1",
  "private": true,
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "express": "3.3.0",
    "ejs": "*",
    "node-sqlserver": "*" <------------赤字部分を追加
  }
}
--------------------------------------------------------
このときファイルの文字コードに気をつけましょう。UTF-8で保存しておかないと正常に動作しない可能性があります。以下すべてのファイルの文字コードがUTF-8になるようにしてください。

１０．コマンドプロンプト上でtestフォルダに移動してから　c:\node\test> npm install　と入力する
　　　と先ほどのpackage.jsonの内容にしたがってインストールが行われます。このとき途中で
　　　黄色のワーニングメッセージが出るかもしれませんが、コンパイル環境が問題なければ
　　　そのまま問題なく終了します。

１１．問題なくインストールができるとtestフォルダにはnode_modulesフォルダが追加されています。
　　　

１２．node_modules>node-sqlserver>build>Releaseフォルダ内のsqlserver.nodeファイルを

　　　node_modules>node-sqlserver>libフォルダ内にコピーします。

１３．testフォルダにあるapp.jsファイルを以下のように編集します。

途中から

----------------------------------

app.get('/', routes.index);

app.get('/users', user.list);

app.post('/',routes.index);  <-----赤字の部分を追加

----------------------------------

以下は変更なし

１４．test>routesフォルダにあるindex.jsファイルを以下のように編集します。

-------------------------------------------------------------------------------

exports.index = function(req, res){

if (req.body.code) {

var sql=require('node-sqlserver');

var cn_str="Driver={SQL Server Native Client 11.0};Server=(local);Database=test;Uid=ユーザー名;Pwd=パスワード;";

sql.query(cn_str,"select * from T_M商品 where 商品コード='" + req.body.code + "'",function(err,rst) {

if (err) {

res.render('index', { title: 'ERROR' });

console.log(err);

}

res.render('index2',{ title: 'Result',

    rst: rst

});

});

}

else {

res.render('index', { title: '検索' });

}

};

-------------------------------------------------------------------------------

１５．test>viewsフォルダにあるindex.ejsファイルを以下のように編集します。

-------------------------------------------------------------------------------

<!DOCTYPE html>

<html>

  <head>

    <meta charset="UTF-8">

    <title><%= title %></title>

    <link rel='stylesheet' href='/stylesheets/style.css' />

  </head>

  <body>

    <h1>検索</h1>

      

      <form method="POST" action="/">

        コード検索: <input type="text" name="code" size="50">

        <input type="submit" value="検索実行">

      </form>

  </body>

</html>

-------------------------------------------------------------------------------

１６．test>viewsフォルダにindex2.ejsファイルを以下の内容で追加します。

-------------------------------------------------------------------------------
<!DOCTYPE html>
<html>
  <head>
    <title><%= title %></title>
    <link rel='stylesheet' href='/stylesheets/style.css' />
  </head>
  <body>
 
    <h1>Data</h1>
 
<form method="POST" action="/">
        コード検索: <input type="text" name="code" size="50">
        <input type="submit" value="検索実行">
</form>

<P>

<table border="1">
 <tr>
<th>商品コード</th>
<th>商品名</th>
<th>標準価格</th>
 </tr>

 <% for(var i=0;i<rst.length;i++){ %>
<tr>
 <td> <%= rst[i]['商品コード'] %> </td>
 <td> <%= rst[i]['商品名'] %> </td>
 <td> <%= rst[i]['価格'] %> </td>
</tr>
 <% } %>

</table>
  </body>
</html>
-------------------------------------------------------------------------------


１７．アプリケーションを動かしてみます。コマンドプロンプトでc:\node\testフォルダに移動して
　　　c:\node\test> node app.jsと入力します。
　　　問題がなければ「Express server listeng on port 3000」と表示されます。


１８．ブラウザで「http://localhost:3000/」にアクセスすると以下のように表示されます。

１９．試しにコードを入力して検索を実行すると以下のような結果になりました。

まだSelect文のテストしかしていませんが、このようにNode.js＋SQLServerの組み合わせでアプリケーションが作成できました。

Node.jsはまだまだ勉強中なのでたいしたサンプルではないですが、少しでも参考になればと思います。

LocalDBをVBAから使ってみる

久しぶりの投稿です。

しばらくKinectさわっていません。
なぜかHoGの投稿に定期的にアクセスがあります。どうしてなのかな？

さて、今回はSQLServer2012から新たに追加になったLocalDBについてです。

SQL Server2012Expressもほとんど同じですが、スタンドアローン環境で使うならLocalDBの方が手軽に使えそうです。

ただ、色々なサイトで解説はあるんですが、VBAでの解説があまりないようです。

試しにExcel2013でADO+NativeClientで接続してみようとすると、最初はうまく接続できませんでした。


最初に書いたコードです。

-----------------------------------------------------------------------------------------------------------
Sub DB接続()

Dim cn As New ADODB.Connection

cn.ConnectionString = "provider=sqlncli11;server=(localdb)\v11.0;database=test;integrated security=true;DataTypeCompatibility=80;MARS Connection=True"

cn.Open

Dim rs As New ADODB.Recordset

rs.Open "select * from T_商品", cn, adOpenStatic, adLockReadOnly

If rs.BOF And rs.EOF Then

Else
Range("A1").Select
ActiveCell.CopyFromRecordset rs
End If

rs.Close
Set rs = Nothing

End Sub
-----------------------------------------------------------------------------------------------------------
（あらかじめ既定のインスタンスにtestデータベースを作成して、T_商品テーブルを追加しています。）

この場合コネクションを開いた時点でエラーが発生しました。

色々試した結果は

「integrated security=true」を「integrated security=SSPI」に変更すれば正常に動作しました。

通常TrueでもADO.NETなどは動くようなのですが・・・・

まぁ、これでEXCELやACCESSでもLocalDBが利用できそうです。

あとLocalDBはSQLOLEDBプロバイダは使えないみたいです。