Gerencie Dados Espaciais com o IBM DB2 Spatial Extender, Parte 1: Adquirindo Dados Espaciais e Desenvolvendo Aplicativos

Dicas e técnicas para desenvolver aplicativos espaciais eficientes

Essa série de tutoriais descreve tarefas comuns para gerenciar dados espaciais com o IBM DB2 ® Spatial Extender, incluindo a importação e criação de dados espaciais, construção e execução de consultas espaciais, trabalhando com a IBM, terceiros e ferramentas espaciais de software livre, ajuste de desempenho e consideração de circunstâncias especiais em um ambiente de data warehouse. Neste primeiro artigo da série, saiba como adquirir dados espaciais e aplicativos de desenvolvimento e como usar arquivos de formato, tabelas de dados espaciais e índices espaciais.

David Adler, Senior Software Engineer, IBM China

David AdlerDavid Adler é responsável pelo desenvolvimento de tecnologia de banco de dados espaciais na IBM há mais de 20 anos — nos últimos 10 anos, trabalha no DB2 Spatial Extender Development.



08/Mar/2012

Antes de iniciar

Saiba o que esperar desse tutorial e como tirar o máximo proveito dele.

Sobre esta série

Há mais de 10 anos, o DB2 Spatial Extender oferece o recurso de gerenciamento e análise de dados espaciais armazenados em tabelas relacionais do DB2. As colunas da tabela podem ser definidas com tipos espaciais para pontos, linhas e polígonos, que podem representar objetos como os exemplos a seguir:

Locais de pontos
Clientes, lojas varejistas, transformadores elétricos, torres de celular
Linhas
Rodovias, linha costeira, rotas de entrega, linhas de transmissão de eletricidade
Polígonos
Territórios de vendas/serviços, risco de alagamento/incêndio, estados/províncias/países

As consultas SQL podem incorporar funções espaciais para analisar relacionamentos espaciais, como encontrar clientes que estejam dentro de uma zona de inundação. Lista 1 mostra um exemplo de código que incorpora funções espaciais.

Lista 1. Consulta que incorpora funções espaciais
SELECT cust_name, cust_addr
FROM customers, floodzones
WHERE ST_Within(cust_loc, flood_loc) = 1

O resultado dessa consulta produz um conjunto de nomes e endereços de clientes cujas casas podem correr o risco de sofrerem com uma inundação. Essas informações são úteis no cálculo de taxas de seguro ou para o envio de uma oferta de compra de seguro contra inundações.

Essa série de tutoriais conduz você pelas tarefas comuns quando se trabalha com dados espaciais no DB2 Spatial Extender. Isso inclui a importação e criação de dados espaciais, construção e execução de consultas espaciais, trabalhando com a IBM, terceiros e ferramentas espaciais de software livre, ajuste de desempenho e consideração de circunstâncias especiais em um ambiente de data warehouse.

Embora o foco desta série de tutoriais esteja no DB2 Spatial Extender em sistemas operacionais Linux®, UNIX® e Windows® , muitos dos conceitos são igualmente aplicáveis a outras ofertas de banco de dados IBM com recursos espaciais, incluindo o seguinte:

  • Spatial Support for DB2 for z/OS®
  • Informix® Spatial Datablade
  • Netezza® Spatial

Tutoriais nessa série

Os tutoriais adicionais a seguir são planejados para esta série de 5 partes.

Parte 2: Implementando casos de uso espacial típicos
Há um determinado número de casos de uso típicos de aplicação espacial, que incluem junções espaciais, consultas à distância, delimitação geográfica e consultas mais próximas. Esse tutorial fornece exemplos de implementação desses casos de uso de modo eficiente.
Parte 3: Ajuste de aplicativo espacial
O desempenho do aplicativo é sempre uma consideração importante. Esse tutorial observa os fatores de desempenho do aplicativo espacial e as ferramentas para a compreensão do comportamento da consulta. Alguns dos fatores considerados incluem dados sequenciais, armazenamento em cluster de dados, compactação e generalização.
Parte 4: Análise espacial em um ambiente de data warehouse
Alcançar uma boa escalabilidade em um ambiente não compartilhado do InfoSphere Warehouse requer considerações adicionais. Esse tutorial aborda as técnicas para conseguir uma boa colocação de consulta espacial. Ele também fornece considerações especiais ao trabalhar com o middleware Esri.
Parte 5: Ferramentas de visualização espacial da IBM, terceiros e software livre
A visualização de dados espaciais é, geralmente, um aspecto importante de um aplicativo espacial. Esse tutorial apresenta algumas das ferramentas que estão disponíveis, incluindo o IBM Geobrowser livre, o software Esri, os serviços de mapeamento da web, como o Google Maps e software Open Source, que usa o driver GeoTools para DB2.

Sobre este tutorial

Esse tutorial apresenta a tecnologia fornecida pelo DB2 Spatial Extender. Ele também oferece abordagens para preencher tabelas do DB2 com dados espaciais: uma etapa crítica antes de as consultas espaciais poderem ser implementadas. E aborda ambientes para a execução de consultas espaciais, incluindo o processador da linha de comandos do DB2, o IBM Data Studio. Os ambientes são integrados em um aplicativo usando JDBC, CLI e outras interfaces.

Objetivos

Ao terminar este tutorial, será possível:

  • Compreender conceitos espaciais importantes com relação aos tipos espaciais e aos sistemas de coordenadas
  • Conhecer abordagens para importação e criação de dados espaciais
  • Compreender a indexação espacial
  • Estar familiarizado com os ambientes para a execução de consultas espaciais

Pré-requisitos

Esse tutorial tem como alvo arquitetos e desenvolvedores de aplicativos que possuem um conhecimento de trabalho de DB2 e da linguagem SQL.

Faça o download dos scripts SQL de amostra, aplicativos Java e dados de amostra a partir da seção Downloads para um diretório conveniente e siga os exemplos apresentados neste tutorial.

Requisitos do sistema

Para trabalhar com as etapas do tutorial, é necessário um ambiente de trabalho DB2 com o Spatial Extender instalado e configurado. Consulte Recursos para obter informações sobre como configurar este ambiente.


Introdução

Visão geral do DB2 Spatial Extender

Instalar e configurar o DB2 Spatial Extender fornece as seguintes características e componentes principais:

Tipos de dados espaciais
Um conjunto de tipos de dados que podem ser usados para definir as colunas da tabela que conterá dados espaciais. Isto inclui ST_Point, ST_Linestring, e ST_Polygon para valores espaciais atômicos. Inclui também ST_MultiPoint, ST_MultiLinestring e ST_MultiPolygon para coleções homogêneas de valores espaciais.
Funções e predicados espaciais
Um grande número de UDFs de SQL para criar valores espaciais, retornar informações sobre valores espaciais, identificar relacionamentos espaciais e realizar operações sobre valores espaciais. Estas UDFs espaciais podem ser incorporadas em consultas SQL, que exploram todos os recursos da linguagem SQL.
Índice espacial
É fornecido um mecanismo de índice espacial para suportar a natureza bidimensional dos dados espaciais. Estão disponíveis ferramentas para ajudar a especificar um índice espacial.
Processador de linha de comandos (CLP) espacial - db2se
O CLP db2se fornece uma interface conveniente da linha de comando para procedimentos espaciais armazenados para operações como ativar espacialmente um banco de dados e importar ou exportar dados espaciais.

Consulte Recursos para obter mais detalhes sobre o Spatial Extender no Centro de Informações DB2.

Sistemas de referência espaciais e sistemas de coordenadas

Os dados espaciais são representados geralmente por tuplas de valores de coordenadas, os mais frequentes são x, y, embora seja fornecido suporte para as coordenadas z e m também. Este tutorial considera apenas dados com valores de coordenada x e y . Ele também considerará os dados que usam apenas os valores de latitude e longitude em graus como valores decimais. Note que quando se trabalha com latitude e longitude, longitude corresponde a x e latitude corresponde a y.

Para executar corretamente as operações espaciais, cada valor espacial deve possuir um sistema de coordenadas associado a ele, que descreva a relação entre os valores de coordenadas para uma posição sobre a superfície da Terra. Embora o Spatial Extender forneça mais de 3.000 sistemas diferentes de coordenadas pré-definidas, este tutorial aborda apenas os dois mais usados: NAD83 para coordenadas na América do Norte e WGS84 para coordenadas mundiais. Observe que a maioria dos dispositivos GPS relata coordenadas usando o WGS84.

Para armazenar e processar dados espaciais de maneira eficiente, o Spatial Extender representa coordenadas internamente como números inteiros de 64 bits. O Spatial Extender gerencia isso por meio do uso de um sistema de referência espacial (SRS), que especifica deslocamentos e um fator de escala usado para converter a representação do usuário em um valor DUPLO para frente e para trás a partir da representação interna. Cada SRS possui também um sistema de coordenadas associado a ele. Na verdade, é o SRS que está associado a cada valor espacial. Um SRS pode ser referido por seu nome ou valor com 128 caracteres (chamado srsName ouSRS_NAME) ou o seu identificador de número inteiro de 32 bits (chamado srid ou SRS_ID). O srid é usado em instruções SQL e o srsName é usado com o CLP db2se, na maioria das vezes, ao importar dados espaciais.

O Spatial Extender fornece o SRS chamado NAD83_SRS_1 com srid 1 para dados que usam o sistema de coordenadas NAD83. O Spatial Extender fornece o SRS chamado WGS84_SRS_1003 com srid 1003 para dados que usam o sistema de coordenadas WGS84. Consulte a documentação no centro de informações do DB2 se precisar trabalhar com um sistema de coordenadas diferente.


Importando dos arquivos de forma

Obtendo arquivos de forma

O padrão de-facto para armazenamento e troca de dados espaciais em um formato de arquivo é a representação do arquivo de forma criada pelo Esri. O Esri fornece arquivos de forma de dados de amostra para fazer o download e usar com o Spatial Extender (consulte Recursos), embora o arquivo de forma necessário para este tutorial esteja incluído em Downloads para a sua conveniência. Os arquivos de forma Esri representam os seguintes tipos de dados:

  • Fronteiras nacionais
  • Fronteiras estaduais
  • Fronteiras municipais
  • Fronteiras de CEP e centroides
  • Principais rodovias
  • Cidades, aeroportos, hospitais, escolas
  • Rios

É possível fazer o download de muitos outros arquivos de forma a partir de outras fontes. Não há custos para usar alguns arquivos de forma, enquanto alguns exigem uma taxa de licença.

Um arquivo de forma é, na verdade, uma coleção de arquivos relacionados que possuem o mesmo nome, mas extensões diferentes do arquivo. Os arquivos mais comuns, e os usados pelo Spatial Extender, incluem os tipos de arquivo a seguir:

<name>.shp
Este é um arquivo binário necessário que contém um cabeçalho descrevendo o tipo de dados (pontos, linhas, polígonos e outras geometrias), o número de registros e os dados de coordenadas reais.
<name>.dbf
Este é um arquivo binário necessário que contém o caractere e os atributos numéricos associados a cada registro. Ele usa, de fato, o formato DBASE e pode ser visualizado com aplicativos que suportam esse formato. Há limitações significativas com este formato. Os nomes de atributos são limitados a 11 caracteres de comprimento e os dados de caractere não podem ser maiores que 255 bytes.
<name>.prj
Este é um arquivo de texto opcional que contém a definição do sistema de coordenadas associado aos dados espaciais. O sistema de coordenadas no arquivo .prj deve corresponder ao sistema de coordenadas do SRS da coluna na qual os dados estão sendo carregados. Se este arquivo não estiver presente, o Spatial Extender assumirá que os dados estão no sistema de coordenadas do SRS especificado.
<name>.shx
Este é um arquivo binário opcional que fornece um índice para o arquivo .shp. O principal uso desse arquivo com o Spatial Extender é para iniciar a importação em um registro diferente do primeiro registro.

Você não é obrigado a conhecer os detalhes desses arquivos, mas se você estiver interessado, siga o link para obter a especificação de Esri na seção Recursos .

Obtendo detalhes sobre os arquivos de forma

É possível usar o comando db2se shape_info O comando do Spatial Extender para obter informações sobre um arquivo de forma em especial, incluindo as geometrias de tipo, as extensões geográficas dos dados e todos os nomes e tipos de atributos. Lista 2 fornece um exemplo do comando usado com o arquivo de forma counties de amostra fornecido com este tutorial (consulte Downloads). A lista de atributos em Lista 2 foi abreviada para poupar espaço.

Lista 2. Atributos de arquivo de forma
db2se shape_info -fileName counties.shp

Shape file information
----------------------
File code                  = 9994
File length (16-bit words) = 758872
Shape file version         = 1000
Shape type                 = 5 (ST_MULTIPOLYGON)
Number of records          = 3141

Minimum X coordinate = -178.217598
Maximum X coordinate = -66.969271
Minimum Y coordinate = 18.921786
Maximum Y coordinate = 71.406235

Shapes do not have Z coordinates.
Shapes do not have M coordinates.
Shape index file (extension .shx) is present.

Attribute file information
--------------------------
dBase file code                = 3
Date of last update            = 2002-02-04
Number of records              = 3141
Number of bytes in header      = 1569
Number of bytes in each record = 461
Number of columns              = 48

Column Number  Column Name      Data Type       Length  Decimal
-------------  ---------------  --------------  ------  -------
            1  NAME             C ( Character)      32        0
            2  STATE_NAME       C ( Character)      25        0
            3  STATE_FIPS       C ( Character)       2        0
...
           48  AVG_SALE97       N (   Numeric)       7        2

Coordinate system definition: "GEOGCS["GCS_North_American_1983",
DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137,298.257222101]],
PRIMEM["Greenwich",0],UNIT["Degree",0.0174532925199433]]"

Importando um arquivo de forma usando a abordagem mais simples

O comando db2se import_shape do Spatial Extender fornece uma maneira fácil de importar arquivos de forma. É possível gravar este comando em um arquivo de script SQL, como db2 -tvf import_counties.sql. Lista 3 mostra um exemplo simples.

Lista 3. Conteúdos do import_counties.sql
!erase counties.msg;

!db2se import_shape sample
-fileName         counties.shp
-srsName          nad83_srs_1
-tableSchema      test
-tableName        counties
-spatialColumn    shape
-client           1
-messagesFile     counties.msg
;

!db2se register_spatial_column sample
-tableSchema      test
-tableName        counties
-columnName       shape
-srsName          nad83_srs_1
;

connect to sample;

create index test.countiesidx on test.counties(shape)
extend using db2gse.spatial_index(.5, 2.0, 0);

runstats on table test.counties and indexes all;

select count(*) from test.counties;

describe table test.counties;

Observações:

  1. Os comandos de sistema operacional podem ser incluídos em um script de DB2 se eles forem prefixados por um ponto de exclamação.
  2. Se o arquivo de mensagens existir, ele é apagado com o comando erase counties.msg . A operação import_shape será finalizada se o arquivo de mensagens já existir.
  3. A maioria dos parâmetros do comando db2se import_shape é bastante intuitiva. A tabela com o esquema e o nome especificados é criada com colunas de atributos correspondentes aos atributos do arquivo de forma. Se a tabela já existir, a operação será finalizada. O nome da coluna espacial é arbitrário, mas o shape é geralmente usado.
  4. O parâmetro -client 1 é importante. Este comando executa a operação de importação no cliente DB2, acessando o arquivo de importação de modo local. Se o parâmetro for definido como -client 0, a operação de importação será executada no servidor e exigirá a especificação de arquivo para ser um local no servidor que está disponível para o servidor DB2.
  5. O comando db2se register_spatial_column cria uma restrição que todos os valores espaciais usam o SRS nad83_srs_1. Ele também cria uma entrada na visualização db2gse.st_geometry_columns que associa este SRS a esta coluna espacial. Isto é útil e, por vezes, requerido por alguns aplicativos de visualização.
  6. O código connect to sample conecta-se ao banco de dados de amostras.
  7. Um índice espacial é criado com o comando create index e valores razoáveis. Os índices espaciais são discutidos posteriormente section.
  8. O comando runstats atualiza as estatísticas da tabela, que é sempre uma boa ideia após o carregamento ou após fazer atualizações significativas.
  9. O comando select count(*) permite que você reúna quantas linhas serão importadas.
  10. O comando describe table ajuda você a reunir as colunas da tabela criada.

Importar um arquivo de forma usando um exemplo um pouco mais complexo

Às vezes, é necessário implementar uma maior flexibilidade no processo de importação. Sobretudo, você pode querer importar em colunas de tipo de dados diferente, mas compatíveis, selecione um subconjunto de colunas para importar ou usar nomes de coluna diferentes dos nomes do atributo do arquivo de forma. Lista 4 ilustra esses recursos.

Lista 4. Conteúdos do import_counties2.sql
!erase counties.msg;
connect to sample;

drop table test.counties;

create table test.counties(
   county_name    varchar(32)
  ,state_name     varchar(25)
  ,state_fips     varchar(2)
  ,county_fips    varchar(3)
  ,area           double
  ,population2000 integer
  ,shape          db2gse.st_multipolygon
  )
  ;

!db2se import_shape sample
-fileName         counties.shp
-inputAttrColumns N(name,state_name,state_fips,cnty_fips,area,pop2000)
-srsName          nad83_srs_1
-tableSchema      test
-tableName        counties
-tableAttrColumns county_name,state_name,state_fips,county_fips,area,population2000
-createTableFlag  0
-spatialColumn    shape
-typeSchema       db2gse
-typeName         st_multipolygon
-messagesFile     counties.msg
-client 1
;

!db2se register_spatial_column sample
-tableSchema      test
-tableName        counties
-columnName       shape
-srsName          nad83_srs_1
;

create index test.countiesidx on test.counties(shape)
extend using db2gse.spatial_index(.5, 2.0, 0);

runstats on table test.counties and indexes all;

select count(*) from test.counties;

describe table test.counties;

Observações:

  1. A tabela é criada com as colunas, tipos de dados e nomes da coluna desejados.
  2. O parâmetro -inputAttrColumns especifica os nomes dos atributos no arquivo de forma a ser importado.
  3. O parâmetro -tableAttrColumns especifica os nomes das colunas da tabela nas quais importar dados. Note que os nomes -inputAttrColumns e -tableAttrColumns são diferentes.
  4. O parâmetro -createTableFlag indica que a tabela já existe e não deve ser criada.
  5. Os parâmetros -typeSchema e -typeName especificam o tipo da coluna espacial. Uma das peculiaridades do formato de arquivo de forma é que ele não especifica se o tipo espacial é uma coleção (multi), portanto, import_shape é padrão para o tipo multi. Para o exemplo, a especificação do tipo multi não é necessária, pois os polígonos de divisão são, na verdade, Multipolígonos. Porém, para alguns aplicativos, os dados a serem importados não são de um tipo multi e podem ser importados em uma coluna não multi.

Criando uma tabela espacial a partir das coordenadas de ponto

Compreendendo as localizações do ponto

Muitos dos dados espaciais importantes na análise são baseados nas localizações do ponto, incluindo o seguinte:

  • Localização da casa do cliente
  • Localização do telefone celular do cliente
  • Localizações de armazenamento
  • Localizações de serviço
  • Localizações de torre de telefone celular
  • Hospitais

A representação mais comum de locais de ponto usa graus de latitude e longitude, que são derivados de um processo de codificação geográfica , de um GPS ou, possivelmente, de coordenadas em um mapa. A codificação geográfica é o processo de conversão de um endereço de rua em um valor de latitude e longitude. Isso é feito geralmente por um software especializado usando um grande conjunto de dados de endereços ou de segmentos de ruas. Muitas vezes, isto é feito em um modo em lotes para lidar com um grande número de endereços, mas também é feito, muitas vezes, por um serviço da web para codificar geograficamente um endereço inserido por um usuário. Muitas empresas já possuem este software instalado e possuem as localizações em um arquivo ou em colunas de banco de dados. Consulte Recursos para obter um artigo do developerWorks com mais detalhes sobre a codificação geográfica com o DB2 Spatial Extender.

Carregando e mantendo valores espaciais

Esta seção do tutorial discute o carregamento e a manutenção de valores espaciais em uma tabela, supondo que a latitude e a longitude já estão disponíveis. Este exemplo aborda o gerenciamento de uma tabela de localizações de hospital.

Consulte Downloads para fazer o download dos dados e scripts SQL usados nesta seção.

Importando e criando a partir de um arquivo

Lista 5 mostra um exemplo que pode ser executado com o comando db2 -tvf create_hospitals.sql

Lista 5. Contents of create_hospitals.sql
drop table test.hospitals;

create table test.hospitals(name varchar(70), longitude double, latitude double);

import from "hospitals.csv" of del
modified by coldel, decpt.
method p (1, 2, 3)
messages "import.msg"
insert into test.hospitals (name, longitude, latitude);

alter table test.hospitals add column location db2gse.st_point;

!db2se register_spatial_column sample
-tableSchema      test
-tableName        hospitals
-columnName       location
-srsName          nad83_srs_1
;

update test.hospitals
set location = db2gse.st_point(longitude, latitude,1)
;

reorg table test.hospitals;

create index test.hospitalsidx on test.hospitals(location)
extend using db2gse.spatial_index(.1, 0, 0);

runstats on table test.hospitals and indexes all;

select count(*) from test.hospitals;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

Observações:

  1. Descarte a tabela caso ela já exista. Faça isso apenas para propósitos de teste.
  2. A tabela de hospital é criada com colunas para o nome, longitude e latitude.
  3. O utilitário importar do DB2 é usado para preencher a tabela de um arquivo contendo campos delimitados com os dados para o nome, longitude e latitude.
  4. A tabela é alterada para incluir uma coluna de tipo db2gse.ST_Point. Se a tabela já existia com a latitude e longitude, as etapas anteriores teriam sido ignoradas.
  5. O comando db2se register_spatial_column cria uma restrição que todos os valores espaciais usam o SRS nad83_srs_1. Ele também cria uma entrada na visualização db2gse.st_geometry_columns que associa este SRS a esta coluna espacial.
  6. O trabalho de criar o valor espacial é feito de maneira simples com uma instrução de atualização SQL que configura a coluna de localização para o resultado do construtor ST_Point, que aceita valores de longitude, latitude e srid. Ele é importante para notar que o valor de longitude vem primeiro, o oposto da ordenação que você geralmente vê. O valor srid de 1 indica que o sistema de referência espacial para o NAD83 deve ser usado.
  7. O utilitário reorg do DB2 é chamado à medida que o processo de atualização da coluna de localização aumenta o tamanho da linha e provavelmente causará estouros na página.
  8. Um índice espacial é criado com valores razoáveis.
  9. O comando runstats é executado para atualizar as estatísticas da tabela. É sempre bom fazer isso após carregar dados ou após fazer atualizações de dados significativas.
  10. O número de linhas é consultado e são selecionadas algumas linhas para hospitais de Nova York. A função espacial db2gse.ST_AsText é usada para retornar uma representação legível do valor espacial.

Fazendo a manutenção de valores espaciais

É muito provável que uma tabela de localizações tenha atualizações, e é importante garantir que os valores espaciais sejam definidos ou atualizados apropriadamente à medida que as linhas são inseridas ou atualizadas. O mecanismo SQL usado para fazer isso é estabelecer acionadores na tabela quando os valores de latitude ou longitude são alterados.

Lista 6 mostra um exemplo de criação de acionadores e teste de consequências.

Lista 6. Conteúdos de create_hospitals_triggers.sql
create trigger test.hosp_loc_update no cascade
before update of latitude, longitude
on test.hospitals
referencing  new as n
for each row mode db2sql
set n.location = db2gse.st_point(n.longitude, n.latitude, 1)
;

create trigger test.hosp_loc_insert no cascade
before insert
on test.hospitals
referencing new as n
for each row mode db2sql
set n.location = db2gse.st_point(n.longitude, n.latitude, 1)
;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

insert into test.hospitals(name, longitude, latitude)
values('New York2', -76.5, -42.3)
;

update test.hospitals
set (longitude, latitude) = (-76.5, -42.3)
where name = 'New York Hospital'
;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

Observações:

  1. Dois acionadores separados, mas muito semelhantes, são criados: um para atualização e um para inserção. Antes dos dados de linha serem inseridos ou atualizados, o construtor espacial é chamado para retornar o valor ST_Point, que será armazenado na coluna de localização.
  2. Para ver o efeito dos acionadores, as linhas a serem modificadas são selecionadas, conforme mostrado em Lista 7.
Lista 7. Linhas a serem modificadas
NAME                      LONGITUDE  LATITUDE   LOCATION_WKT
------------------------- ---------- ---------- ------------------------------
New York Hospital           -73.9537    40.7645 POINT (-73.953751 40.764542)
New York Hospital           -73.7537    41.0275 POINT (-73.753746 41.027599)
New York State Hospital     -73.9517    41.5273 POINT (-73.951797 41.527317)
  3 record(s) selected.
  1. Uma nova linha é inserida e as linhas para o Hospital de Nova York são atualizadas. É possível visualizar os valores de localização atualizados, conforme mostrado em Lista 8.
Lista 8. Valores de localização atualizados
.
NAME                      LONGITUDE  LATITUDE   LOCATION_WKT
------------------------- ---------- ---------- ------------------------------
New York Hospital           -76.5000   -42.3000 POINT (-76.500000 -42.300000)
New York Hospital           -76.5000   -42.3000 POINT (-76.500000 -42.300000)
New York State Hospital     -73.9517    41.5273 POINT (-73.951797 41.527317)
New York2                   -76.5000   -42.3000 POINT (-76.500000 -42.300000)
  4 record(s) selected.
  1. Para fins de legibilidade, a primeira parte do nome é selecionada, a longitude e latitude são convertidas em valores decimais e a coluna de localização é convertida para a representação well-known text (wkt).

Trabalhando com índices espaciais

Na maioria dos aplicativos de banco de dados é importante definir índices em colunas evitar a digitalização de todas as linhas de uma tabela e verificar cada linha, observando se ela atende aos critérios de uma consulta. Isso é especialmente importante em pesquisas espaciais, porque a verificação espacial é mais complexa do que a simples verificação de valores numéricos ou de caracteres.

Em um banco de dados relacional, um índice é criado, geralmente, sobre os valores em uma coluna, onde há uma sequência linear simples dos valores do menor para o maior. Isso permite uma árvore binária ou índice B-tree a ser criado, que permite que o banco de dados navegue rapidamente para um valor especial ou intervalo sequencial de valores.

Os dados espaciais são inerentemente bidimensionais (ou superiores), o que não é diretamente aplicável a um índice de árvore B, por isso têm sido desenvolvidas técnicas tão diferentes. A abordagem utilizada no Spatial Extender é um índice de grade, que associa valores espaciais a uma grade quadrada. Os valores espaciais de linha e polígono podem interceptar mais do que uma célula de grade, enquanto que um valor de ponto espacial pode se conectar, no máximo, com uma célula de grade. Ao definir os tamanhos das células de grade, é necessário substituir a necessidade de minimizar o número de células que cruzam um valor espacial com relação à necessidade de minimizar o número de valores espaciais contidos dentro de uma célula de grade. Para lidar eficientemente com valores espaciais com tamanhos muito diferentes, é possível especificar até três tamanhos de células de grade. As células de grade são, então, indexadas pelo Spatial Extender usando o índice de árvore B do DB2.

Um índice espacial é criado com o comando create index do DB2 com uma cláusula adicional extend using , como mostra a Lista 9.

Lista 9. Usando o comando de índice de criação
create index test.countiesidx on test.counties(shape)
extend using db2gse.spatial_index(1.2, 3.0, 14.0);

As unidades para especificar os tamanhos de células de grade são as mesmas unidades do sistema de coordenadas, que são graus de latitude e longitude para os dados de municípios. Como era esperado, os municípios possuem uma boa gama de tamanhos, por isso faz sentido especificar os tamanhos de células de grade diferentes que melhor se ajustam aos diferentes tamanhos de municípios.

Um grau de latitude é de aproximadamente 70 milhas ou 110 km. Um grau de longitude varia de aproximadamente 70 milhas no Equador a cerca de 45 milhas ou 72 km a 50 graus de latitude.

Determinando os tamanhos de célula de grade de índice espacial

Determinar um tamanho ideal de célula de grade ou tamanhos de células de grade pode não ser óbvio. Em geral, para os valores espaciais de linha e polígono, o tamanho da célula de grade deve ser um pouco maior que o tamanho que manteria uma linha de tamanho médio ou polígono. Para obter valores de ponto espaciais, o tamanho da célula de grade deve ser de cerca de 1/10 da largura de uma região de consulta típica.

Para simplificar esta determinação, são fornecidas duas ferramentas para analisar os valores espaciais em uma tabela e fornecer tamanhos de células de grade recomendados para serem usados ao criar o índice. Não se preocupe em obtê-los precisamente corretos, o desempenho será bom para uma variação bastante ampla nos parâmetros de índice.

Usando a ferramenta de Orientador de Índice gseidx

A ferramenta de linha de comando gseidx é fornecida com a instalação do Spatial Extender e está disponível em todas as plataformas suportadas. São fornecidas informações relevantes sobre índices espaciais e os detalhes de gseidx no centro de informações do DB2 na seção Usando índices e visualizações para acessar dados espaciais.

Lista 10 mostra um exemplo simples de uso do gseidx.

Lista 10. Conteúdos do advise_counties.sql
!gseidx "connect to sample
  get geometry statistics
  for column test.counties(shape)
  advise";

Este comando pode ser executado usando o comando db2 -tvf advise_counties.sql e resultará na saída mostrada em Lista 11.

Lista 11. Saída do comando db2 -tvf advise_counties.sql
Number of Rows: 3141
Number of non-empty Geometries: 3141
Number of empty Geometries: 0
Number of null values: 0

Extent covered by data:
    Minimum X: -178.217598
    Maximum X: -66.969271
    Minimum Y: 18.921786
    Maximum Y: 71.406235

Query Window Size:     Suggested Grid Sizes:           Index Entry Cost:
--------------------   -----------------------------   ----------------------
      0.01:              1.2,         3,        14             4.6
      0.02:              1.2,         3,        14             4.6
      0.05:              1.2,         3,        14             4.7
       0.1:              1.2,         3,        14             4.9
       0.2:              1.2,         3,        14             5.3
       0.5:              1.2,         3,        14             6.6
         1:              1.2,         3,        14             9.3
         2:              1.2,         3,        14              16
         5:              1.8,       3.6,        13              49
        10:              2.9,       8.7,        26             130
        20:              4.6,        14,        49             390
        50:               12,        72,         0            1700

As duas primeiras seções de Lista 11 fornecem estatísticas sobre os dados espaciais que foram analisados. As três colunas do meio da última seção fornecem tamanhos de grade sugeridos para usar na criação de um índice espacial.

A primeira coluna contém valores que correspondem aos tamanhos de janela típicos de consulta. No caso de um aplicativo que apresentará um mapa, esta é a largura típica da região que seria exibida. Por exemplo, se um usuário exibiria mais comumente um mapa com amplitude de 10 milhas, isto corresponderia a cerca de 0,25 grau. Olhando para a tabela, é possível ver que os tamanhos de grade sugeridos são os mesmos para os tamanhos de janela de consulta de 0,2 e 0,5 grau, de modo que você usaria (1,2, 3, 14) para criar o índice.

A última coluna não é tão importante. É uma estimativa do número de entradas do índice que seria referenciado para satisfazer uma consulta.

Orientador de Índice Java

Também é possível fazer o download de um Orientador de Índice espacial baseado em Java a partir do website do Spatial Extender. Esta ferramenta fornece uma interface com o usuário para selecionar uma tabela e coluna espacial para analisar. Consulte Recursos para obter informações sobre onde conseguir esta ferramenta.

Um exemplo da interface com o usuário para o Orientador de Índice Java é mostrado em Figura 1.

Figura 1. Interface com o usuário para o Orientador de Índice Java
Interface com o usuário para o Orientador de Índice Java

O algoritmo usado neste Orientador de Índice varia da ferramenta gseidx e resulta em recomendações diferentes. No entanto, os valores sugeridos funcionam da mesma maneira. O valor cost é o número estimado de páginas de índice que seriam referenciadas, não o número de entradas de índice, conforme relatado pela ferramenta gseidx.


Explorando problemas e dicas comuns

Simplificando a sintaxe da função espacial

Todas as funções espaciais são definidas no esquema db2gse, que deve ser prefixado para o nome da função para ser identificado corretamente pelo DB2. Há duas maneiras de simplificar a sintaxe espacial.

Verificando db2gse

A primeira maneira de simplificar a sintaxe espacial é fazer com que o DB2 suponha que ele deve verificar o esquema db2gse ao resolver funções. Isso é feito usando a função definir o caminho da função atual declaração antes de executar as consultas que envolvem funções espaciais. Isto permite que ele elimine o nome do esquema sobre as chamadas de função. Lista 12 mostra um exemplo.

Lista 12. Definir a declaração de caminho da função atual
set current function path = current function path, db2gse;
select
   substr(name, 1, 25) as name
  ,st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      DISTANCE
------------------------- ------------------------
Adventist Home              +2.42936545898570E+001
Greene County Memorial Ho   +2.27887983783443E+001
  2 record(s) selected.

Usando a notação de método

A segunda maneira para simplificar a sintaxe espacial é usar a notação do operador , que é suportada pelo DB2 LUW, embora isto não seja compatível com os outros produtos IBM com banco de dados espaciais. Usando a notação de método, é possível especificar o nome da função espacial após o valor espacial, conforme mostrado em Lista 13.

Lista 13. Usando a notação de método
set current function path = current function path, db2gse;
select
   substr(name, 1, 25) as name
  ,location..st_distance(st_point(-74.237449, 42.036976,1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      DISTANCE
------------------------- ------------------------
Adventist Home              +2.42936545898570E+001
Greene County Memorial Ho   +2.27887983783443E+001
  2 record(s) selected.

Nas seções a seguir, os exemplos não especificam o esquema, porque eles supõem que o caminho da função tenha sido definido anteriormente.

Ordenando as coordenadas

Ao trabalhar com coordenadas usando latitude e longitude, a longitude corresponde a x e a latitude corresponde a y. Quando as pessoas fornecem as coordenadas de localização ou usam um dispositivo, como um GPS, as coordenadas são geralmente na ordem latitude, longitude, portanto, é muito comum o usuário errar ao especificar as coordenadas na ordem inversa quando se trabalha com latitude e longitude.

Por exemplo, para encontrar hospitais em um raio de 20 quilômetros de Woodstock, NY (latitude 42,036976, -74,237449 longitude), a consulta seria escrita conforme mostrado em Lista 14.

Lista 14. Exemplo de coordenada
select name
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

Trabalhando com well-known text

O Spatial Extender suporta a representação de well-known text (WKT) de valores espaciais usados como constantes em pesquisas espaciais. A representação WKT é uma especificação internacional criada pelo Open Geospatial Consortium. Consulte a seção espacial do centro de informações do DB2 (consulte Recursos) para obter detalhes sobre o suporte do WKT no Spatial Extender.

Usando o WKT, a consulta de exemplo em Lista 14 poderia ser escrita conforme mostrado em Lista 15.

Lista 15. Exemplo de coordenada em WKT
select name
from test.hospitals
where st_distance(location,st_point('POINT (-74.237449 42.036976)',1), 'STATUTE MILE')
      < 25.0

Note que as coordenadas são separadas por um espaço, em vez de uma vírgula, conforme seria o esperado.

O WKT também pode ser usado para representar os valores espaciais de linha e polígono. É importante notar que os valores que definem um par de coordenadas são separados por um espaço e os pares de coordenadas são separados por uma vírgula. Além disso, ao definir os valores poligonais, inclua parênteses duplos no início e no fim da lista de coordenadas que definem o polígono, conforme mostrado em Lista 16.

Lista 16. Definindo valores de polígono
select name
from test.hospitals
where st_within(location,st_polygon(
'POLYGON ((-74.1 42.0, -74.1 42.1, -74.0 42.1, -74.0 42.0, -74.1 42.0))',1)) = 1

Selecionando valores espaciais

Os valores espaciais são usados com maior frequência em uma consulta, mas, na verdade, não precisam ser retornados para o usuário ou aplicativo como parte do conjunto de resultados, tal como no exemplo anterior. Se você executar uma consulta, como select * from test.hospitals ou select location from test.hospitals, provavelmente será possível ver uma longa cadeia de caractere hexadecimal que não é muito significativa, porque essa é a representação compacta e interna dos dados espaciais. Se quiser ver as coordenadas de forma legível, você precisará usar a função ST_AsText para retornar o valor espacial na representação WKT. É uma boa prática restringir o comprimento dos dados de caracteres retornados com as funções SUBSTR ou VARCHAR SQL, conforme mostrado em Lista 17.

Lista 17. Usando a função st_astext
select
   substr(name, 1, 25) as name
  ,varchar(st_astext(location), 30) as location
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LOCATION
------------------------- ------------------------------
Adventist Home            POINT (-73.785400 42.139526)
Greene County Memorial Ho POINT (-73.878181 42.230648)
  2 record(s) selected.

Outro formato legível é o Geography Markup Language (GML), que é uma representação XML para valores espaciais definidos pelo Open Geospatial Consortium. É possível selecionar valores espaciais neste formato usando a função ST_AsGML , conforme mostrado em Lista 18.

Lista 18. Usando a função st_asgml
select
   substr(name, 1, 25) as name
  ,varchar(st_asgml(location), 90) as location
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LOCATION
------------------------- ----------------------------------------
Adventist Home            <gml:Point srsName="EPSG:4269"><gml:pos>
                          -73.785400 42.139526</gml:pos></gml:Point>

Greene County Memorial Ho <gml:Point srsName="EPSG:4269"><gml:pos>
                          -73.878181 42.230648</gml:pos></gml:Point>
  2 record(s) selected.

Ao trabalhar com aplicativos de mapas da web e marcadores de exposição para objetos de ponto no banco de dados, muitas vezes é útil selecionar as coordenadas como X e Y (ou, mais provavelmente como longitude e latitude). Isso é feito facilmente com as funções ST_X e ST_Y , conforme mostrado em Lista 19.

Lista 19. Usando as funções st_x e st_y
select
   substr(name, 1, 25) as name
  ,st_x(location) as longitude
  ,st_y(location) as latitude
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LONGITUDE                LATITUDE
------------------------- ------------------------ ------------------------
Adventist Home              -7.37854000000000E+001   +4.21395260000000E+001
Greene County Memorial Ho   -7.38781810000000E+001   +4.22306480000000E+001
  2 record(s) selected.

Quando tratar um valor espacial

Um determinado número de funções espaciais que podem criar diferentes tipos de valores espaciais é documentado como retornando o tipo genérico ST_Geometry. Se você tentar usar uma função espacial que é válida apenas para um tipo espacial específico, você receberá um erro, conforme mostrado em Lista 20.

Lista 20. Mensagem de erro de tipo espacial incompatível
A chamada da rotina "ST_PERIMETER" é ambígua. O argumento na posição "1"
não possui um ajuste melhor. SQLCODE=-245, SQLSTATE=428F5, DRIVER=3.62.80

Por exemplo, o código em Lista 21 retorna este erro, porque a função st_buffer possui um tipo de retorno de st_geometry, mesmo que o resultado real seja do tipo st_polygon.

Lista 21. Exemplo de código usando o tipo espacial incompatível
 select
  name
 ,st_perimeter(st_buffer(location, .1)) as perimeter
from test.hospitals
where name like 'New York%'

Isto pode ser resolvido informando o DB2 que ele deveria treat o resultado como st_polygon, como mostra a Lista 22.

Lista 22. Usando a função treat
select
  name
 ,st_perimeter(TREAT(st_buffer(location, .1) as db2gse.st_polygon))
  as perimeter
from test.hospitals
where name like 'New York%'

O código é executado sem erro e retorna a saída mostrada em Lista 23.

Lista 23. Resultado do uso da função treat
NAME                    PERIMETER
----------------------- ------------------------
New York Hospital         +6.28206837256265E-001
New York Hospital         +6.28206837256265E-001
New York State Hospital   +6.28206837256265E-001
  3 record(s) selected.

Executando as consultas espaciais

Como as consultas espaciais não são diferentes de qualquer outra consulta SQL, qualquer ambiente que pode ser usado para executar consultas SQL pode ser usado​para executar consultas que incluam operações espaciais. Nas seções anteriores, as consultas espaciais foram executadas usando o processador de linha de comando DB2, fazendo referência a um arquivo de instruções SQL. Esta seção discute alguns dos outros ambientes para a execução de consultas espaciais, incluindo o IBM Data Studio, os aplicativos da linguagem Java e os aplicativos em linguagem C.

Trabalhando com o IBM Data Studio

O IBM Data Studio é um ambiente livre e baseado em Eclipse no qual são desenvolvidos e testados aplicativos de banco de dados. O Data Studio substitui o DB2 Control Center, que foi substituído e não está mais disponível com o DB2 Versão 10. Consulte Recursos para obter informações sobre o download e configuração do Data Studio.

O IBM Data Studio totalmente do cliente permite que você execute comandos SQL, bem como faça desenvolvimento em Java. O cliente total é recomendado para as seguintes seções deste tutorial.

Quando você tiver instalado o IBM Data Studio e o colocado em funcionamento, crie um ambiente para executar as consultas, concluindo as seguintes etapas.

  1. Clique em File > New > Data Development Project.
  2. Especifique um nome para o projeto, como SpatialProject.
  3. Selecione um banco de dados ao qual se conectar.
  4. AppendDB2GSE para obter o caminho padrão e clique em Finish.
  5. Clique com o botão direito no nome do seu projeto na área de janela à esquerda e selecione Novo SQL ou Script XQuery.
  6. Nomeie o seu projeto de uma maneira parecida com query_hospital_countiese clique em Finish.
  7. Copie o código da Lista 24 na área de entrada de script.
Lista 24. Código para a área de entrada de script
SELECT h.name, st_astext(h.location)
FROM test.hospitals AS h, test.counties AS c
WHERE st_within(h.location, c.shape) = 1
AND c.state_name = 'New York'
AND c.county_name = 'Greene';
  1. Pressione Ctrl+S para salvar o script.
  2. Pressione F5 para executar a consulta. A consulta é executada e apresenta uma tela, conforme mostrado em Figura 2. Clique na guia Result1 para visualizar os seus resultados reais.
Figura 2. Hospitais na consulta por município no Data Studio
Hospitais na consulta por município no Data Studio

Clique para ver a imagem maior

Figura 2. Hospitais na consulta por município no Data Studio

Hospitais na consulta por município no Data Studio

Repita as etapas para a consulta de distância SQL mostrada em Lista 25.

Lista 25. Consulta de distância SQL
select
  name
 ,st_astext(location) as location_wkt
 ,st_distance(location, st_point(-74.237449, 42.036976, 1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location, st_point(-74.237449, 42.036976, 1), 'STATUTE MILE') < 25.0
order by distance

A consulta é executada e apresenta uma tela, conforme mostrado em Figura 3.

Figura 3. A consulta de distância de hospitais no Data Studio
A consulta de distância de hospitais no Data Studio

Trabalhando com a interface JDBC em aplicativos Java

Esta seção descreve como executar consultas espaciais usando o JDBC no ambiente Data Studio. Se você possuir um JAVA SDK instalado, o programa JDBC de amostra disponível em Downloads também pode ser compilado e executado de maneira simples em um ambiente de linha de comando.

Conclua as etapas a seguir para criar um projeto de desenvolvimento Java no Data Studio e execute as mesmas consultas que você executou como scripts SQL.

  1. Clique em Arquivo > Novo > Java Development Project.
  2. Especifique um nome para o projeto, como SpatialJDBCProjecte clique em Next.
  3. Na janela de Configurações Java, clique na guia Libraries .
  4. Clique no botão Add External JARs e navegue para o diretório sqllib\java em sua instalação do DB2.
  5. Selecione os arquivos db2jcc.jar e db2jcc_license_cu.jar e clique em Finish.
  6. Clique com o botão direito no nó src sob o seu novo projeto e clique em New > Class.
  7. Especifique JDBCSpatialQuery como o nome da sua classe e clique em Finish.
  8. Abra o programa Java de amostra a partir de Downloads em um editor de texto, copie todo o texto e substitua a definição mínima de classe na janela do editor Data Studio.
  9. Clique em Ctrl+S para salvar o arquivo.
  10. Se você não estiver usando o banco de dados SAMPLE, modifique o código fonte para substituir o nome do seu banco de dados e salve o arquivo.
  11. Clique com o botão direito na janela do editor e selecione Run As > Java Application. Figura 4 mostra a janela do console na área de janela da parte inferior.
Figura 4. Consulta JDBC no Data Studio
Consulta JDBC no Data Studio

Explicação do aplicativo

A função estática main simplesmente cria uma conexão com o banco de dados SAMPLE usando a autorização do usuário atual por padrão ou o ID do usuário e senha especificados, se fornecidos como parâmetros. Uma instância da classe é criada e, então, cada um dos métodos de instância para executar consultas é chamado com a conexão de banco de dados e parâmetros apropriados.

Função hospitalInCounty1

Esta seção descreve a abordagem mais simples, embora não seja a mais eficiente. A consulta SQL é construída de forma idêntica à consulta executada anteriormente no tutorial no editor de script Data Studio, substituindo na cadeia de caractere os valores dos parâmetros para os nomes do município e do estado como constantes. Um objeto statement Java é criado e, em seguida, a consulta SQL é passada para o método executeQuery . Um ciclo é, então, definido para ler cada linha do conjunto de resultados, ficando o nome e a localização na forma de textos, que são a saída para o console. Quando isso for concluído, o conjunto de resultados e da declaração será fechado. Lista 26 mostra um exemplo.

Lista 26. Exemplo usando constantes
 void hospitalInCounty1(Connection con, String stateName, String countyName)
                        throws SQLException {
                Statement stmt;
                ResultSet rs;
                // Query hospitals that are within the specified state and county
                String sel1 = "SELECT h.name, db2gse.st_astext(h.location) "
                                + "FROM test.hospitals AS h, test.counties AS c "
                                + "WHERE db2gse.st_within(h.location, c.shape) = 1 "
                                + "AND c.state_name = '" + stateName + "' "
                                + "AND c.county_name = '" + countyName + "' ";

                System.out.println("\n\nQuery hospitals in " + countyName + ", "
                                + stateName);
                stmt = con.createStatement();
                rs = stmt.executeQuery(sel1);

                // display the result set
                // rs.next() returns false when there are no more rows
                while (rs.next()) {
                        String name1 = rs.getString(1);
                        String location = rs.getString(2);
                        System.out.println("Hospital name: '" + name1 + "'; location: "
                                        + location);
                }
                rs.close();
                stmt.close();
        }

Função hospitalInCounty2

Esta função é semelhante a Lista 26, mas usa marcadores de parâmetro para os parâmetros em vez de constantes. Isto é particularmente importante se instruções SQL semelhantes forem executadas repetidamente, porque o DB2 irá armazenar em cache as instruções SQL compiladas recentemente e reutilizá-las quando for necessário, mesmo se forem provenientes de um aplicativo diferente. Isto pode ser útil quando diversos usuários estão executando o mesmo aplicativo com valores diferentes.

Para usar marcadores de parâmetro, coloque um caractere de ponto de interrogação (?) na instrução SQL ao invés do valor de parâmetro real. Um objetoPreparedStatement Java é criado a partir da conexão, passando na instrução SQL. O método setString é, então, chamado para configurar os valores de parâmetro para os nomes de estado e município. O restante da lógica é o mesmo.

Se a mesma instrução SQL será executada diversas vezes dentro do mesmo aplicativo, seria ainda mais eficiente salvar o PreparedStatement e reutilizá-lo ao fornecer novos valores de parâmetro. Lista 27 mostra um exemplo.

Lista 27. Exemplo usando pontos de interrogação no lugar de valores de parâmetro
void hospitalInCounty2(Connection con, String stateName, String countyName)
                        throws SQLException {
                PreparedStatement pstmt;
                ResultSet rs;
                // Query hospitals that are within the specified state and county
                String sel1 = "SELECT h.name, db2gse.st_astext(h.location) "
                                + "FROM test.hospitals AS h, test.counties AS c "
                                + "WHERE db2gse.st_within(h.location, c.shape) = 1 "
                                + "AND c.state_name = ?" + "AND c.county_name = ? ";

                System.out.println("\n\nQuery hospitals in " + countyName + ", "
                                + stateName);
                pstmt = con.prepareStatement(sel1);
                pstmt.setString(1, stateName); // set state name parameter
                pstmt.setString(2, countyName); // set county name parameter
                rs = pstmt.executeQuery();

                // display the result set
                // rs.next() returns false when there are no more rows
                while (rs.next()) {
                        String name1 = rs.getString(1);
                        String location = rs.getString(2);
                        System.out.println("Hospital name: '" + name1 + "'; location: "
                                        + location);
                }
                rs.close();
                pstmt.close();
        }

Função hospitalDistance

Esta função hospitalDistance implementa a consulta para localizar hospitais em um raio de 25 milhas do local especificado. A abordagem também usa marcadores de parâmetro para as coordenadas da localização especificada. Note o uso da expressão CAST na instrução SQL. Lista 28 mostra um exemplo.

Lista 28. Usando a função hospitalDistance
String sel2 = "SELECT name, db2gse.st_astext(location), "
            + "db2gse.st_distance(location, db2gse.st_point(CAST (? AS DOUBLE), "
            + "CAST (? AS DOUBLE), 1), 'STATUTE MILE') AS distance "
            + "FROM test.hospitals "
            + "WHERE db2gse.st_distance(location, "
            + "db2gse.st_point(CAST (? AS DOUBLE), CAST (? AS DOUBLE), 1), "
            + "STATUTE MILE')  < 25.0 "
            + "ORDER BY distance";

Para que o construtor de ponto seja reconhecido de forma adequada, é necessário converter explicitamente os parâmetros de longitude e latitude como valores DOUBLE.

Trabalhando com a interface CLI em aplicativos na linguagem C

A instalação do DB2 Spatial Extender inclui um programa de amostra gseRunGseDemo no diretório sqllib/samples/extenders/spatial que pode ser executado diretamente para verificar a instalação espacial por meio do carregamento de dados espaciais, da execução de vários procedimentos espaciais armazenados e a execução de consultas espaciais de amostra com relação aos dados que foram carregados. O gseRunGseDemo é um aplicativo em linguagem C que usa a interface do DB2 CLI e é fornecido em forma de fonte que pode ser examinado, modificado e compilado em seu próprio ambiente. O aplicativo está documentado na seção espacial do centro de informações do DB2 sob o tópico Escrevendo aplicativos e usando o programa de amostra (consulte Recursos).

Trabalhando com outras linguagens de aplicativos

Geralmente, qualquer linguagem que permita a execução de instruções SQL do DB2 pode ser usada para executar consultas espaciais. Isso é particularmente útil com linguagens orientadas a web, como PHP, Ruby e Perl, que podem ser usadas em conjunto com as APIs de mapeamento da web fornecidas por serviços como o Google, Yahoo e Esri ArcGIS Online.


Conclusão

Esse primeiro tutorial da série apresentou os recursos espaciais do DB2, as metodologias para a importação e criação de dados espaciais e os ambientes para a execução de consultas espaciais. Os próximos tutoriais abordarão casos típicos de usos espaciais, o ajuste para obter o melhor desempenho, o trabalho em um ambiente de data warehouse e o uso de ferramentas de visualização.

Agradecimentos

Agradeço a Amyris Rada pela revisão e sugestões para melhorar esse tutorial.


Downloads

DescriçãoNomeTamanho
Sample SQL scripts and Java code for this tutorialsqlscripts.zip5KB
Sample spatial data for this tutorialsampledata.zip1400KB

Recursos

Aprender

Obter produtos e tecnologias

  • Faça o download do DB2 Express-C e do DB2 Spatial Extender, que são versões totalmente funcionais e sem encargos do DB2 e do DB2 Spatial Extender.
  • Faça o download do DB2 Spatial Extender, que corresponde a uma instalação de servidor DB2 existente. Os dados espaciais de amostra no formato de arquivo de forma para uso com o Spatial Extender também estão disponíveis.
  • Faça o download do Geobrowser para DB2 e Informix, que é livre para ser usado com qualquer versão do DB2 ou Informix que tenha o recurso espacial instalado.
  • Faça o download do IBM Data Studio, que fornece recursos básicos aos desenvolvedores e DBAs para o gerenciamento e desenvolvimento de banco de dados para DB2 e Informix sem custo algum.
  • Crie seu próximo projeto de desenvolvimento com a Versão de avaliação do software IBM, disponível para download diretamente do developerWorks.

Discutir

Comentários

developerWorks: Conecte-se

Los campos obligatorios están marcados con un asterisco (*).


Precisa de um ID IBM?
Esqueceu seu ID IBM?


Esqueceu sua senha?
Alterar sua senha

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


A primeira vez que você entrar no developerWorks, um perfil é criado para você. Informações no seu perfil (seu nome, país / região, e nome da empresa) é apresentado ao público e vai acompanhar qualquer conteúdo que você postar, a menos que você opte por esconder o nome da empresa. Você pode atualizar sua conta IBM a qualquer momento.

Todas as informações enviadas são seguras.

Elija su nombre para mostrar



Ao se conectar ao developerWorks pela primeira vez, é criado um perfil para você e é necessário selecionar um nome de exibição. O nome de exibição acompanhará o conteúdo que você postar no developerWorks.

Escolha um nome de exibição de 3 - 31 caracteres. Seu nome de exibição deve ser exclusivo na comunidade do developerWorks e não deve ser o seu endereço de email por motivo de privacidade.

Los campos obligatorios están marcados con un asterisco (*).

(Escolha um nome de exibição de 3 - 31 caracteres.)

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


Todas as informações enviadas são seguras.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=80
Zone=Information Management
ArticleID=800532
ArticleTitle=Gerencie Dados Espaciais com o IBM DB2 Spatial Extender, Parte 1: Adquirindo Dados Espaciais e Desenvolvendo Aplicativos
publish-date=03082012