Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 0 down vote favorite

Possible Duplicate:
how to find 20 closest points efficiently
How to find points, sort them based on closeness, and get 1-20, 21-40, etc. Efficiently using Myisam and mysql

Actually I want to find N closest points.

However, I learn that such thing don't exist in mysql myisam. I asked quesetions here no answer.

So I'll follow people's advice to search for points in a rectangle and then keep expanding the rectangle till I got enough points.

How would I do so? What would be the exact mysql command to do so?

I think I will make PhP loop. If possible, it'll be great if the loop is inside the mysql command itself.

Steps I already took: 1. I already add spatial extension. 2. I already ask around if, given that I have the spatial extension, I can easily get N nearest neighbors for myIsam mysql. I do NOT find ANY answer that works for mySQL myISam spatial index. Some says such answer do not exist because mysql is late comer.

how to find 20 closest points efficiently

So please don't put comment like "you can add spatial extension." That's like starting from the beginning. I know. It's listed on the question I linked.

Actually it's a good comment because it remind me I didn't mention what I went through before asking.

I wouldn't have asked this question if adding spatial extension worked easily.

In short I wouldn't have asked anything until I am really stuck and have searched for this for hours.

Again I ALREADY have spatial indexes. I wouldn't be in GIS if I don't even know what spatial index is.

Now I want actual mysql command to take advantage of that so called spatial index.

share|improve this question
I already add spatial extension. Then what? – Jim Thio Jun 21 '12 at 7:43
then you can get the closest points with single query, if you have some contraints concerning these technologies so you got to use just MySQL without extension, you can add a column named "Distance", the calculate the distance sqrt((X-X0)² + (Y-Y0)²) , then sort by this column and limit by N – geogeek Jun 21 '12 at 7:46
That means computing 1.6 millions distance for each row. The location of business doesn't change. The location of people change and hence distance between businesses and people change all the time. – Jim Thio Jun 21 '12 at 7:48
3  
There are several answers to this in how to find 20 closest points efficiently and that question has been asked for less than a day - be patient :-) – iant♦ Jun 21 '12 at 7:54
1  
@iant, I didn't put that I want mysql and myisam solution for that in the original question. I added latter because everyone give answer for post gis. Not their fault. I think I made another question to respect those who gave answers there. – Jim Thio Jun 21 '12 at 8:06
show 6 more comments

marked as duplicate by iant♦, underdark♦ Jun 21 '12 at 8:36

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Browse other questions tagged or ask your own question.