60577

SQL search using Haversine in Doctrine

Question:

I want to do a search by coordinates, i.e. I want to have a function that works like this:

function getLocationsInCircle($lat, $long, $minDist, $maxDist){ //return all the places that are at least $minDist //kilometers away and no more than $maxDist kilometers away }

I have a "location" table that stores all location Ids and their latitude and longitude.

The haversine formula is good enough for what I want to do

6371 * ACOS(SIN(RADIANS( $lat )) * SIN(RADIANS( latitude )) + COS(RADIANS( $lat )) * COS(RADIANS( latitude )) * COS(RADIANS( longitude ) - RADIANS( $long )))

I just don't see how to run that query in Doctrine.

Answer1:

Try this :

Doctrine_query::create()->select('id') ->addSelect("(6371 * ACOS(SIN(RADIANS($latitude)) * SIN(RADIANS(l.latitude)) + COS(RADIANS($latitude)) * COS(RADIANS(l.latitude)) * COS(RADIANS(l.longitude) - RADIANS($longitude)))) as Distance") ->from('Location l') ->having("Distance > $minDist AND Distance < $maxDist");

Recommend

  • How to select longitude, latitude around specifc location from mysql?
  • iPhone application Getting data from Mysql database
  • Python 3.5.1 - Give user 3 invalid attempts then terminate pgm (Simple FOR loop)
  • How to save a leaflet map with drawn shapes/points on it in Shiny?
  • What is the unit of measurement used in MySQL's spatial Buffer call?
  • What is the metric for distance_upper_bound parameter in scipy.spatial.KDTree.query?
  • Error: Aesthetics must be either length 1 or the same as the data (4)
  • Parse weird bug in Swift that causes ACL write permissions to change to an objectId
  • R ggplot2 combining geom_tile and coord_map(“moll”)
  • Javascript : access return of other function
  • how to check radius of 10 meter from x,y position - from GPS
  • What is the fastest way to select nearest geographical place from mysql database?
  • Tokenize python source code examples (in Python)
  • How do you open .txt file containing matrix in Matlab?
  • Modify a Google App Engine entity id?
  • Can I use jquery to blank textarea fields or ajax like input boxes?
  • How can I see a list of all files that are different between two Hg repositories?
  • custom string delimiters stringtemplate-4
  • Insertion large number of Entities into SQL Server 2012 [duplicate]
  • Receiver has no segue with identifier“***”
  • Google map in top corner left
  • Examples of how to a STS in .Net 4.5 using WCF
  • Updating Dojo provide
  • android google indoor map
  • Zoom in and out of jPanel
  • Firefox Extension - Monitor refresh and change of tab
  • Saving Changes After In-App Purchase Has Been Purchased
  • Is there a perl module to validate passwords stored in “{crypt}hashedpassword” “{ssha}hashedpassword
  • Jackson Parser: ignore deserializing for type mismatch
  • one Local Olampyad Questions on Informatic in 2011
  • To display the title for the current loaction in map in iphone
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • How to format a variable of double type
  • Comma separated Values
  • Trying to get generic when generic is not available
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?