Querying data using spatial functions - Hologres: Central de Documentação do Alibaba Cloud

No Hologres, você pode usar as funções espaciais do PostGIS para consultar dados espaciais em tabelas. Este tópico descreve como importar dados de uma máquina local para o Hologres e usar essas funções para consultar os dados.

Pré-requisitos

Você criou uma instância do Hologres. Para mais informações, consulte Purchase a Hologres instance.
Você criou um banco de dados na instância do Hologres. Para mais informações, consulte Create a database.
Você baixou os dados espaciais de exemplo usados neste tópico. Para baixar os dados, clique em nos links a seguir:
- tabela accommodations
- tabela zipcodes

Informações básicas

O PostGIS é uma extensão espacial do PostgreSQL que fornece recursos espaciais, incluindo objetos, índices, funções e operadores.

Este tópico oferece tabelas de dados de exemplo com diversos tipos de informações espaciais, como longitude, latitude, coordenadas e distância. Você usará o HoloWeb para criar tabelas, importar dados locais para as tabelas accommodations e zipcode e, em seguida, usar as funções espaciais do PostGIS para consultar os dados.

Procedimento

Etapa	Descrição
Step 1: Create Hologres tables	Em um banco de dados da sua instância do Hologres, crie a tabela accommodations para armazenar dados de hospedagem, como localizações geográficas (longitude e latitude) e nomes dos anúncios. Crie também a tabela zipcode para armazenar dados de códigos postais de Berlim.
Step 2: Import the sample spatial data	Use o HoloWeb para importar arquivos de dados locais para as tabelas accommodations e zipcode.
Step 3: Use spatial functions to query data	Use funções espaciais do PostGIS para consultar os dados espaciais nas duas tabelas do Hologres.

Etapa 1: Criar tabelas no Hologres

Crie as tabelas accommodations e zipcode no seu banco de dados.

Faça login no Editor SQL do HoloWeb.
Clique em no ícone abaixo da aba SQL Editor para abrir a janela Ad-hoc Query. Na barra de ferramentas da janela de consulta, selecione o Instance Name e o Database do seu Hologres.
Ative a extensão PostGIS.
No editor SQL, insira a instrução a seguir e clique em Run.
```
CREATE extension IF NOT EXISTS postgis; -- Enable the PostGIS extension.
```
Crie a tabela accommodations.
Execute a seguinte instrução SQL para criar a tabela accommodations. Esta tabela armazena dados de hospedagem, como localizações geográficas (longitude e latitude), nomes dos anúncios e outros detalhes.

Nota
Após criar a tabela, verifique o resultado. No painel Table Directory à esquerda, clique em no ícone de atualização e acesse public > Table. Alternativamente, consulte os Operational Logs para confirmar se a criação da tabela foi bem-sucedida.
```
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
);
```
Crie a tabela zipcode.
Execute a instrução SQL a seguir para criar a tabela zipcode, destinada a armazenar dados de códigos postais de Berlim.

Nota
Após criar a tabela, verifique o resultado. No painel Table Directory à esquerda, clique em no ícone de atualização e acesse public > Table. Alternativamente, consulte os Operational Logs para confirmar se a criação da tabela foi bem-sucedida.
```
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)
 );
```

Etapa 2: Importar os dados espaciais de exemplo

Use o recurso Import On-premises File no HoloWeb para importar dados para as tabelas accommodations e zipcode.

No console do HoloWeb, clique em Data Solutions na barra de navegação superior.
Na página Data Solutions, clique em Import On-premises File no painel de navegação à esquerda e, em seguida, clique em New Data Import à direita.
Especifique a tabela do Hologres para a qual deseja importar os dados.

Na caixa de diálogo Import On-premises File, insira um nome para o job e selecione sua instância, banco de dados e tabela (accommodations ou zipcode). Em seguida, clique em Next.

Defina os dados a serem importados e o formato de codificação.

Na aba Upload File, configure os parâmetros a seguir e clique em Next.

Parâmetro	Descrição
Select File	Clique em Browse para fazer upload do arquivo de dados a ser importado. Apenas arquivos com extensão .txt, .csv ou .log são suportados. Selecione o arquivo de dados accommodations ou zipcode que você baixou.
Delimiter	Delimitador usado para separar as entradas de dados. Neste exemplo, selecione SEMICOLON. Nota Você também pode selecionar a opção à direita da lista suspensa e especificar um delimitador personalizado conforme as necessidades do seu negócio.
Original Encoding	Para este exemplo, selecione UTF-8.
First Line as Header	Por padrão, esta opção não está selecionada. Se a primeira linha dos dados a serem importados servir como cabeçalho da tabela no Hologres, marque esta opção.

Confirme as configurações.

Na aba Import Overview, revise as informações de importação na página Upload. Esta página exibe um resumo que inclui o nome do job, banco de dados de destino, schema e tabela. Após confirmar que as informações estão corretas, clique em Execute para iniciar a importação.
Verifique o resultado da execução.
Ao concluir o job, o sistema indica se a execução foi bem-sucedida na etapa Import Overview. Se a execução falhar, visualize a causa do erro para solução de problemas e importe os dados novamente.
Você também pode executar instruções SQL no editor SQL para consultar a quantidade de entradas de dados ou os dados detalhados na tabela de destino do Hologres:
- Verificar a quantidade de entradas de dados
  
  A tabela accommodations contém 22.248 linhas e a tabela zipcode contém 190 linhas.
  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.
- Consultar os dados detalhados
  SELECT * FROM accommodations; -- Query the rows in the accommodations table. SELECT * FROM zipcode; -- Query the rows in the zipcode table.

Etapa 3: Usar funções espaciais para consultar dados

Depois de criar as tabelas necessárias no Hologres e importar os dados espaciais de exemplo, use funções espaciais para consultar os dados espaciais no Hologres. Os exemplos a seguir servem como referência. Para informações sobre a sintaxe das funções espaciais, consulte PostGIS for geographic information analysis.

Consulte o número de linhas na tabela accommodations em que o Identificador do Sistema de Referência Espacial (SRID) é 4326.
- Código de exemplo:
```
SELECT COUNT(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;
```
- Resultado retornado:
```
 count 
-------
 22248
(1 row)
```

Use o formato well-known text (WKT) para consultar objetos geométricos que atendam às condições especificadas. Neste exemplo, verifique se os códigos postais na tabela zipcodes do Hologres estão armazenados no World Geodetic System 1984 (WGS84). O sistema usa um SRID de 4326.

Nota

Apenas entradas de dados espaciais que pertencem ao mesmo sistema de referência espacial podem ser referenciadas entre si.

Código de exemplo:

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

Resultado retornado:

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)

Use o formato GeoJSON para consultar a superfície, o tamanho da superfície e o número de pontos na superfície para Mitte, em Berlim, com o SRID definido como 10117.

Código de exemplo:

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

Resultado retornado:

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

Consulte a quantidade de acomodações a menos de 500 metros do Portão de Brandemburgo, com o SRID definido como 4326.

Código de exemplo:

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

Resultado retornado:
```
 count 
-------
    29
(1 row)
```

Faça uma estimativa aproximada da localização do Portão de Brandemburgo com base nas informações sobre acomodações próximas.

Código de exemplo:

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
;

Resultado retornado:
```
 count 
-------
    60
(1 row)
```

Consulte as informações detalhadas de todas as acomodações ao redor do Portão de Brandemburgo e ordene-as por preço em ordem decrescente.

Código de exemplo:

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
;

Resultado retornado:

                        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)

Consulte as informações detalhadas da acomodação com o preço mais alto e seu código postal.

Código de exemplo:

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

Resultado retornado:

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

Consulte as acomodações populares em Berlim, agrupe-as por código postal e ordene os grupos pelo volume de pedidos.

Código de exemplo:

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
;

Resultado retornado:

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