Localización Geografíca Por IP Usando SQL Server

Introducción

En Internet es el concepto del sitio web de análisis para facilitar el seguimiento de todos los visitantes las actividades y patrones de uso. Una de las dimensiones de la pista es la información geográfica de los visitantes, que puede obtenerse usando la dirección IP de la información que se recoge cuando un usuario entra en una página web. En este artículo describiremos un proceso simple que permite a su sistema de información mostrar la información geográfica de los visitantes.

Ámbito

Este artículo no describe el proceso necesario para capturar la información IP del usuario. Este proceso es una solución a nivel de las aplicaciones que se pueden construir con ASP.NET, PHP, JSP, Python, Ruby, etc. El ámbito de aplicación de este artículo se limita a la utilización de la dirección IP para descubrir los datos geográficos. Estos datos se compone del País, región, ciudad, código postal y el código de área cuando corresponda. Algunos países no tienen el concepto de código postal. :s

¿Qué es una dirección IP?

Cuando un usuario entra en una página web, la aplicación web tiene la capacidad para recopilar información de este usuario. Uno de estos elementos de datos es la dirección IP. La dirección IP tiene un formato de xxx.xxx.xxx.xxx( de 4 octetos separados por puntos ej. 189.23.45.21), y es una dirección lógica asignada a un dispositivo. Esto es lo que identifica a su dirección de Internet, y está compuesto de segmentos que identifican su ubicación geográfica.

¿Qué necesito para mapear una dirección IP a una ubicación geográfica?

Para asignar la dirección IP a una representación geográfica, el sistema de mapeo de los datos geográficos necesita informacion sobre sus ubicaciones. Esta información es proporcionada por varias empresas. En este caso, nosotros estamos usando el GEOLiteCity datos, que es gratuito. Para obtener estos datos, visite aqui y descargar el archivo ZIP que contiene dos archivos, bloques y ubicación CSV(comma separate value). El archivo de mapas de un bloque de números IP a una ubicación. El archivo tiene la ubicación de información geográfica. Tenga en cuenta que hay frecuentes cambios a estos archivos, de modo que asegúrese de leer la descripción de sus servicios.

Para importar estos datos a su base de datos, primero debe crear la tabla de definiciones. Nosotros necesitamos crear el Bloque Ubicación geográfica y tablas. Esta es la tabla de definiciones: (también puede descargar scripts(las secuencias de comandos)).

[caption id="attachment_20" align="alignnone" width="461" caption="tablas"][/caption]

Puedes importar los datos a través de su método preferido. La primera línea en el archivo CSV es una declaración de derechos de autor. La segunda línea de la cabecera de la columna, así que asegúrese de eliminar o pasar por alto  la primera línea durante el proceso de importación. También he incluido una tabla de registro de visitantes que pueden ser utilizados para rastrear la información del usuario. Esta tabla es muy simple, y no incluye todos los posibles elementos de datos que pueden ser recogidos.

Para simplificar el proceso de imporatacion de datos podemos ejecutar un BULK INSERT, usando la sentencia:

BULK INSERT GeoLiteCity_Location
FROM 'GeoLiteCity-Location.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
KEEPNULLS)
GO

BULK INSERT GeoLiteCity_Blocks
FROM 'GeoLiteCity-Blocks.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a',
KEEPNULLS)
GO

Esto tarda un poco puesto que son cerca de 85000 registros por tabla, jeje en fin, Seguimos.

Solución

Una vez que los datos se ha importado, te darás cuenta de que la dirección IP en los datos no se ve nada parecido 192.15.10.125. La información se almacena realmente como un número de IP. Este valor numérico es lo que nos permite hacer una serie de comparación. Una serie de números IP se asigna a una determinada ubicación. Esto es lo que nos permite hacer la asociación, pero el primer paso es averiguar cómo convertir una dirección IP a un número IP. Aquí es donde una función definida por el usuario nos puede ayudar. Primero tenemos que convertir la dirección IP a un número de IP usando la función ConvertIP2Num a continuación:

CREATE function dbo.ConvertIp2Num(@ip nvarchar(15))
returns bigint
as
begin

declare @delimiter NVARCHAR(1), @SUBNET_MASK INT

set @delimiter = '.'
set @SUBNET_MASK = 256

DECLARE @textXML XML;
SELECT    @textXML = CAST('<col>' + REPLACE(@ip, @delimiter, '</col><col>') + '</col>' AS XML);

DECLARE @idx int, @ipNum float
SET @idx = 4
SET @ipNum = 0

declare @segments table(id int ,col int)
INSERT INTO @segments(id, col)
--reorder the sections. must start from the right segment (4 to 1)
SELECT  ROW_NUMBER () OVER (ORDER BY col) as id,
T.col.value('.', 'int') as col
FROM    @textXML.nodes('/col') T(col)
order by id desc

--convert segments to number
select  @ipNum = @ipNum + (cast((col % @SUBNET_MASK) as float) * power(@SUBNET_MASK,@idx-id))
from @segments

return cast(@ipNum as bigint)
end

sqleros.com.arEjemploGeolocalizacionGO

Esta función primero la dirección IP se divide en cuatro segmentos (delimitado por un punto). Aquí es donde realmente el XML se convierte en la mano. Acabamos de crear una cadena XML y utilizar el analizador para hacer la división para nosotros por la selección de los nodos XML. Usamos el Row_Number () para crear un factor que nos ayudará a llegar al segmento de peso (es decir, el segmento: 192 ha RowNumber: 1 y con un peso de: 4-1 = 3). Ahora aplicar la fórmula de conversión, que consiste en la asignación de una base cero peso a cada segmento (cero a partir de la serie de sesiones de la derecha) y multiplicando por este segmento (256 ^ n) o potencia (256, n) donde n = peso. El último paso es añadir todos los resultados del segmento. Por ejemplo, IP 192.15.10.125 se convierte de la siguiente manera:

[caption id="attachment_29" align="alignnone" width="270" caption="Conversion de ip"][/caption]

El resultado es el número que se puede utilizar para la consulta geográfica tablas. Para ello, puede crear una consulta similar a la de debajo de la cual devuelve la información geográfica.

[caption id="attachment_31" align="alignnone" width="459" caption="Resultado"][/caption]

Conclusión:

Con este artículo, tuve la oportunidad de mostrar de un simple proceso de crear su propia base de datos de GEO buscar y dar solución a un mapa de la dirección IP a su información de ubicación. Todavía hay otros elementos a considerar como automatizar el proceso de importación para descargar los nuevos archivos, convertir la dirección IP a valor en numeros , integrar esta información en un almacén de datos, y crear informes que muestran su equipo de marketing en las regiones lo que los clientes están ubicados.

Fuente original en ingles Arhivos adicionales

Esta función primero la dirección IP se divide en cuatro segmentos (delimitado por un punto). Aquí es donde realmente el XML se convierte en la mano. Acabamos de crear una cadena XML y utilizar el analizador para hacer la división para nosotros por la selección de los nodos XML. Usamos el Row_Number () para crear un factor que nos ayudará a llegar al segmento de peso (es decir, el segmento: 192 ha RowNumber: 1 y con un peso de: 4-1 = 3). Ahora aplicar la fórmula de conversión, que consiste en la asignación de una base cero peso a cada segmento (cero a partir de la serie de sesiones de la derecha) y multiplicando por este segmento (256 ^ n) o potencia (256, n) donde n = peso. El último paso es añadir todos los resultados del segmento. Por ejemplo, IP 192.15.10.125 se convierte de la siguiente manera: