[SQL Server] [IP2LOCATION用] IPアドレスの国情報(Country)、都道府県情報(Region)、都市情報(City)取得する方法

inno
2016-08-12 11:58 998 0
IPアドレスについて下記の情報を取得する方法を説明したいと思います。
・国情報(Country)
・都道府県情報(Region)
・都市情報(City)

まず、この情報を持っているデータを取得しましょう。

IP2Location™ LITE IP-COUNTRY-REGION-CITY Database
(IP2LOCATION-LITE-DB3.CSV)

https://lite.ip2location.com/database-ip-country-region-city

上記のリンク先から「IPV4 CSV」と言うファイルをダウンロードします。
今回はCSVファイルを利用する方法を説明したいと思います。
このCSVファイルは月1回更新されます。
毎月初旬頃更新されますので、毎月手動でデータを更新する必要があります。
その理由はIPアドレスの情報が日々変わるからです。
昨日は日本だったIPが今日はアメリカになったりします。
今回利用するCSVファイルは無料バージョンなので、月1回の更新まで待つしかありません。

それではCSVファイルのデータを登録するテーブルとINDEXを作成しましょう。

-- #### テーブル作成
CREATE TABLE dbo.tGeoIpRegion
(
StartNo bigint
,EndNo bigint
,NationCode varchar(5)
,NationName varchar(100)
,RegionName varchar(100)
,CityName varchar(100)
)
GO

-- #### INDEX 作成
CREATE NONCLUSTERED INDEX [IX_tGeoIpRegion_StartNo_EndNo] ON [dbo].[tGeoIpRegion] 
(
[StartNo] ASC,
[EndNo] ASC
)
GO


次はデータを登録しましょう。
ダウンロードした「IP2LOCATION-LITE-DB3.CSV」ファイルを BULK INSERT を利用して登録したいと思います。
BULK INSERT する際はXML形式のFORMATFILEを指定して登録したいと思います。
メモ帳などを利用して「IP2LOCATION-LITE-DB3_Bulk_Insert.xml」ファイル新しく作成して下記の内容を保存してください。


<?xml version="1.0"?>
<BCPFORMAT
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="&quot;"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="&quot;,&quot;"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="&quot;\r\n"/>
</RECORD>
<ROW>
<COLUMN SOURCE="2" NAME="StartNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="3" NAME="EndNo" xsi:type="SQLBIGINT"/>
<COLUMN SOURCE="4" NAME="NationCode" xsi:type="SQLCHAR"/>
<COLUMN SOURCE="5" NAME="NationName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="6" NAME="RegionName" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="7" NAME="CityName" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>


準備ができましたら、下記の BULK INSERT のSQL文を実行してデータを登録しましょう。
ファイルのパースは皆さんの環境に合わせて変更して使ってください。
登録時間は環境によって異なりますが、約1分くらいかかります。

-- #### BULK INSERTを利用してデータを一括登録
BULK INSERT dbo.tGeoIpRegion
FROM
'C:\innoya\IP2LOCATION-LITE-DB3.csv'
WITH 
(
FORMATFILE = 'C:\innoya\IP2LOCATION-LITE-DB3_Bulk_Insert.xml'
)
GO


次はIPアドレスに紐付く各情報を取得する関数を作成しましょう。
・国情報を取得する関数 ：fnGetGeoIPNationCode
・都道府県情報を取得する関数 ：fnGetGeoIPRegionName
・都市情報を取得する関数 ：fnGetGeoIPCityName


■ 国情報を取得する関数 ：fnGetGeoIPNationCode

CREATE FUNCTION dbo.fnGetGeoIPNationCode (
    @IpAddr varchar(39)
)
RETURNS VARCHAR(100) AS  
BEGIN
DECLARE @RtnVal varchar(100)
SET @RtnVal = ''

-- #### IP情報にPORT情報がある場合、IPのみ取得 #### START ####
DECLARE @CheckPort int
SET @CheckPort = CHARINDEX(':', @IpAddr)

IF (@CheckPort > 0)
BEGIN
SET @IpAddr = LEFT(@IpAddr,@CheckPort-1)
END
-- #### IP情報にPORT情報がある場合、IPのみ取得 #### END ####


-- #### IPを数字に変更 #### START ####

DECLARE @IpNum bigint --100件検索時に12秒
--DECLARE @IpNum numeric(18,0) --100件検索時に12秒
--DECLARE @IpNum decimal(21,0) --速度遅い：100件検索時に25秒
DECLARE @ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint

SET @ip1 = PARSENAME(@IpAddr, 4)
SET @ip2 = PARSENAME(@IpAddr, 3)
SET @ip3 = PARSENAME(@IpAddr, 2)
SET @ip4 = PARSENAME(@IpAddr, 1)

--SET @IpNum = @ip1*(256*256*256)+@ip2*(256*256)+@ip3*(256)+@ip4
SET @IpNum = @ip1*(16777216)+@ip2*(65536)+@ip3*(256)+@ip4

-- #### IPを数字に変更 #### END ####

SELECT TOP 1 @RtnVal = NationCode 
FROM dbo.tGeoIPRegion WITH (NOLOCK) 
--WHERE StartNo <= @IpNum AND EndNo >= @IpNum　-- 検索速度遅い
WHERE @IpNum BETWEEN StartNo AND EndNo -- 検索速度速い

RETURN @RtnVal

END
GO



■ 都道府県情報を取得する関数 ：fnGetGeoIPRegionName

CREATE FUNCTION dbo.fnGetGeoIPRegionName (
    @IpAddr varchar(39)
)
RETURNS VARCHAR(100) AS  
BEGIN
DECLARE @RtnVal varchar(100)
SET @RtnVal = ''

-- #### IP情報にPORT情報がある場合、IPのみ取得 #### START ####
DECLARE @CheckPort int
SET @CheckPort = CHARINDEX(':', @IpAddr)

IF (@CheckPort > 0)
BEGIN
SET @IpAddr = LEFT(@IpAddr,@CheckPort-1)
END
-- #### IP情報にPORT情報がある場合、IPのみ取得 #### END ####


-- #### IPを数字に変更 #### START ####

DECLARE @IpNum bigint --100件検索時に12秒
--DECLARE @IpNum numeric(18,0) --100件検索時に12秒
--DECLARE @IpNum decimal(21,0) --速度遅い：100件検索時に25秒
DECLARE @ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint

SET @ip1 = PARSENAME(@IpAddr, 4)
SET @ip2 = PARSENAME(@IpAddr, 3)
SET @ip3 = PARSENAME(@IpAddr, 2)
SET @ip4 = PARSENAME(@IpAddr, 1)

--SET @IpNum = @ip1*(256*256*256)+@ip2*(256*256)+@ip3*(256)+@ip4
SET @IpNum = @ip1*(16777216)+@ip2*(65536)+@ip3*(256)+@ip4

-- #### IPを数字に変更 #### END ####

SELECT TOP 1 @RtnVal = RegionName 
FROM dbo.tGeoIPRegion WITH (NOLOCK) 
--WHERE StartNo <= @IpNum AND EndNo >= @IpNum　-- 検索速度遅い
WHERE @IpNum BETWEEN StartNo AND EndNo -- 検索速度速い

RETURN @RtnVal

END
GO


■ 都市情報を取得する関数 ：fnGetGeoIPCityName

CREATE FUNCTION dbo.fnGetGeoIPCityName (
    @IpAddr varchar(39)
)
RETURNS VARCHAR(100) AS  
BEGIN
DECLARE @RtnVal varchar(100)
SET @RtnVal = ''

-- #### IP情報にPORT情報がある場合、IPのみ取得 #### START ####
DECLARE @CheckPort int
SET @CheckPort = CHARINDEX(':', @IpAddr)

IF (@CheckPort > 0)
BEGIN
SET @IpAddr = LEFT(@IpAddr,@CheckPort-1)
END
-- #### IP情報にPORT情報がある場合、IPのみ取得 #### END ####


-- #### IPを数字に変更 #### START ####

DECLARE @IpNum bigint --100件検索時に12秒
--DECLARE @IpNum numeric(18,0) --100件検索時に12秒
--DECLARE @IpNum decimal(21,0) --速度遅い：100件検索時に25秒
DECLARE @ip1 bigint, @ip2 bigint, @ip3 bigint, @ip4 bigint

SET @ip1 = PARSENAME(@IpAddr, 4)
SET @ip2 = PARSENAME(@IpAddr, 3)
SET @ip3 = PARSENAME(@IpAddr, 2)
SET @ip4 = PARSENAME(@IpAddr, 1)

--SET @IpNum = @ip1*(256*256*256)+@ip2*(256*256)+@ip3*(256)+@ip4
SET @IpNum = @ip1*(16777216)+@ip2*(65536)+@ip3*(256)+@ip4

-- #### IPを数字に変更 #### END ####

SELECT TOP 1 @RtnVal = CityName 
FROM dbo.tGeoIPRegion WITH (NOLOCK) 
--WHERE StartNo <= @IpNum AND EndNo >= @IpNum　-- 検索速度遅い
WHERE @IpNum BETWEEN StartNo AND EndNo -- 検索速度速い

RETURN @RtnVal

END
GO

これで作業が全部終わりました。
それではテストをしてみましょう。

DECLARE @IpAddr varchar(39)
SET @IpAddr = '126.131.110.29'

SELECT dbo.fnGetGeoIPNationCode(@IpAddr) AS [国情報]

SELECT dbo.fnGetGeoIPRegionName(@IpAddr) AS [都道府県情報]

SELECT dbo.fnGetGeoIPCityName(@IpAddr) AS [都市情報]

実行結果




今回は関数を作成して利用する方法を説明しましたが、

関数の内容を利用すればSPに作ることもできます。
都道府県のデータを英語ではなく日本語で取得したい場合は下記のテーブルとデータを登録ご利用ください。

-- #### テーブル作成
CREATE TABLE [dbo].[tGeoIpRegionName](
[RegionName] [varchar](20) NOT NULL,
[RegionNameJP] [nvarchar](10) NULL,
CONSTRAINT [PK_tGeoIpRegionName] PRIMARY KEY CLUSTERED 
(
[RegionName] ASC
)
) ON [PRIMARY]

-- #### データ登録
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Aichi', '愛知県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Akita', '秋田県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Aomori', '青森県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Chiba', '千葉県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Ehime', '愛媛県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Fukui', '福井県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Fukuoka', '福岡県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Fukushima', '福島県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Gifu', '岐阜県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Gunma', '群馬県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Hiroshima', '広島県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Hokkaido', '北海道')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Hyogo', '兵庫県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Ibaraki', '茨城県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Ishikawa', '石川県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Iwate', '岩手県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Kagawa', '香川県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Kagoshima', '鹿児島県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Kanagawa', '神奈川県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Kochi', '高知県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Kumamoto', '熊本県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Kyoto', '京都府')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Mie', '三重県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Miyagi', '宮城県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Miyazaki', '宮崎県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Nagano', '長野県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Nagasaki', '長崎県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Nara', '奈良県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Niigata', '新潟県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Oita', '大分県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Okayama', '岡山県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Okinawa', '沖縄県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Osaka', '大阪府')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Saga', '佐賀県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Saitama', '埼玉県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Shiga', '滋賀県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Shimane', '島根県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Shizuoka', '静岡県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Tochigi', '栃木県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Tokushima', '徳島県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Tokyo', '東京都')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Tottori', '鳥取県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Toyama', '富山県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Wakayama', '和歌山県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Yamagata', '山形県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Yamaguchi', '山口県')
INSERT INTO dbo.tGeoIpRegionName (RegionName, RegionNameJP) VALUES ('Yamanashi', '山梨県')

コメント

[SQL Server] クーポンコード(シリアルコード)発行する方法
2016-08-12 1020
[SQL Server] [IP2LOCATION用] IPアドレスの国情報(Country)、都道府県情報(Region)、都市情報(City)取得する方法
2016-08-12 998
[SQL Server] 使い捨てメールサービス遮断(Block)してください。
2016-03-18 2732
MaxMind GEOIP Function for Microsoft SQL SERVER
2016-02-16 1077
[SQL Server] IPAddressからCクラス(C Class)まで抽出（取得）する方法
2016-02-10 1019
[SQL Server] 照合順序の「Japanese_CI_AS」の意味
2016-01-05 8345
[SQL Server] データ整列が２つの場合,効率が良いページング(paging)方法
2015-08-17 1494
[SQL Server] 効率が良いページング(paging)方法
2015-08-17 1793
[SQL Server] 銀行口座処理のようにデータを1件ずつロックしながら処理する方法(sp_getapplock)
2015-08-05 1858
[SQL Server] 「StoredProcedure ‘dbo.pGetData’のスクリプトに失敗しました。」対応方
2015-07-10 1714