Calculating distance using MySQL

Calculating distance using MySQL” is a very useful blog post for everyone who works with geographical location data and MySQL. It shows a simple example of how to calculate the distance between two coordinates on a sphere (Earth in particular) within the MySQL itself.

SELECT ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267)
);

The above will return 1148978.6738241839, which is the distance between the two points in meters.

This functionality is available since MySQL 5.7. Have a look at the documentation of the spacial convenience functions.

MariaDB has similar functionality, but with a slightly different function names. Use ST_DISTANCE() instead of ST_Distance_Sphere(). Have a look at this blog post for more details.

Goodbye Waze, hello Google Maps

I am a big fan of social apps, especially those that address a particular problem, usually outside of the generic social networks. Unfortunately, many of these apps suffer from the same set of problems – insufficient user base to make them useful, competition from larger apps with overlapping functionality, and feature stagnation.

If I find the application useful, I try to ignore these problems for as long as I can. But, unfortunately, at some point even the best of us give up.

Last year I gave up Swarm and Foursquare. This time around, I am giving up Waze in favor of Google Maps.

Waze is like a social network for drivers. There are plenty of maps and navigation apps, but Waze went further. The app had the functionality to assist with mapping the roads, reporting police and road hazards, and some basic social and gaming functionality, where you could communicate and compete with other drivers. (The competition wasn’t speed based, but rewarded drivers who contributed the most.)

Waze never was a big hit, at least not here in Cyprus. But it was big enough to be acquired by Google back in 2013 for over one billion dollars.

Waze wasn’t shut down after the acquisition and the deal kind of made since, as Google would get real-time human contributions to compliment its automated ways of Google Maps.

But it didn’t solve the problems of Waze at all, if not made them even worse. More and more people started using Google Maps. The development of Waze slowed down to a crawl. And even the most vital features for such an app were never added.

As far as I was concerned, I could even live without the large user base. But there is one particular feature that kept annoying me until now, which was never added. There is no way to drop a pin on the map. Yes, that’s right, Waze is a map and navigation app without a pin. Instead, you can either search for places to go, or enter a street address to go to.

Cyprus is the country where street addresses are seldom used for navigation. Most of the cities grew out of small villages that overlapped with time. Which means, there is no preset design for the cities, like in the USA with the street-avenue grid. And most of the villages had the streets named after the same people, which, in the city causes lots of confusion with several streets in different parts, named the same. Heck, we even have streets with the same name crossing each other.

Try telling Waze that you are going to your friends house. You know where it is on the map, but you don’t know the exact address. (Yes, you might know the street name, but not the number.) And you’ll know what I mean.

On top of that, with fewer and fewer users contributing to the app, the data gets obsolete. There are places that have closed years ago. There are places that have moved to a different address. And there are plenty of new places that Waze knows nothing about.

And since you’ve got me complaining, here’s another feature that I miss, which is also missing or inadequately implemented in all the other apps I’ve tried – custom repeatable routes with multiple stop points.

Google Maps has a very basic “Commute to” feature, where you can just set your work and home, and then quickly navigate to either one or the other. Waze and many other apps have the same. But that only takes you so far.

Here are two scenarios which are a pain in pretty much every navigation app:

  1. More than two commute entries. Yes, work and home are common destination points for most of the user base. But what about school? Many of us are not 18 anymore and need to drive the kids to or from school. Sometimes, even more often than we navigate to home or work. People might have more than two jobs. Or they might have other destinations that they visit on a daily basis. It might a doctor’s office, or an older relative for a quick check. Why not expand the short list of “Commute to” entries to more than 2. Make it 3 or 5, and that covers most frequent routes for most people.
  2. More than two points in a route. Sure, home to work, and work to home, makes sense. But for over a year I had to commute to work, while picking up two colleagues on the way in, and dropping them home on the way back. Even dropping off the kids to school on the way to work is a common scenario among the parents I know. Why can’t we just connect the dots? Create a new route from one place to another, add a couple of stops in between, and save it in the shortlist for quick access. This will even help with the navigation part as well. The app won’t have to insist on re-routing me on every turn, when I briefly drive in the direction opposite to my office to pick up a colleague.

So for the last couple of month, I haven’t used Waze for my navigation needs. I tried a whole lot of other apps, and after a brief try outs, I decided to use Google Maps for now. It’s far from perfect, but it sucks less than others.

And just after I’ve made my mind, I came across the news that Google Maps will get the speed limits and radar locations feature. A feature inspired by Waze.

Oh, well. That’s good to know. But that just confirms my decision of letting Waze go and using Google Maps. At least for now. We’ll see what the future brings. Hopefully Google won’t kill the Goolge Maps app, like it did so many others.

Goodbye Waze and thanks for all the good times. I’ve enjoyed our time together, but now it’s time to drive forward. Hello Google Maps. Please learn from the mistakes of Waze. You’ve paid the money already.

Terrible Maps

Via this kottke blog post I came across the @TerribleMaps Twitter account that collects links and images of funny, non-sense, inaccurate and other terrible maps.  Most of this stuff is quite funny.

PHP: Countries and currencies


Many software projects deal with the lists of countries and currencies.  Some of the most common tasks include country an currency dropdowns, country flags next to the IP, or pre-filling country codes in phone numbers.

All of that information is of course standardized and you often just need a library or two to provide and use it.  And there are many of those.  We’ve been mainly using these two:

Today, however, I came across a better option – antonioribeiro/countries, which is a collection of country and currency information for Laravel PHP framework.  Laravel is not required though.  This library provides way more information and in a much more flexible way.  It includes:

  • 266 countries (with more codes, common names, official names, and more)
  • 256 currencies
  • 1,570 flags
  • 248 geometry maps
  • 248 topology maps
  • 4,526 states
  • 7,376 cities
  • and a lot more!