Este artigo foi traduzido por máquina.
Sob A tabela
Espacial suporte de dados no SQL Server 2008
Bob Beauchemin
Download do código disponível na Galeria de código do MSDN
Procure o código on-line
Conteúdo
Usando o tipo de dados Geografia
Índices espaciais
Visualizadores
O tipo de dados Geometry
Visualização espacial
O SQL Server biblioteca dados espaciais
Quebra automática para cima
SQL Server 2008 oferece vários aprimoramentos, incluindo novos tipos de dados, novas instruções T-SQL e alterações incrementais em suporte Microsoft .NET Framework, suporte a XML e o recurso de agente de serviços. De longe, a alteração de maior e mais interessante para desenvolvedores é o suporte do SQL Server 2008 para dados espaciais — uma poderosa adição à caixa de ferramentas do programador do SQL Server. Nesta coluna, vou apresente uma visão geral breve dos dados espaciais e ilustrar algumas utilizações interessantes.
Dados espaciais são definidos como dados que são representados por imagens 2D ou 3D. SQL Server 2008 suporta imagens 2D e 3D pode ser adicionado em uma versão futura. Dados espaciais podem ser subdivididos em dados geométricos (dados que podem usar cálculos que envolvam geometria Euclidian) e dados geográficos (dados que identifica locais geográficos e limites na Terra).
Usando o tipo de dados Geografia
Vamos começar com um aplicativo simples de dados espaciais, que pode ser útil para processamento sistemas (OLTP) que envolvem clientes de mais tradicional transações online. Dados espaciais é útil no caso porque cada cliente tem um endereço. Você geralmente pensar endereços como rua, cidade, estado, país e CEP, mas um endereço é um ponto na Terra e também pode ser representado como um par de latitude/longitude. Tecnicamente, um endereço pode compõem uma parcela de Terra que pode ser representada como um polígono, mas vamos continuar em um único ponto nesse caso, apenas para manter a simplicidade. Saber onde um cliente reside como latitude/longitude permite que você responder perguntas como:
- Quais são as ramificações de bancário mais próximos três para um cliente específico?
- Que vendedor fica mais próximo ao cliente?
- Como muitos clientes da sua empresa tem em um raio de 10 metros de, por exemplo, Seattle, Washington?
- Como muitos clientes live mais de 2 quilômetros de seu local de filial mais próximo?
O processo de converter um endereço em um par de latitude/longitude é chamado endereço geocodificação. Inúmeros serviços on-line (incluindo MapPoint, Virtual Earth e Google Terra) fornecendo geocodificação como um serviço. Para converter um endereço nos EUA para um ponto, você pode encapsular uma chamada para a geocodificação MapPoint Web Services em uma função do SQLCLR. Isso é mostrado no exemplo de código. Mas onde você deve armazenar a latitude/longitude dentro de seu banco de dados do SQL Server?
SQL Server 2008 vem com os dois tipos de dados necessários para armazenar dados espaciais: geometria e Geografia. Os dois tipos de dados são implementados usando a arquitetura .NET para tipos definidos pelo usuário, que significa que eles podem ter propriedades e métodos. Vamos continuar com o tipo de dados de Geografia agora porque isso mapeia mais de perto para o problema.
Você pode definir uma instância de um tipo de Geografia com uma simples SQL declaração de variável (DECLARE @g Geografia) ou como uma coluna em uma tabela, e você pode inicializar este tipo de dados em uma variedade de formas. Para um tipo de Geografia representando uma instância de um ponto, a maneira mais simples é usar o método estático STPointFromText do tipo Geografia. O método STPointFromText requer não apenas uma representação textual de um ponto de formato de texto conhecidos do Consortium Geospatial abrir (WKT) — ou seja, POINT(x,y) — mas também um identificador de referência espacial (SRID). O SRID identifica o sistema de referência espacial usado para um mapeamento round-Terra ou Terra simples e, por enquanto, é suficiente saber que o geocoder MapPoint Web service usa coordenadas GPS que correspondem às SRID 4326. Isso representa o 1984 de sistema Geodetic World (WGS 84). (Para localizar todos os os SRIDs que ofereça suporte ao SQL Server 2008, basta consultar o sys.spatial_reference_systems de tabela de metadados.)
Portanto, seu código para inicializar a Geografia do endereço poderia esta aparência:
DECLARE @addr nvarchar(256) = 'Some sample address, City, State, Zip';
DECLARE @addr_as_xy nvarchar(30);
DECLARE @g geography;
SET @addr_as_xy = dbo.Geocoder(@addr);
SET @g = geography::STPointFromText(@addr_as_xy, 4326);
Observe que como geografia é implementada como um tipo de dados baseados no .NET, há alguns pontos interessantes mencionar sobre o código. Em primeiro lugar, STPointFromText é um método estático, para que ela deve ser chamada com a sintaxe datatype::method. Além disso, os nomes de método de tipos de dados baseados no .NET são distinção entre maiúsculas e minúsculas assim, STPointFromText deve usar maiúsculas e minúsculas exata.
Há uma coisa mais importante a destacar: a função geocoder deve ser codificada para retornar o local como "Gráficos" em vez de latitude/longitude. Abrir Consortium Geospatial define seu WKB e binário conhecido (WKB) formata como usar x, y pares de coordenadas. Portanto, o que está sendo passado para STPointFromText deve ser POINT(Longitude/Latitude), não POINT(Latitude/Longitude). Se você usaria latitude/longitude em vez disso, SQL Server será constrói instâncias de Geografia usando linguagem geográfico de marcação (GML), um vocabulário XML que usa latitude/longitude. Ou, se você tiver um ponto, há um método estático especial no tipo de dados de geometria que usa três parâmetros: SRID, latitude e longitude.
Agora que você tiver geocoded suas informações de cliente, você pode também ir em frente e geocode seu banco ramificar locais (ou outras informações de escritório filial, informações de depósito ou nada relacionados ao local) e seus locais de vendedor, assim. Suponha que você terminar com tabelas chamadas de cliente, vendedor e ramificação. Cada um deles deve ter uma coluna (vamos chamá-lo geog para manter a simplicidade) de Geografia do tipo que indica o local.
Você pode usar essas informações para responder às perguntas mencionadas anteriormente. Para isso, você precisará usar métodos no tipo de dados Geografia. O método para calcular a distância entre dois objetos de geografia não surpreendentemente, é, chamado STDistance. Os métodos que pode ser usados para responder à pergunta terceira (quantos clientes existem dentro de um raio de 10 metros de Seattle) seria STBuffer e STIntersects. SQL Server também tem um método que é usado para calcular um buffer mais aproximado chamado BufferWithTolerance (ele é um pouco mais rápido do que calcular um buffer exato).
Para responder a primeira pergunta, você usaria uma consulta semelhante a esta:
SELECT TOP(3) b.name,
c.geog.STDistance(b.geog)/1000 [Distance in km]
FROM customer c, branch b
WHERE c.customerid = '12345' -- this query looks at customer 12345
ORDER BY c.geog.STDistance(b.geog)
A consulta para calcular o vendedor mais próximo deve ser feita em aproximadamente da mesma maneira.
Quanto as pessoas que moram dentro de um raio de 10 metros de Seattle? Para esta consulta, você deve colocar um buffer ao redor dos limites de cidade de Seattle (representado por um polígono dos limites de cidade ou um ponto que você designa como luzes da cidade de Seattle) e selecione todos os pontos (clientes) que fazem a interseção desse buffer. Representados no código, isso é:
-- or declare POINT for "downtown Seattle"
-- 1609.344 meters per mile
DECLARE @Seattle geography = 'POLYGON(....)'; SELECT c.customerid FROM
customer c WHERE c.geog.STIntersects(@Seattle.STBuffer(10 * 1609.344));
Esses exemplos ilustram pontos mais interessantes sobre como você interage com instâncias do tipo Geografia. Quando você usa STBuffer, por exemplo, que é um método de instância no tipo de dados de Geografia, você usar a sintaxe instance.method em vez da sintaxe de type::method que você poderia usar para métodos estáticos como STPointFromText. Além disso, observe que, quando você estiver calculando a distância entre dois localidades ou determinar se uma instância de Geografia cruza outra, você usar a sintaxe Instance1.method(Instance2).
Para responder a pergunta sobre o número de clientes que moram mais de dois quilômetros partir da ramificação banco mais próximo, você deve poder agregar as localidades de todas as ramificações de banco, com um buffer de dois quilômetros ao redor de cada uma delas e então olhe para os clientes que não fazem a interseção com esse conjunto de localidades. O tipo de dados de geografia tem um método chamado STUnion, mas ele é especificado como um método em uma instância de Geografia que retorna a união entre essa instância e outra instância.
Isso não é a agregação de baseada em conjunto que tinha em mente. Felizmente, o SQL Server 2005 introduziu agregados definidos pelo usuário que podem ser gravados no código do .NET, e você pode usar um aqui para obter uma união agregada de um conjunto de valores. Sem entrar muito em aspectos específicos da codificação agregados definidos pelo usuário, eles exigem quatro inicializações de método: inicialização (inicialização de resposta), encerrar (resposta retorno), Accumulate (chamado para cada linha) e direta (chamado ao mesclar o processamento de vários threads).
Agregada, o método Accumulate pode simplesmente unir a geografia de linha atual com a geografia resposta sempre que ele é chamado. Na verdade, você não precisa escrever esta agregação mesmo; ele faz parte do Projeto de ferramentas espacial do SQL Server no CodePlex. Este agregado ainda permitirá que você passar em um buffer para cada linha, que resolve o problema de buffer de dois metros. A consulta, em seguida, pode ser escrita assim:
SELECT COUNT(*)
FROM customer c
WHERE
c.geog.STIntersects(
SELECT dbo.GeographyUnionAggregate(b.geog,2*1609.344) FROM branch b)=0
Como você pode ver, esses exemplos, endereços de geocodificação e armazená-las junto com de formulário o escritório de postagem do endereço podem trazer valor comercial para um aplicativo.
Índices espaciais
Depois que sua empresa recebe maior, calcular a distância entre cada cliente e cada vendedor ou cada cliente e cada filial de banco pode se tornar muito lenta. Suporte a dados espacial do SQL Server 2008 inclui indexação espacial. Os índices espaciais são índices de B-árvore comuns que devem fazer consultas espaciais executado mais rapidamente, assim como os índices relacionais no SQL Server fazem consultas relacionais executado mais rapidamente.
Mapear dados espaciais bidimensionais para uma árvore B unidimensional é realizada por meio de tessellation; que é, dividir a área de backup em subáreas pequenas e registrar as subáreas que se cruzam cada instância espacial. Para o tipo de dados a geografia, isso significa que dividir o mundo inteiro em hemispheres e Projetando cada hemisphere em um plano. Cada instância de Geografia, em seguida, aborda uma ou mais subseções (lado a lado) desse plano; o índice espacial deve conter uma linha para cada lado que abrange uma instância. Para o tipo de cálculo, porque você está especificando seu próprio sistema de coordenadas rectangular, você pode especificar os limites (caixa delimitadora) que aborda o índice espacial.
Há um número de regras que determinam como espaciais instâncias que interceptam lado a lado é mapeado para linhas em um índice espacial, e você pode definir bem como o sistema de grade serão ser para o índice espacial em vários níveis. Para obter mais informações sobre as especificações de índices espaciais, Manuais online do SQL Server é a melhor referência.
Para retornar para o sistema cliente, você pode definir um índice espacial na coluna geog na sua tabela de clientes com a seguinte linguagem de definição de dados (DDL):
CREATE SPATIAL INDEX cust_geog_idx
ON dbo.customer(geog)
GRIDS =(LEVEL1=HIGH,LEVEL2=HIGH,LEVEL3=HIGH,LEVEL4=HIGH));
Definindo este índice com uma grade granularidade alta seria melhor para seu índice porque cada endereço de cliente é um ponto, que só apareceria em um quadrado, ao contrário de linhas ou polígonos, que podem se cruzam muitas peças.
Você deve observar que índices espaciais são usados somente com certos métodos espaciais. Atualmente, eles são usados com os seguinte predicados Geografia:
instance1.STIntersects(instance2) = 1
instance1.STEquals(instance2) = 1
instance1.STDistance(instance2) < number
instance1.STDistance(instance2) <= number
Quando você está lidando com geometria em vez de Geografia, o índice espacial também funciona com o STContains, STOverlaps, STTouches e STWithin métodos, mas somente quando estiver verificando 1 (verdadeiro). Com base na maneira como os índices espaciais trabalha, você realmente deseja rephrase sua consulta espacial sobre "clientes que moram mais de dois quilômetros de uma ramificação bancária" para contar os clientes que STIntersects retorna True e subtrair esse número do número total de clientes.
Visualizadores
Observe que nos exemplos alguns que trabalhou por meio de até o momento, seria realmente útil ver os dados espaciais em um formulário diferente de linhas e colunas. Na verdade, talvez você tenha notado que selecionando o tipo de dados espacial próprio retorna sua representação binária. Usando o método ToString ou o método de STAsText retorna o formato WKB. Que é um pouco melhor, mas ele ainda não lhe qualquer idéia sobre localizações geográficas, a menos que você pode projeto pares de Latitude/Longitude para um mapa de cabeça. Dados espaciais precisam quase sempre um visualizador gráfico para torná-lo mais úteis e quando você está falando sobre dados geográficos, esse visualizador normalmente deve incluir um mapa.
SQL Server Management Studio 2008 inclui um visualizador simples sob a forma de uma guia espacial resultados na janela de resultados da consulta. Este visualizador funciona com uma Geografia ou coluna de cálculo na consulta resulta e plota os tipos de dados espaciais em uma grade. Se várias colunas espaciais aparecer no resultado da consulta, você pode escolher a visualizar. A coluna para ser exibida deve estar no formato binário do SQL Server; usando ToString() ou STAsText() não funciona com o visualizador.
Para um tipo de dados a geografia, você mesmo obter opções de mapa projeções como Mercator ou Equirectangular, mas os dados não sobrepostos em um mapa por padrão. Um barata e fácil maneira sobrepor um mapa se você tiver uma tabela que contém dados da estrutura de tópicos do mapa é fazer uma UNION ALL entre o conjunto de linhas e um conjunto de linhas que contém a Geografia do mapa. a Figura 1 mostra pontos que representa um conjunto de mais de 700 cidades do banco de dados Mondial no Visualizador do SQL Server Management Studio. A consulta do SQL Server que gerou a ele foi:
SELECT geog, name
FROM Mondial.dbo.city
WHERE geog IS NOT NULL
.gif)
Figura 1 um conjunto de linhas de pontos da tabela Mondial banco de dados Cidade
Uma opção ainda melhor é usar um visualizador comercial ou shareware que fornece a sobreposição de mapa por padrão, como mostrado na Figura 2 . Você observará que na guia resultados espacial no SQL Server Management Studio mostra um conjunto de registros de mais de 700 pontos com uma sobreposição de mapa. A consulta do SQL Server que produziu-tem esta aparência:
SELECT geog, name
FROM Mondial.dbo.city
WHERE geog IS NOT NULL
UNION ALL
SELECT geog, cntry_name
FROM SpatialSamples.dbo.cntry00
.gif)
A Figura 2 de um conjunto de linhas de pontos com um mapa de sobreposição
Um visualizador que você pode usar é Geoquery 2008 programa de Craig Dunn. Este é um visualizador livre para resultados de consulta do SQL Server 2008 que inclua as projeções de mapa e também permite que você escolha a espessura de cor e linha de seus objetos de dados espacial. Você também poderia escrever código para exibir os dados no Microsoft Virtual Earth ou Terra Google, mas que está fora do escopo desta coluna.
O tipo de dados Geometry
Vamos voltar para o outro tipo de dados espacial que eu utilizada no rapidamente, o tipo de dados geometria e Explique onde você poderia usar geometria em oposição a geografia. O tipo de dados de geometria representa um avião simples com x e y coordenadas, em oposição a geografia que representa latitude e longitude, angles do centro para a superfície da Terra (coordenadas ellipsoidal). O tipo de dados de geometria pode ser usado para problemas que não precisa levar forma a Terra em consideração, relativamente pequenos superfícies planos, como layouts de escritório cúbica ou depósitos. Quando você está tentando localizar escritório retangular um colega de trabalho em baia Terra, não importa a curvatura da Terra, portanto, usando o tipo de dados de geometria e cálculos lineares serão suficiente. Quando você está lidando com a geometria, você definir a unidade de medida e o ponto de origem (permitir que 0,0 ser o canto inferior esquerdo de seu depósito, por exemplo).
Embora, às vezes, você pode usar o tipo de cálculo para espacial locais a Terra, o processo é um pouco mais complicado. Como há um número de diferentes maneiras de mapear a Terra para um sistema de coordenadas, e como elas afetam métodos de cálculo como STArea e STDistance, suas instâncias de dados espacial precisará especificar um SRID.
Se você estiver usando geometria ou geografia, um SRID é necessária. Para o tipo de dados de geometria, se você tiver obrigadas o sistema de coordenadas e a unidade de medida você (por exemplo, quando o mapeamento de um layout de escritório), você escolheria SRID 0, que especifica um sistema de referência espacial desconhecido ou local. SRID 0 é o padrão para o tipo de dados de geometria no SQL Server. Para geografia, é importante que você escolher uma dos SRIDs que SQL Server é capaz de usar. SRID 4326 é o padrão e o que é usado por sistemas GPS.
Os métodos geográficos embutidos no SQL Server podem usam qualquer um dos sistemas de referência espacial 390 que são enumerados na tabela de metadados do sistema, sys.spatial_reference_systems. Esses sistemas de referência espacial provenientes oDe europeu Petroleum pesquisa grupo (EPSG)registro de parâmetro geodetic. Não existe EPSG — suas funções foram absorver para o grupo de pesquisa Petroleum Europa. Você pode supor que uma associação de óleo e gás produtores seria deseja ter certeza que tenham as posições mais precisas na Terra quando eles vá Explorando.
Cada SRID não apenas nomes de uma unidade de medida (a maioria dos uso metros), mas também incluem uma seqüência de caracteres que especifica uma referência (conjunto de pontos de referência na Terra), geoid, sistema de coordenadas e mapear projeção. Para obter mais informações sobre sistemas de coordenada, você deve leia do Isaac Kunen white paper"Introdução aos sistemas de Coordinate espacial: mapeamentos simples para um planeta redondo."
Como cálculo do SQL Server Geografia tipo de dados é sensível ao SRID que é usado, a tentativa de usar métodos espaciais (por exemplo, distância e Intersects) entre instâncias de tipos espaciais que não têm o mesmo SRID retornará o valor NULL. Falarei mais sobre a conversão entre SRIDs posteriormente.
Visualização espacial
Abordei alguns aplicativos mais óbvios para dados espaciais e o fato de que muitas delas use visualizadores para fornecer uma exibição gráfica dos dados. Suponha que, em vez de simplesmente usar um mapa dos países do mundo ou os estados nos EUA, queria visualizar meus dados por Projetando-lo em relação a limites de região ou correlacioná-lo com Distritos congressional ou com dados censitárias? Neste caso, eu poderia ter que adquirir o que eu chamo de "dados de referência espacial". Dados de referência espacial podem ser baixados do sites públicos ou comprados de empresas comoInstituto de pesquisa de sistemas ambientais (ESRI). Observe que a disponibilidade de dados de livre referência espacial pública amplamente varia dependendo do local personalizado. Nos EUA, a maior parte os dados que já falamos sobre o, como limites de região e dados censitárias, é mais livremente disponíveis do que em muitos outros países.
Portanto, você já passou fora seu site da Web pública Favoritos, por exemplo oDOS EUA Do censitárias Bureau Tiger sitee arquivos de referência geográfica baixados. Mas os arquivos estão no formato ESRI Shapefile, não do SQL Server colunas de Geografia nas tabelas.
Como importar esses arquivos para o SQL Server? E muitos arquivos disponíveis publicamente usam SRID 4269, não o 4326 SRID seu geocoder emitida. Como você converter entre SRIDs diferentes para que todos os seus métodos espaciais não retornará NULL? O problema que precise ser resolvido aqui é a mais de um problema de extração transformação-carga (ETL) que um problema de conversão simples. Deseja colocar junto dados não-espaciais como censitárias faixa de números ou valores de população. E, embora você poderia (em teoria) transformar os dados entre SRIDs dinamicamente, isso poderia diminuir suas consultas consideravelmente. Isso é definitivamente um problema ETL.
SQL Server 2008, na verdade, não enviar nada na caixa para ajudá-lo aqui. Há programas como ferramenta de Shape2SQL do Morten Nielsen que carrega Shapefiles em tabelas do SQL Server. Mas Shapefiles não o somente tipo de espacial do arquivo de terceiros e você pode desejar executar transformações entre SRIDs ou outras transformações mais especializadas. Há uma variedade de produtos de terceiros comerciais para especializados em transformações de dados espacial e em massa carregar. Eles incluem FME SAFE software para o SQL Server, o Extender espacial da coleção para SQL Server e, claro, linha do ESRI de produtos do sistema (GIS) de informações geográficas. Elas podem mover dados para SQL Server ou mover dados do SQL Server para um GIS completa. Com êxito usei o produto de software SAFE chamado FME para SQL Server, que inclui uma biblioteca de transformações, oferece suporte ao quase todos formato de dados espacial e ainda fornece uma série de componentes para o sistema de ETL do SQL Server: SQL Server Integration Services.
O SQL Server biblioteca dados espaciais
O formato que você irá escolher usar para mover dados e saia do SQL Server será dependem necessidades específicas de sua organização e também nas necessidades de seu aplicativo específico. Tipos de dados do SQL Server intrinsecamente dão suporte a formatos WKB e WKT, bem como formato de idioma (GML) de marcação geográfica. SQL Server expõe os tipos de geometria e Geografia e os métodos associados em uma biblioteca baseados no .NET chamado Microsoft.SqlServer.Types.dll. Não só é esta biblioteca fornecido com o SQL Server, mas como ele é baseado no .NET, você pode baixá-la como parte do SQL Server 2008 Feature Pack e mover os cálculos e transformações para a camada intermediária ou cliente, se necessário. Tenha em mente que apenas um lado do servidor fornece espaciais índices para otimizar as consultas de dados espacial.
SQL Server, na verdade, está armazenando instâncias de classes .NET SqlGeometry e SqlGeography; as classes e os métodos são intrínsecos para a biblioteca. Você pode usar métodos espaciais em consultas do SQL Server ou encapsular as operações em T-SQL ou procedimentos armazenado do SQLCLR, funções definidas pelo usuário e disparadores. Você pode estender a funcionalidade básica usando SqlGeometry e SqlGeography como membros em tipos de definido pelo usuário do .NET e agregados definidos pelo usuário, como o GeographyUnionAggregate que usei anteriormente.
A biblioteca de dados espacial também inclui um construtor de API que pode ser usada para otimizar o carregamento e de transformações personalizadas e agregações. Isso consiste em um SqlGeometryBuilder SqlGeographyBuilder e coletor interfaces (IGeometrySink e IGeographySink) que você pode usar a construção de otimizado de instâncias de dados espacial. Você implementar uma das interfaces coletor, que permite que você começar ou terminar números, adicionar linhas e definir o SRID apropriado. Depois dos dados no lugar, você simplesmente chamar o método popular criem as instâncias que você configurou.
Quebra automática para cima
Os elementos que abordei aqui representam apenas um pouco gosto da funcionalidade que é sido adicionado ao SQL Server com a inclusão de tipos de dados espacial e uma biblioteca de espacial padrão da indústria. Dados que você reunir através de geocodificação, dados de referência pública, ou seus próprios dados espaciais para depósitos e filiais podem ser armazenados no SQL Server, gerenciados com as ferramentas administrativas mesmas que você usa para outros tipos de dados de negócios, indexado, consultado e analisadas para colocar um todo nova dimensão para seus negócios.
Para começar, lembre-se de que qualquer tipo de dados que você poderá capturar com um sistema GPS pode ser importado e pode ser usado com o restante do seus dados de banco de dados. Com recursos GPS se tornando mais comum em telefones, câmeras, automobiles e outros dispositivos e aparelhos e com muitos sistemas de informação agora contendo dados de endereço, a possibilidade de integrar dados GPS interessantes recursos — aqueles que realmente atender às necessidades do usuário — é realmente ilimitado. Portanto, fique atento para alguns desenvolvimentos interessantes.
Bob Beauchemin é um profissional de aplicativo de banco de dados centralizada e arquiteto, autor do curso e instrutor, gravador e Developer a capacidade de parceiro emSQLskills. Ele é gravado livros e artigos no SQL Server, acesso a dados e tecnologias de integração e segurança do banco de dados. Você pode contatá-lo embobb@sqlskills.com.