Gunakan fungsi spasial untuk kueri data - Hologres - Alibaba Cloud - Hologres

Di Hologres, Anda dapat menggunakan fungsi spasial PostGIS untuk mengkueri data spasial dalam tabel. Topik ini menjelaskan cara mengimpor data dari mesin on-premises ke Hologres dan menggunakan fungsi spasial PostGIS untuk mengkueri data di Hologres.

Prasyarat

Instans Hologres telah dibuat. Untuk informasi selengkapnya, lihat Purchase a Hologres instance.
Database telah dibuat di instans Hologres. Untuk informasi selengkapnya, lihat Create a database.
Data spasial sampel yang digunakan dalam topik ini telah diunduh. Untuk mengunduh data tersebut, klik tautan berikut:
- tabel accommodations
- tabel zipcodes

Informasi latar belakang

PostGIS adalah ekstensi spasial PostgreSQL yang menyediakan fitur spasial, termasuk objek, indeks, fungsi, dan operator.

Topik ini menyediakan tabel data sampel yang berisi berbagai jenis informasi spasial, seperti bujur, lintang, koordinat, dan jarak. Anda dapat mengikuti langkah-langkah berikut untuk menggunakan HoloWeb guna membuat tabel, mengimpor data lokal ke tabel accommodations dan zipcode, lalu mengkueri data spasial menggunakan fungsi spasial PostGIS.

Prosedur

Langkah	Deskripsi
Langkah 1: Buat tabel Hologres	Di database instans Hologres Anda, buat tabel accommodations untuk menyimpan data akomodasi seperti lokasi geografis (bujur dan lintang), nama listing, dan data lainnya. Selain itu, buat tabel zipcode untuk menyimpan data kode pos Berlin.
Langkah 2: Impor data spasial sampel	Gunakan fitur Import On-premises File HoloWeb untuk mengimpor data dari file lokal ke tabel accommodations dan zipcode.
Langkah 3: Gunakan fungsi spasial untuk mengkueri data	Gunakan fungsi spasial PostGIS untuk mengkueri data spasial di kedua tabel Hologres tersebut.

Langkah 1: Buat tabel Hologres

Ikuti langkah-langkah berikut untuk membuat tabel accommodations dan zipcode di database Anda.

Masuk ke halaman SQL Editor di konsol HoloWeb.
Klik ikon di bawah tab SQL Editor untuk membuka jendela Ad-hoc Query. Di bilah alat jendela kueri, pilih Instance Name dan Database Hologres Anda.
Muat ekstensi PostGIS.
Masukkan pernyataan SQL berikut di editor perintah, lalu klik Run.
```
CREATE extension IF NOT EXISTS postgis; -- Load the PostGIS extension.
```
Buat tabel accommodations.
Jalankan pernyataan SQL berikut untuk membuat tabel accommodations. Tabel ini menyimpan data akomodasi, termasuk lokasi geografis (bujur dan lintang), nama listing, dan data lainnya.
Catatan
Setelah tabel dibuat, Anda dapat memverifikasi hasilnya. Di sisi kiri jendela kueri, klik ikon di sebelah Table Directory dan navigasikan ke public > Table. Atau, periksa Operational Logs untuk memastikan bahwa tabel tersebut ada.
```
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
);
```
Buat tabel zipcode.
Jalankan pernyataan SQL berikut untuk membuat tabel zipcode, yang menyimpan data kode pos Berlin.
Catatan
Setelah tabel dibuat, Anda dapat memverifikasi hasilnya. Di sisi kiri jendela kueri, klik ikon di sebelah Table Directory dan navigasikan ke public > Table. Atau, periksa Operational Logs untuk memastikan bahwa tabel tersebut ada.
```
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)
 );
```

Langkah 2: Impor data spasial sampel

Setelah membuat tabel, gunakan fitur Import On-premises File HoloWeb untuk mengimpor data ke tabel accommodations dan zipcode.

Di konsol HoloWeb, klik Data Solutions di bilah navigasi atas.
Di halaman Data Solutions, klik Import On-premises File di panel navigasi kiri, lalu klik New Data Import.
Tentukan tabel Hologres tempat Anda ingin mengimpor data.
Di kotak dialog Import On-premises File, masukkan nama pekerjaan, pilih instans, database, dan tabel tujuan (accommodations atau zipcode), lalu klik Next.

Tentukan data yang perlu diimpor dan format encoding-nya.

Di tab Upload File, konfigurasikan parameter sesuai tabel berikut, lalu klik Next. 需要导入的数据信息

Parameter	Deskripsi
Select File	Klik Browse untuk mengunggah file data yang ingin diimpor. Hanya file dengan ekstensi .txt, .csv, atau .log yang didukung. Pilih file data accommodations atau zipcode yang telah Anda unduh.
Delimiter	Pembatas yang digunakan untuk memisahkan entri data. Dalam contoh ini, pilih SEMICOLON. Catatan Anda juga dapat memilih opsi di sebelah kanan daftar drop-down dan menentukan pembatas kustom sesuai kebutuhan bisnis Anda.
Original Encoding	Dalam contoh ini, pilih UTF-8.
First Line as Header	Secara default, opsi ini tidak dipilih. Jika baris pertama data yang ingin diimpor digunakan sebagai header tabel Hologres, pilih opsi ini.

Konfirmasi konfigurasi.
Di tab Import Overview, tinjau detail impor. Jika semua informasi sudah benar, klik Upload.
Verifikasi hasil eksekusi.
Setelah pekerjaan selesai, sistem akan menampilkan apakah eksekusi berhasil pada langkah Import Overview. Jika eksekusi gagal, Anda dapat melihat penyebab error untuk troubleshooting dan mengimpor data lagi.
Anda juga dapat menjalankan pernyataan SQL di editor SQL untuk mengkueri jumlah entri data atau data detail di tabel Hologres tujuan:
- Periksa jumlah entri data
  Tabel accommodations harus berisi 22.248 catatan, dan tabel zipcode harus berisi 190 catatan.
```
SELECT COUNT(*) FROM accommodations; -- Query the number of records in the accommodations table.
SELECT COUNT(*) FROM zipcode; -- Query the number of records in the zipcode table.
```
- Kueri Data Terperinci
```
SELECT * FROM accommodations; -- Query the records in the accommodations table.
SELECT * FROM zipcode; -- Query the records in the zipcode table.
```

Langkah 3: Gunakan fungsi spasial untuk mengkueri data

Setelah tabel Hologres yang diperlukan dibuat dan data spasial sampel diimpor ke tabel tersebut, Anda dapat menggunakan fungsi spasial untuk mengkueri data spasial di Hologres. Contoh berikut disediakan sebagai referensi. Untuk informasi tentang sintaks fungsi spasial, lihat PostGIS for geographic information analysis.

Kueri jumlah catatan di tabel accommodations yang memiliki Spatial Reference Identifier (SRID) 4326.
- Kode sampel:
```
SELECT COUNT(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
```
- Hasil yang dikembalikan:
```
 count 
-------
 22248
(1 row)
```

Gunakan format well-known text (WKT) untuk mengkueri objek geometri yang memenuhi kondisi tertentu. Dalam contoh ini, Anda dapat memeriksa apakah kode pos di tabel Hologres zipcodes disimpan dalam World Geodetic System 1984 (WGS84). Sistem ini menggunakan SRID 4326.

Catatan

Hanya entri data spasial yang berada dalam sistem referensi spasial yang sama yang dapat saling mereferensikan satu sama lain.

Kode sampel:

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

Hasil yang dikembalikan:

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)

Gunakan format GeoJSON untuk mengkueri permukaan, ukuran permukaan, dan jumlah titik pada permukaan untuk Mitte di Berlin dengan SRID diatur ke 10117.

Kode sampel:

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

Hasil yang dikembalikan:

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

Kueri jumlah akomodasi dalam jarak 500 meter dari Gerbang Brandenburg dengan SRID diatur ke 4326.

Kode sampel:

SELECT  COUNT(*)
FROM    public.accommodations
WHERE   ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500
;

Hasil yang dikembalikan:
```
 count 
-------
    29
(1 row)
```

Lakukan perkiraan kasar lokasi Gerbang Brandenburg berdasarkan informasi akomodasi di sekitarnya.

Kode sampel:

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
;

Hasil yang dikembalikan:
```
 count 
-------
    60
(1 row)
```

Kueri informasi detail semua akomodasi di sekitar Gerbang Brandenburg dan urutkan akomodasi tersebut secara menurun berdasarkan harga.

Kode sampel:

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
;

Hasil yang dikembalikan:

                        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.5136623602882)
 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)

Kueri informasi detail akomodasi dengan harga tertinggi beserta kode pos-nya.

Kode sampel:

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)
;

Hasil yang dikembalikan:

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

Kueri akomodasi populer di Berlin, kelompokkan akomodasi berdasarkan kode pos, lalu urutkan kelompok tersebut berdasarkan volume pesanan.

Kode sampel:

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
;

Hasil yang dikembalikan:

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