代码之家 › 专栏 › 技术社区 › Uffo

将sql转换为Zend_Table

q zend-framework mysql php

Uffo · 技术社区 · 13 年前

如何转换:

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;

到Zend_Table。

我在数据库中有两个键,lat和long,我想获得接近用户的数据,查询由第一个用户提供,该用户将其转换为yo Zend_Table,但如果我有来自以下用户的数据 在同一个城市 它不返回任何数据,我想查询需要改进,以显示更大距离的结果

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

我不知道该怎么做,你们能帮我吗?

//乐

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

在查找与你关系密切的用户时,这个查询似乎是错误的,这个应该可以做到

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

2 回复 | 直到 13 年前

Bill Karwin 13 年前

使用ZF 1.12.4-dev、PHP 5.3.26、MySQL 5.6.3进行测试

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());

使用ZF 2.2.5-dev、PHP 5.3.26、MySQL 5.6.3进行测试

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());

测试数据和测试SQL查询(不带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;

SQL测试的输出:

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

PHP测试输出:

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

)

boojer 13 年前

$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');