Needed: Spatial Geometry/R-Tree Support

February 17th, 2005 11:12 PM

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 13th, 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.


that’s ridiculous! how can they claim to support spatial geometry without a working distance() function (that’s already documented no less!)?

oh, and elmer is a dual processer server i’ll have you know! :)

Posted by: gary on February 18th, 2005 6:57 PM

Well, I guess that’s my bad. I’ve fixed the post, but the point still stands that moving from my G5 to elmer will still involve quite a bit of performance degredation (in slower cpu and less available memory).

Posted by: kasei on February 18th, 2005 7:09 PM

That’s what you get when you go out and buy a new spanking new system.

Posted by: Matt on February 18th, 2005 10:09 PM