Trabalhando com SQL CLR e Spatial Data

[UPDATE: 2011-08-07] Se você se deparou com este artigo e não sabe o que é SQL CLR ou Spatial Data, dê uma olhada antes nos links abaixo:

Trabalhando com SQL CLR – Resumo (Introdução, Stored Procedures, Funções, Tipos, WebService)

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

Spatial Data: STGeomFromText – Árvore de Natal

Spatial Data: Envolvendo e cercando polígonos

Spatial Data: Desmembrando Polígonos!?

Spatial Data: Uniões e Intersecções

[/UPDATE]

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:

6 pensamentos sobre “Trabalhando com SQL CLR e Spatial Data

  1. Grande Paulo.. tudo certo? Otimo seu post, tanto que fiquei com uma duvida.

    Por acaso os dados espaciais do SQL Server foram implementados utilizando CLR? Pergunto isso porque nao lembro onde, mas ja vi que para utilizar dados espaciais o SQL Server carrega uma assemblie. E pelo o que voce retrata a sua adaptacao de CLR ficou mais performatica.

    Sera que existe alguma diferenca grande no desenvolvimento do spatial data dentro do SQL Server, ou foi mais pelos calculos pesados que estava realizando?

    Abracos,
    Marcos Freccia

    • Olá Marcos, no cenário que comentei, se dá pelo fato de que os tipos geometry e geography são tipos nativos CLR armazenados em formato binário (varbinary), mas para as lógicas e representações (como as instâncias e métodos), é feito o uso nativo do assembly Microsoft.SqlServer.Types, o que gera muitas serializações/desserializações quando tratados em funções e procedures T-SQL (e até mesmo quando estão nas tabelas).

      Assim, evitamos o numero excessivo de serializações/desserializações geradas pela manipulação destes dados quando são trabalhados em funções e procedures T-SQL, colocando os cálculos diretamente em nossos assemblies customizados. Deste modo, onde tínhamos de 2 a 30 serializações/desserializações (em alguns casos paramos até de contar), tivemos somente a desserialização de entrada nos métodos (stored procedures e functions CLR) e a serialização final para o SQL Server.

      Pensamos inicialmente utilizar os índices “espaciais” do SQL Server sobre os tipos geometry e geography, mas com isso não tivemos mudança para questões de variáveis que ainda não estavam armazenadas nas tabelas indexadas.

      Também tivemos vantagens significativas com CLR para situações que recebíamos dados XML e precisávamos gerar a partir destes dados os equivalentes em geometry e geography, no qual, inicialmente tínhamos gargalos tanto na leitura dos XML e quanto na geração em spatial data com muita demora (de 15s a 180s), com CLR gerando diretamente os dados não tivemos demoras expressivas (máximo 10s).

      Podíamos até ter colocado os processos CLR que desenvolvemos somente em nível de aplicação, se não fossem duas questões, conseguimos deixar rápido tanto consultas quanto os cálculos com CLR (que eram poucas, pois muitas resolvemos com só com ajustes no T-SQL e outras, com índices “espaciais”) e alguns principais processos não tinha uma aplicação propriamente dita entre eles (importação direta entre um banco de dados para outro e um linked server com um MySQL).

      Por fim, entre mortos e feridos (funções e procedures eliminadas e que sofreram mudanças), o projeto esta caminhando perfeitamente bem.

      Se não consegui te responder alguma coisa, ou te deixei com mais dúvidas, me avise!

      • Paulo,

        Entendi sim o porque da utilizacao, levado em consideracao o teu cenario realmente acredito ter sido uma boa escolha..
        Eu nunca tinha visto digamos que um case de alguem utilizando dados espaciais. Quando tiveres mais novidades vai postando pra gente ver.. 🙂

        Obrigado e abracos,
        Marcos Freccia

  2. Pingback: Trabalhando com Spatial Data no SQL Server – Resumo « SQL From Hell.com

  3. Pingback: Trabalhando com SQL CLR – Resumo « SQL From Hell.com

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s