Homepage
Preface();
Recently, I was lucky to be part of an awesome project called the Breaking Boundaries Tour.
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.
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.
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.
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.
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.
Timezone information?
Yes. Time, dates, timezones: hairy worms in hairy cans which many developers hate to open, but have to sooner or later.
In the case of Breaking Boundaries Tour, we had one major occasion where we needed the correct timezone information: where did the post happen?
Where did it happen?
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.
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.
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!
Simple eh?
Correct, except for one small detail in the feature request, in what zone was the post actually made?
Well…damn.
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.
We need a more robust solution. We need PostGIS.
But, how can a spatial database help us to figure out the timezone?
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.
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.
crSo let me take you on a short trip to show you how I used the above data in conjunction with PostGIS and PostgreSQL.
Getting the data
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.
This will give you a zip which you can extract:
$ unzip tz_world.zip
Unzipping will create a directory called “world” in which you can find the needed shapefile package files.
Next you will need to make sure that your database is PostGIS ready. Connect to your desired database (let us call it bar) as a superuser:
$ psql -U postgres bar
And create the PostGIS extension:
CREATE EXTENSION postgis;
Now go back to your terminal and load the shapefile into your database using the original owner of the database (here called foo):
$ shp2pgsql -S -s 4326 -I tz_world | psql -U foo bar
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.
What have we got?
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):
public | geography_columns | view | postgres
public | geometry_columns | view | postgres
public | raster_columns | view | postgres
public | raster_overviews | view | postgres
public | spatial_ref_sys | table | postgres
public | tz_world | table | foo
public | tz_world_gid_seq | sequence | foo
You will see the standard PostGIS bookkeeping and you will find the tz_world table together with a gid sequence.
Let us describe the table:
\d tz_world
And get:

So we have:
- gid: an arbitrary id column
- tzid: holding the standards compliant textual timezone identification
- geom: holding polygons in SRID 4326.
Also notice we have two indexes made for us:
- tz_world_pkey: a simple B-tree index on our gid
- tzid: holding the standards compliant textual timezone identification
This is a rather nice set, would you not say?
Using the data
So how do we go about using this data?
As I have said above, we need to figure out in which polygon (timezone) a certain point resides.
Let us take an arbitrary point on the earth:
- latitude: 35.362852
- longitude: 140.196131
This is a spot in the Chiba prefecture, central Japan.
Using the Simple Features functions we have available in PostGIS, it is trivial to find out in which polygon a certain point resides:
SELECT tzid
FROM tz_world
WHERE
ST_Intersects(ST_GeomFromText(‘POINT(140.196131 35.362852)’, 4326), geom);
And we get back:
tzid
————
Asia/Tokyo
Awesome!
In the above query I used the function ST_Intersects which checks if a given piece of geometry (our point) shares any space with another piece. If we would check the execute plan of this query:
EXPLAIN ANALYZE SELECT tzid
FROM tz_world
WHERE
ST_Intersects(ST_GeomFromText(‘POINT(140.196131 35.362852)’, 4326), geom);
We get back:
QUERY PLAN
——————————————————————————————————————————
Index Scan using tz_world_geom_gist on tz_world (cost=0.28..8.54 rows=1 width=15) (actual time=0.591..0.592 rows=1 loops=1)
Index Cond: (‘0101000020E61000006BD784B446866140E3A430EF71AE4140’::geometry && geom)
Filter: _st_intersects(‘0101000020E61000006BD784B446866140E3A430EF71AE4140’::geometry, geom)
Total runtime: 0.617 ms
That is not bad at all, a runtime of little over 0.6 Milliseconds and it is using our GiST index.
But, if a lookup is using our GiST index, a small alarm bell should go off inside your head. Remember my last chapter on the PostGIS series? I kept on babbling about index usage and how geometry functions or operators can only use GiST indexes when they perform bounding box calculations.
The latter might pose a problem in our case, for bounding boxes are a very rough approximations of the actual geometry. This means that when we arrive near timezone borders, our calculations might just give us the wrong timezone.
So how can we fix this?
This time, we do not need to.
This is one of the few blessed functions that makes use of both an index and is very accurate.
The ST_Intersects first uses the index to perform bounding box calculations. This filters out the majority of available geometry. Then it performs a more expensive, but more accurate calculation (on a small subset) to check if the given point is really inside the returned matches.
We can thus simply use this function without any more magic…life is simple!
Implementation
Now it is fair to say that we do not wish to perform this calculation every time a user views a post, that would not be very efficient nor smart.
Rather, it is a good idea to generate this information at post time, and save it for later use.
The way I have setup to save this information is twofold:
- I only save a UTC (GTM) generalized timestamp of when the post was made.
- I made an extra column in my so-called “posts” table where I only save the string that represents the timezone (Asia/Tokyo in the above case).
This keeps the date/time information in the database naive of any timezone and makes for easier calculations to give the time in either the clients timezone or in the timezone the post was originally written. You simply have one “root” time which you can move around timezones.
On every insert of a new post I have created a trigger that fetches the timezone and inserts it into the designated column. You could also fetch the timezone and update the post record using Python, but opting for an in-database solution saves you a few extra, unneeded round trips and is most likely a lot faster.
Let us see how we could create such a trigger.
A trigger in PostgreSQL is an event you can set to fire when certain conditions are met. The event(s) that fire have to be encapsulated inside a PostgreSQL function. Let us thus first start by creating the function that will insert our timezone string.
Creating functions
In PostgreSQL you can write functions in either C, Procedural languages (PgSQL, Perl, Python) or plain SQL.
Creating functions with plain SQL is the most straightforward and most easy way. However, since we want to write a function that is to be used inside a trigger, we have even a better option. We could employ the power of the embedded PostgreSQL procedural language to easily access and manipulate our newly insert data.
First, let us see which query we would use to fetch the timezone and update our post record:
UPDATE posts
SET tzid = timezone.tzid
FROM (SELECT tzid
FROM tz_world
WHERE ST_Intersects(
ST_SetSRID(
ST_MakePoint(140.196131, 35.362852),
4326),
geom)) AS timezone
WHERE pid = 1;
This query will fetch the timezone string using a subquery and then update the correct record (a post with “pid” 1 in this example).
How do we pour this into a function?
CREATE OR REPLACE FUNCTION set_timezone() RETURNS TRIGGER AS $$
BEGIN
UPDATE posts
SET tzid = timezone.tzid
FROM (SELECT tzid
FROM tz_world
WHERE ST_Intersects(
ST_SetSRID(
ST_MakePoint(NEW.longitude, NEW.latitude),
4326),
geom)) AS timezone
WHERE pid = NEW.pid;
RETURN NEW;
END $$
LANGUAGE PLPGSQL IMMUTABLE;
First we use the syntax CREATE OR REPLACE FUNCTION to indicate we want to create (or replace) a custom function. Then we tell PostgreSQL that this function will return type TRIGGER.
You might notice that we do not give this function any arguments. The reasoning here is that this function is “special”. Functions which are used as triggers magically get information about the inserted data available.
Inside the function you can see we access our latitude and longitude prefixed with NEW. These keywords, NEW and OLD, refer to the record after and before the trigger(s) happened. In our case we could have used both, since we do not alter the latitude or longitude data, we simply fill a column that is NULL by default. There are more keywords available (TG_NAME, TG_RELID, TG_NARGS, …) which refer to properties of the trigger itself, but that is beyond today’s scope.
The actual SQL statement is wrapped between double dollar signs ($$). This is called dollar quoting and is the preferred way to quote your SQL string (as opposed to using single quotes). The body of the function, which in our case is mostly the SQL statement, is surrounded with a BEGIN and END keyword.
A trigger function always needs a RETURN statement that is used to provide the data for the updated record. This too has to reside in the body of the function.
Near the end of our function we need to declare in which language this function was written, in our case PLPGSQL.
Finally, the IMMUTABLE keyword tells PostgreSQL that this function is rather “functional”, meaning: if the inputs are the same, the output will also, always be the same. Using this caching keyword gives our famous PostgreSQL planner the ability to make decisions based on this knowledge.
Creating triggers
Now that we have this functionality wrapped into a tiny PLPGSQL function, we can go ahead and create the trigger.
First you have the event on which a trigger can execute, these are:
- INSERT
- UPDATE
- DELETE
- TRUNCATE
Next, for each event you can specify at what timing your trigger has to fire:
- BEFORE
- AFTER
- INSTEAD OF
The last one is a special timing by which you can replace the default behavior of the mentioned events.
For our use case, we are interested in executing our function AFTER INSERT.
CREATE TRIGGER set_timezone
AFTER INSERT ON posts
FOR EACH ROW
EXECUTE PROCEDURE set_timezone();
This will setup the trigger that fires after the insert of a new record.
Wrapping it up
Good, that all there is to it.
We use a query, wrapped in a function, triggered by an insert event to inject the official timezone string which is deducted by PostGIS’s spatial abilities.
Now you can use this information to get the exact timezone of where the post was made and use this to present the surfing client both the post timezone time and their local time.
For the curious ones out there: I used the MomentJS library for the client side time parsing. This library offers a timezone extension which accepts these official timezone strings to calculate offsets. A lifesaver, so go check it out.
Also, be sure to follow the bros while they scooter across the States!
And as always…thanks for reading!
POSTS

最新IT速報を手に入れる方法
IT速報とは? IT速報とは、IT業界の最新ニュースやトレンドをいち早くキャッチできる情報のことを指します。 テクノロジーの進化が早い業界だからこそ、最新情報を知ることは重要です。 IT速報をチェックするメリット 最新技術の動向を把握できる AIやクラウド技術、サイバーセキュリティなどの最新情報をリアルタイムで取得。 業界の流れをいち早くキャッチし、スキルアップにつなげる。 転職やキャリアアップに役立つ 需要の高いスキルや企業動向を把握できる。 IT企業の新サービスや求人情報をチェックし、キャリア戦略を立てるのに役立つ。 競争力を高める 競争の激しいIT業界で、最新の知識を持つことで差をつけられる。 企業のDX戦略や技術採用の傾向を知ることで、ビジネスの成功に貢献。 IT速報を入手するおすすめの方法’ ニュースサイトを活用する ITmedia、Gigazine、TechCrunch Japanなどの専門メディアをチェック。 RSSリーダーを活用して効率よく情報収集。 SNSやフォーラムを活用する TwitterでIT系アカウントをフォロー(例:@itmedia_news、@techcrunchjapan)。 RedditやQiitaでエンジニアの意見を収集。 メルマガやポッドキャストを活用する Tech系メディアのメルマガを購読し、重要情報を逃さない。 IT系ポッドキャストを聴いて移動時間を有効活用。 IT速報を活用するポイント 情報の取捨選択をする フェイクニュースや誇張された情報に注意。 信頼できる情報源を見極め、必要な情報だけを取り入れる。 実践に活かす 学んだ内容を自分のプロジェクトに取り入れる。

最新のITニュースとトレンド
ITニュースとは? ITニュースとは、テクノロジー業界の最新情報やトレンド、新しい製品、サービス、企業の動向などを指します。 日々進化するIT業界では、新技術や市場の変化が頻繁に発生するため、最新情報をキャッチアップすることが重要です。 最近話題のITニュース AIの進化と活用 ChatGPTやGoogle Bardなどの生成AIが急成長。 企業でのAI導入が進み、業務効率化やカスタマーサポートに活用されている。 画像生成AIや音声認識技術も進化し、クリエイティブ分野にも影響。 クラウドサービスの拡大 AWS、Azure、Google Cloudの競争が激化。 企業のクラウド移行が加速し、オンプレミス環境からの移行が進む。 セキュリティ対策やデータ保護が重要な課題に。 Web3とブロックチェーン技術 仮想通貨の市場変動が続く中、ブロックチェーン技術の応用が広がる。 NFT(非代替性トークン)の活用が進み、デジタルアートやゲーム業界で話題に。 分散型アプリケーション(DApps)の発展も注目される。 サイバーセキュリティの強化 ランサムウェア攻撃の増加により、企業のセキュリティ対策が強化。 ゼロトラストセキュリティの導入が進む。 個人情報保護法の改正により、データ管理の厳格化が求められる。 ITニュースをキャッチアップする方法 ニュースサイトやブログを活用 TechCrunch、Gigazine、ITmediaなどのテクノロジー専門メディアをチェック。 公式ブログや企業のプレスリリースも参考に。 SNSやYouTubeを活用 TwitterやLinkedInで業界の専門家をフォロー。 YouTubeで最新の技術解説動画を見る。 オンラインセミナーやイベントに参加

ITパスポート試験完全ガイド
ITパスポートとは? ITパスポート(iパス)は、情報処理推進機構(IPA)が実施する国家資格の一つです。 ITの基礎知識を証明する資格で、IT初心者やビジネスパーソンにおすすめです。 ITパスポートを取得するメリット ITの基礎知識が身につく ITに関する基本的な仕組みや用語を学べる。 DX時代に必要なITリテラシーを強化できる。 就職・転職に有利 IT業界だけでなく、一般企業でも評価される。 新卒や未経験者のIT転職の第一歩として有効。 受験しやすい 全国各地で随時受験可能なCBT(Computer Based Testing)方式。 合格率は50%前後で、比較的取得しやすい。 ITパスポート試験の内容 ストラテジ系(経営・戦略) ITを活用した経営戦略やマーケティングの知識。 例:SWOT分析、PDCAサイクル、クラウドの活用。 マネジメント系(管理・運用) ITプロジェクトの管理やセキュリティ対策。 例:システム開発ライフサイクル、リスク管理。 テクノロジ系(技術・知識) ハードウェア、ソフトウェア、ネットワークの基本。 例:CPU、データベース、プログラミングの基礎。 ITパスポートの勉強方法 公式テキスト・過去問を活用 IPA公式サイトに掲載されているシラバスを確認。 過去問を解くことで出題傾向を把握。 オンライン講座を活用

IT業界への転職ガイド
IT転職とは? IT転職とは、エンジニアやデザイナー、プロジェクトマネージャーなどIT関連の職種に転職することを指します。 未経験者から経験者まで、さまざまなキャリアパスが存在し、スキルや資格を活かして活躍できます。 IT転職の魅力 需要が高い DX(デジタルトランスフォーメーション)の進展により、IT人材の需要が急増。 将来的にも安定した職種。 高収入が期待できる 経験やスキル次第で年収アップの可能性大。 フリーランスとして独立する道も。 柔軟な働き方が可能 リモートワークやフレックスタイム制を導入している企業が多い。 ワークライフバランスを重視した働き方ができる。 IT転職におすすめの職種 ソフトウェアエンジニア プログラムを開発し、アプリやシステムを構築。 Java、Python、JavaScriptなどの言語が人気。 インフラエンジニア サーバーやネットワークの設計・管理。 AWSやAzureなどのクラウド技術が重要。 データサイエンティスト データ分析を通じてビジネスの意思決定を支援。 AI・機械学習の知識が求められる。 IT転職を成功させるポイント 必要なスキルを身につける プログラミングやネットワークの基礎を学ぶ。 実務経験を積める環境を探す。 資格を取得する 基本情報技術者試験やAWS認定資格などが役立つ。 転職エージェントを活用する

IT資格の完全ガイド:あなたにピッタリの資格はどれ?
IT資格とは? IT資格とは、情報技術(IT)に関する知識やスキルを証明するための資格のことです。 エンジニアやプログラマーはもちろん、IT業界に興味がある人にもおすすめです。 IT資格を取得することで、 スキルアップ:最新の技術や知識を学べる キャリアアップ:転職や昇進に有利 信頼性の向上:専門的な知識があることを証明できる では、具体的にどんな資格があるのか見ていきましょう! 初心者向けIT資格 基本情報技術者試験(FE) どんな資格? 情報処理技術者試験の中でも、ITの基礎を学ぶのに最適な資格。 なぜおすすめ? ITの基礎知識を体系的に学べる ITエンジニアの登竜門 国家資格なので信頼度が高い どんな人に向いている? IT業界への第一歩を踏み出したい人 基礎からしっかり学びたい人 ITパスポート(IP) どんな資格? IT初心者向けの資格で、ITの基本的な知識を証明できる。 なぜおすすめ? 試験の難易度が比較的低め 文系でも取得しやすい ITを活用するすべての職種に役立つ どんな人に向いている? IT未経験者 事務職や営業職でIT知識を身につけたい人 中級者向けIT資格

IT技術とギャンブルの関連性、歴史、そして将来
IT技術の発展とギャンブルの関連性 IT技術とギャンブルというのは、一見関連性がないように思われますが、実は深いつながりがあります。 この記事では、その関連性と歴史、そして現代にギャンブルにおいてIT技術がどのように生かされているのかについて解説します。 昔はシンプルなギャンブルばかりだった ギャンブルの歴史は古く、古代エジプトの時代から庶民が行っていたことは有名です。 そして、その様式は時代と共に変化していますが、IT技術の発展と普及の以後では大きく違います。 こうした技術が普及する前は、ギャンブルにおける計算はあくまで人間が簡単に行えるものに限られていたり、不正防止技術もアナログな方法が用いられていたりして、シンプルでわかりやすいものが大半でした。 そして、プレイヤーがルールに対して深い理解をしていないとそもそもプレイが難しいものや、「親」と呼ばれるゲームマスターが必要だったりと、気軽にプレイがしづらいものも多くありました。 しかし、IT技術もしくは電子計算技術が一般的になるにつれて、計算のスピードアップ化、そしてそれに伴う複雑な計算の増加や、「親」の電子化により、プレイの難易度は飛躍的に低下していきます。 そして、それに伴いプレイ人口の増加や、情報通信技術による国を超えた勝負が可能になりました。 パチンコなど、電子機械を使うギャンブルの登場 日本における最もポピュラーなギャンブルはパチンコですが、これもIT技術なくしてはプレイできません。 パチンコを行うための機器は電子的に操作されており、それにより玉の出方や確率などはプログラムによって制御されています。 近年では、最新の技術を用いたグラフィックや仕組みを活用しており、ますます高度化が激しくなっており、プレイヤーを魅了しています。 競馬など、既存のギャンブルも徐々に電子化 IT技術の普及前は、競馬等のギャンブルが盛んでしたが、こちらも時代に合わせて電子化が進んでいます。 たとえば、電子的なセンサーを利用してどの馬が勝利したのかを判定したり、馬券の倍率も確率論等を用いて数学的に決定されています。 これらは、IT技術が可能にした非常に早い計算速度を誇るコンピュータにより可能になりました。 IT技術を用いたギャンブルの次のステージはオンラインカジノ そして現在は、IT技術を更に駆使した次世代のギャンブルとして、オンラインカジノが主流になりつつあります。 オンラインカジノというのは、その名の通りインターネット上にカジノを再現したもので、これは近年のインターネット通信技術の発展により生まれました。 従来のギャンブルと比較して、オンラインカジノには下記の利点があることから、プレイ人口は増え続けています。 いつでもどこでもプレイできる 実際に店舗を構える必要がなく、すべての要素がオンライン上で再現されていることから、インターネットに接続できるデバイスさえあれば、いつでもどこでもプレイ可能です。 オンラインカジノはグラフィックや演出に凝ったものが多いのですが、近年はスマートフォンの高性能化が進んでおり、こうした高いスペックが要求されていたゲームも気軽になったことも人気の理由の一つです。 高い還元率 他のギャンブルと比べて経営にまつわるコストが低く、それが高い還元率を実現しています。 どのギャンブルでも、その運営には、店舗の賃料、人件費など多くの費用がかかっていましたが、オンラインカジノなら、基本的にはサイトの運営と変わらない上、サーバーさえ十分に用意すればプレイヤーが増えれば増えるほど売上に対する粗利益の割合が高くなるため、ビジネスとしても魅力的です。 したがって、運営側からしても高い還元率でプレイヤーを集める方が良いという判断が合理的とも言えます。 お得な入金不要ボーナス