Hologres で PostGIS を使用してポリゴン内のポイントを検索 - Hologres - Alibaba Cloud - Hologres

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

前提条件

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

背景情報

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

このトピックでは、緯度、経度、座標、距離など、さまざまな種類の空間情報を含むサンプルデータテーブルを提供します。HoloWeb を使用してテーブルを作成し、ローカルデータを accommodations テーブルと zipcode テーブルにインポートし、PostGIS 空間関数を使用してデータをクエリします。

手順

ステップ	説明
ステップ1：Hologres テーブルの作成	Hologres インスタンスのデータベースに、地理的な場所 (緯度と経度) やリスティング名などの宿泊施設データを格納する accommodations テーブルを作成します。また、ベルリンの郵便番号データを格納する zipcode テーブルも作成します。
ステップ2：サンプル空間データのインポート	HoloWeb を使用して、ローカルデータファイルを accommodations テーブルと zipcode テーブルにインポートします。
ステップ3：空間関数を使用したデータクエリ	PostGIS 空間関数を使用して、2 つの Hologres テーブルの空間データをクエリします。

ステップ1：Hologres テーブルの作成

データベースに accommodations テーブルと zipcode テーブルを作成します。

HoloWeb SQL エディターにログインします。
SQL エディター タブの下にあるアイコンをクリックして、一時クエリ ウィンドウを開きます。クエリウィンドウのツールバーで、Hologres の インスタンス名 と データベース を選択します。
PostGIS 拡張を有効にします。
SQL エディタで、次のステートメントを入力し、実行をクリックします。
```
CREATE extension IF NOT EXISTS postgis; -- PostGIS拡張を有効にします。
```
accommodations テーブルを作成します。
次の SQL ステートメントを実行して accommodations テーブルを作成します。このテーブルには、地理的な場所 (緯度と経度)、リスティング名、その他の詳細などの宿泊施設データが格納されます。

説明
テーブルが作成された後、結果を確認できます。左側の テーブルカタログ ペインで、更新アイコンをクリックし、public > テーブル に移動します。または、運用ログ を確認して、テーブルが正常に作成されたことを確認できます。
```
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
);
```
zipcode テーブルを作成します。
次の SQL ステートメントを実行して、ベルリンの郵便番号データを格納する zipcode テーブルを作成します。

説明
テーブルが作成された後、結果を確認できます。左側の テーブルカタログ ペインで、更新アイコンをクリックし、public > テーブル に移動します。または、運用ログ を確認して、テーブルが正常に作成されたことを確認できます。
```
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：サンプル空間データのインポート

HoloWeb の ローカルファイルのワンクリックインポート 機能を使用して、accommodations および zipcode テーブルにデータをインポートします。

HoloWeb コンソールで、上部のナビゲーションバーのデータソリューションをクリックします。
データソリューション ページで、左側のナビゲーションペインの ローカルファイルのワンクリックインポート をクリックし、次に右側の データインポートの作成 をクリックします。
インポート先の Hologres テーブルを指定します。

ローカルファイルのワンクリックアップロード ダイアログボックスで、ジョブ名を入力し、インスタンス、データベース、およびテーブル (accommodations または zipcode) を選択します。次に次へをクリックします。

インポートするデータとエンコーディング形式を指定します。

データソーステーブルの選択 タブで、次のパラメーターを設定し、[次へ] をクリックします。

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

設定の確認

[Import Overview] タブの実行ページで、インポート情報を確認します。このページには、ジョブ名、ターゲットデータベース、スキーマ、テーブルなどの概要が表示されます。情報が正しいことを確認したら、[実行] をクリックしてインポートを開始します。
実行結果の確認
ジョブが完了すると、[Import Overview] ステップで実行が成功したかどうかがシステムに表示されます。実行が失敗した場合は、エラーの原因を表示してトラブルシューティングを行い、データを再度インポートできます。
SQL エディターで SQL ステートメントを実行して、インポート先の Hologres テーブルのデータエントリ数や詳細データをクエリすることもできます。
- データエントリ数の確認
  
  accommodations テーブルには 22,248 行、zipcode テーブルには 190 行が含まれています。
```
SELECT COUNT(*) FROM accommodations; -- accommodationsテーブルの行数をクエリします。
SELECT COUNT(*) FROM zipcode; -- zipcodeテーブルの行数をクエリします。
```
- 詳細データのクエリ
```
SELECT * FROM accommodations; -- accommodationsテーブルの行をクエリします。
SELECT * FROM zipcode; -- zipcodeテーブルの行をクエリします。
```

ステップ3：空間関数を使用したデータクエリ

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

accommodations テーブルで、空間参照系識別子 (SRID) が 4326 の行数をクエリします。
- サンプルコード：
```
SELECT COUNT(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
```
- 返された結果：
```
 count 
-------
 22248
(1 row)
```

Well-Known Text (WKT) 形式を使用して、指定された条件を満たすジオメトリオブジェクトをクエリします。この例では、Hologres の zipcode テーブルの郵便番号が世界測地系 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 形式を使用して、郵便番号が「10117」であるベルリンのミッテ地区のサーフェス、サーフェスサイズ、およびサーフェス上のポイント数をクエリします。

サンプルコード：

SELECT  ogc_field
        ,spatial_name
        ,spatial_type
        ,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

SRID を 4326 に設定して、ブランデンブルク門から 500 メートル以内にある宿泊施設の数をクエリします。
- サンプルコード：
```
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)