Mac Geekery

I have a need. I have to make latitude/longitude (LL) points more reasonable and usable by humans.

This is quite possibly one of the more difficult tasks out there right now, and also one of the most-outsourced as a result. The reason is simple: the dataset is huge and the calculations quite intensive.

In order to get information about an LL, like the city, state, county, zip code, or (most complicated) street and approximate address, you have to do some geometric math. Let’s start with counties, since that happened to be the first thing I had some success in. You get a list of counties and their borders as geometric shapes. You then take a range from the LL and find all polygons that touch that range. Then you further filter to the one that point is in. You do this because bounding box calculations are faster than polygon intersection calculations.

Given a database of tens of thousands of polygons, you have do some pretty intense things to index them properly so that a query like this can succeed. Luckily, it’s really just the same things over and over again because it’s the same problem over and over again. And when something like that happens, a software package tends to come to the rescue. For this, the software package is PostGIS an extension to the PostgreSQL database server. By using this package properly, we don’t have to worry too much about the math behind this and can just pass in an LL and get back some answers.

Installing PostgreSQL and PostGIS

This one’s easy if you have MacPorts installed.

sudo port install postgis

Sit back and wait as it fetches PostgreSQL, PostGIS, and all other required packages. When it’s done, get the server as well.

sudo port install postgresql83-server

Setting up PostgreSQL

When it has finished working, you are the proud owner of an installation, but it’s not setup yet. To do this, we need to setup PgSQL and then setup a new database with the PgGIS extensions.

First, we need the PgSQL tools in our PATH so add /opt/local/lib/postgresql83/bin/ to your PATH environment variable.

To make this permanent, add it to your .profile file.

Now, we make the default data store as per the instructions that MacPorts just displayed:

Since the datastore is ready, we can setup PgSQL to start up with the system now. Let’s enable the launchd file that MacPorts installed so this is handled for us.

We have the datastore setup and the server running now, so now we make the database and user. Everything we do for now needs to be done as the postgres user since that’s the user the install script created and blessed. We’ll make another user soon.

By default, PgSQL tries to connect with the name of the current user and to a database of the same name. Since we’re doing this locally we can make life a little easier and take advantage of that. For the following commands, use your username as the argument.

You can return to your normal user now and when you enter the psql command it should just drop you in. Tada.

Setting up PostGIS

Now that we have a database and a handy login, we need to setup the datatypes and functions that link PostgreSQL to PostGIS’ library. Before we load those, however, we have to enable the PL/pgSQL language extension. To do this:

The following commands should then install the functions:

And, amazingly, that’s all there is here. We’ve just run a lot of SQL commands against the database to teach it about what PostGIS can do and now you’re ready to play.

Importing Some Data

The US Census Bureau makes a yearly dataset called TIGER/Line available that includes various sets of data ranging from ZIP codes and counties down to street-level data. In total, you’re looking at over 100GB of data. Right now, we’re going to just look at counties, which comes out to about 120MB of shape data.

Now in the GIS world the boundaries of an object, such as a political line, are called shapes. These are stored in shapefiles and those files are loaded and parsed by the GIS apps. Here, we need to convert this list of shapes into polygons in the database. Luckily, PostGIS comes with some tools to help us here, so we just get to get the shapefile for the counties and then we’re good.

Get the county file from the Census site and open it up. Inside there’s a shapefile called tl_2008_us_county.shp (or tl_2008_us_county00.shp).

You can also get the UAC file if you want to hit-test urban centers (aka cities and towns).

Run the following command to load the data:

You will get a lot of output as this inserts a lot of data. Just wait for it to finish. When it’s done, you’ll have loaded the system with the shapes of all the counties in the US (or cities, if that’s the file you chose). Now we have to play with it.

Playing With the Data

Here’s a jump start on querying the data; you really want to read up on the syntax and what it’s doing if you’re going to use this in production.

You’ll note that ran a little slow. The reason is we’re not using the index at all. Normally what you want to do is setup a bounding box and limit the brute-force hit test to the results of that bounding box. So let’s rewrite the query to do that.

Note that the units of measure here are all degrees of LL. The Internet has a lot more reading on this topic and can tell you how to migrate the measurements to meters or what-have-you if you would like that.

That should return almost instantly. We limited the counties and cities to those that intersect within one degree of our location and then searched only those.