I have a table that has 2.5 million IP addresses mostly in the U.S. I want to use this data to find their time zone. So far I have downloaded the GeoLite city tables from Maxmind and imported them to my server.
http://dev.maxmind.com/geoip/legacy/geolite/
The first table in Maxmind (Blocks) has a starting IP integer column an ending IP integer column and a LocID corresponding to an integer that is within that range. The table starts from the integer 16 million and goes to about 1.5 billion. The second table has geographical information corresponding to the LocID in the first table.
In a CTE, I used the code below to convert the IP addresses in my table to the integer format. The code seems to output the correct value. I also included the primary key ID column, and the regular IP address.The CTE is called CTEIPInteger.
(TRY_CONVERT(bigint, PARSENAME(IpAddress,1)) +
TRY_CONVERT(bigint, PARSENAME(IpAddress,2)) * 256 +
TRY_CONVERT(bigint, PARSENAME(IpAddress,3)) * 65536 +
TRY_CONVERT(bigint, PARSENAME(IpAddress,4)) * 16777216 ) as IPInteger
I then created a non clustered index on both the starting and ending IP integer columns.
I tried using a join as follows.
select IPAddress,IPInteger,LocID
from CTEIPInteger join Blocks
on IPInteger>= StartIpNum and IPInteger<=EndIpNum
The first 1000 records load pretty quickly but after the computer runs forever without outputting anything.
For the Blocks table, I have also tried indexes on just StartIPNum and I also tried with an index on only the LocID.
How should I obtain the time zones? Am I using the right database? If I have to, I might be willing to pay for Geolocation service.