Gregory Todd Williams

I’m speechless. Support for spatial geometry has been in MySQL since version 4.1.0 was released in April of 2003. I can only assume that the Distance() function has been documented on the MySQL dev page since that release. However, MySQL bug #5039, posted on August 13^th, 2004 (16 months since 4.1 was released!) complains that the author is “unable to get distance function in 4.1 beta to work.” Sure enough, the next day a poster confirms, “Verified against 4.1.4; function does not appear to exist.” Another adds, “As documented, Distance() is not yet implemented.” The bug info hasn’t been updated in six months, and there’s no indication if it will ever be fixed, or if the 5.0 development release has or will have a fix.

Erik suggested that I drop MySQL entirely and move over to Postres, where spatial geometry support seems more stable. I used Fink to install PostgreSQL 7.4.6, and got a simple table working in no time. I imported Rhode Island Tiger/Line data, gave my program a latitude/longitude pair, and it gave me back a (correct) street address. Slick.

I’m not sure if this is going to scale without jumping through a lot of hoops. I imported the California Tiger/Line data, and queries dropped to the timeframe of about a minute. It doesn’t look like Postgres can use an R-Tree on a Line/Path, and even if it could, the <-> distance operator doesn’t work with an R-Tree index. Also, it’s going to be a disappointment when and if I ever get something workable developed on my dual-G5, and want to move it to a (very modestly powered) single processor Intel webserver. I guess we’ll see.