Posted By

brownrl on 05/20/15

Tagged

Versions (?)

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

Statistics

Viewed 779 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.

Highlighted TextExpand

$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.