如何插入带有yii模型的mysql空间点? [英] How do I insert a mysql spatial point with a yii model?
问题描述
我有一个模型类型,该类型是从mysql表生成的,该表具有地址数据以及一个名为"coordinates"的空间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
,created
,coordinates
)值(: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'].')'));
这篇关于如何插入带有yii模型的mysql空间点?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!