6 Articles tagged “ip4r”

We have loaded Open Street Map points of interests in the article The Most Popular Pub Names — which compares PostgreSQL with MongoDB for simple geographical queries, and is part of our PostgreSQL Extensions article series. In today’s article, look at how to geolocalize an IP address and locate the nearest pub, all within a single SQL query!

For that, we are going to use the awesome ip4r extension from RhodiumToad.

In our previous article about Loading Geolocation Data, we did load some data into PostgreSQL and saw the quite noticable impact of a user transformation. As it happens, the function that did the integer to IP representation was so naive as to scratch the micro optimisation itch of some Common Lisp hackers: thanks a lot guys, in particular stassats who came up with the solution we’re seeing now.

As I’ve been mentionning in the past already, I’m currently rewriting pgloader from scratch in Common Lisp. In terms of technical debt that’s akin to declaring bankrupcy, which is both sad news and good news as there’s suddenly new hope of doing it right this time.

We still have this problem to solve with extensions and their packaging. How to best organize things so that your extension is compatible with before 9.1 and 9.1 and following releases of PostgreSQL? Well, I had to do it for the ip4r contribution, and I wanted the following to happen: dpkg-deb: building package `postgresql-8.3-ip4r' ... dpkg-deb: building package `postgresql-8.4-ip4r' ... dpkg-deb: building package `postgresql-9.0-ip4r' ... dpkg-deb: building package `postgresql-9.1-ip4r' ... And here’s a simple enough way to achieve that.

If you’ve not been following closely you might have missed out on extensions integration. Well, Tom spent some time on the patches I’ve been preparing for the last 4 months. And not only did he commit most of the work but he also enhanced some parts of the code (better factoring) and basically finished it. At the previous developer meeting his advice was to avoid putting too much into the very first version of the patch for it to stand its chances of being integrated, and while in the review process more than one major PostgreSQL contributor expressed worries about the size of the patch and the number of features proposed.