TimeZone by Coordinate

Question

As the title infers I need to find a time zone (or perhaps just the UTC offset) based on a pair of coordinates. I've been searching for different solutions, and there is a couple of web services out there but I need to be able to access the application offline. As the timezones isn't completely based on longitude it doesn't seem that easy...

I though about querying an ESRI shapefile I've got containing all the countries in world and their timezones, but it seems kind of complex. If that should be the solution, do you know of any .NET library providing this functionality?

Answer 1

Check the tz database. I know it associates names to timezones (like city, countries, EST, etc). But I believe there is an extension for coordinates somewhere.

Answer 2

The solution was to export an ESRI shapefile to SQL Server 2008 using the new spatial datatypes.

If anyone has a better solution feel free to post!

Answer 3

Hi Morten,

I need to address this exact same problem. Any chance you could export and post that SQL Server table contents anywhere? That would be very appreciated.

And while at it, your .Net or SQL code to return the TZ would be really handy too... :)

Cheers and thanks!

Answer 4

Hi Harris, I've tried to export the table but it doesn't seem possible to export geometry types to text .. however it wasn't that hard to do anyway.. You need to google the shapefile Manifold produced a few years ago which maps all the contries in the world and their timezones.. Then you need to export that data to SQL Server 2008 using some program.. I used Manifold (remember to use Enterprise Edition or above).. Then I query the data using the following stored procedure:

USE [MyDb]
GO
/****** Object:  StoredProcedure [dbo].[GetTimeZone]    Script Date: 11/18/2009 21:23:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetTimeZone]
    @Latitude float,
    @Longitude float
AS
    /* SET NOCOUNT ON */

DECLARE @g geometry

/* Validation */
IF @Latitude > 90 OR @Latitude < -90 OR @Longitude > 180 OR @Longitude < -180
    RAISERROR('Latitude or longitude out of range', 16, 1)

IF @Latitude IS NULL OR @Longitude IS NULL
    RAISERROR('Latitude or longitude cannot be null', 16, 1)

SET @g = geometry::Point(@Longitude, @Latitude, 4326);

IF EXISTS(SELECT * From TimeZones WHERE Shape.STContains(@g) = 1)
    /* Point exists on map, get the info */
    SELECT Name, LocalSumme, Offset, AreaI FROM TimeZones WHERE Shape.STContains(@g) = 1
ELSE
    /* Point is an international water */
    IF(@Longitude >= 0)
     SELECT NULL AS Name, NULL AS LocalSumme, FLOOR((@Longitude + 7.5) / 15) AS Offset, NULL AS AreaI
    ELSE
     SELECT NULL AS Name, NULL AS LocalSumme, -FLOOR((-@Longitude + 7.5) / 15) AS Offset, NULL AS AreaI

There's a problem in the shapefile because national waters isn't mapped. I though of maybe using @g.STBuffer() to address this problem..