SQL SQLite方言

SQLite 方言は OGR SQL 方言 の代替SQL方言として使用できます. これは, GDAL/OGRがSQLiteのサポートをビルドされていることを前提としています. 空間関数を利用するためには, Spatialite のサポートも望ましいです.

SQLite方言はOGR SQL方言と同様に, 任意のOGRデータソースで使用できます. SQLite 方言は, GDALDataset::ExecuteSQL() の方言パラメータとして SQLite 文字列を渡すか, ogrinfo や ogr2ogr ユーティリティの -dialect オプションでリクエストできます.

これは主にSELECT文を実行するために使用されますが, 更新をサポートするデータソースの場合, INSERT/UPDATE/DELETE文も実行できます. GDALは内部的に SQLiteの仮想テーブルメカニズム を使用しているため, ALTER TABLEのような操作はサポートされていません. ALTER TABLEやDROP TABLEを実行するには, OGR SQL 方言 を使用してください.

データソースがSQLiteデータベース(GeoPackage, SpatiaLite)の場合, SQLite方言はネイティブSQL方言として機能し, 仮想テーブルメカニズムは使用されません. この場合でも, "-dialect INDIRECT_SQLITE" を指定することでGDALに仮想テーブルを使用させることができます. これは必要な場合にのみ使用するべきです. 仮想テーブルメカニズムを経由するとパフォーマンスに影響する可能性があるためです.

SQLステートメントの構文は完全にSQLite SQLエンジンのものです. 以下のページを参照してください:

SELECT文

SELECT文は, レイヤーの地物(データベースのテーブル行に類似)を取得するために使用されます. クエリの結果は地物の一時レイヤーとして表されます. データソースのレイヤーはデータベースのテーブルに類似し, 地物の属性は列の値に類似します. OGR SQLITE SELECT文の最も単純な形式は次のようになります:

SELECT * FROM polylayer

WHERE, JOIN, USING, GROUP BY, ORDER BY, sub SELECTなどを含むより複雑なステートメントを使用することもできます.

使用できるテーブル名は, ExecuteSQL()メソッドが呼び出されるデータソースで利用可能なレイヤー名です.

OGRSQLと同様に, 他のデータソースのレイヤーを参照することも可能です. 次の構文を使用します: "other_datasource_name"."layer_name".

SELECT p.*, NAME FROM poly p JOIN "idlink.dbf"."idlink" il USING (eas_id)

マスターデータソースがSQLiteデータベース(GeoPackage, SpatiaLite)の場合, 間接的なSQLite方言を使用する必要があります. そうでない場合, 追加のデータソースは決して開かれませんが, 結合に使用されるテーブルはマスターデータベースから検索されます.

ogrinfo jointest.gpkg -dialect INDIRECT_SQLITE -sql \
"SELECT a.ID,b.ID FROM jointest a JOIN \"jointest2.shp\".\"jointest2\" b ON a.ID=b.ID"

結果の列リスト, WHERE, JOINなどの句で使用できる列名は, レイヤーのフィールド名です. 式, SQLite関数, 空間関数なども使用できます.

WHERE句やJOIN句でフィールドに対する条件が表現されると, できる限り, それらは基になるOGRレイヤーに適用される属性フィルタとして変換されます. キーフィールドにインデックスがない場合, JOINは非常に重い操作になる可能性があります.

LIKE演算子

SQLiteでは, LIKE演算子は大文字小文字を区別しません. PRAGMA case_sensitive_like = 1 が発行されていない限りです.

Starting with GDAL 3.9, GDAL installs a custom LIKE comparison, such that UTF-8 characters are taken into account by LIKE operator.

For case insensitive comparisons, this is restricted to the ASCII, Latin-1 Supplement, Latin Extended-A, Latin Extended-B, Greek and Coptic and Cyrillic Unicode categories.

デリミテッド識別子

レイヤーや属性の名前がSQLの予約キーワードである場合(例: 'FROM')や, 数字やアンダースコアで始まる場合, クエリで "デリミテッド識別子" として扱われ, ダブルクォーテーションで囲まれます. ダブルクォーテーションは, 厳密に必要でない場合でも使用できます.

SELECT "p"."geometry", "p"."FROM", "p"."3D" FROM "poly" p

SQLステートメントがコマンドシェルで使用され, ステートメント自体がダブルクォーテーションで囲まれている場合, 内部のダブルクォーテーションは \ でエスケープする必要があります.

ogrinfo p.shp -sql "SELECT geometry \"FROM\", \"3D\" FROM p"

ジオメトリフィールド

Geometry fields can be explicitly specified in the result column list of a SELECT, or automatically selected if the * wildcard is used.

For OGR layers that have a non-empty geometry column name (generally for RDBMS datasources), as returned by OGRLayer::GetGeometryColumn(), the name of the geometry special field in the SQL statement must be the name of the geometry column of the underlying OGR layer. If the name of the geometry column in the source layer is empty, like with shapefiles etc., the name to use in the SQL statement must be "geometry". Here we'll use it case-insensitively (as all field names are in a SELECT statement):

SELECT EAS_ID, GEOMETRY FROM poly

リターン:

OGRFeature(SELECT):0
EAS_ID (Real) = 168
POLYGON ((479819.84375 4765180.5,479690.1875 4765259.5,[...],479819.84375 4765180.5))

SELECT * FROM poly

リターン:

OGRFeature(SELECT):0
AREA (Real) = 215229.266
EAS_ID (Real) = 168
PRFEDEA (String) = 35043411
POLYGON ((479819.84375 4765180.5,479690.1875 4765259.5,[...],479819.84375 4765180.5))

地物ID (FID)

地物IDは地物の特別なプロパティであり, 地物の属性として扱われません. 場合によっては, 地物IDをクエリや結果セットで通常のフィールドとして利用できると便利です. その場合は rowid という名前を使用します.

GDAL 3.8以降, レイヤーに名前付きFID列がある場合 (OGRLayer::GetFIDColumn() != ""), この名前も使用できます.

フィールドワイルドカードの展開には地物IDは含まれませんが, 次のような構文を使用して明示的に含めることができます:

SELECT rowid, * FROM nation

これの名前を変更することも可能です:

SELECT rowid AS fid, * FROM nation

OGR_STYLE特殊フィールド

OGR_STYLE 特殊フィールドは, OGRFeature::GetStyleString() によって返される地物のスタイル文字列を表します. このフィールドと LIKE 演算子を使用することで, クエリの結果をスタイルでフィルタリングすることができます. 例えば, アノテーション地物を選択することができます:

SELECT * FROM nation WHERE OGR_STYLE LIKE 'LABEL%'

統計関数

標準の COUNT(), SUM(), AVG(), MIN(), MAX() に加えて, 次の集計関数が利用可能です:

  • STDDEV_POP(numeric_value): (GDAL >= 3.10) numerical population standard deviation.

  • STDDEV_SAMP(numeric_value): (GDAL >= 3.10) numerical sample standard deviation

Ordered-set aggregate functions

The following aggregate functions are available. Note that they require to allocate an amount of memory proportional to the number of selected rows (for MEDIAN, PERCENTILE and PERCENTILE_CONT) or to the number of values (for MODE).

  • MEDIAN(numeric_value): (GDAL >= 3.10) (continuous) median (equivalent to PERCENTILE(numeric_value, 50)). NULL values are ignored.

  • PERCENTILE(numeric_value, percentage): (GDAL >= 3.10) (continuous) percentile, with percentage between 0 and 100 (equivalent to PERCENTILE_CONT(numeric_value, percentage / 100)). NULL values are ignored.

  • PERCENTILE_CONT(numeric_value, fraction): (GDAL >= 3.10) (continuous) percentile, with fraction between 0 and 1. NULL values are ignored.

  • MODE(value): (GDAL >= 3.10): mode, i.e. most frequent input value (strings and numeric values are supported), arbitrarily choosing the first one if there are multiple equally-frequent results. NULL values are ignored.

Spatialite SQL関数

GDAL/OGRが Spatialite ライブラリのサポートでビルドされている場合, 多くの 追加のSQL関数 が利用可能になります. 特に空間関数は, 結果の列フィールド, WHERE句などで使用できます.

SELECT EAS_ID, ST_Area(GEOMETRY) AS area FROM poly WHERE
    ST_Intersects(GEOMETRY, BuildCircleMbr(479750.6875,4764702.0,100))

リターン:

OGRFeature(SELECT):0
EAS_ID (Real) = 169
area (Real) = 101429.9765625

OGRFeature(SELECT):1
EAS_ID (Real) = 165
area (Real) = 596610.3359375

OGRFeature(SELECT):2
EAS_ID (Real) = 170
area (Real) = 5268.8125

SQLiteの緩い型付けメカニズムのため, ジオメトリ式が最初の行でNULL値を返すと, 一般的にOGRはその列をジオメトリ列として認識しません. そのため, 非NULLジオメトリが最初に返されるように結果をソートすることが役立つ場合があります:

ogrinfo test.shp -sql "SELECT * FROM (SELECT ST_Buffer(geometry,5) AS geometry FROM test) ORDER BY geometry IS NULL ASC" -dialect sqlite

OGRデータソースSQL関数

ogr_datasource_load_layers(datasource_name[, update_mode[, prefix]]) 関数は, データソースのすべてのレイヤーを VirtualOGR tables として自動的にロードするために使用できます.

sqlite> SELECT load_extension('libgdal.so');

sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT ogr_datasource_load_layers('poly.shp');
1
sqlite> SELECT * FROM sqlite_master;
table|poly|poly|0|CREATE VIRTUAL TABLE "poly" USING VirtualOGR('poly.shp', 0, 'poly')

OGRレイヤーSQL関数

次のSQL関数が利用可能で, レイヤー名に対して動作します: ogr_layer_Extent(), ogr_layer_SRID(), ogr_layer_GeometryType() および ogr_layer_FeatureCount()

SELECT ogr_layer_Extent('poly'), ogr_layer_SRID('poly') AS srid,
    ogr_layer_GeometryType('poly') AS geomtype, ogr_layer_FeatureCount('poly') AS count

OGRFeature(SELECT):0
srid (Integer) = 40004
geomtype (String) = POLYGON
count (Integer) = 10
POLYGON ((478315.53125 4762880.5,481645.3125 4762880.5,481645.3125 4765610.5,478315.53125 4765610.5,478315.53125 4762880.5))

OGR圧縮関数

ogr_deflate(text_or_blob[, compression_level]) は, ZLib deflateアルゴリズムで圧縮されたバイナリblobを返します. CPLZLibDeflate() を参照してください.

ogr_inflate(compressed_blob) は, ZLib deflateアルゴリズムで圧縮されたblobから解凍されたバイナリblobを返します. 解凍されたバイナリが文字列の場合, CAST(ogr_inflate(compressed_blob) AS VARCHAR) を使用してください. CPLZLibInflate() を参照してください.

その他の関数

hstore_get_value() 関数は, "key=>value,other_key=>other_value,..." のようにフォーマットされたHSTORE文字列からキーに関連付けられた値を抽出するために使用できます.

SELECT hstore_get_value('a => b, "key with space"=> "value with space"', 'key with space') --> 'value with space'

OGRジオコーディング関数

次のSQL関数が利用可能です: ogr_geocode(...) および ogr_geocode_reverse(...).

ogr_geocode(name_to_geocode [, field_to_return [, option1 [, option2, ...]]]) where name_to_geocode is a literal or a column name that must be geocoded. field_to_return if specified can be "geometry" for the geometry (default), or a field name of the layer returned by OGRGeocode(). The special field "raw" can also be used to return the raw response (XML string) of the geocoding service. option1, option2, etc.. must be of the key=value format, and are options understood by OGRGeocodeCreateSession() or OGRGeocode().

この関数は内部的にOGRGeocode() APIを使用しています. 詳細については, それを参照してください.

SELECT ST_Centroid(ogr_geocode('Paris'))

リターン:

OGRFeature(SELECT):0
POINT (2.34287687375113 48.856622357411)

ogrinfo cities.csv -dialect sqlite -sql "SELECT *, ogr_geocode(city, 'country_code') AS country_code, ST_Centroid(ogr_geocode(city)) FROM cities"

リターン:

OGRFeature(SELECT):0
  city (String) = Paris
  country_code (String) = fr
  POINT (2.34287687375113 48.856622357411)

OGRFeature(SELECT):1
  city (String) = London
  country_code (String) = gb
  POINT (-0.109415723431508 51.5004964757441)

OGRFeature(SELECT):2
  city (String) = Rennes
  country_code (String) = fr
  POINT (-1.68185479486048 48.1116771631195)

OGRFeature(SELECT):3
  city (String) = New York
  country_code (String) = us
  POINT (-73.9388908443975 40.6632061220125)

OGRFeature(SELECT):4
  city (String) = Beijing
  country_code (String) = cn
  POINT (116.3912972 39.9057136)

ogr_geocode_reverse(longitude, latitude, field_to_return [, option1 [, option2, ...]]) は, longitude, latitude がクエリする座標です. field_to_return は, OGRGeocodeReverse() によって返されるレイヤーのフィールド名である必要があります (例: 'display_name'). ジオコーディングサービスの生の応答(XML文字列)を返すために, 特別なフィールド "raw" も使用できます. option1, option2などは, key=value形式である必要があり, OGRGeocodeCreateSession() または OGRGeocodeReverse() で理解されるオプションです.

ogr_geocode_reverse(geometry, field_to_return [, option1 [, option2, ...]]) は, geometryが(Spatialite)ポイントジオメトリである場合の代替構文としても受け入れられます.

この関数は内部的に OGRGeocodeReverse() API を使用しています. 詳細については, それを参照してください.

Spatialite空間インデックス

Spatialite空間インデックスメカニズムは, SQLに空間インデックス仮想テーブルが言及されていることを確認するか, より新しいVirtualSpatialIndex拡張機能を使用することでトリガーされます. この場合, 空間クエリの高速化に使用するために, メモリ内のRTreeが構築されます.

例えば, 1つのレイヤーに空間インデックスを使用して実際のジオメトリ交差計算の数を制限することで, 2つのレイヤー間の空間交差を行う:

SELECT city_name, region_name FROM cities, regions WHERE
    ST_Area(ST_Intersection(cities.geometry, regions.geometry)) > 0 AND
    regions.rowid IN (
        SELECT pkid FROM idx_regions_geometry WHERE
            xmax >= MbrMinX(cities.geometry) AND xmin <= MbrMaxX(cities.geometry) AND
            ymax >= MbrMinY(cities.geometry) AND ymin <= MbrMaxY(cities.geometry))

またはよりエレガントな方法:

SELECT city_name, region_name FROM cities, regions WHERE
    ST_Area(ST_Intersection(cities.geometry, regions.geometry)) > 0 AND
    regions.rowid IN (
        SELECT rowid FROM SpatialIndex WHERE
            f_table_name = 'regions' AND search_frame = cities.geometry)