{"id":14,"date":"2021-05-06T05:55:42","date_gmt":"2021-05-06T05:55:42","guid":{"rendered":"https:\/\/shisaa.jp\/?page_id=14"},"modified":"2021-05-13T07:29:07","modified_gmt":"2021-05-13T07:29:07","slug":"homepage","status":"publish","type":"page","link":"https:\/\/shisaa.jp\/","title":{"rendered":"Homepage"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Preface();<\/h1>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n

Recently, I was lucky to be part of an awesome<\/i><\/strong> project called the Breaking Boundaries Tour.<\/p>\n\n

This project is about two brothers, Omar and Greg Colin, who take their Stella scooters to make a full round trip across the United States. And, while they are at it, try to raise funding for Surfer’s Healing Folly Beach – an organization that does great work enhancing the lives of children with autism through surfing . To accommodate this trip, they wished to have a site where visitors could follow their trail live, as it happened. A marker would travel across the map, with them, 24\/7.<\/p>\n\n

Furthermore, they needed the ability to jump off their scooters, snap a few pictures, edit a video, write some side info and push it on the net, for whole the world to see. Immediately after they made their post, it had to appear on the exact spot they where at when snapping their moments of beauty.<\/p>\n\n

To aid in the live tracking of their global position, they acquired a dedicated GPS tracking device which sends a latitude\/longitude coordinate via a mobile data network every 5 minutes.<\/p>\n\n

Now, this (short) post is not about how I build the entire application, but rather about how I used PostGIS and PostgreSQL for a rather peculiar matter: deducting timezone information.<\/p>\n\n

For those who are interested though: the site is entirely build in Python using the Flask “micro framework” and, of course, PostgreSQL as the database.<\/p>\n<\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
<\/div>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Timezone information?<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n

Yes. Time, dates, timezones: hairy worms in hairy cans which many developers hate to open, but have to sooner or later.<\/p>\n\n

In the case of Breaking Boundaries Tour, we had one major occasion where we needed the correct timezone information: where did the post happen?<\/p>\n<\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
<\/div>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Where did it happen?<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n

A feature we wanted to implement was one to help visitors get a better view of when a certain post was written. To be able to see when a post was written in your local timezone is much more convenient then seeing the post time in some foreign zone.<\/p>\n\n

We are lazy and do not wish to count back- or forward to figure out when a post popped up in our frame of time.<\/p>\n\n

The reasoning is simple, always calculate all the times involved back to simple UTC (GMT). Then figure out the clients timezone using JavaScript, apply the time difference and done!<\/p>\n\n

Simple eh?<\/p>\n\n

Correct, except for one small detail in the feature request, in what zone was the post actually made?<\/p>\n\n

Well…damn.<\/p>\n\n

While you heart might be at the right place while thinking: “Simple, just look at the locale of the machine (laptop, mobile phone, …) that was used to post!”, this information if just too fragile. Remember, the bothers are crossing the USA, riding through at least three major timezones. You can simply not expect all the devices involved when posting to always adjust their locale automatically depending on where they are.<\/p>\n\n

We need a more robust solution. We need PostGIS.<\/p>\n\n

But, how can a spatial database help us to figure out the timezone?<\/p>\n\n

Well, thanks to the hard labor delivered to us by Eric Muller from efele.net, we have a complete and maintained shapefile of the entire world, containing polygons that represent the different timezones accompanied by the official timezone declarations.<\/p>\n\n

This enables us to use the latitude and longitude information from the dedicated tracking device to pin point in which timezone they where while writing their post.<\/p>\n\n

crSo let me take you on a short trip to show you how I used the above data in conjunction with PostGIS and PostgreSQL.<\/p>\n<\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
<\/div>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t

Getting the data<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n

The first thing to do, obviously, is to download the shapefile data and load it in to our PostgreSQL database. Navigate to the Timezone World portion of the efele.net site and download the “tz_world” shapefile.<\/p>\n\n

This will give you a zip which you can extract:<\/p>\n\n

$ unzip tz_world.zip<\/p>\n\n

Unzipping will create a directory called “world” in which you can find the needed shapefile package files.<\/p>\n\n

Next you will need to make sure that your database is PostGIS ready. Connect to your desired database (let us call it bar<\/i><\/strong>) as a superuser:<\/strong><\/p>\n\n

$ psql -U postgres bar<\/p>\n\n

And create the PostGIS extension:<\/p>\n\n

CREATE EXTENSION postgis;<\/p>\n\n

Now go back to your terminal and load the shapefile into your database using the original owner of the database (here called foo):<\/p>\n\n

$ shp2pgsql -S -s 4326 -I tz_world | psql -U foo bar<\/p>\n\n

As you might remember from the PostGIS series, this loads in the geometry from the shapefile using only simple geometry (not “MULTI…” types) with a SRID of 4326.<\/p>\n<\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
<\/div>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t

What have we got?<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n

This will take a couple of seconds and will create one table and two indexes. If you describe your database (assuming you have not made any tables yourself):<\/p>\n\n

public | geography_columns | view | postgres
\npublic | geometry_columns | view | postgres
\npublic | raster_columns | view | postgres
\npublic | raster_overviews | view | postgres
\npublic | spatial_ref_sys | table | postgres
\npublic | tz_world | table | foo
\npublic | tz_world_gid_seq | sequence | foo<\/p>\n\n

You will see the standard PostGIS bookkeeping and you will find the tz_world<\/i><\/strong> table together with a gid<\/i><\/strong> sequence.<\/p>\n\n

Let us describe the table:<\/p>\n\n

\\d tz_world<\/p>\n\n

And get:<\/p>\n<\/div>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t
\n

So we have:<\/p>\n\n