Convert sql to Zend_Table

Question

How can I convert this:

SELECT latitude, longitude, SQRT(
    POW(69.1 * (latitude - [startlat]), 2) +
    POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;

to Zend_Table.

I have two keys, lat and long in the database and I want to get the data which is close to the user,the query works provided by the first user which converted it yo Zend_Table but if I have data from people which are in the same city it doesn't return any data, I guess the query needs to be improved, to also show results for a bigger distance

$this->select()->from(array('c' => 'content'))......

I have noe idea how to do this, can you guys help me?

//LE

$data2->setIntegrityCheck(FALSE)
        ->from(array('c' => 'content'), array('location', 'id', 'user_id', 'date', 'attachment', 'content','lat','long'))
        ->columns(array(
            'distance'  => "SQRT(POW(69.1 * (lat - {$location['lat']}), 2) + POW(69.1 * ({$location['long']} - long) * COS(lat / 57.3), 2))"
        ))
        ->having('distance < ?', 25)

It looks like the query is wrong for finding users close to you, this one should do the trick

ASIN(
SQRT( POWER(SIN((@orig_lat -
abs( 
dest.lat)) * pi()/180 / 2),2) + COS(@orig_lat * pi()/180 ) * COS( 
abs
(dest.lat) *  pi()/180) * POWER(SIN((@orig_lon – dest.lon) *  pi()/180 / 2), 2) ))

as distance

Answer 1

Tested with ZF 1.12.4-dev, PHP 5.3.26, MySQL 5.6.13

Zend_Db_Table::setDefaultAdapter($adapter);
$table = new Zend_Db_Table("TableName");

$startLat = 39.0;
$startLng = 122.0;

$select = $table->select()
    ->from($table, array("latitude", "longitude",
        "distance" => "SQRT(
            POW(69.1 * (latitude - $startLat), 2) +
            POW(69.1 * ($startLng - longitude) * COS(latitude / 57.3), 2))"))
    ->having("`distance` < ?", 25)
    ->order("distance");

$rowset = $table->fetchAll($select);

print_r($rowset->toArray());

Tested with ZF 2.2.5-dev, PHP 5.3.26, MySQL 5.6.13

use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Expression;

$table = new TableGateway("TableName", $adapter);

$startLat = 39.0;
$startLng = 122.0;

$rowset = $table->select(
    function (Select $select) {
        global $startLat, $startLng;
        $select->columns(
            array(
                "latitude",
                "longitude",
                "distance" => new Expression("SQRT(
                    POW(69.1 * (latitude - ?), 2) +
                    POW(69.1 * (? - longitude) * COS(latitude / 57.3), 2))",
                    array($startLat, $startLng))
                )
            );
        $select->having->lessThan("distance", 25);
        $select->order("distance");
    }
);

print_r($rowset->toArray());

Test data and test SQL query (without PHP):

use test;

drop table if exists TableName;

create table TableName (
    id int auto_increment primary key,
    latitude numeric(9,4) NOT NULL,
    longitude numeric(9,4) NOT NULL,
    key (latitude,longitude)
);

insert into TableName (latitude, longitude) values
(10.0, 10.0),
(20.0, 20.0),
(30.0, 30.0),
(40.0, 40.0),
(50.0, 50.0),
(60.0, 60.0),
(70.0, 70.0),
(39.2, 122.2);

set @startlat = 39.0;
set @startlng = 122.0;

SELECT latitude, longitude, SQRT(
        POW(69.1 * (latitude - @startlat), 2) +
        POW(69.1 * (@startlng - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName
HAVING distance < 25
ORDER BY distance;

Output of SQL test:

$ mysql -E < 19057187.sql
*************************** 1. row ***************************
 latitude: 39.2000
longitude: 122.2000
 distance: 17.484284526375415

Output of PHP test:

$ php 19057187.php
Array
(
    [0] => Array
        (
            [latitude] => 39.2000
            [longitude] => 122.2000
            [distance] => 17.484284526375415
        )

)
Array
(
    [0] => Array
        (
            [latitude] => 39.2000
            [longitude] => 122.2000
            [distance] => 17.48428452637566
        )

)

Answer 2

$db = Zend_Db_Table::getDefaultAdapter();
$select = $db->select();
$select->from('TableName', array('latitude', 'longitude'));
$select->columns(array(
    'distance'  => "SQRT(POW(69.1 * (latitude - {$startLat}), 2) + POW(69.1 * ({$startLong} - longitude) * COS(latitude / 57.3), 2))"
));
$select->having('distance < ?', 25);
$select->order('distance ASC');