1

I've been scouring the internet for days and have finally admitted defeat. I can't find any articles on geospatial distance queries using mysql that are newer than mid-2011. I've combed the mysql docs, googled for hours, done tag searches and text searches here in SO... nothing.

So the question stands: I need to make a pretty run-of-the-mill radius-based query on a mysql database. I'd prefer to use as few add-ons as possible, but I'll take what I can get. Here's a sample:

CREATE TABLE `test_locations` (
    `location_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `locaiton_name` VARCHAR(32) NOT NULL,
    `latlng` POINT NOT NULL
);

SELECT `location_name`, X(`latlng`) as `lat`, Y(`latlng`) as `lng`
FROM `test_locations`
WHERE DISTANCE(PointFromText('POINT(41.78231 -88.712151)'), latlng) < 15;

Yes, this is pseudo-code to a certain degree, because I haven't seen any reference to a (working) mysql native DISTANCE() function yet, but I just can't imagine that in 2 whole years nothing has been done with this. I must be blind....

Thanks in advance for any insights.

kael
  • 6,585
  • 5
  • 21
  • 27
  • 1
    What exactly makes you think, that there's a more "modern" way to do spacial queries than 2 years ago? I mean, if you did some research and didn't find anything, i would assume, there's no news on this issue. – Michael Härtl Mar 19 '13 at 14:38
  • Yes, for instance this http://stackoverflow.com/questions/4687312/querying-within-longitude-and-latitude-in-mysql?rq=1 seems like a good source of information but doesn't meet your 'more recent' criteria. I wouldn't worry about 2 years of aging. I would worry if it was more like 8 years. – Michael Durrant Mar 19 '13 at 14:52
  • Sorry, I didn't mean to be disdainful with my terminology. By "modern" I simply meant that at the time of most of the 2011 writings, the mysql spatial extensions were still in in active development according to most of the posts I read. However, I haven't found any writings about functions that appear to have been implemented since then. "Modern" is important to me from a performance perspective. Rather than crunching a complex function for each row, I'd like to use an index that someone (smarter than me) has designed specifically for the purpose. – kael Mar 19 '13 at 15:17

1 Answers1

1

I guess the broader question is, for a given technical problem, how long can one expect to hide in a cave before someone else solves the issue?

Although DISTANCE still isn't with us, there's no need to hibernate just yet - try the code under 'More Follow-up' in Storing Lat Lng values in MySQL using Spatial Point Type

Ironically the question there is actually "will this be future-proof or will I need to update it in the future", whereas your question is "why hasn't anyone published updated code recently?"

Community
  • 1
  • 1
Paul Gregory
  • 1,733
  • 19
  • 25
  • That "More Follow-up" section was indeed what I was looking for. My bad for missing it the first time I saw that post. As I stated in my comment to the original question, "modern" was meant to imply a solution that utilizes built-in optimizations for a task that I perceive, at least, to be increasingly prevalent. This appears to be it. – kael Mar 19 '13 at 15:26