Trabalhando com Spatial Data no SQL Server – Resumo

Foi me apresentado um cenário com a utilização de Spatial Data do SQL Server 2008, só que com um pequeno problema… Havia algumas funções e procedure com cálculos complexos que demoravam muito para retornar os dados que o cenário precisava. Assim, propus a utilização de funções CLR para fazer estes cálculos (e alguns acessos a webservices que eles precisavam), dai surgiu outro problema… Como utilizar SQL CLR com Spatial Data?

O primeiro passo foi adicionar o Assembly “Microsoft.SqlServer.Types” ao projeto CLR:

Desta forma, se tornou praticável a utilização de Spatial Data (SQL CLR nativo) no projeto SQL CLR de forma bem transparente, ex.:

[SqlFunction]
public static SqlGeometry Buffer5(SqlGeometry geo)
{
    return geo.STBuffer(5);
}

Mas ai outro problema surgiu, como criar pontos, linhas e linhas com Spatial Data no CLR? Assim começou a busca por entender o funcionamento da classe SqlGeometryBuilder:

Gerando um ponto:

[SqlFunction]
public static SqlGeometry GetPoint()
{
    // -- Instanciando o objeto
    var g = new SqlGeometryBuilder();

    // -- Definido o SRID
    g.SetSrid(0);

    // -- Iniciando o geométrico do tipo Ponto
    g.BeginGeometry(OpenGisGeometryType.Point);

    // -- Ponto inicial da figura
    g.BeginFigure(1, 1);

    // -- Término da figura
    g.EndFigure();

    // -- Término do tipo geométrico
    g.EndGeometry();

    // -- Recuperando o tipo geométrico gerado
    return g.ConstructedGeometry;
}

// -- OU

[SqlFunction]
public static SqlGeometry GetPointSimple()
{
    return SqlGeometry.Point(1, 1, 0);
}

Gerando uma linha:

[SqlFunction]
public static SqlGeometry GetLine()
{
    var g = new SqlGeometryBuilder();

    g.SetSrid(0);

    g.BeginGeometry(OpenGisGeometryType.LineString);

    // -- Ponto inicial da figura
    g.BeginFigure(1, 1);

    // -- Adicionando ponto seguinte da figura
    g.AddLine(50, 50);

    g.EndFigure();

    g.EndGeometry();

    return g.ConstructedGeometry;
}

Gerando um polígono:

[SqlFunction]
public static SqlGeometry GetSquare()
{
    var g = new SqlGeometryBuilder();

    g.SetSrid(0);

    g.BeginGeometry(OpenGisGeometryType.Polygon);

    // -- Ponto inicial da figura
    g.BeginFigure(0, 0);

    // -- Adicionando pontos seguintes da figura
    g.AddLine(0, 10);
    g.AddLine(10, 10);
    g.AddLine(10, 0);

    // -- Adicionando ponto inicial para fechar o polígono
    g.AddLine(0, 0);

    g.EndFigure();

    g.EndGeometry();

    return g.ConstructedGeometry;
}

Gerando um multiponto:

[SqlFunction]
public static SqlGeometry GetPoint2()
{
    var g = new SqlGeometryBuilder();

    g.SetSrid(0);

    // -- Iniciando o geométrico do tipo Multiponto
    g.BeginGeometry(OpenGisGeometryType.MultiPoint);

    // -- Iniciando o primeiro ponto
    {
        g.BeginGeometry(OpenGisGeometryType.Point);

        g.BeginFigure(1, 1);

        g.EndFigure();

        g.EndGeometry();
    }

    // -- Iniciando o segundo ponto
    {
        g.BeginGeometry(OpenGisGeometryType.Point);

        g.BeginFigure(2, 1);

        g.EndFigure();

        g.EndGeometry();
    }

    g.EndGeometry();

    // -- Recuperando o tipo geométrico gerado
    return g.ConstructedGeometry;
}

Por fim o resultado de cada função:

Spatial Data: Uniões e Intersecções

Publicado em 29/12/2010 por Paulo R. Pereira

Algumas possibilidades interessantes dos Spatial Types é a combinação de polígonos, seja por união, intersecção ou exclusão.

A forma de união mais simples é a utilização do UNION ALL do próprio T-SQL, que possibilita combinar em uma mesma visualização (no SSMS) alguns pontos ou até milhares de polígonos.

DECLARE @P1 geometry
DECLARE @P2 geometry
SET @P1 = geometry::Point(10, 10, 0).STBuffer(5)
SET @P2 = geometry::Point(15, 10, 0).STBuffer(5)

SELECT @P1
UNION ALL
SELECT @P2

Em seguida, temos a união de dois polígonos pelo método STUnion:

SELECT @P1.STUnion(@P2)

Identificando a intersecção de dois polígonos pelo método STIntersection:

SELECT @P1.STIntersection(@P2)

O método STDifference combina dois polígonos removendo do primeiro polígono a área do segundo polígono:

SELECT @P1.STDifference(@P2)

SELECT @P2.STDifference(@P1)

E por fim, o método STSymDifference que combina dos polígonos removendo destes a área de intersecção:

SELECT @P1.STSymDifference(@P2)

Spatial Data: Desmembrando Polígonos!?

Publicado em 14/12/2010 por Paulo R. Pereira

Um código interessante que procuro utilizar para demonstrar a complexidade dos polígonos dos tipos geométricos / geográficos do SQL Server, desmembra estes polígonos encontrando todos os pontos que os formam.

O primeiro exemplo é uma CTE simples que desmembra os pontos de círculo gerado pela função STBuffer:

DECLARE @P geometry
SET @P = geometry::Point(1, 1, 0).STBuffer(100)

;WITH AUX (ID) AS (
	SELECT 1
	UNION ALL
	SELECT ID + 1
	FROM AUX
)

SELECT TOP (@P.STNumPoints() - 1)
	ID,
	@P.STPointN(ID) AS Point
FROM AUX

OPTION(MAXRECURSION 0)

Sim, um círculo no SQL Server é formado ‘literalmente’ por muuuitos pontos, então é de se pensar duas vezes antes de armazená-lo no banco de dados.

Este segundo exemplo é uma consulta um pouco mais complexa, para desmembrar os pontos dos polígonos de todos os polígonos de uma tabela:

DECLARE @T TABLE (
	Id INT IDENTITY,
	Geo geometry NOT NULL
)

INSERT INTO @T
VALUES ('POLYGON((1 0, 2 1, 2 2, 3 3, 2 3, 1 2, 1 0))')
, ('POLYGON((3 2, 3 1, 5 1, 5 2 , 4 3, 3 2))')

;WITH AUX (ID) AS (
	SELECT 1
	UNION ALL
	SELECT ID + 1
	FROM AUX
)

SELECT Id, 0 AS Id2, Geo.STAsText() AS Descrição, Geo AS Binário
FROM @T

UNION ALL

SELECT Id, Geom.Id2, Geom.Point.STAsText(), Geom.Point.STBuffer(0.05)
FROM @T T
CROSS APPLY (
	SELECT TOP (Geo.STNumPoints() - 1)
		ID AS Id2,
		Geo.STPointN(ID) AS Point
	FROM AUX
) AS Geom

ORDER BY Id, Id2

OPTION(MAXRECURSION 0)

Spatial Data: Envolvendo e cercando polígonos

Publicado em 07/12/2010 por Paulo R. Pereira

Ao se trabalhar com Spatial Data, alguns métodos podem ser bem úteis para agrupar, envolver, criar “cercas” com estes tipos de dados e outras soluções mais complexas.

O método STEnvelope envolve pontos, linhas e polígonos, criando um polígono a partir das extremidades horizontais e verticais. Este primeiro exemplo demonstra a utilização deste método sobre uma circunferência gerada a partir de um ponto.

DECLARE @P1 geometry
SET @P1 = geometry::Point(15, 10, 0).STBuffer(5)

SELECT @P1
UNION ALL
SELECT @P1.STEnvelope()

O segundo exemplo, o método STEnvelope sobre um polígono.

DECLARE @P2 geometry
SET @P2 = ('POLYGON((1 0, 2 1, 2 2, 3 3, 2 3, 1 2, 1 0))')

SELECT @P2
UNION ALL
SELECT @P2.STEnvelope()

O método STConvexHull utiliza os pontos das extremidades do dados geográficos / geométrica criando um polígono. O exemplo abaixo demonstra a utilização deste método sobre dois polígonos agrupados pelo método STUnion.

DECLARE @P1 geometry
DECLARE @P2 geometry
SET @P1 = ('POLYGON((1 0, 2 1, 2 2, 3 3, 2 3, 1 2, 1 0))')
SET @P2 = ('POLYGON((3 2, 3 1, 5 1, 5 2 , 4 3, 3 2))')

SELECT @P1.STUnion(@P2)
UNION ALL
SELECT @P1.STUnion(@P2).STConvexHull()

Outro exemplo demonstrando a utilização do método sobre uma estrutura multiponto:

DECLARE @PS geometry
SET @PS = ('MULTIPOINT((3 2), (5 1), (5 4), (4 2), (2 2))')

SELECT @PS.STBuffer(0.1)
UNION ALL
SELECT @PS.STConvexHull()

Algumas soluções de indexação podem ser realizadas com estes métodos de forma prática, exemplo, agrupar em registro cidades por estado e criar soluções de cercas, úteis para monitoramento de frota de veículos e distribuidoras.

Spatial Data: STGeomFromText

Publicado em 13/12/2009 por Paulo R. Pereira

Boa noite pessoas!

Prosseguindo os artigos sobre dados espaciais, vou demonstrar um pouco a criação de pontos, linhas e polígonos por meio de texto. A partir deste momento, os códigos vão parecer estranhos para quem nunca trabalhou com estes dados geométricos em outro banco de dados.

Antes que critiquem a forma do SQL Server trabalhar com estes dados, tenho que informar ele segue as especificações do OGC (Open Geospatial Consortium http://en.wikipedia.org/wiki/Simple_Features), permitindo compatibilidade com outros SGDB que também seguem (IBM DB2, IBM Informix, MySQL, Oracle e PostgreSQL).

A geração de dados espaciais por meio de texto é a forma mais simples para geração destes dados (também existe a possibilidade gerar estes dados por meio de XML e tipos binários).

O primeiro exemplo é a criação de um ponto por meio de texto:

SELECT geometry::STPointFromText('POINT(1 1)', 0)

O resultado será o mesmo da utilização do método “sem texto”:

SELECT geometry::Point(1, 1, 0)

Para a geração de uma linha, é utilizado método STLineFromText e o texto possui a declaração do tipo a ser criado com a ordem dos pontos correspondentes a linhas. Exemplo:

SELECT geometry::STLineFromText('LINESTRING(0 0, 1 1)', 0)

Uma vantagem desta forma de geração é a possibilidade de criar uma seqüência de linhas, sem a necessidade de concatenar com outras linhas, somente adicionando mais pontos. Exemplo:

SELECT geometry::STLineFromText('LINESTRING(1 1, 2 2, 2 3, 1 0)', 0)

E o fato de linhas, pontos e polígonos serem do mesmo tipo, permite o uso dos mesmos métodos, exemplo:

SELECT geometry::STLineFromText('LINESTRING(1 1, 2 2, 2 3, 1 0)', 0).STBuffer(0.1)

Para a criação de polígonos é necessário especificar pelo menos três pontos e por fim novamente o primeiro. Exemplo:

SELECT geometry::STPolyFromText('POLYGON((1 1, 2 2, 2 3, 1 1))', 0)

Um forma genérica de criar estes tipos é utilizando o método “STGeomFromText”, lembrando que métodos genéricos sempre geram um custo para identificar o que será executado.

Exemplo do uso do método “STGeomFromText”:

SELECT geometry::STGeomFromText('POLYGON((1 1, 3 2, 2 2, 1 1))', 0)

Para terminar, segue um exemplo utilizando todos os métodos de criação de dados geométricos que foram demonstrados até agora:

SELECT geometry::STGeomFromText('POLYGON((1 1, 5 1, 3 4.7, 1 1))', 0)

UNION ALL

SELECT geometry::STPolyFromText('POLYGON((2.5 1, 3.5 1, 3.5 0, 2.5 0, 2.5 1))', 0)

UNION ALL

SELECT geometry::STGeomFromText('LINESTRING(2.5 5, 3.5 5, 3 5.8, 2.5 5)', 0).STBuffer(0.02)

UNION ALL

SELECT geometry::STLineFromText('LINESTRING(2.5 5.5, 3.5 5.5, 3 4.7, 2.5 5.5)', 0).STBuffer(0.02)

UNION ALL

SELECT geometry::STGeomFromText('POINT(1 1)', 0).STBuffer(0.25)

UNION ALL

SELECT geometry::STPointFromText('POINT(2 1)', 0).STBuffer(0.25)

UNION ALL

SELECT geometry::Point(3, 1, 0).STBuffer(0.25)

UNION ALL

SELECT geometry::Point(4, 1, 0).STBuffer(0.25)

UNION ALL

SELECT geometry::Point(5, 1, 0).STBuffer(0.25)

Acredito que a partir destes exemplos, seja possível ter uma boa noção de como criar tipos geométricos. Lembrando que eles não nasceram para ficar “desenhando” no SQL Server. Para o próximo artigo estou validando a possibilidade de utilizar alguns algoritmos mais complexos para demonstrar os vários métodos que estes tipos possuem, então até o próximo artigo!

Se eu não voltar a escrever ainda neste ano: Chanukah Sameach, Feliz Natal, Bonne Noël, Feliz Ano Novo!

Spatial Data: Introdução aos tipos geométricos

Publicado em 06/12/2009 por Paulo R. Pereira

Boa tarde pessoas!

Algumas funcionalidades para tratamentos de “dados espaciais” foram introduzidos no SQL Server 2008 (e no 2005 sem muito suporte, por meio de um assembly CLR opcional). Mas o que são estes tipos de dados? Bem, eles são tipos CLR nativos da engine de banco de dados do SQL Server 2008. O suporte a estes tipos de dados possibilita inserir dentro da engine tratamento a dados geométricos e geográficos. Basicamente, agora você pode armazenar pontos, linhas e polígonos como um único tipo de dado dentro do banco de dados, indexar, executar cálculos dos simples aos mais complexos.

Bom, creio que eu já tenha vendido a idéia, então vamos ao nosso primeiro exemplo:

SELECT geometry::Point(1,1,0)

E temos como resultado:

0x00000000010C000000000000F03F000000000000F03F

Interessante, não é? Bem antes que me chamem de louco, no resultado desta consulta é possível verificar que existirá uma aba “Spatial results”:

Nesta aba, você poderá acompanhar os resultados de suas consultas com tipos geométricos.

Geometry presente na sintaxe é a “classe” que define os tipos geométricos e Point é um “método estático” que permite criar por meio de coordenadas a posição do ponto, como no exemplo “1, 1”. Já “0” é um “número mágico” (como disse o Javier Loria na palestra de Spatial Data no TechEd de 2009), pois por enquanto não há muita utilidade falarmos sobre ele.

Visto que um ponto não possui uma ‘área visível’, vamos utilizar o método STBuffer para determinar uma área, exemplo:

SELECT geometry::Point(1,1,0).STBuffer(1)

Resultado:

Uma forma de exibir vários resultados de tipos geométricos juntos é utilizando UNION ALL, exemplo:

SELECT geometry::Point(10,10,0).STBuffer(1)

UNION ALL

SELECT geometry::Point(20,20,0).STBuffer(1)

UNION ALL

SELECT geometry::Point(25,10,0).STBuffer(1)

Resultado:

Para trabalhar com variáveis ou criar colunas do tipo geométrico, você pode declarar simplesmente como “geometry”, exemplo:

DECLARE @Ponto geometry

SET @Ponto = geometry::Point(10,10,0)

SELECT @Ponto.STBuffer(1)

Uma dica importante: STBuffer cria poligono sobre o tipo geométrico, criando um dado bem maior que o original (como é possível ver na aba Results dos exemplos anteriores), desta forma, se você deseja salvar um ponto, linha ou polígono no banco de dados, não salve-o com o método STBuffer, ou você estará salvando um dado muito maior e mais complexo do que é dado realmente é. Limite o uso do método STBuffer para cálculos e exibição de consultas.

Caso você possua alguma identificação para seus dados geométricos, por exemplo, um nome, você pode visualizar este nome no resultado de suas consultas:

Exemplo:

SELECT geometry::Point(10,10,0).STBuffer(1), 'Meu Ponto' AS Titulo

Por enquanto é uma introdução, nos próximos artigos estarei trabalhando melhor outras funcionalidades presentes no SQL Server para estes tipos de dados.

Caso você deseje conhecer um pouco mais:
http://msdn.microsoft.com/en-us/library/bb933876.aspx

SQL From Hell.com

Um nome bem intuitivo para muita informação!

Arquivo da categoria: Spatial Data

Trabalhando com Spatial Data no SQL Server – Resumo

Trabalhando com SQL CLR e Spatial Data

Spatial Data: Uniões e Intersecções

Spatial Data: Desmembrando Polígonos!?

Spatial Data: Envolvendo e cercando polígonos

Spatial Data: STGeomFromText

Spatial Data: Introdução aos tipos geométricos

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso:

Compartilhe isso: