Distance in SQLITE


/ Published in: PHP
Save to your folder(s)

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.


Copy this code and paste it in your HTML
  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";

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.