全部產品
Search
文件中心

Hologres:使用空間函數查詢資料方法

更新時間:Jun 30, 2024

Hologres支援使用PostGIS的空間函數查詢包含空間資料的表。本文以匯入本機資料至Hologres並使用空間函數查詢資料為例,為您介紹在Hologres中使用PostGIS空間函數的操作方法。

前提條件

背景資訊

PostGIS是資料庫PostgreSQL的空間擴充,PostGIS可以提供空間對象、空間索引、空間操作函數和空間操作符等空間資訊服務功能。

本文為您提供了樣本資料表,包含各類空間資訊(經度、緯度、座標、距離等)。您可通過如下操作使用Hologres的HoloWeb,建立表並一鍵匯入本機資料至accommodations表和zipcodes表中,通過PostGIS的空間函數可查詢兩個表中所包含的空間資訊資料。

操作步驟概述

操作流程

描述

步驟一:建立表

在Hologres執行個體的資料庫中,建立accommodations表用於儲存住宿地的地理位置(經度和緯度)、列表名稱和其他資料。建立zipcodes表用於儲存柏林郵遞區號資料。

步驟二:匯入測試資料

通過HoloWeb一鍵匯入本機資料源至accommodations表和zipcodes表中。

步驟三:使用空間函數查詢資料

使用PostGIS的空間函數查詢表中的空間資料。

步驟一:建立表

根據如下步驟,在資料庫中建立accommodations表和zipcodes表。

  1. 登入HoloWeb控制台SQL編輯器頁面。

  2. 單擊新增SQL視窗,選擇已建立並登入的Hologres執行個體和資料庫。

  3. 載入PostGIS外掛程式。

    在命令編輯地區,輸入如下SQL命令,單擊運行

    create extension if not exists postgis; -- 載入PostGIS外掛程式
  4. 建立accommodations表。

    運行如下SQL語句,建立accommodations表,用於儲存住宿地的地理位置(經度和緯度)、列表名稱和其他資料。

    說明

    建立成功後,您可右擊左側表目錄下的public > ,單擊重新整理查看錶建立結果。或通過作業記錄,查看錶是否建立成功。

    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
    );
  5. 建立zipcodes表。

    運行如下SQL語句,建立zipcodes表,用於儲存柏林郵遞區號資料。

    說明

    建立成功後,您可右擊左側表目錄下的public > ,單擊重新整理查看錶建立結果。或通過作業記錄,查看錶是否建立成功。

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

步驟二:匯入測試資料

表建立成功後,您可通過HoloWeb的一鍵本地檔案匯入功能,向已建立成功的accommodations表和zipcodes表中匯入資料。

  1. HoloWeb控制台中,單擊頂部資料方案

  2. 資料方案頁面,單擊左側一鍵本地檔案匯入,在右側介面中單擊建立資料匯入

  3. 選擇需要上傳資料的表。

    在彈出的一鍵本地檔案上傳對話方塊,填寫作業名稱並選擇已建立的執行個體、資料庫和表(accommodations表或zipcodes表),單擊下一步選擇需要匯入資料的表。

  4. 選擇需要上傳的資料和格式。

    選擇資料來源表頁簽下,您可根據如下內容進行配置,配置完成後單擊下一步。需要匯入的資料資訊

    參數項

    參數項選擇描述

    選擇檔案

    單擊瀏覽,上傳需要匯入的資料表。僅支援尾碼為.txt、.csv、.log類型的檔案。請選擇前提條件中為您提供的accommodations資料表和zipcodes資料表。

    選擇分隔字元

    選擇資料的分割符,本樣本中選擇分號。

    說明

    您也可以根據自身資料的內容,單擊分隔字元右側的選項,使用自訂分隔字元。

    原始字元集

    本樣本中選擇UTF-8

    首行為標題

    預設為空白。如您匯入的資料表中的資料第一行為標題,請選中該選項。

  5. 確認需要匯入的資料資訊。

    在匯入總覽頁簽下,您可查看需要匯入的資訊是否正確,完成後單擊執行執行確認

  6. 查看資料匯入執行結果。

    執行完成後,系統會提示您匯入狀態是否成功。如執行失敗,系統將提示您失敗原因,您可根據原因進行修改後重新匯入資料。

    您也可以通過如下代碼,在SQL編輯器中查看資料的記錄數量以及表中的內容。

    • 查看錶中記錄數量

      accommodations表中有記錄數22248條,zipcodes表中有記錄數190條。

      select count(*) from accommodations; -- 查詢accommodations表中的記錄數量
      select count(*) from zipcode; -- 查詢zipcode表中的記錄數量
    • 查看錶中內容

      select * from accommodations; -- 查詢accommodations表中的記錄
      select * from zipcode; -- 查詢zipcode表中的記錄

步驟三:使用空間函數查詢資料

表資料建立並匯入成功後,您可根據需求使用空間函數查詢空間資料,部分查詢操作樣本如下。空間函數的文法詳情,請參見空間函數

  • 查詢accommodations表中的記錄數,其中SRID取值為4326。

    • 程式碼範例:

      SELECT count(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
    • 運行結果:

       count 
      -------
       22248
      (1 row)
  • 使用WKT格式查詢符合某些附加屬性的幾何體對象。您可以驗證此郵遞區號資料是否也儲存在WGS 84中,該系統使用SRID取值為4326。

    說明

    空間資料必須儲存在同一空間參照系中才能實現相互操作。

    • 程式碼範例:

      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格式查詢柏林米特(SIRD取值為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,...
  • 查詢索柏林中列出的住宿數的熱點,根據郵遞區號將熱點住宿進行分組,並按照供應量進行排序操作。

    • 程式碼範例:

      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)