Posted By

brownrl on 05/20/15

Tagged

Versions (?)

Last Edited at 05/20/15 01:54am

Statistics

Viewed 945 times

Favorited by 0 user(s)

Related snippets

Distance in SQLITE

/ Published in: PHP

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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

$db = new PDO('sqlite:db/database.sqlite');
 
$db->sqliteCreateFunction('distance', function () {
    $geo = func_get_args();
    foreach( $geo as $k => $v ) {
        $geo[$k] = deg2rad( $v );
    }
    return round(acos(sin($geo[0]) * sin($geo[2]) + cos($geo[0]) * cos($geo[2]) * cos($geo[1] - $geo[3])) * 6378.14, 3);
}, 4);
 
$lat = ( isset( $_GET['lat'] ) ) ? floatval( $_GET['lat'] ) : 50.5039;
$lng = ( isset( $_GET['lng'] ) ) ? floatval( $_GET['lng'] ) : 4.46994;
$pp = 25;
$offset = 0;
if( isset( $_GET['offset'] ) && ! empty( $_GET['offset'] ) ) {
    $offset = $pp * intval( $_GET['offset'] );
}
 
$q = "SELECT oc.content_id,
    distance(
        (
            SELECT value_text FROM contents
            INNER JOIN contentvalues ON value_content_id = content_id
            INNER JOIN typefields ON value_field_id = field_id
            WHERE field_alias = 'latitude' AND content_id = oc.content_id
        ) , (
 
            SELECT value_text FROM contents
            INNER JOIN contentvalues ON value_content_id = content_id
            INNER JOIN typefields ON value_field_id = field_id
            WHERE field_alias = 'longitude' AND content_id = oc.content_id
        ) , $lat , $lng ) AS dis
 
        FROM contents AS oc WHERE ".time()." BETWEEN content_publish_start_date AND content_publish_end_date ".
        "ORDER BY dis ASC LIMIT $offset , $pp";

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.