ANOTAÇÕES SOBRE O USO DE
COLUNAS DO TIPO "COORDENADA GEOGRÁFICA"
EM SQL SERVER E C#
Aeh galera, Pedro de
volta!
Voltando ao tema
"Lidando com uma Coluna do Tipo Coordenada Geográfica no SQL Server".
Dêem uma olhada nas
anotações abaixo:
Elas mostram:
·
como criar uma tabela com uma coluna do tipo "Coordenada
Geográfica"
·
como inserir registros nesta tabela, usando as funções geography
·
STGeomFromText
·
Point
·
Como fazer SELECT da coluna "Coordenada Geográfica,
·
formatando como texto
·
obtendo latitude e longitude
·
como declarar variáveis "Coordenada Geográfica" em memória
·
como usar a função .STDistance para listar pontos dentro de uma
determinada região
Tudo isto são exemplos
"da vida real", que estou empregando no SGA.
As anotações terminam com
alguns exemplos "demo", mostrando como criar linhas e polígonos.
Ao longo do texto, há
links para os sites que usei como base.
Hope you enjoy!
USE [SGAPerto_Mobile]
GO
/******
Object: Table [dbo].[Locais] Script Date: 05/30/2019 16:48:16 ******/
CREATE TABLE
[dbo].[Locais](
[IdLocal] [int] IDENTITY(1,1) NOT NULL,
[NomeLocal] [varchar](50) NOT NULL,
[PontoLocal] [geography] NOT NULL,
CONSTRAINT
[PK_Locais] PRIMARY KEY
CLUSTERED
(
[IdLocal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON
[PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/******
Object: Index [IX_Locais] Script Date: 05/30/2019 16:48:16 ******/
CREATE UNIQUE
NONCLUSTERED INDEX
[IX_Locais] ON [dbo].[Locais]
(
[NomeLocal] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Artigo:
Convert Latitude/Longitude (Lat/Long) to Geography Point
-- Note que
usando StGeomFromText para criar um Ponto, os parâmetros são LONGITUDE /
LATITUDE
insert into
Locais (NomeLocal, PontoLocal)
select 'Perto', geography::STGeomFromText('POINT(-51.005615 -29.952576)',
4326)
union all
select 'Casa
do Pedro', geography::STGeomFromText('POINT(-51.224386 -30.039285)',
4326)
union all
select 'Campus
do Vale UFRGS', geography::STGeomFromText('POINT(-51.119298 -30.072127)',
4326)
-- Note que
usando Point (método estático) para criar um Ponto, os parâmetros são LATITUDE
/ LONGITUDE
insert into
Locais (NomeLocal,PontoLocal)
select 'Zaffari
Cidade Baixa', geography::Point(-30.038163, -51.221145, 4326)
union all
select 'Colégio
Pão dos Pobres', geography::Point(-30.040824, -51.228015, 4326)
union all
select 'Bar
Pinguim', geography::Point(-30.037915, -51.222291, 4326)
-- Note que
usando STAsText para converter um Ponto para String, a exibição é no formato
POINT(LONGITUDE LATITUDE)
select *,
PontoLocal.STAsText()
as PontoLocal_Formatado,
PontoLocal.Lat as
Latitude, PontoLocal.Long
as Longitude from
Locais
declare @ondeEstou as geography
select @ondeEstou = PontoLocal from
Locais where NomeLocal =
'Casa do Pedro'
select *
from
( select
*, PontoLocal.STDistance(@ondeEstou) as Distancia from
Locais ) as
meuSelect
where meuSelect.Distancia < 500
order by
Distancia
-- Descrição do
Método STDistance no Microsoft.docs
declare @pontoPerto as geography = geography::STGeomFromText('POINT(-29.952576 -51.005615 )', 4326)
declare @pontoCasa as geography = geography::STGeomFromText('POINT(-30.039285 -51.224386)',
4326)
declare @pontoCampusDoVale as geography = geography::STGeomFromText('POINT(-30.072127 -51.119298)',
4326)
select @pontoCasa.STDistance(@pontoPerto) as Casa_Perto
select @pontoCasa.STDistance(@pontoCampusDoVale) as Casa_Facul
select @pontoPerto.STDistance(@pontoCampusDoVale) as Perto_Facul
-- Artigo: Como converter um
Android ArcGis para latitude e longitude
-- Agora fresqueando com
Linhas e polígonos
-- No SGA vou usar apenas
pontos e calcular a distãncia entre eles.
DECLARE @g geography;
DECLARE @h geography;
SET @g =
geography::STGeomFromText('LINESTRING(-122.360 47.656,
-122.343 47.656)', 4326);
SET @h =
geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g,@h
CREATE TABLE
SpatialTable
( id int IDENTITY (1,1),
GeogCol1 geography,
GeogCol2 AS
GeogCol1.STAsText()
);
GO
INSERT INTO
SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('LINESTRING(-122.360 47.656,
-122.343 47.656 )', 4326));
INSERT INTO
SpatialTable (GeogCol1)
VALUES (geography::STGeomFromText('POLYGON((-122.358 47.653 ,
-122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 4326));
GO
Note que Geography é um
User Defined Type. Antes do SQL 2008,
ele não existia.
A partir do 2008 ele
(penso eu) já vem instalado.
Veja este excelente
artigo para mais informações sobre "CLR User-Defined Types":
Pode ver também
CLR User-Defined Types -
SQL Server
Para ver se o seu banco
tem ou não os tipos de dados "geoespaciais", vá na "árvore de
objetos", abra "Programmability" -> Types e veja se tem lá
Spatial Data Types.
(você também pode ver se
o Microsoft.SqlServer.Types está instalado no seu SQL simplesmente fazendo
SELECT * FROM SYS.ASSEMBLIES)
Em C#, para usar dados
geoespaciais, é precisoa biblioteca Microsoft.SQLServer.Types também esteja
instalado, tal como no SQL Server.
Esta DLL vai trazer a
definição do tipo SQLGeography - equivalente no C# ao tipo Geography do SQL.
O método recomendado é ir
no NuGet e pegar de lá o SQLServer.Types para o seu projeto.
Daí é só declarar as
variáveis, normalmente:
public
Microsoft.SqlServer.Types.SqlGeography PosicaoGeografica { get; set; }
:-)
Mas, vejam o que
aconteceu quando eu fui usar um objeto DEFINIDO EM UM WEB SERVICE
e que tem uma propriedade
do tipo SQLGeography na minha Web Forms Appliction:
EU NÃO CONSEGUIA ACESSAR
AS PROPRIEDADES .Lat e .Long da propriedade!
Bom, eu não consigo
acessar MÉTODOS de um objeto de um tipo definido dentro de um Web Service. Isso faz todo sentido: só posso acessar
métodos do Web Service que tenham o decorator [WebMethod]. Mas, aparentemente, TAMPOUCO CONSIGO ACESSAR
PROPRIEDADES READ-ONLY de objetos definidos no Web Service!
Bom, como eu lidei com
isto?
Criei minhas propriedades
"customizadas"! :-)
Tenho propriedades
"Latitude" e "Longitude" que são nullables of double, e que
NÃO SÃO PERSISTIDAS.
Só servem para SETAR A PROPRIEDADE
DO TPO SQLGEOGRAPHY SEM TER QUE CHAMAR MÉTODOS DO NAMESPACE
MICROSOFT.SQLSERVER.TYPES.SQLGEOGRAPHY DE FORA DO WEB SERVICE.
Seto Latitude, ele tenta
setar a PosicaoGeografica, não consegue porque ainda não tem Longitude.
Seto Longitude, ele vê que
já tem Latitude, e gera um Point com estas coordenadas.
Pronto, já tenho minha
posição geográfica armazenada, prontinha pra ser salva.
Além disto, posso usar as
propriedades GetLatitude e GetLongitude para recuperar estes valores.
Talvez esta encrenca toda
possa ser melhorada, mas tudo funciona!
Veja o código abaixo:
public class Unidade
{
[NomeColuna("UND_POSICAO_GEOGRAFICA")]
public
Microsoft.SqlServer.Types.SqlGeography PosicaoGeografica { get; set; }
private void SetaPosicaoGeografica()
{
if ((! Latitude.HasValue) || (!
Longitude.HasValue))
{
PosicaoGeografica = null;
}
else
{
//System.Globalization.CultureInfo
culturaEnUs =
System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
//String
tempStrLatitudeLongitude = "POINT(" +
Latitude.Value.ToString(culturaEnUs) + " " +
Longitude.Value.ToString(culturaEnUs) + ")";
//Char[] tempArrCharLatitudeLongitude =
tempStrLatitudeLongitude.ToArray();
//System.Data.SqlTypes.SqlChars
tempSqlCharLatitudeLongitude = new
System.Data.SqlTypes.SqlChars(tempArrCharLatitudeLongitude);
//PosicaoGeografica =
Microsoft.SqlServer.Types.SqlGeography.STGeomFromText(tempSqlCharLatitudeLongitude,
4326);
PosicaoGeografica =
Microsoft.SqlServer.Types.SqlGeography.Point(Latitude.Value, Longitude.Value,
4326);
}
}
private double? _Latitude;
public double? Latitude
{
get
{ return _Latitude; }
set
{
if (value < -90 || value
> 90)
{
throw new ApplicationException("A
Latitude tem de estar entre -90 e 90 graus.");
}
_Latitude = value;
SetaPosicaoGeografica();
}
}
private double? _Longitude;
public double? Longitude
{
get
{ return _Longitude; }
set
{
if (value < -180 || value
> 180)
{
throw new
ApplicationException("A Longitude tem de estar entre -180 e 180
graus.");
}
_Longitude = value;
SetaPosicaoGeografica();
}
}
// Estas propriedades GetLatitude
// e GetLontitude
// são necessárias porque, por alguma
razão cabalística, a propriedade "Lat" da Posicao Geografica não é
disponibilizada fora desta solucao
// se eu não colocar o SET vazio,
tampouco consigo usar estes GetLatitude e GetLongitude.
// Deduzo que, assim como os métodos,
as propriedades ReadOnly de uma classe disponibilizada através de um WebService
não são acessíveis de fora do WebService.
// (Obs: Faz sentido não enxergar os
métodos da classe, só consigo usar em um WebService o que está decorado como
[WebMethod].
//
Que isso ocorra com as propriedades readonly de uma classe é, pra mim,
no mínimo intrigante!
//
// Obs: Uso PosicaoGeografica.IsNull em
lugar de PosicaoGeografica == null porque, estranhamente, mesmo que debugando e
fazendo um Watch eu veja o objeto como {Null},
//
fazer ?PosicaoGeografica == null na janela Immediate tem como
resposta... false! :-o
public double? GetLatitude
{
get
{
if ((PosicaoGeografica != null)
&& (! PosicaoGeografica.IsNull))
return
(double?)PosicaoGeografica.Lat;
else
return null;
}
set
{
}
}
public double? GetLongitude
{
get
{
if ((PosicaoGeografica != null)
&& (! PosicaoGeografica.IsNull))
return
(double?)PosicaoGeografica.Long;
else
return null;
}
set
{
}
}
[NomeColuna("UND_ID")]
public Guid? idUnidade { get; set; }
etc, etc, etc. - vai
definindo propriedades "as usual".
E tem mais um
truquezinho.
Tenho que passar a minha
variável SQLGeography como parâmetro para uma SP para poder salvar o valor, não
é mesmo?
Então, eu populo os
parâmetros fazendo um SQLCommandBuilder.DeriveParameters, e depois populo os
valores a partir de uma "coleção de parãmetros" onde cada item tem um
string que é o nome e um objeto que é o value.
SqlCommandBuilder.DeriveParameters(cmd);
foreach (ProcParam param in
proc.ParamInType)
cmd.Parameters["@" + param.Name].Value = param.Value;
Beleza.
Mas... "parece"
que o SQL 2008 tem um probleminha, e não popula a propriedade UdtTypeName para
os parâmetros do tipo UDT, e daí dá erro quando tento rodar a procedure!
Resolvi isso na base da
"marreta":
foreach (SqlParameter item in
cmd.Parameters)
{
// A "marreta"
aih no UdtTypeName eh porque o DeriveParameters nao popula esta propriedade.
// Parece que isto soh
ocorre com SQl 2008.
if (item.SqlDbType ==
SqlDbType.Udt)
// if (item.Value is
Microsoft.SqlServer.Types.SqlGeography)
TIVE QUE REMOVER ESTE "IF", porque se o parâmetro estiver NULL
ele não vai ter tipo. Portanto esta
rotina só funciona se TODOS os UDTs forem Geography! :-(
item.UdtTypeName =
"geography";
}
Sobre isto, veja
Error Passing SqlGeometry
Parameter To Stored Procedure · Issue #43 · jonwagner/Insight.Database
Ah, mais uma coisa: quando eu uso o dado geoespacial como
parâmetro em uma SP, eu coloco um default NULL nele.
Assim não dá erro se não
for passado.
Ver se um objeto
SQLGeography é nulo é chato bagaray, ele pode ser != null e ainda assim ter a
propriedade .IsNull verdadeira.
"Creio" que isto é porque um objeto pode conter uma COLEÇÃO de
objetos geoespaciais. Ainda sou recruta
na coisa e não tenho certeza. Em todo
caso, fazer o parâmetro ter um default NULL é jogar no seguro!
CREATE PROCEDURE
SGA_SP_UNIDADE_SALVAR(@inACAO NUMERIC(1), @inUND_POSICAO_GEOGRAFICA
GEOGRAPHY = NULL,
@inUND_ATIVO bit,
@outCod_Error NUMERIC(9) OUTPUT)
AS
etc etc etc
Bom, finalizando, aí vai
um exemplo de SP que recebe uma coordenada geográfica, uma distãncia em metros,
e retorna os registros de uma tabela (que, óbvio, tem uma coluna do tipo
coordenada geográfica) que estejam dentro da "distância" em relação
ao "ponto de referência".
/****** Object:
StoredProcedure [dbo].[SGA_SP_UNIDADE_CONSULTAR_NO_RAIO_DE] Script Date: 07/03/2019 14:38:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SGA_SP_UNIDADE_CONSULTAR_NO_RAIO_DE]') AND type in (N'P', N'PC'))
DROP PROCEDURE
[dbo].[SGA_SP_UNIDADE_CONSULTAR_NO_RAIO_DE]
GO
/****** Object:
StoredProcedure [dbo].[SGA_SP_UNIDADE_CONSULTAR_NO_RAIO_DE] Script Date: 07/03/2019 14:38:15 ******/
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE
[dbo].[SGA_SP_UNIDADE_CONSULTAR_NO_RAIO_DE](
@inPontoDeReferencia
GEOGRAPHY,
@inDistanciaEmMetros
NUMERIC(9),
@outCod_Error
NUMERIC(9) OUTPUT)
AS
/*********************************************************************************************************
Objeto..........: SGA_SP_UNIDADE_CONSULTAR_NO_RAIO_DE
Schema..........: dbo
Projeto/Equipe..:
PertoSGA/DSR
Responsavel.....: Pedro
Pereira
Date............:
02.julho.2019
Objetivo........: RETORNAR
A LISTA DAS UNIDADES DENTRO DE UM DADO RAIO (@inDistanciaemMetros) DE UM PONTO
DE REFERÊNCIA (@inPontoDeReferencia)
(Unidades
que não tenham uma Posição Geográfica cadastrada nunca serão retornadas).
Alteração.......:
Data Reponsavel HashTag Comment
**********************************************************************************************************/
BEGIN
DECLARE
@UndAux INT;
SET NOCOUNT ON;
SET XACT_ABORT ON; SET NOCOUNT ON;
BEGIN TRY
--Validação
inicial se usuario existe
SET @outCod_Error = 0
SELECT SGA_UNIDADE.UND_ID,
SGA_UNIDADE.UND_NOME,
SGA_UNIDADE.GRP_ID,
SGA_UNIDADE.UND_CODIGO,
SGA_UNIDADE.CID_ID,
SGA_UNIDADE.UND_BAIRRO,
SGA_UNIDADE.UND_ENDERECO,
SGA_UNIDADE.UND_TELEFONE,
SGA_UNIDADE.UND_DESCRICAO,
SGA_UNIDADE.UND_POSICAO_GEOGRAFICA,
SGA_UNIDADE.UND_ATIVO,
SGA_UNIDADE.UND_POSICAO_GEOGRAFICA.STDistance(@inPontoDeReferencia)
as UND_DISTANCIA
FROM
SGA_UNIDADE
where
SGA_UNIDADE.UND_POSICAO_GEOGRAFICA.STDistance(@inPontoDeReferencia) <=
@inDistanciaEmMetros
AND
UND_ATIVO = 1
order by UND_DISTANCIA
END TRY
BEGIN CATCH
exec
error_handler_sp; SET
@outCod_Error = 1;
--SGA0001
END CATCH
END
GO
Vou incluir aqui também
um... "anexo": uma chamada
desta SP. Não vai fazer muito sentido,
porque esta chamada está inserida em um contexto de classes de persistência da
minha aplicação que eu não vou incluir aqui.
Mas achei que não ficaria completo sem este exemplo, e vocês são rapazes
espertos que podem perfeitamente preencher as lacunas das rotinas e classes que
são referenciadas.
[WebMethod]
public void
UnidadeNoRaioDePesquisar(PertoSGA_WebService.DA.DBAccess.ExecUnidadeRaioPesquisarParam
param, ref List<TO.Unidade> unidades)
{
DA.ExecProcResult exProcResult =
new DA.ExecProcResult();
DA.DBAccess.ExecUnidadeNoRaioDePesquisar(ref exProcResult,
new
DBAccess.ExecUnidadeRaioPesquisarParam {
inPontoDeReferencia =
param.inPontoDeReferencia,
inDistanciaEmMetros =
param.inDistanciaEmMetros
});
if (exProcResult.DataTable != null)
{
unidades =
Utils.DataTableToList<TO.Unidade>(exProcResult.DataTable);
}
}
