空間関数を使用してデータをクエリする - Hologres - Alibaba Cloud ドキュメントセンター

Hologres では、PostGIS 空間関数を使用してテーブル内の空間データをクエリできます。このトピックでは、オンプレミスマシンから Hologres にデータをインポートし、PostGIS 空間関数を使用して Hologres 内のデータをクエリする方法について説明します。

前提条件

Hologres インスタンスが購入済みであること。詳細については、Hologres インスタンスを購入するをご参照ください。
Hologres インスタンスにデータベースが作成されていること。詳細については、データベースを作成するをご参照ください。
このトピックで使用される空間データのサンプルがダウンロードされていること。データをダウンロードするには、次のリンクをクリックします。
- accommodations テーブル
- zipcodes テーブル

背景情報

PostGIS は PostgreSQL の空間拡張機能であり、オブジェクト、インデックス、関数、演算子などの空間機能を提供します。

このトピックで使用されるデモテーブルには、経度、緯度、座標、距離など、さまざまな種類の空間データが含まれています。手順 1 と手順 2 を実行して HoloWeb を使用し、ダウンロードした空間データのサンプルをオンプレミスマシンから、作成した accommodations テーブルと zipcodes テーブルにインポートできます。次に、手順 3 を実行して PostGIS 空間関数を使用し、2 つの Hologres テーブル内の空間データをクエリできます。

手順

手順	説明
手順 1: Hologres テーブルを作成する	Hologres インスタンスのデータベースに 2 つのテーブルを作成します。accommodations Hologres テーブルは、各宿泊施設の経度と緯度、名前などの宿泊施設情報を格納するために使用されます。zipcodes Hologres テーブルは、ベルリンの郵便番号を格納するために使用されます。
手順 2: 空間データのサンプルをインポートする	HoloWeb を使用して、オンプレミスマシンから accommodations テーブルと zipcodes テーブルに空間データをインポートします。
手順 3: 空間関数を使用してデータをクエリする	PostGIS 空間関数を使用して、2 つの Hologres テーブル内の空間データをクエリします。

手順 1: Hologres テーブルを作成する

accommodations テーブルと zipcodes テーブルを作成するには、次の操作を実行します。

にログインし、[SQL エディター] タブに移動します。
[新しい SQL クエリ] をクリックします。[インスタンス] ドロップダウンリストから Hologres インスタンスを選択し、[データベース] ドロップダウンリストから Hologres テーブルを作成するデータベースを選択します。
PostGIS 拡張機能をインストールします。
SQL エディターに次のステートメントを入力し、[実行] をクリックします。
```
create extension if not exists postgis; -- PostGIS 拡張機能をインストールします。
```
accommodations Hologres テーブルを作成します。
次の SQL ステートメントを実行して、accommodations Hologres テーブルを作成します。このテーブルは、各宿泊施設の経度と緯度、名前などの宿泊施設情報を格納するために使用されます。
説明
[テーブルディレクトリ] セクションで、[更新] アイコンをクリックし、public > テーブル を選択して、Hologres テーブルが作成されているかどうかを確認できます。[実行ログ] セクションで、Hologres テーブルが作成されているかどうかを確認することもできます。
```
CREATE TABLE public.accommodations (
  id INTEGER PRIMARY KEY,
  shape GEOMETRY,
  name VARCHAR(100),
  host_name VARCHAR(100),
  neighbourhood_group VARCHAR(100),
  neighbourhood VARCHAR(100),
  room_type VARCHAR(100),
  price SMALLINT,
  minimum_nights SMALLINT,
  number_of_reviews SMALLINT,
  last_review DATE,
  reviews_per_month NUMERIC(8,2),
  calculated_host_listings_count SMALLINT, 
  availability_365 SMALLINT
);
```
zipcodes Hologres テーブルを作成します。
次の SQL ステートメントを実行して、zipcodes Hologres テーブルを作成します。このテーブルは、ベルリンの郵便番号を格納するために使用されます。
説明
[テーブルディレクトリ] セクションで、[更新] アイコンをクリックし、public > テーブル を選択して、Hologres テーブルが作成されているかどうかを確認できます。[実行ログ] セクションで、Hologres テーブルが作成されているかどうかを確認することもできます。
```
CREATE TABLE public.zipcode (
  ogc_field INTEGER PRIMARY KEY NOT NULL,
  wkb_geometry GEOMETRY,
  gml_id VARCHAR(256),
  spatial_name VARCHAR(256),
  spatial_alias VARCHAR(256),
  spatial_type VARCHAR(256)
 );
```

手順 2: 空間データのサンプルをインポートする

accommodations テーブルと zipcodes テーブルを作成したら、[オンプレミスファイルのインポート] ページで、ダウンロードした空間データのサンプルをオンプレミスマシンから 2 つのテーブルにインポートします。

[HoloWeb コンソール] で、上部のナビゲーションバーにある [データソリューション] をクリックします。
[データソリューション] タブで、左側のナビゲーションウィンドウの [オンプレミスファイルのインポート] をクリックします。次に、このページで [新しいデータインポート] をクリックします。
データをインポートする Hologres テーブルを指定します。
[オンプレミスファイルのインポート] ダイアログボックスの [ターゲットテーブルの選択] 手順で、ジョブ名を入力し、Hologres インスタンス、作成したデータベース、作成した Hologres テーブル (accommodations または zipcodes) を選択し、[次の手順] をクリックします。

インポートする必要があるデータとエンコード形式を指定します。

[ファイルの選択] 手順で、次の表に示すパラメーターを設定し、[次の手順] をクリックします。需要导入的数据信息

パラメーター	説明
ファイルの選択	インポートするデータを含むファイル。[参照...] をクリックし、オンプレミスマシンからファイルを選択します。.txt、.csv、.log ファイルがサポートされています。この例では、ダウンロードした accommodations テーブルまたは zipcodes テーブルを選択します。
区切り文字	データエントリを区切るために使用される区切り文字。この例では、SEMICOLON を選択します。説明ドロップダウンリストの右側にあるオプションを選択し、ビジネス要件に基づいてカスタム区切り文字を指定することもできます。
元のエンコード	データのエンコード形式。この例では、UTF-8 を選択します。
最初の行をヘッダーとして使用	デフォルトでは、このオプションは選択されていません。インポートするデータの最初の行を Hologres テーブルのヘッダーとして使用する場合は、このオプションを選択します。

設定を確認します。
[インポートの概要] 手順で、データインポートジョブが想定どおりに設定されているかどうかを確認します。ジョブが想定どおりに設定されている場合は、[実行] をクリックします。
実行結果を確認します。
ジョブが完了すると、[インポートの概要] 手順に実行が成功したかどうかが表示されます。実行に失敗した場合は、トラブルシューティングのためにエラーの原因を表示し、データを再度インポートできます。
SQL エディターで SQL ステートメントを実行して、宛先 Hologres テーブル内のデータエントリの数または詳細データをクエリすることもできます。
- データエントリの数をチェックする
  この例では、accommodations Hologres テーブルには 22,248 件のデータエントリが含まれており、zipcodes Hologres テーブルには 190 件のデータエントリが含まれています。
```
select count(*) from accommodations; -- accommodations Hologres テーブルのデータ エントリの数を確認します。
select count(*) from zipcodes; -- zipcodes Hologres テーブルのデータ エントリの数を確認します。
```
- 詳細データをクエリする
```
select * from accommodations; -- accommodations Hologres テーブルの詳細データをクエリします。
select * from zipcodes; -- zipcodes Hologres テーブルの詳細データをクエリします。
```

手順 3: 空間関数を使用してデータをクエリする

必要な Hologres テーブルを作成し、空間データのサンプルをテーブルにインポートしたら、空間関数を使用して Hologres 内の空間データをクエリできます。次の例は参考用です。空間関数の構文については、地理情報分析のための PostGIS をご参照ください。

空間参照系識別子 (SRID) が 4326 に設定されている accommodations Hologres テーブルのデータエントリの数をクエリします。
- サンプルコード:
```
SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
```
- 返された結果:
```
 count 
-------
 22248
(1 row)
```

Well-Known Text (WKT) 形式を使用して、指定された条件を満たすジオメトリオブジェクトをクエリします。この例では、zipcodes Hologres テーブルの郵便番号が World Geodetic System 1984 (WGS84) に格納されているかどうかを確認できます。システムは 4326 の SRID を使用します。

説明

同じ空間参照系にある空間データエントリのみが相互に参照できます。

サンプルコード:

SELECT  ogc_field
        ,spatial_name
        ,spatial_type
        ,ST_SRID(wkb_geometry)
        ,ST_AsText(wkb_geometry)
FROM    public.zipcode
ORDER BY spatial_name
;

返された結果:

ogc_field  spatial_name  spatial_type  st_srid  st_astext
---------------------------------------------------------------
0           10115        Polygon        4326     POLYGON((...))
4           10117        Polygon        4326     POLYGON((...))
8           10119        Polygon        4326     POLYGON((...))
...
(190 rows returned)

GeoJSON 形式を使用して、SRID が 10117 に設定されているベルリンの Mitte の表面、表面サイズ、表面上の点の数をクエリします。

サンプルコード:

SELECT  ogc_field
        ,spatial_name
        ,ST_AsGeoJSON(wkb_geometry)
        ,ST_Dimension(wkb_geometry)
        ,ST_NPoints(wkb_geometry)
FROM    public.zipcode
WHERE   spatial_name = '10117'
;

返された結果:

ogc_field  spatial_name  spatial_type                                   st_dimension  st_npoint
-----------------------------------------------------------------------------------------------
4           10117         {"type":"Polygon", "coordinates":[[[...]]]}    2             331

ブランデンブルク門から 500 m 以内にある宿泊施設の数をクエリします。SRID は 4326 に設定されています。
- サンプルコード:
```
SELECT  COUNT(*)
FROM    public.accommodations
WHERE   ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500
;
```
- 返された結果:
```
 count 
-------
    29
(1 row)
```

近くの宿泊施設に関する情報に基づいて、ブランデンブルク門のおおよその位置を推定します。

サンプルコード:

WITH
    poi(loc) AS ( 
        SELECT st_astext(shape) 
        FROM accommodations 
        WHERE name LIKE '%brandenburg gate%' )
SELECT  COUNT(*)
FROM    accommodations a
        ,poi p
WHERE   ST_DistanceSphere(a.shape, ST_GeomFromText(p.loc, 4326)) < 500
;

返された結果:
```
 count 
-------
    60
(1 row)
```

ブランデンブルク門周辺のすべての宿泊施設に関する詳細情報をクエリし、価格で降順に並べ替えます。

サンプルコード:

SELECT  name
        ,price
        ,ST_AsText(shape)
FROM    public.accommodations
WHERE   ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500
ORDER BY price DESC
;

返された結果:

                        name                        | price |                st_astext                 
----------------------------------------------------+-------+------------------------------------------
 DUPLEX APARTMENT/PENTHOUSE in 5* LOCATION! 7583    |   300 | POINT(13.3826510209548 52.5159819722552)
 DUPLEX-PENTHOUSE IN FIRST LOCATION! 7582           |   300 | POINT(13.3799997083855 52.5135918444834)
 Luxury Apartment in Berlin Mitte with View         |   259 | POINT(13.3835653528534 52.516360156825)
 BIG APT 4 BLNCTY-CNTR 43-H6                        |   240 | POINT(13.3800222998777 52.5134224506894)
 BIG APARTMENT-PRIME LOCATION-BEST PRICE! B0303     |   240 | POINT(13.379745196599 52.5162648947249)
 BIG APARTMENT IN BRILLIANT LOCATION-CTY CENTRE B53 |   240 | POINT(13.381383105167 52.5157082721072)
 SONYCENTER: lux apartment - 3room/2bath. WIFI      |   235 | POINT(13.3743158954191 52.5125308432819)
 CENTRE APARTMENT FOR 6 | 8853                      |   220 | POINT(13.3819039478615 52.5134866767369)
 BIG APARTMENT FOR 6 - BEST LOCATION 8863           |   209 | POINT(13.3830430841658 52.5147824286783)
 3 ROOMS ONE AMAZING EXPERIENCE! 8762               |   190 | POINT(13.3819898503053 52.5144190764637)
 AAA LOCATION IN THE CENTRE H681                    |   170 | POINT(13.3821787206534 52.5129769242004)
 H672 Nice Apartment in CENTRAL LOCATION!           |   170 | POINT(13.3803137710339 52.5132386929089)
 "Best View -best location!"                        |   170 | POINT(13.3799551247135 52.5147888483851)
 H652 Best Location for 4!                          |   170 | POINT(13.3805705422409 52.5143845784482)
 H651 FIT´s for Four in a 5* Location!              |   150 | POINT(13.3822063502184 52.5134994650996)
 NEXT TO ATTRACTIONS! H252                          |   110 | POINT(13.3823616629115 52.5136258446666)
 CTY Centre Students Home| G4                       |   101 | POINT(13.3808081476226 52.5130957830586)
 Room for two with private shower / WC              |    99 | POINT(13.3786877948382 52.5208018292043)
 StudentsHome CityCentre Mitte 91-0703              |    95 | POINT(13.3810390515141 52.5142363781923)
 FIRST LOCATION - FAIR PRICE K621                   |    80 | POINT(13.3823909855061 52.5131554670458)
 LONG STAY FOR EXPATS/STUDENTS- CITY CENTRE | K921  |    75 | POINT(13.380320945399 52.512364557598)
 Nice4Students! City Centre 8732                    |    68 | POINT(13.3810147526683 52.5136623602892)
 Comfy Room in the heart of Berlin                  |    59 | POINT(13.3813167311819 52.5127345388756)
 FO(U)R STUDENTS HOME-Best centre Location!         |    57 | POINT(13.380850032042 52.5131726958513)
 Berlin Center Brandenburg Gate !!!                 |    55 | POINT(13.3849641540689 52.5163902851474)
 !!! BERLIN CENTER BRANDENBURG GATE                 |    55 | POINT(13.379997730927 52.5127577639174)
 Superb Double Bedroom in Central Berlin            |    52 | POINT(13.3792991992688 52.5156572293422)
 OMG! That's so Berlin!                            |    49 | POINT(13.3754883007165 52.5153487677272)
 Apartment in Berlin's old city center              |    49 | POINT(13.3821761577766 52.514037240604)
(29 rows)

最も価格の高い宿泊施設とその郵便番号に関する詳細情報をクエリします。

サンプルコード:

SELECT  a.price
        ,a.name
        ,ST_AsText(a.shape)
        ,z.spatial_name
        ,ST_AsText(z.wkb_geometry)
FROM    accommodations a
        ,zipcode z
WHERE   price = 9000
AND     ST_Within(a.shape, z.wkb_geometry)
;

返された結果:

price   name                                 st_astext                                  spatial_name      st_astext
-------------------------------------------------------------------------------------------------------------------------------------------------
9000    Ueber den Dächern Berlins Zentrum    POINT(13.334436985013 52.4979779501538)    10777             POLYGON((13.3318284987227 52.4956021172799,...

ベルリンの人気のある宿泊施設をクエリし、郵便番号でグループ化し、注文量でグループを並べ替えます。

サンプルコード:

SELECT  z.spatial_name AS zip
        ,COUNT(*) AS numAccommodations
FROM    public.accommodations a
        ,public.zipcode z
WHERE   ST_Within(a.shape, z.wkb_geometry)
GROUP BY zip
ORDER BY numAccommodations DESC
;

返された結果:

zip      numaccommodations
----------------------------
10245    872
10247    832
10437    733
10115    664
...
(187 rows returned)