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

Anda telah membuat instans Hologres. 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 akan menggunakan HoloWeb untuk membuat tabel, mengimpor data lokal ke tabel accommodations dan zipcode, lalu menggunakan fungsi spasial PostGIS untuk mengkueri data tersebut.

Prosedur

Langkah	Deskripsi
Langkah 1: Buat tabel Hologres	Buat tabel accommodations di database instans Hologres Anda untuk menyimpan data akomodasi seperti lokasi geografis (bujur dan lintang) serta nama listing. Buat tabel zipcode untuk menyimpan data kode pos Berlin.
Langkah 2: Impor data spasial sampel	Gunakan HoloWeb untuk mengimpor file data 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

Buat tabel accommodations dan zipcode di database Anda.

Masuk ke HoloWeb SQL Editor.
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.
Aktifkan ekstensi PostGIS.
Masukkan pernyataan berikut di editor SQL dan klik Run.
```
CREATE extension IF NOT EXISTS postgis; -- Enable the PostGIS extension.
```
Buat tabel accommodations.
Jalankan pernyataan SQL berikut untuk membuat tabel accommodations. Tabel ini menyimpan data akomodasi, seperti lokasi geografis (bujur dan lintang), nama listing, dan detail lainnya.

Catatan
Setelah tabel dibuat, Anda dapat memverifikasi hasilnya. Di panel Table Directory di sebelah kiri, klik ikon refresh , lalu navigasikan ke public > Table. Atau, Anda dapat memeriksa Operational Logs untuk memastikan tabel berhasil dibuat.
```
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 guna menyimpan data kode pos Berlin.

Catatan
Setelah tabel dibuat, Anda dapat memverifikasi hasilnya. Di panel Table Directory di sebelah kiri, klik ikon refresh , lalu navigasikan ke public > Table. Atau, Anda dapat memeriksa Operational Logs untuk memastikan tabel berhasil dibuat.
```
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

Gunakan fitur Import On-premises File di HoloWeb untuk mengimpor data ke tabel accommodations dan zipcode.

Di HoloWeb console, klik Data Solutions di bilah navigasi atas.
Pada halaman Data Solutions, klik Import On-premises File di panel navigasi kiri, lalu klik New Data Import di sebelah kanan.
Tentukan tabel Hologres tempat Anda ingin mengimpor data.

Pada kotak dialog Import On-premises File, masukkan nama job dan pilih instans, database, serta tabel Anda (baik accommodations maupun zipcode). Lalu klik Next.

Tentukan data yang perlu diimpor dan format encoding-nya.

Pada tab Upload File, konfigurasikan parameter berikut dan klik Next.

Parameter	Deskripsi
Select File	Klik Browse untuk mengunggah file data yang akan diimpor. Hanya file dengan ekstensi .txt, .csv, atau .log yang didukung. Pilih file data accommodations atau zipcode yang telah Anda unduh.
Delimiter	Delimiter 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 delimiter kustom sesuai kebutuhan bisnis Anda.
Original Encoding	Untuk contoh ini, pilih UTF-8.
First Line as Header	Secara default, opsi ini tidak dipilih. Jika baris pertama data yang ingin Anda impor digunakan sebagai header tabel Hologres, pilih opsi ini.

Konfirmasi konfigurasi.

Pada tab Import Overview, tinjau informasi impor pada halaman Upload. Halaman ini menampilkan ringkasan termasuk nama job, database tujuan, skema, dan tabel. Setelah memastikan informasi sudah benar, klik Execute untuk memulai impor.
Verifikasi hasil eksekusi.
Setelah job selesai, sistem akan menampilkan apakah eksekusi berhasil pada langkah Import Overview. Jika eksekusi gagal, Anda dapat melihat penyebab error untuk troubleshooting dan mengimpor data kembali.
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 berisi 22.248 baris, dan tabel zipcode berisi 190 baris.
```
SELECT COUNT(*) FROM accommodations; -- Query the number of rows in the accommodations table.
SELECT COUNT(*) FROM zipcode; -- Query the number of rows in the zipcode table.
```
- Kueri data detail
```
SELECT * FROM accommodations; -- Query the rows in the accommodations table.
SELECT * FROM zipcode; -- Query the rows 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 baris di tabel accommodations yang memiliki Spatial Reference System 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
        ,spatial_type
        ,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 radius 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 berdasarkan harga secara menurun.

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

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)