Category: MySQL

Articles about the MySQL relational data management system.

Unique Numbers in Oracle and MySQL

It’s common in database work to need unique integers. They get used in id columns in tables, and for other purposes. This note compares and contrasts the Oracle and MySQL approaches to doing this. MySQL handles this need with autoincrement columns, in tables and Oracle handles it with database objects called sequences. In MySQL, when you have an autoincrement column… Read more →

Fast nearest-location finder for SQL (MySQL, PostgreSQL, SQL Server)

I’ve spent enough time goofing around with location-finder software that it’s worth writing up how to do it.   Of course, finding distances on the surface of the earth means using Great Circle distances, worked out with the Haversine formula, also called the Spherical Cosine Law formula. The problem is this: Given a table of locations with latitudes and longitudes,… Read more →

Filling in missing data with sequences of cardinal integers

When you’re extracting summaries of information from a database, sometimes rows are missing.  For example, if you are keeping track of number of customers by day, you might use a query like this: View the code on Gist. You might get a result set like this: 2014-03-07 122 2014-03-08 355 2014-03-10 234 2014-03-11 119 Notice that the row for 2014-03-09… Read more →

Publicly Available Datasets

Sometimes learning to use data systems like MySQL means you need to get your hands on various publicly available sets of data. Here are some sources. Pro Publica, the investigative news powerhouse, has a Data Store. It’s mostly health care related material. Not all their datasets are free, but some are. If you’re interested in historical meteorological observations (weather data),… Read more →

The Vincenty great-circle distance formula

This Vincenty formula is a more numerically stable version of the spherical cosine law formula (commonly and wrongly known as the Haversine formula) for computing great circle distances. The question of numerical stability comes up specifically when the distances between points are small. In those cases the cosine is very close to 1, so the inverse cosine function is not… Read more →

SQL Reporting by time intervals

A version of this article specific to the Oracle DBMS is here. It’s often helpful to use SQL to group information by periods of time. For example, we might like to examine sales data. For example, we might have a table of individual sales transactions like so. Sales: sales_id int sales_time datetime net decimal(7,2) tax decimal(7,2) gross decimal(7,2) Each time… Read more →