Google Maps data in database

Posted on 2012-09-04
Medium Priority
Last Modified: 2012-09-14
I am creating a data backend for a Google Maps development.
I know how to perform the PHP request, teh SQL statement and the display mechanism.

I am using the location script for my SQL Statement:
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

I have a table that has the following:
Name, Description, Address1, City, State, Zip, lat, long, etc...

my question is indexing the datatable for fast response.
What I don't normally see is how the datatable is formed.
if I have 100,000 records, should I place indexes?
and if so? where?

They will be searching distance from zip?  search by zip? city,state? and single address.
they may filter from there.

Any ideas would be great.  I got the code, I just want to optimize the backend.
Question by:Evan Cutler
LVL 24

Expert Comment

ID: 38367222
If you have latitude and longitude data and want to do this in MySQL, you need to use the spatial extensions.  See http://dev.mysql.com/doc/refman/5.1/en/spatial-extensions.html

The thing with your current method is there's no way an index can help.  That's why extensions such as this were developed.
LVL 26

Accepted Solution

Tomas Helgi Johannsson earned 2000 total points
ID: 38368065

Having indexes on a table is crucial when searching large databases with sql queries in regards to performance.
What you should do is form your queries with "where" clauses where your search criteria is one or more of your columns for example

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
FROM markers
where name = ...
HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

This query would benefit from the index (name,lat,long,id)

All colums of a where clause should be in an index or indexes.

   Tomas Helgi

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question