CODEWITHSUNDEEP
mysqlyiigeospatialspatial
User
User

Reputation: 66041

How do I insert a mysql spatial point with a yii model?

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.

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

Upvotes: 3

Views: 2493

Answers (2)

ajaybc
ajaybc

Reputation: 4059

Small edit in @dlnGd0nG 's answer if you are using Yii 2

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

Upvotes: 5

dInGd0nG
dInGd0nG

Reputation: 4114

Try this instead

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

Upvotes: 4

Related Questions