如何使用 yii 模型插入 mysql 空间点?

2022-01-04 00:00:00 spatial geospatial mysql yii

我有一个模型类型，它是从一个 mysql 表生成的，该表具有地址数据和一个名为坐标"的空间 POINT 字段.创建或更新模型时，我想对地址进行地理编码并将纬度和经度坐标存储在 POINT 字段中.

I have a model type that was generated from a mysql table that has address data and also a spatial POINT field named "coordinates". When a model is created or updated I want to geocode the address and store the latitude and longitude coordinates in the POINT field.

我的理解是这样做的方法是在模型的 beforeSave 方法中对地址进行地理编码.我已经做到了这一点，并将坐标放在一个关联数组中.现在我的问题是如何将这些数据插入到我的坐标字段中?这就是我正在尝试的:

My understanding is the way to do this is to geocode the address in the model's beforeSave method. I have done this and have the coordinates in an associative array. Now my question is how can I insert this data into my coordinates field? This is what I'm trying:

public function beforeSave()
{
    $singleLineAddress = $this->getSingleLineAddress();
    $coords = Geocoder::getCoordinates($singleLineAddress);

    // WORKS: using the following line works to insert POINT(0 0)
    //$this->coordinates = new CDbExpression("GeomFromText('POINT(0 0)')");

    // DOESN'T WORK: using the following line gives an error
    $this->coordinates = new CDbExpression("GeomFromText('POINT(:lat :lng)')",
        array(':lat' => $coords['lat'], ':lng' => $coords['lng'] ));

    return parent::beforeSave();
}

当我这样做时，我收到以下错误:

When I do this I get the following error:

CDbCommand 执行 SQL 语句失败:SQLSTATE[HY093]:无效的参数号:绑定变量的数量不匹配代币的数量.执行的SQL语句是:INSERT INTO place(city、state、name、street、postal_code、phone, 创建,坐标) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5,UTC_TIMESTAMP(), GeomFromText('POINT(:lat :lng)'))

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. The SQL statement executed was: INSERT INTO place (city, state, name, street, postal_code, phone, created, coordinates) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, UTC_TIMESTAMP(), GeomFromText('POINT(:lat :lng)'))

推荐答案

试试这个

 $this->coordinates = new CDbExpression("GeomFromText(:point)",
        array(':point'=>'POINT('.$coords['lat'].' '.$coords['lng'].')'));

相关文章