August 3, 2016 at 8:31 am
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.
August 25, 2016 at 11:35 pm
I have been spending a lot of time on timezones and I'll share what we've been doing ...
First of all, everything I am doing is with the IANA timezones so this would only apply if that's what you are doing as well. There is a shapefile that geographically defines those timezones as polygons at efele.net/maps/tz/world/[/url]. I downloaded the archive from there and imported the data into an on-site SQL Server using a little free client tool called Shape2SQL. (I still need to figure out how to do this through an automated means.)
From there, I can take geographical coordinates to determine the IANA timezone through a query against that imported data. We also store all the IANA definitions in SQL as well but the conversion logic lives in a .Net assembly ... that kind of logic just doesn't fit as well in t-sql.
There may be a vendor who would do this all and sell it as a service that would work for you, but I have not found it for our situation. I had a meeting with some licensing people at Microsoft (Bing Maps) last week because we need to geocode a few million addresses a year and I know for a fact they don't do anything with timezone resolution because they were worried that was what I was asking them for. Google has some API's that will return timezones but my understanding of their licensing model is not compatible with what we need to do so I have not pursued that path.
This is by no means meant to be exhaustive ... it's just what I know about converting geolocation to timezone in the midst of a project I am working on. Good luck!
August 26, 2016 at 5:59 am
bteraberry (8/25/2016)
I have been spending a lot of time on timezones and I'll share what we've been doing ...First of all, everything I am doing is with the IANA timezones so this would only apply if that's what you are doing as well. There is a shapefile that geographically defines those timezones as polygons at efele.net/maps/tz/world/[/url]. I downloaded the archive from there and imported the data into an on-site SQL Server using a little free client tool called Shape2SQL. (I still need to figure out how to do this through an automated means.)
From there, I can take geographical coordinates to determine the IANA timezone through a query against that imported data. We also store all the IANA definitions in SQL as well but the conversion logic lives in a .Net assembly ... that kind of logic just doesn't fit as well in t-sql.
There may be a vendor who would do this all and sell it as a service that would work for you, but I have not found it for our situation. I had a meeting with some licensing people at Microsoft (Bing Maps) last week because we need to geocode a few million addresses a year and I know for a fact they don't do anything with timezone resolution because they were worried that was what I was asking them for. Google has some API's that will return timezones but my understanding of their licensing model is not compatible with what we need to do so I have not pursued that path.
This is by no means meant to be exhaustive ... it's just what I know about converting geolocation to timezone in the midst of a project I am working on. Good luck!
Interesting stuff - thanks for sharing. I found Geonames (http://www.geonames.org/) to be a good source of data. I haven't implemented everything because of time available, but it's on my long-term list. I've just added the site you're using for the shapes to my list.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply