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

Hologres は、PostGIS 空間関数を使用して、空間データを含むテーブルのクエリをサポートしています。このトピックでは、ローカルデータを Hologres にインポートし、空間関数を使用してデータをクエリする方法について説明します。

前提条件

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

背景情報

PostGIS は、PostgreSQL データベースの空間拡張機能です。情報サービス向けの空間オブジェクト、空間インデックス、空間関数、および空間オペレーターを提供します。

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

手順の概要

手順	説明
ステップ 1：テーブルの作成	Hologres データベースに accommodations テーブルを作成し、宿泊施設の場所 (経度と緯度)、リスティング名、その他のデータを格納します。zipcodes テーブルを作成し、ベルリンの郵便番号データを格納します。
ステップ 2：テストデータのインポート	HoloWeb を使用して、ワンクリックでローカルデータを accommodations テーブルと zipcodes テーブルにインポートします。
ステップ 3：空間関数を使用したデータクエリ	PostGIS 空間関数を使用して、テーブル内の空間データをクエリします。

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

次の手順に従って、データベースに accommodations テーブルと zipcodes テーブルを作成します。

HoloWeb コンソールの SQL エディターページにログインします。
[SQL Editor] タブの下にあるアイコンをクリックして、[Ad-hoc Query] ウィンドウを開きます。クエリウィンドウのツールバーで、作成してログオンした Hologres インスタンスの [Instance Name] と [データベース] を選択します。
PostGIS 拡張機能をロードします。
コマンドエディターで、次の SQL コマンドを入力し、Run をクリックします。
```
CREATE extension IF NOT EXISTS postgis; -- PostGIS 拡張機能をロードします
```
accommodations テーブルを作成します。
次の SQL 文を実行して、宿泊施設の場所 (経度と緯度)、リスティング名、その他のデータを格納するための accommodations テーブルを作成します。

説明
テーブルが作成されたら、クエリウィンドウの左側にあるTable Directoryの右にあるアイコンをクリックし、[public] > テーブルをクリックして作成結果を表示します。または、Operational Logsでテーブルが正常に作成されたかどうかを確認します。
```
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 テーブルを作成します。
次の SQL 文を実行して、ベルリンの郵便番号データを格納するための zipcodes テーブルを作成します。

説明
テーブルが作成された後、クエリウィンドウの左側にあるTable Directoryの右側にあるアイコンをクリックし、次に[public] > テーブルをクリックして作成結果を表示します。または、Operational Logsを確認して、テーブルが正常に作成されたかどうかを確認します。
```
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 の Import On-premises File 機能を使用して、[accommodations] テーブルと [zipcodes] テーブルにデータをインポートします。

HoloWeb コンソールの上部にある Data Solutions をクリックします。
Data Solutions ページで、左側の Import On-premises File をクリックします。ページの右側で、New Data Import をクリックします。
データをアップロードするテーブルを選択します。

Import On-premises File ダイアログボックスで、ジョブ名を入力し、インスタンス、データベース、およびテーブル (accommodations または zipcodes) を選択します。Next をクリックします。

データファイルとフォーマットを選択します。

Upload File タブで、次の設定を構成し、[次へ] をクリックします。需要导入的数据信息

パラメーター	パラメーターの説明
ファイルの選択	参照をクリックしてデータファイルをアップロードします。.txt、.csv、または .log 拡張子を持つファイルのみがサポートされています。前提条件で提供されている accommodations および zipcodes データテーブルを使用します。
区切り文字の選択	データの区切り文字を選択します。この例では、セミコロンを選択します。説明区切り文字フィールドの横にあるオプションをクリックして、データに基づいてカスタムの区切り文字を使用することもできます。
ソース文字セット	この例では、UTF-8 を選択します。
最初の行をヘッダーとして使用	デフォルトでは、このオプションは選択されていません。データファイルの最初の行にヘッダーが含まれている場合は、このオプションを選択します。

データインポート情報を確認します。

[インポート概要] タブで、情報が正しいことを確認し、Upload をクリックします。
データインポートの結果を表示します。
実行後、インポートが成功したかどうかが表示されます。失敗した場合は、その理由が表示されます。問題を修正して、データを再インポートしてください。
SQL エディターで次のコードを実行して、レコード数を確認し、テーブルの内容を表示することもできます。
- レコード数の確認
  
  accommodations テーブルには 22,248 レコードが含まれています。zipcodes テーブルには 190 レコードが含まれています。
```
SELECT COUNT(*) FROM accommodations; -- accommodations テーブルのレコード数をクエリします
SELECT COUNT(*) FROM zipcode; -- zipcode テーブルのレコード数をクエリします
```
- テーブルの内容の表示
```
SELECT * FROM accommodations; -- accommodations テーブルのレコードをクエリします
SELECT * FROM zipcode; -- zipcode テーブルのレコードをクエリします
```

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

テーブルの作成とデータインポートが正常に完了したら、必要に応じて空間関数を使用して空間データをクエリします。以下の例は、一般的なクエリ操作を示しています。空間関数の構文の詳細については、「空間関数」をご参照ください。

SRID が 4326 である accommodations テーブルのレコード数をクエリします。
- コード例：
```
SELECT COUNT(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
```
- 結果：
```
 count 
-------
 22248
(1 row)
```

WKT フォーマットを使用して、特定の属性を持つジオメトリオブジェクトをクエリします。郵便番号データも SRID 4326 を使用する WGS 84 に格納されていることを確認します。

説明

相互運用性を確保するには、空間データが同じ空間参照系を使用する必要があります。

コード例：

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
        ,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,...

SoBerlin に掲載されている宿泊施設のホットスポットを特定します。ホットスポットを郵便番号でグループ化し、供給量でソートします。

コード例:

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)