Search code examples
mysqlpostgresqldoctrine-ormgeolocationcodeigniter-2

Geolocation queries in Doctrine2

I am using Doctrine2 and CodeIgniter2 for my test application. I have a table in my database that stores all the geographic locations have fields

  1. Name
  2. Latitude
  3. Longitude
  4. Created(Timestamp)

I see that the sql statement by haversine formula to select locations will look like

(as mentioned in another answer)

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;

Now I am finding it difficult to do this using create query builder. I am not sure if DQL or querybuilder even supports trigonometric functions. Also there are chances that my db will be migrated to postgre or can stay with MySql (yes, this is really a pain in the back) as that decision is out of my control.

All I was told is to use doctrine's methods to achieve this and hence the db will become scalable in the future once it migrates to any of the doctrine's supported platforms. I know this is absurd. But is it really possible to query geolocation data using the latitude and longitude values in the database?

Regards,

Ashok Srinivasan

Solution

  • DQL only provides the following functions:

    • ABS
    • CONCAT
    • CURRENT_DATE()
    • CURRENT_TIME()
    • CURRENT_TIMESTAMP()
    • LENGTH(str)
    • LOCATE(needle, haystack [, offset])
    • LOWER(str)
    • MOD(a, b)
    • SIZE(collection)
    • SQRT(q)
    • SUBSTRING(str, start [, length])
    • UPPER(str)
    • DATE_ADD(date, days, unit)
    • DATE_SUB(date, days, unit)
    • DATE_DIFF(date1, date2)

    However, you can create your own functions (radians for example) Adding your own functions to the DQL language.