Searching a MySQL database for values within radius (lat/long)

This question already has an answer here:

  • MySQL Great Circle Distance (Haversine formula) 9 answers

I don't have all that much SQL experience and honestly don't have a clue where to go from here, most people that ask a question like this are using MS Server or something, and can use a bunch of parameters/values that I can't. I'm using MySQL.

Here's the code in Java I use to create a random location within X miles.

public static GeoPosition randomLocation(GeoPosition location, double radius) { Random random = new Random(); // Convert radius from miles to meters double meters = radius * 1609.34; // Convert radius from meters to degrees double radiusInDegrees = meters / 111300f; double u = random.nextDouble(); double v = random.nextDouble(); double w = radiusInDegrees * Math.sqrt(u); double t = 2 * Math.PI * v; double x = w * Math.cos(t); double y = w * Math.sin(t); // Adjust the x-coordinate for the shrinking of the east-west distances double new_x = x / Math.cos(location.latitude()); double foundLongitude = new_x + location.longitude(); double foundLatitude = y + location.latitude(); return new GeoPosition(foundLongitude, foundLatitude); }

These GeoPositions (Groups of longitude/latitude) are stored in the database. However now I need to figure out how to get all of the rows in the database within a radius of X miles.

When writing this out in Java, the distanceBetween method looked like this:

public static double distanceBetween(GeoPosition a, GeoPosition b) { double longDif = a.longitude() - b.longitude(); double distance = Math.sin(deg2rad(a.latitude())) * Math.sin(deg2rad(b.latitude())) + Math.cos(deg2rad(a.latitude())) * Math.cos(deg2rad(b.latitude())) * Math.cos(deg2rad(longDif)); distance = Math.acos(distance); distance = rad2deg(distance); distance = distance * 60 * 1.1515; // Convert to meters distance = distance * 0.8684; // Convert to miles. return distance; } private static double rad2deg(double rad) { return (rad * 180.0 / Math.PI); } private static double deg2rad(double deg) { return (deg * Math.PI / 180.0); }

and I just had to loop through a collection to find all of the positions, where distanceBetween was true. However this seems like more work on the client that needs to be done.

Is there a proper way to handle returning all results within X miles of a latitude/longitude from the database? because returning every result from the server is going to become a bandwidth killer really fast.

Question marked as PHP as well, because that's what I'm going to be using to poll the database.

-------------Problems Reply------------

In your place I simply moved the distance calculation to the mysql side. Although your code is complex, the Haversine formula with it can be calculated, is not. Here you can see also mysql code examples for the calculation, it doesn't even need a stored function.

Unfortunately, this simple formula is already too complex to be indexable from mysql. This solution will solve your bandwidth killing problem, but maybe it will overload your mysql. It will depend on your query rate and the number of your positions.

In case of a mysql overload I suggest to ask this in a new question.

There is a stackexchange site for geographic information systems here.

Category:java Views:5 Time:2018-03-12
Tags: php mysql java

Related post

  • How to search a MySQL database for a specific string 2010-03-21

    I am trying to set up a search feature on my site that will only return exact matches to keyword entered by the user. So if the user searches "dog" I don't want an article titled "Doggy Style" to appear in the search results (just an example I don't

  • How to search sql server database for string? 2012-02-07

    I know it's possible, but don't know how. I need to search Microsoft SQL database for all mentions of specific string. For example: I would like to search all tables, views, functions, stored procedures, ... for string "tblEmployes". (Not data within

  • Monitor MySQL table for changes within a C# program? 2010-04-21

    Is it possible to monitor a mysql table for changes within a c# application? I basically want an event to be raised when data is inserted into the table. The only thing I can think of now is to query the table every 100ms or so. --------------Solutio

  • One single MySQL database for many web2py applications 2011-11-07

    I have 3 apps under my web2py framework. Can I use one single MySQL database for all of them (by prefixing tables) so users just need only one single registration to access those 3 apps? I fail on my attempts. Or any other solutions? Thanks so much.

  • sync two mysql database for two remote machine 2010-11-11

    Any solutions can sync two mysql database for two remote machine? i have to sync periodically thanks --------------Solutions------------- Use replication with master-master or master-slave. I am assuming you want a one-way sync - if you need to go se

  • Automatic Replica Database of mySql database for backup? 2011-12-26

    I have mysql database in my web project. I need to create a replica of my mysql database for backup. My web interface interacts directly with one database, i need that other backup database to get automatically updated whenever there are some changes

  • How to query a MYSQL database for all values within an array 2011-02-23

    I have a MYSQL database with with a users and a activity table. The user table containing user info and the activity table containing messages about a users activity on this site (similar to facebooks news feed). Within the users table i have a store

  • Setting Up an Easily Searchable MySQL Database for Word Searches 2011-06-26

    I have appx. 2TB of text that I want to turn into a searchable database, where I will usually be searching to see if 2-4 word expressions exist in the database (for instance I might do a search to see if the phrase "these are four words", or "three c

  • Search MySql database for a HTML link using PHP 2011-05-08

    I have mysql table that contatins item_id and link columns the link column contains html tags and the actual link. now I want to search the database for a link that doesn't contain html tags what is the best way to do this in php? --------------Solut

  • search cell for values within an array and return a matched value 2014-09-05

    Say that I have a cell that contains the sentence "The blue fox" and I have an array set up with various words including the word "blue." Is there a function that will check the cell to see if if contains any values within an array and then return th

  • What's the best way to search a MySQL database with PHP? 2008-09-30

    Say if I had a table of books in a MySQL database and I wanted to search the 'title' field for keywords (input by the user in a search field); what's the best way of doing this in PHP? Is the MySQL LIKE command the most efficient way to search? -----

  • What is the best way to query a database for records within n miles of a zip code? 2009-02-09

    I have a list of records in my database and each record is associated with a zip code. What is the "best-practice" for querying all the records in my database to find all entries that are within n miles of another zip code? Each zip code has a lat/lo

  • How to perform search on MySQL table for a website 2009-10-10

    How do I perform a search similar to that of Wikipedia on a MySQL table (or several tables at a time) without crawling the database first? (Search on wikipedia used to show you the relevancy in percentage). What I'm looking for here is how to determi

  • How to design a MySQL database for zipcode based service? 2011-05-27

    I'm trying to design a MySQL database to store user zipcode preferences for providing a particular service. For example, user A who is a plumber is willing to travel to x,y, and z zipcodes to provide his service. I've been thinking about the various

  • How to structure mysql database for use with sphinx? 2011-07-08

    I am trying to make a database of products that can be searched by many facets(like newegg or amazon). At first I was going to try to do the whole thing with mysql but further research has led me to believe that is a bad idea so instead I am thinking

  • best way to set up a MySQL database for storing web data 2011-08-16

    I will be using curl to retrieve thousands of adult websites. My goal is to store them in MySQL to allow users to easily search the new database and find their desired page without having to endure all the popups, spyware, etc. It will be an adult we

  • How can I avoid separating the search query to search a MySQL database? 2010-08-12

    When a user tries to search for people on our system, we've noticed that they often type a full name for their search. How can we enable full name searching on our database when first name and last name are stored in different columns? --------------

  • MySQL database for hashes 2011-02-03

    I'm currently trying to create a mySQL database that holds hashes such as MD5 hashes. I'm using PHPmyAdmin version 3.3.9, and MySQL client version: 4.1.22 I already created a database named hashes. I'm new to mySQL so how can I add a table with data

  • How can I query the mysql database for a variable, if exists create another variable, if not insert? 2011-03-31

    say I have a variable $id = mt_rand(); how can I query the mysql database to see if the variable exists in the row id, if it does exist then change the variable $id, once the variable is unique to all other stored ids, then insert it into the databas

  • Designing my MySQL Database for rating things on different days 2011-07-07

    I've been using reddit as my help recently, and they've been great but then I found this site and feel it's a much more appropriate place to ask my questions. Hopefully the helpfulness trend continues here. Let me start by saying I know this project

  • Search a MySQL database using blob data? 2011-07-08

    Is it possible to search through a MySQL database using blob data to find close matches with other blob data types? I have created my database with images stored as blob. I was wondering if it was possible to take a picture similar to the one in the

  • How to get details from mysql database for android? 2011-08-06

    How can I connect MySQL and Android? I have MySQL db in remote I want get the values from that database and display those content in Androidlist view. I have tried many web service samples, but I have met some problems. I have tried this using json a

  • How do I set up slave MySQL database for reporting purposes only? 2011-08-15

    Given a large MySQL production database, optimized for fast inserts, how would I go about setting up a "slave" database that would be optimized for fast searches? In my head, the slave would basically be a replica of the main, but with significantly

  • Returning "sorry, no results found" on a search of MySQL Database 2011-08-15

    I currently have a search feature for a knowledge base that filters out an array of common words (a, able, about, etc) so that they are not sent in the query. Unfortunately, when someone searches just one of those common words it returns nothing (whe

Copyright (C), All Rights Reserved.

processed in 0.156 (s). 11 q(s)