locked
How to get timezone (offset) for a given Geography point? RRS feed

  • Question

  • Hi everybody...

    Anyone knows how to obtain the time zone for any geography (lat, lng) point?

    I have a GPS device that transmit lat, lng, and time in UTC, but i need to know the time offset of that coordinate using sql 2008.

    I will appreciate any help. Thanks.


    Bernardo Salazar
    Thursday, January 26, 2012 6:40 PM

Answers

All replies

  • You can download a set of polygon data representing timezones of the world from http://efele.net/maps/tz/world (shapefile format).

    Load this data into SQL Server, and then use STIntersects() to determine the polygon in which your point lies.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, January 26, 2012 6:46 PM
    Answerer
  • Or, you can use the webservice at http://www.earthtools.org/webservices.htm#timezone which will return an XML response containing the local time and GMT time offset for a requested latitude/longitude.
    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, January 26, 2012 6:55 PM
    Answerer
  • Very appreciated your help Tanoshimi. I will try the shapefile first, 'cos i find more quick to obtain results. I already use the google webservice to do reverse geocoding, using a small CLR procedure i developed, but its internet provider service-dependant, and here in Venezuela this service really sucks.

    Many thanks again for your guidance.

     


    Bernardo Salazar
    Thursday, January 26, 2012 7:00 PM
  • Having local data is definitely preferable to depending on a webservice, especially if your internet connection is a bit dodgy :)

    The only thing to be aware of is that timezones are not as static as many people think - they get revised every now and again. (In fact, if I recall correctly, didn't Venezuela create its own new timezone about 5 years ago?). So, you might have to update your local data occassionally.

     


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, January 26, 2012 7:30 PM
    Answerer
  • Effectively, our "genius" dictator/president declared the offset of our time zone as -4.30, doing a complete mess in time calculations.

    Now im importing the .shp file into sql using Shape2SQL tool (http://www.sqlmusings.com/2011/02/27/how-to-import-shapefile-shp-spatial-data-into-sql-server-2008/), but tools fails in converting the tz_world_mp at row 140. downloading the tz_world now...


    Bernardo Salazar
    Thursday, January 26, 2012 7:35 PM
  • A quick note: http://www.earthtools.org/webservices.htm#timezone returns wrong timezone for Venezuela (returns -4).  http://ws.geonames.org/timezone?lat=10.01&lng=-67.01 returns a valid value (-4.5) 


    Bernardo Salazar
    Thursday, January 26, 2012 7:49 PM
  • Ready. converted sucessfully the TZ_WORLD (27732 records), the Shape2SQL generated a table with two columns (ID and Geom), but i dont see anything related to timezone in that table... please can you show me the path?

    Sorry for disturbing you.


    Bernardo Salazar
    Thursday, January 26, 2012 8:35 PM
  • The shapefile has an attribute column called tzid, which gives you the standard tzid timezone name associated with each polygon (the Olson id). If this isn't in your SQL Server table then there's something wrong with your shapefile loader tool.

    If you want to know the standard/DST offset from UTC associated with each Olson id, you can look them up at http://en.wikipedia.org/wiki/List_of_tz_database_time_zones


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Thursday, January 26, 2012 11:56 PM
    Answerer
  • I was forced to take the webservice option... :(

    Unfortunately, the shapefile importer doesn't translate the data correctly, the ID columns not correspond to Olsol table's ID. was more easy to make a 3-line modification to my existing CLR procedure  :p

    Anyway many many thanks for show me the path. Greetings, and the force be with you. :D

     

     


    Bernardo Salazar
    Friday, January 27, 2012 4:43 PM
  • tanoshimi,

    How to load ESRI Shapefile into SQL Server?


    Vladimir Kelman http://www.google.com/profiles/vkelman

    Tuesday, December 24, 2013 4:02 PM
  • Hi tanoshimi,

    I had imported the tz_world shape file data into SQL table and prepared sample procedure to get the geography location passing based on Latitude and Longitude values. Now I want to calculate/Display UTC Offset and UTC DST Offset values for the same Geography location.How can I do it??

    DECLARE @g geography = 'POINT(' + cast(@longitude as nvarchar) + ' ' + cast(@latitude as nvarchar) + ')';

    SELECT [ID],[TZID],[geom] FROM [dbo].[tz_world] WHERE geom.STIntersects(@g) = 1

    How can I import the List of tz database time zones data into SQL server new table with Id(Identity column), TZ, comments, UTC Offset, UTC DST Offset columns data from below link.

    https://en.wikipedia.org/wiki/List_of_tz_database_time_zones

    I will appreciate any help..

    Thanks,

    Nagaraj

    Friday, July 8, 2016 1:06 AM
  • Nagaraj,

    I don't think importing IANA timezone database into SQL Server is a good idea. Look at:
    http://nodatime.org/
    https://github.com/mj1856/SqlServerTimeZoneSupport
    http://stackoverflow.com/a/16874070/236391

    Speak with Matt Johnson

    Ask questions on https://groups.google.com/forum/?hl=en#!forum/noda-time


    Vladimir Kelman http://www.google.com/profiles/vkelman

    Friday, July 8, 2016 4:40 PM