Doing a (very) Little GIS with MySQL

Long Story Short: http://maps.co.mecklenburg.nc.us/demo_eplan/

Long Story Long:

A problem recently came to my desk by way of the Fire Department. It seems they will soon begin using some cool software written by a university in Texas called E-Plan, which manages things like Tier II facilities. A Tier II facility is one that, when set ablaze, makes pretty multi-colored clouds that have the potential to turn any active inhalers into brain-eating zombies. Or something like that. Remember, I’m the guy that lists “911” as my emergency contact.

Anyways, the Fire Department wanted the software to be able to do a quick demographic summary from buffers (say 1, 2, and 5 miles) around the facility. After a talk with the very bright folks in Texas, it turned out they were already using Google Maps to show the locations. So they really just needed something to draw a buffer and return the demographic information. A survey of their software revealed they used MySQL 5 and were of the Java persuasion.

My first goal being to use software that’s already there, I decided to take a look at MySQL’s spatial capabilities. MySQL has had some modicum of spatial features since 4ish, but I remember looking at it quite a while back and giving it a giant meh.

After further research, I’m uping my meh to a MEH!, but there’s enough there to do what we needed to do in a roundabout way by pushing some of it on to the Google Maps API.

It looks like MySQL’s distance searching and buffering functions are not there. Not as in not very good - just not there. After seeing some hacks on the MySQL end of things to calculate sort-of distances, I decided to keep looking.

What MySQL will do is intersect two geometries and return a result set. It’s not what I’d call survey accurate (there’s a lot of talk of using MBR’s in posts I saw), but for demographic summaries of 2000 Census data at those distances it’s good enough.

First I loaded block group points into MySQL as a spatial layer. I used a shp2mysql.exe I found, but it looks like I could have used OGR to do it, or you can add features via your own script, like (imagine more data fields in there):

CREATE TABLE census_blocks (ID INT NOT NULL PRIMARY KEY , ObjectID INT, STATE_FIPS VARCHAR(255), CNTY_FIPS VARCHAR(255), gc_geom POINT); INSERT INTO census_blocks VALUES ('0','106674','37','035',GeometryFromText('POINT (-81.102280464747793 35.743680731288123)',4326) );

Viola, spatial data layer census_blocks. Now all I needed was the geometry of a buffer.

I’m a firm believer in the axiom a good developer borrows, a great developer steals. I was grabbing code faster than a drowning man grabs the nearest Kate Winslet, but I owe the biggest hat tip to this one: http://esa.ilmari.googlepages.com/circle.htm .

I took some of the code that makes the circle polygon to create a list of points making up the polygon, like so:

var geom = ""; for (i=0; i < points.length; i++) { if (geom.length > 1) geom = geom + ","; geom = geom + points[i].lng() + " " + points[i].lat(); } geom = geom + "," + points[0].lng() + " " + points[0].lat();

That last line puts the first point in again at the end to complete the polygon. Then I use a little jQuery call to send the request to a tiny PHP page (which Texas would change to a tiny Java something) and load the results via AJAX:

$.get("mysql_intersect.php", { geometry: geom}, function(data) { $("#results").html(data); } );

All the PHP file does is format the MySQL call so it looks something like this:

select sum(pop2000), sum(pop2004) from census_blocks where Intersects(ogc_geom, GeomFromText('POLYGON((" . $geom . "))'))

That call returns records where the geometry of census_blocks intersects the geometry of a polygon we build from the points we passed from Google Maps. Now we have some population summaries for our buffer from MySQL, which I format a little and send back to the requesting page. MySQL runs the query pretty darn quickly (sub second).

While I like and use MySQL, it isn’t the ideal spatial database. By splitting some of the processing between Google Maps and MySQL, however, I could eek out a solution the E-Plan folks might be able to use without having to change their software stack.