Write a manual on how to optimize a mysql DB for finding nearby or nearest places based on geo coordinates

Please read the below very carefully and only respons if you are 100 % sure you can do the job.

BACKGROUND INFORMATION:

I have a mysql Database containing names of places and geocoordinates: latitude and longitude. A typical record in the database would be:

id: 124124123

name: New York

latitude: 40.713956

longitude: [url removed, login to view]

The database runs on a system with the following details:

PHP: 5.3.8

CentOS 5.0 64-bit

Apache [url removed, login to view]

MySQL [url removed, login to view]

There are two ways for finding 1 or more places:

1. Finding the nearest place due to a given latitude and longitude (currently there is a query saying latitude AND longitude should both be between two given values, then calculating the distance and sorting by distance and selecing the record with the smallest distance)

2. Finding all the places within a certain circle around a point defined by latitude and longitude (Currently there is a query that just selects all the latitude and longitude values between certain tresholds)

As the database is large the database should have proper indexes (e.g. spatial indexes) that provide a real fast search.

TASK:

Write a manual on how to improve this database in order to quickly find 1 or more nearby places given a latitude and longitude as input values.

The manual should be idiot proof, meaning that every step is described and this will work for a database given the above details of both mysql version and system. It should be taken into account that the current queries consist of combination of several tables and the table containing the latitude and longitude contains a large number of columns (approx. 20). The manual will contain:

- Step by step on how the current database will be improved (how to apply indexes etc.)

- Example queries on how to select the nearest place based on input of latitude and longitude values

- Example queries of how to select the nearest places base on input of latitude and longitude values within a circle with a given radius.

MySQL PHP

Project ID: #4211053

About the project