Gunakan Fungsi Spasial untuk Meminta Data - Hologres

Dalam Hologres, Anda dapat menggunakan fungsi spasial PostGIS untuk menanyakan data spasial dalam tabel. Topik ini menjelaskan cara mengimpor data dari mesin lokal ke Hologres serta menggunakan fungsi spasial PostGIS untuk menanyakan data di Hologres.

Prasyarat

Instans Hologres telah dibeli. Untuk informasi lebih lanjut, lihat Beli instans Hologres.
Database telah dibuat di instans Hologres. Untuk informasi lebih lanjut, lihat Buat database.
Data spasial sampel yang digunakan dalam topik ini telah diunduh. Untuk mengunduh data, gunakan tautan berikut:
- Tabel accommodations
- Tabel zipcodes

Informasi latar belakang

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

Tabel demo yang digunakan dalam topik ini mencakup berbagai jenis data spasial, seperti bujur, lintang, koordinat, dan jarak. Untuk memulai, lakukan Langkah 1 dan Langkah 2 guna menggunakan HoloWeb mengimpor data spasial sampel yang diunduh dari mesin lokal Anda ke tabel Hologres accommodations dan zipcodes yang telah dibuat. Selanjutnya, lakukan Langkah 3 untuk memanfaatkan fungsi spasial PostGIS dalam menanyakan data spasial pada kedua tabel Hologres tersebut.

Prosedur

Langkah	Deskripsi
Langkah 1: Buat tabel Hologres	Buat dua tabel dalam database di instans Hologres Anda. Tabel Hologres accommodations digunakan untuk menyimpan informasi akomodasi, seperti bujur, lintang, dan nama setiap akomodasi. Tabel Hologres zipcodes digunakan untuk menyimpan kode pos di Berlin.
Langkah 2: Impor data spasial sampel	Gunakan HoloWeb untuk mengimpor data spasial dari mesin lokal Anda ke tabel Hologres accommodations dan zipcodes.
Langkah 3: Gunakan fungsi spasial untuk menanyakan data	Gunakan fungsi spasial PostGIS untuk menanyakan data spasial dalam dua tabel Hologres.

Langkah 1: Buat tabel Hologres

Ikuti langkah-langkah berikut untuk membuat tabel Hologres accommodations dan zipcodes.

Masuk ke Editor SQL Konsol HoloWeb.
Pada tab SQL Editor, klik ikon . Jendela Temporary Query akan terbuka. Di bilah alat, pilih Instance Name Hologres Anda dan Database.
Muat ekstensi PostGIS.
Di editor, masukkan perintah SQL berikut, lalu klik Run.
```
CREATE extension IF NOT EXISTS postgis; -- Muat ekstensi PostGIS.
```
Buat tabel accommodations.
Jalankan pernyataan SQL berikut untuk membuat tabel accommodations. Tabel ini menyimpan informasi tentang akomodasi, seperti lokasi geografis (bujur dan lintang) serta nama daftar.
Catatan
Setelah tabel berhasil dibuat, klik ikon di sebelah kanan Table Catalog di sisi kiri jendela kueri, lalu klik public > Table untuk melihat hasilnya. Alternatifnya, Anda dapat memeriksa Running Log untuk memastikan bahwa tabel telah 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 yang menyimpan kode pos untuk Berlin.
Catatan
Setelah tabel berhasil dibuat, klik ikon di sebelah kanan Table Catalog di sisi kiri jendela kueri, lalu klik public > Table untuk melihat hasilnya. Alternatifnya, Anda dapat memeriksa Running Log untuk memastikan bahwa tabel telah 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

Setelah tabel Hologres accommodations dan zipcodes dibuat, impor data spasial sampel yang diunduh dari mesin lokal Anda ke dua tabel tersebut melalui halaman Import On-premises File.

Di Konsol HoloWeb, klik Data Solution di bilah navigasi atas.
Pada tab Data Solution, klik Import On-premises File di panel navigasi kiri. Kemudian, klik New data import di halaman ini.
Tentukan tabel Hologres tempat Anda ingin mengimpor data.
Pada langkah Pilih Tabel Target dalam kotak dialog Import On-premises File, masukkan nama pekerjaan, pilih instans Hologres, database yang telah dibuat, serta tabel Hologres yang tersedia (accommodations atau zipcodes). Klik Next Step.

Tentukan data yang akan diimpor beserta format encoding-nya.

Pada langkah Select File, konfigurasikan parameter sesuai tabel berikut, lalu klik Langkah Berikutnya. 需要导入的数据信息

Parameter	Deskripsi Parameter
Pilih File	File yang berisi data yang akan diimpor. Klik Browse... dan pilih file dari mesin lokal Anda. File .txt, .csv, dan .log didukung. Dalam contoh ini, pilih tabel accommodations atau zipcodes yang Anda unduh.
Pemisah	Pemisah yang digunakan untuk memisahkan entri data. Dalam contoh ini, pilih SEMIKOLON. Catatan Anda juga dapat memilih opsi di sebelah kanan daftar drop-down dan menentukan pemisah kustom sesuai kebutuhan.
Encoding Asli	Format encoding data. Dalam contoh ini, pilih UTF-8.
Baris Pertama sebagai 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 langkah Ikhtisar Impor, periksa apakah konfigurasi pekerjaan impor data sudah sesuai harapan. Jika sudah, klik Execution.
Verifikasi hasil eksekusi.
Setelah pekerjaan selesai, sistem akan menampilkan status eksekusi di langkah Ikhtisar Impor. Jika eksekusi gagal, tinjau penyebab kesalahan untuk pemecahan masalah dan ulangi proses impor.
Anda juga dapat menjalankan pernyataan SQL di Editor SQL untuk memeriksa jumlah entri data atau menanyakan detail data pada tabel Hologres tujuan:
- Periksa jumlah entri data
  Dalam contoh ini, tabel Hologres accommodations berisi 22.248 entri data, sedangkan tabel Hologres zipcodes berisi 190 entri data.
```
SELECT COUNT(*) FROM accommodations; -- Menanyakan jumlah catatan di tabel accommodations.
SELECT COUNT(*) FROM zipcode; -- Menanyakan jumlah catatan di tabel zipcode.
```
- Menanyakan data rinci
```
SELECT * FROM accommodations; -- Menanyakan catatan di tabel accommodations.
SELECT * FROM zipcode; -- Menanyakan catatan di tabel zipcode.
```

Langkah 3: Gunakan fungsi spasial untuk menanyakan data

Setelah tabel Hologres yang diperlukan dibuat dan data spasial sampel diimpor ke dalam tabel, Anda dapat menggunakan fungsi spasial untuk meminta data spasial di Hologres. Berikut ini adalah contoh untuk referensi. Untuk informasi lebih lanjut tentang sintaksis fungsi spasial, lihat PostGIS untuk Analisis Informasi Geografis.

Meminta jumlah entri data di tabel Hologres accommodations dengan pengenal sistem referensi spasial (SRID) disetel ke 4326.
- Kode sampel:
```
SELECT COUNT(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
```
- Hasil yang dikembalikan:
```
 count 
-------
 22248
(1 row)
```

Gunakan format teks terkenal (WKT) untuk meminta objek geometri yang memenuhi kondisi tertentu. Dalam contoh ini, Anda dapat memeriksa apakah kode pos di tabel Hologres zipcodes disimpan dalam Sistem Geodetik Dunia 1984 (WGS84). Sistem ini menggunakan SRID 4326.

Catatan

Hanya entri data spasial yang berada dalam sistem referensi spasial yang sama yang dapat dirujuk 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 meminta permukaan, ukuran permukaan, dan jumlah titik pada permukaan untuk Mitte di Berlin dengan SRID disetel 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

Meminta jumlah akomodasi dalam radius 500 meter dari Gerbang Brandenburg dengan SRID disetel 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 estimasi kasar lokasi Gerbang Brandenburg berdasarkan informasi tentang akomodasi terdekat.

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

Meminta informasi rinci tentang semua akomodasi di sekitar Gerbang Brandenburg dan urutkan akomodasi 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:

                        nama                        | harga |                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 baris)

Meminta informasi rinci tentang akomodasi dengan harga tertinggi dan kode posnya.

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

Meminta akomodasi populer di Berlin, kelompokkan akomodasi berdasarkan kode pos, lalu urutkan kelompok 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)