I have a mysql database that contains cities, countries, timezones and latitude and longitude fields. I am trying to find the timezone for specific cities by using their latitude and longitute coordinates but I am bringing up inaccurate results. I know my php query is inaccurate and that there is a formula that is needed to do this correctly. Hoping somone might have some experience with this and can help. Thank you in advance.

This is inaccurate, but you can see what I am trying to do.

$result = mysql_query("select time_zone from timezones where latitude = '$latitude' and longitude ='$longitude' limit 0,1");

while ($row = mysql_fetch_array($result)){
$time_zone = $img_info['time_zone'];

}

    to avoid confusion I know it should be $row['time_zone'] not $img_info['time_zone'] oooops

      No, there is no "formula" - time zone boundaries are politically determined (just look at a map of the world's time zones).

      You can get a rough estimate; assuming all of your longitudes are meased in degrees East, subtract each from 360 if it's greater than 180, then divide by 15 (=360/24) to convert from degrees to hours. But that would be wrong as easily as it would be right.

      Or you can get a GIS extension for your DBMS and find map data for it that includes time zone boundaries.

      Or you can use the city's name to look up the timezone, and use latititude and longitude to cities near the specified point. That could also be inaccurate, since even the nearest city to a given point might be on the other side of a timezone boundary (or more than one).

        Found my answer, there actually is a formula for what I was looking for and it works perfectly. Here it is for those who are curious.

        $miles = ''whatever distance you would like the radius of your search to be";

        $query = mysql_query("SELECT time_zone,
        ( 3959 acos( cos( radians('$latitude') )
        cos( radians( latitude ) )
        cos( radians( longitude ) -
        radians('$longitude') ) +
        sin( radians('$latitude') )
        sin( radians( latitude ) ) ) )
        AS distance FROM timezones HAVING distance < '$miles' LIMIT 0, 1", $conn); //I limited my results to 1.

        while($row = mysql_fetch_array($query)){
        $time_zone = $row['time_zone'];

        }

          sounds a lot like weedpacket's last scenario (subject to the same limitations, of course).

            Write a Reply...