Posted By

brownrl on 05/20/15


Tagged

php longitude distance latitude SQLite


Versions (?)

Distance in SQLITE


 / Published in: PHP
 

URL: http://www.itsgotto.be/cv

This is damn crazy! sQlite does not have all the trig functions need to do distances. So here is how you load that into sQlite on the fly.

  1. $db = new PDO('sqlite:db/database.sqlite');
  2.  
  3. $db->sqliteCreateFunction('distance', function () {
  4. $geo = func_get_args();
  5. foreach( $geo as $k => $v ) {
  6. $geo[$k] = deg2rad( $v );
  7. }
  8. return round(acos(sin($geo[0]) * sin($geo[2]) + cos($geo[0]) * cos($geo[2]) * cos($geo[1] - $geo[3])) * 6378.14, 3);
  9. }, 4);
  10.  
  11. $lat = ( isset( $_GET['lat'] ) ) ? floatval( $_GET['lat'] ) : 50.5039;
  12. $lng = ( isset( $_GET['lng'] ) ) ? floatval( $_GET['lng'] ) : 4.46994;
  13. $pp = 25;
  14. $offset = 0;
  15. if( isset( $_GET['offset'] ) && ! empty( $_GET['offset'] ) ) {
  16. $offset = $pp * intval( $_GET['offset'] );
  17. }
  18.  
  19. $q = "SELECT oc.content_id,
  20. distance(
  21. (
  22. SELECT value_text FROM contents
  23. INNER JOIN contentvalues ON value_content_id = content_id
  24. INNER JOIN typefields ON value_field_id = field_id
  25. WHERE field_alias = 'latitude' AND content_id = oc.content_id
  26. ) , (
  27.  
  28. SELECT value_text FROM contents
  29. INNER JOIN contentvalues ON value_content_id = content_id
  30. INNER JOIN typefields ON value_field_id = field_id
  31. WHERE field_alias = 'longitude' AND content_id = oc.content_id
  32. ) , $lat , $lng ) AS dis
  33.  
  34. FROM contents AS oc WHERE ".time()." BETWEEN content_publish_start_date AND content_publish_end_date ".
  35. "ORDER BY dis ASC LIMIT $offset , $pp";

Report this snippet  

You need to login to post a comment.