Gunakan fungsi spasial untuk kueri data - Hologres

Hologres mendukung pengkuerian tabel yang berisi data spasial menggunakan fungsi spasial PostGIS. Topik ini menjelaskan cara mengimpor data lokal ke Hologres dan menggunakan fungsi spasial untuk mengkueri data tersebut.

Prasyarat

Anda telah membuat instans Hologres. Untuk informasi selengkapnya, lihat Beli instans Hologres.
Anda telah membuat database di instans Hologres Anda. Untuk informasi selengkapnya, lihat Buat database.
Anda telah mengunduh tabel data sampel yang disediakan dalam contoh ini. Gunakan tautan berikut untuk mengunduh file:
- tabel data accommodations
- tabel data zipcodes

Informasi latar belakang

PostGIS adalah ekstensi spasial untuk database PostgreSQL yang menyediakan objek spasial, indeks spasial, fungsi spasial, dan operator spasial untuk layanan informasi geografis.

Topik ini menyediakan tabel data sampel yang mencakup informasi spasial seperti bujur, lintang, koordinat, dan jarak. Anda dapat menggunakan HoloWeb di Hologres untuk membuat tabel dan mengimpor data lokal ke tabel accommodations dan zipcodes dengan satu klik. Selanjutnya, gunakan fungsi spasial PostGIS untuk mengkueri data spasial dalam tabel-tabel tersebut.

Ikhtisar prosedur

Prosedur	Deskripsi
Langkah 1: Buat tabel	Buat tabel accommodations di database Hologres Anda untuk menyimpan lokasi akomodasi (bujur dan lintang), nama listing, dan data lainnya. Buat tabel zipcodes untuk menyimpan data kode pos Berlin.
Langkah 2: Impor data uji	Gunakan HoloWeb untuk mengimpor data lokal ke tabel accommodations dan zipcodes dengan satu klik.
Langkah 3: Kueri data menggunakan fungsi spasial	Gunakan fungsi spasial PostGIS untuk mengkueri data spasial dalam tabel.

Langkah 1: Buat tabel

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

Masuk ke halaman HoloWeb console 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 dari instans Hologres yang telah Anda buat dan masuki.
Muat ekstensi PostGIS.
Masukkan perintah 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 guna menyimpan lokasi akomodasi (bujur dan lintang), nama listing, dan data lainnya.

Catatan

Setelah tabel dibuat, klik ikon di sebelah kanan Table Directory di sisi kiri jendela kueri, lalu klik public > Table untuk melihat hasil pembuatan. Atau, periksa 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 zipcodes.
Jalankan pernyataan SQL berikut untuk membuat tabel zipcodes guna menyimpan data kode pos Berlin.

Catatan
Setelah tabel dibuat, klik ikon di sebelah kanan Table Directory di sisi kiri jendela kueri, lalu klik public > Table untuk melihat hasil pembuatan. Atau, periksa 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 uji

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

Di HoloWeb console, klik Data Solutions di bagian atas.
Pada halaman Data Solutions, klik Import On-premises File di sebelah kiri. Di sisi kanan halaman, klik New Data Import.
Pilih tabel tempat mengunggah data.

Pada kotak dialog Import On-premises File, masukkan nama pekerjaan dan pilih instans, database, serta tabel Anda (accommodations atau zipcodes). Klik Next.

Pilih file data dan formatnya.

Pada tab Upload File, konfigurasikan pengaturan berikut lalu klik Next. 需要导入的数据信息

Parameter	Deskripsi parameter
Select file	Klik Browse untuk mengunggah file data. Hanya file berekstensi .txt, .csv, atau .log yang didukung. Gunakan tabel data accommodations dan zipcodes yang disediakan dalam prasyarat.
Select separator	Pilih pemisah data. Dalam contoh ini, pilih semicolon. Catatan Anda juga dapat mengklik opsi di samping kolom pemisah untuk menggunakan pemisah kustom sesuai data Anda.
Source character set	Dalam contoh ini, pilih UTF-8.
First row as header	Secara default, opsi ini tidak dipilih. Jika baris pertama file data Anda berisi header, pilih opsi ini.

Konfirmasi informasi impor data.

Pada tab Import Overview, pastikan informasinya benar, lalu klik Upload.
Lihat hasil impor data.
Setelah dieksekusi, sistem akan menampilkan status keberhasilan impor. Jika gagal, sistem akan menunjukkan alasannya. Perbaiki masalah tersebut dan impor ulang data.
Anda juga dapat menjalankan kode berikut di SQL Editor untuk memeriksa jumlah catatan dan melihat isi tabel.
- Periksa jumlah catatan
  
  Tabel accommodations berisi 22.248 catatan. Tabel zipcodes 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
```
- Lihat isi tabel
```
SELECT * FROM accommodations; -- Query records in the accommodations table
SELECT * FROM zipcode; -- Query records in the zipcode table
```

Langkah 3: Kueri data menggunakan fungsi spasial

Setelah berhasil membuat dan mengimpor data ke tabel, gunakan fungsi spasial untuk mengkueri data spasial sesuai kebutuhan. Contoh berikut menunjukkan operasi kueri umum. Untuk detail sintaks fungsi spasial, lihat Fungsi spasial.

Kueri jumlah catatan dalam tabel accommodations yang memiliki SRID 4326.

Contoh kode:

SELECT COUNT(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;

Hasil:
```
 count 
-------
 22248
(1 row)
```

Gunakan format WKT untuk mengkueri objek geometri dengan atribut tertentu. Verifikasi bahwa data kode pos juga disimpan dalam WGS 84, yang menggunakan SRID 4326.

Catatan

Data spasial harus menggunakan sistem referensi spasial yang sama agar dapat saling beroperasi.

Contoh kode:

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

Hasil:

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 poligon Berlin Mitte (kode pos 10117), dimensinya, dan jumlah titik dalam poligon tersebut.

Contoh kode:

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:

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 (SRID 4326).

Contoh kode:

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

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

Dapatkan perkiraan lokasi Gerbang Brandenburg dari akomodasi terdekat.

Contoh kode:

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:
```
 count 
-------
    60
(1 row)
```

Kueri informasi lengkap semua akomodasi di dekat Gerbang Brandenburg, diurutkan berdasarkan harga secara menurun.

Contoh kode:

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:

                        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 akomodasi termahal beserta kode posnya.

Contoh kode:

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:

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

Identifikasi titik panas akomodasi yang terdaftar di SoBerlin. Kelompokkan titik panas berdasarkan kode pos dan urutkan berdasarkan pasokan.

Contoh kode:

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:

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