Advanced Search

Return to Snippet

Revision: 69266
at May 20, 2015 01:54 by brownrl
Initial Code 
$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";
Initial URL 
http://www.itsgotto.be/cv
Initial Description 
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.
Initial Title 
Distance in SQLITE
Initial Tags 
php
Initial Language 
PHP