Loading [MathJax]/extensions/Safe.js
MENU

SpatiaLiteでSQL


GIS教材の空間データベースではPostGISを使っていますが、同じことをSpatiaLiteでやってみます。

SpatiaLiteはインストール不要のデータベースエンジンです。

サンプルデータとしてdatabaseをダウンロードして使います。

 


空間データベースの作成
よく使うSQL文
属性検索
属性情報の表示
属性情報の計算
属性値に基づく抽出
属性情報に基づく集計
空間検索
指定した領域に基づく検索
データの領域による検索
空間結合
QGISでのデータベース接続

 

 


空間データベースの作成

空間データのインポート

Windowsの場合、databaseはパスに日本語名を含まない場所に置いておくといいです。

SpatiaLiteを起動して、データベースを作成しておいて、stationとtokyoの2つのshapeデータを読み込みます。

CharsetのEncodingはUTF-8です

こんな感じです。

Geometry型の列について、画像としてその内容を確認・保存

Map Preview

 

 

TOP

 


よく使うSQL文

SELECT文・・・・・DBのテーブルからデータを検索し、取得する文
SELECT ・・・・・ 列名
FROM ・・・・・テーブル名
WHERE ・・・・条件の指定(表示するデータの選択)列=値など

列 = 値 ・・・  列が値と一致するものを抽出
列 >= 値 ・・・ 列が値(数値)より大きいものを抽出
列 <= 値 ・・・ 列が値(数値)より小さいものを抽出

※ 他に良く使うもの AND a=b and c>=d aがbと一致していてcがd(数値)以上のもの
OR a=b and c>=d aがbと一致しているかcがd(数値)以上のもの
LIKE a= LIKE ‘新%’ a列に含まれる新○○を抽出する あいまい検索
( チェック   GROUP BY ・・・・ グループ化する集約関数)

TOP

 

属性検索

入力したSQL文を実行する際は、緑枠の実行ボタンをクリックします。

TOP

 

属性情報の表示

東京23区の行政区域の情報を表示する。
SELECT name FROM tokyo;
を実行。

TOP

 

属性情報の計算

属性情報には、区ごとの人口数が入力されているため、その合計を求める。
SELECT sum(population) FROM tokyo;
を実行すると、23区内の人口が計算できる。

TOP

 

属性値に基づく抽出

属性値に基づく抽出として東日本旅客鉄道に所属する駅名を抽出する。
SELECT name FROM station WHERE operator = ‘東日本旅客鉄道’;
とし、実行する。

TOP

 

属性情報に基づく集計

鉄道会社ごとに駅の数を計算する。
SELECT operator,count(name) FROM station GROUP BY operator;
を実行する。

SELECT operator,count(name) AS “n” FROM station GROUP BY operator;

を実行すると、鉄道会社ごとのデータをテーブルnとして表示できる。

 

TOP

 

空間検索

勝どき駅のジオメトリを表示する

ジオメトリはPostGISではgeomだが、SpatiaLiteではGeometry

SELECT name,Geometry FROM station WHERE name = ‘勝どき’;
を実行する。

TOP

 

指定した領域に基づく検索

勝どき駅から半径1000m以内の駅を表示する。

PostGISでは ST_DWithin という関数を使うが、SpatiaLiteでは PtDistWithin という関数になる。

PtDistWithin(ジオメトリ1,ジオメトリ2,距離)

上で取得した勝どき駅のジオメトリは座標値なのでMakePoint()を使用。4326はWGS84でメートル単位で取得する場合に使用。

SELECT name,Geometry
FROM station
WHERE PtDistWithin(station.Geometry,MakePoint(-5082.074912,-37837.009589,4326),1000);

を実行。

TOP

 

データの領域による検索

tokyoテーブルのGeometry(ジオメトリー)に含まれる勝どき駅を抽出。

SELECT
station.name,tokyo.name
FROM tokyo
JOIN station
ON ST_Contains (tokyo.Geometry, station.Geometry)
WHERE station.name = ‘勝どき’;

を実行。

 

TOP

 

空間結合

空間結合として、東京23区の区ごとの駅数を計算してみます。

元本では空間インデックスを使ってやってますが、どうもPostGISでもSpatiaLiteでもインデックスの使い方がよく分かりませんでした。

インデックスなしで(速度を無視して)やってみます。

SELECT tokyo.name, count(*) AS count
FROM station LEFT JOIN tokyo
ON ST_WithIn(station.Geometry, tokyo.Geometry)
GROUP BY tokyo.name;

こんな感じです。

TOP

 


QGISでのデータベース接続

メニュで、データベース -> DBマネージャー を開きます。

SpatiaLiteで右クリックしてメニュを出して、「新しい接続」をクリック

上で作成したデータベースを選びます。

テーブルを見るとこんな感じ

station

tokyo

それぞれで、右クリックでメニュを出して、「キャンバスに追加」します。

こんな感じです。

TOP


Next

QGIS上でSQLを発行して結果を表示してみる

 


 

 

Be the first to comment

Leave a Reply

Your email address will not be published.


*