Timezone lookup using latitude and longitude (part 1)

First timezones are evil, but sometimes you need to work with them. Sometimes is easy to consume some third party api to get timezone information based on a given location using geocoding but there also exists some alternatives.

Then inspired on this blog post and the project Timezone boundary builder decided to create a test project using fastapi and postgis to do the timezone lookup using the latitude and longitude.

The project timezone boundary builder provide in their releases a shapefile that can be imported into a postgresql database with postgis shp2pgsql tool

the file that we need is timezones.shapefile.zip the content :

├── dist
│   ├── combined-shapefile.dbf
│   ├── combined-shapefile.prj
│   ├── combined-shapefile.shp
│   └── combined-shapefile.shx

Setup the database, is asumed that postgis is already installed in the system:

-- pgsql service=local_server
CREATE DATABASE tzdb;
\c tzdb
CREATE EXTENSION postgis;

Import the data into the database:

# shp2pgsql will create a table in the database
# and pipe the shape data in postgresql dump format.
shp2pgsql -D combined-shapefile.shp timezones | psql -U <db user> -d tzdb

The table now should contains 3 columns gid an id, tzid timezone name and geom shape geometry (multypolygon)

from here we can test the timezone lookup in the database using the postgis functions like this:

-- latitude: 12, longitude: -86
SELECT gid, tzid FROM timezones WHERE st_contains(geom, st_makepoint(-86, 12));

-- expected result

 gid |      tzid       
-----+-----------------
 136 | America/Managua
(1 row)

Next post would be the fastapi setup to make use of the data and expose an endpoint to query the timezone.