3ENGINE

Programación y otros cachivaches

Etiqueta: SQL

Página 1/4

Tecnologia

Cómo guardar coordenadas geográficas en SQL Server


Introducción

SQL Server soporta dos tipos de datos espaciales:

  • geography: Superficies elipsoidales. Tiene en cuenta la superficie curva de la Tierra para sus cálculos. Se trabaja en longitud y latitud.
  • geometry: Superficies planas. La Tierra se trata como una proyección plana, no tiene en cuenta la forma elipsoidal de la Tierra. Adecuado para distancias cortas (edificios, calles,..)

Los tipos geography y geometry se crean a partir de objetos vectoriales, especificados en formato Well-Known Text (WKT) o Well-Known Binary (WKB).

Los objetos vectoriales admitidos son:

  • Point: una ubicación.
  • MultiPoint: una serie de puntos.
  • LineString: una serie de cero o más puntos conectados por líneas.
  • MultiLineString: un conjunto de linestrings.
  • Polygon: una región contigua descrita por un conjunto de linestrings.
  • MultiPolygon: un conjunto de polígonos.
  • GeometryCollection: una recopilación de tipos geométricos.

El modo mas sencillo de crear estos objetos es mediante el uso de los métodos de geografía
estáticos de Open Geospatial Consortium (OGC) incluidos en SQL Server que devuelven una instancia de geography o geometry a partir de una representación WKT:

  • STGeomFromText
  • STPointFromText
  • STMPointFromText
  • STLineFromText
  • STMLineFromText
  • STPolyFromText
  • STMPolyFromText
  • STGeomCollFromText

El método mas polivalente es STGeomFromText. Un ejemplo:

geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);

Donde el primer parámetro es un WKT con el objeto vectorial y el segundo parámetro es el identificador de referencia espacial (SRID).

El SRID corresponde a un sistema de referencia espacial. Sirve para identificar unívocamente el sistema de coordenadas utilizado para definir columnas de información espacial u objetos espaciales individuales en una columna espacial. Para averiguar la lista de SRID soportadas por SQL Server tenemos la vista sys.spatial_reference_systems:

select * from sys.spatial_reference_systems

Normalmente esto se compone de un sistema de coordenadas geodésicas, el más utilizado es el WGS84, con un patrón matemático de tres dimensiones que representa la tierra por medio de un elipsoide, mas un sistema de coordenadas cartesiano que pasa el modelo 3D a uno en 2D llamado proyección.

proy

Los dos mas comunes son el EPSG:3857 y el EPSG:4326, el primero es el usado en Google Maps, OpenStreetMap y otros mientras que el segundo es usado en GPS. La proyección de Mercator es las más utilizada y es en la que se basan EPSG:3857 y EPSG:4326. Representa la superficie esférica terrestre sobre una superficie cilíndrica, tangente al ecuador, que al desplegarse genera un mapa terrestre plano.

413px-Usgs_map_mercator.svg

Caso práctico

Despues de esta pequeña introducción 😉 un caso práctico.

1. Para guardar las coordenadas gráficas, he creado una tabla con un campo de tipo geography:

CREATE TABLE ejemplo (
[id] [int] NOT NULL PRIMARY KEY,
[geom] [geography] NOT NULL 
)

2. Para obtener coordenadas de prueba. En bing maps he añadido a Mis Lugares un área y dos puntos.

Cómo guardar coordenadas geográficas en SQL Server

Uno de los puntos se encuentra dentro del área y otro fuera. Por si alguien siente curiosidad el área delimita una parte del monasterio budista de Plana Novella. En concreto la zona donde están los mantras.

Ahora exporto las coordenadas a KML.

Cómo guardar coordenadas geográficas en SQL Server

Del KML me quedo con las coordenadas del área y monto el siguiente script para guardar las coordenadas geográficas en SQL Server:

INSERT INTO ejemplo (id, geom)
VALUES (1, 'POLYGON (( 1.8534866159243046 41.291925369663, 1.8540418331903874 41.291659349930804, 1.8537119214815556 41.291254272345995, 1.8531727974695622 41.29151021718217, 1.8534866159243046 41.291925369663))')
GO

3. Ahora quiero saber si hay una relación espacial entre el área y los dos puntos. En concreto quiero saber si alguno de los dos puntos se encuentra dentro del área. Hay un buen artículo que habla de este tema y que explica los métodos OGC disponibles.

SELECT id 
FROM ejemplo 
WHERE geom.STIntersects(geography::STGeomFromText('POINT (1.8535724466127812 41.29165531932045)', 4326)) = 1

SELECT id
FROM ejemplo
WHERE geom.STIntersects(geography::STGeomFromText('POINT (1.85385139635033 41.291979782656405)', 4326)) = 1



Tecnologia

MySQL: Error 1064 – You have an error in your SQL syntax … near ‘TYPE=MyISAM’


Al ejecutar el siguiente script para crear una tabla en un MySQL 5.6:

CREATE TABLE my_table(
id INT(11) NOT NULL AUTO_INCREMENT ,
name VARCHAR(60) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY id(id)
) TYPE=MYISAM;

MySQL retorna un error 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘TYPE=MyISAM’.

El problema está en TYPE=MYISAM; porque TYPE quedó desfasada a partir de MySQL 4.0 y se eliminó definitivamente en MySQL 5.5. Para que funcione hay que sustituir TYPE por ENGINE.

CREATE TABLE my_table(
id INT(11) NOT NULL AUTO_INCREMENT ,
name VARCHAR(60) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY id(id)
) ENGINE=MYISAM;



Tecnologia

SQL Server: Cómo averiguar si una columna o tabla existe en una base de datos


Para averiguar si una columna o tabla existe en una base de datos SQL Server puedes consultar las tablas de sistema INFORMATION_SCHEMA.TABLES y INFORMATION_SCHEMA.COLUMNS.
Ademas esta última tabla nos da información de las columnas, como el tipo, longitud, si es nullable, etc.

Para saber si existe una tabla escribe lo siguiente:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'NOMBRE_TABLA'

Para saber si existe una columna de una tabla escribe lo siguiente:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NOMBRE_COLUMNA' AND TABLE_NAME = 'NOMBRE_TABLA'

Si por ejemplo, quieres añadir una columna a un tabla sólo si ésta no existe haz lo siguiente:

IF not exists
(
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NOMBRE_COLUMNA' AND TABLE_NAME = 'NOMBRE_TABLA'
)
BEGIN
  ALTER TABLE NOMBRE_TABLA ADD NUEVO_CAMPO int
END



Tecnologia

SQL Server: Error en la restauración de la base de datos. Copia distinta de la existente


Al intentar restaurar una base de datos SQL SERVER, a veces puede aparecer un mensaje del estilo «Error en la restauración de la base de datos ‘xxxxxxx’. El conjunto de copia de seguridad contiene una copia de una base de datos distinta de la existente»:

Error en la restauración de la base de datos

Puedes intentar solucionar el problema activando la opción ‘Sobrescribir la base de datos existente (WITH REPLACE)’ que se encuentra en la pestaña ‘Opciones’:

Error en la restauración de la base de datos