mysql spatial view create update

Yii 1.1 General Discussion
1

hi,

I’m a yii newbie, and fighting with spatial data in table under yii. I have table


CREATE TABLE `locations` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(20) NOT NULL,

  `position` point NOT NULL,

  `radius` int(11) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

normal view method was not able to view date and thrown exception, I’ve done it in this way


	public function beforeFind()

	{

		$criteria = new CDbCriteria;

		$criteria->select = "id,name, astext(position) AS position, radius";

		$this->dbCriteria->mergeWith($criteria);

	}

I hope it’s quite correct with yii philosophy, but generally works and for example under gridview in column position I receive strings with geoms from table. In this case points like POINT(xx.xxx yy.yyy). For me it’s ok.

I have problem with create and update record. I’ve tried beforeSave with string modification to GeomFromText(‘POINT(xx.xxx yy.yyy)’) but without success. Every time I receive


CDbCommand failed to execute the SQL statement: SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field. The SQL statement executed was: INSERT INTO `locations` (`name`, `position`, `radius`) VALUES (:yp0, :yp1, :yp2)

any helpfull hand with solution?

2

Hi smiodus,

I’m curious if you ever found a solution to doing an insert with the point data type? I’m just starting a Yii-based project and have some tables with point data type columns, so I’m anticipating running into the same issue.

Any additional insights you can provide on your experience working with spatial data in Yii would be much appreciated as well!

3

Have you tried using a CDbExpression for this?

You could override beforeSave in your model and set the attribute with the point-field to


new CDbExpression('POINT(:lat :lon)', array('lat' => 0, 'lon' => 0))
4

Thanks Javache, that worked perfectly! (Not to mention is a much more elegant solution than I was expecting to find…)

5

hi ho! and to insert a polygon? :( i’m using postgis

ty!

6

well, I did so:

$model->the_geom = new CDbExpression(“ST_GeomFromText(:polygon)”,array(‘polygon’=>‘POLYGON(’.$model->the_geom.’)’));

and the input text pattern (0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)

;D

7

My sessions stored in the database.

In table with the sessions I want to add a column of type Point, there to keep the coordinates of the user.

I extending CDBHttpSession and overrides some methods:


protected function createSessionTable($db,$tableName)

{

$sql="

CREATE TABLE $tableName

(

	id CHAR(32) PRIMARY KEY,

	expire INTEGER,

	data VARCHAR(2000),

        lat DECIMAL(11,6),

        lon DECIMAL(11,6),

        id_user INTEGER,

        ll Point NOT NULL,

        Spatial Index ll_key (ll)

    ) ENGINE=MyISAM;

        ";

		$db->createCommand($sql)->execute();

                

}

I do so that the recording session, and added id_user, and its coordinates are in table:




    public function writeSession($id,$data)

    {


        try

        {

            $lat=Yii::app()->user->lat;

                        $lon=Yii::app()->user->lon;

                        $idUser=Yii::app()->user->id;

                        $ll=new CDbExpression('POINT(:lat, :lon)', array(':lat' => $lat, ':lon' => $lon));

                        

                        $expire=time()+$this->getTimeout();

            $db=$this->getDbConnection();

            $sql="SELECT id FROM {$this->sessionTableName} WHERE id=:id";

            if($db->createCommand($sql)->bindValue(':id',$id)->queryScalar()===false)

                $sql="INSERT INTO {$this->sessionTableName} (id, data, expire, lat, lon, id_user, ll) VALUES (:id, :data, $expire, $lat, $lon, $idUser, $ll)";

            else

                $sql="UPDATE {$this->sessionTableName} SET expire=$expire, data=:data, lat=$lat, lon=$lon, id_user=$idUser, ll=$ll WHERE id=:id";

            $db->createCommand($sql)->execute();

                        //->bindValue(':id',$id)->bindValue(':data',$data)

        }

        catch(Exception $e)

        {

            if(YII_DEBUG)

                echo $e->getMessage();

            // it is too late to log an error message here

            return false;

        }

        return true;

    }

and receiving an error:

CDbCommand failed to execute the SQL statement: SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field. The SQL statement executed was: INSERT INTO session (id, expire) VALUES (:id, :expire) .

If I remove the code from the table to create spatial index, ll Point, and the values ​​of ll from Insert and Update expressions (writeSession function) - it works

pls,help me.

8

I am experiencing the same issue which is caused by the fact that inside the ‘values’ section of the SQL insert method, the ‘GeomFromText’ method’s return value is not accepted as a Geometric object (it is just the binary string).

An ‘UPDATE SET xxx=GeomFromText(…)’ works ok, but an INSERT … VALUES(GeomFromText()…) does not work well. The query should use intermediate variable ; the challenge is on how to do that in Yii.

9

For those looking for a quick example, I’ve added my beforeSave() and beforeFind() functions derived from the posts above(thanks!).

I will be referring to a table with the columns:

"id" INT

"timestamp" DATETIME

"position" POINT

My application expects user input in the format X,Y where X is the decimal degrees longitude and Y is latitude. To enable quick validation, I’ve added a rule in the rules() function of my model as follows:




    array('position','match', 'not'=>false, 'pattern'=>'/\d+(\.\d+)?,\d+(\.\d+)?/'),

My beforeSave() function takes this validated string, splits it, then replaces the default position CDbExpression.




    protected function beforeSave(){

        parent::beforeSave();

        $parts = split(',',$this->position);

        if(count($parts) > 1){

            $pointString = 'POINT(' . $parts[0] . ' ' . $parts[1].')';

            $this->position = new CDbExpression("GeomFromText(:point)",array('point' => $pointString));

            return true;

        } else {

            return false;

        }

    }

To show the user the same output as they put in, I can’t use the default text from AsText(). For a point, you can use the X() and Y() function along with the CONCAT() function to format your output however you want. In the example below, I’ve formatted my POINT column named “position” to return as “X,Y”.




    protected function beforeFind(){

        parent::beforeFind();

        $criteria = new CDbCriteria;

        $criteria->select = "id,timestamp,CONCAT(X(position),',',Y(position)) AS position"; // YOU MUST TYPE ALL OF YOUR TABLE'S COLUMN NAMES HERE

        $this->dbCriteria->mergeWith($criteria);

    }

Hope this Helps!

10

Hi

I forgot about this post, for your information, here is more or less what I ended up with (I distilled down the code, so I may have forgotten to include/update some essential pieces - I did not include all required methods (rules, …)).

Basically I get the area data from the database as Text, I work with that in the application, and before the save it’s converted to binary format again.

I also get the SQL server to determine some other properties of the area such as its center, its area.

I am using other PHP libraries that understand WKT to use the geo-data.


<?php


class GeoArea extends CActiveRecord

{

    // WKT text version of area

	public $geo_area_txt;




    // Center latitude

    public $lat;

    // Center longitude

    public $lon;

    // Area taken by the polygone.

    public $area;




	

	protected function beforeFind() {

		parent::beforeFind();

		$select=array();

		$prefix = $this->getTableAlias(false, false).".";

		$criteria = new CDbCriteria;


		$select_all=array();

		if($this->dbCriteria->select==='*') {

    		$select_all=array('*');

		}

		$criteria->select = CMap::mergeArray($select_all, array(

                        		'AsText('.$this->dbConnection->quoteColumnName('geo_area').') AS geo_area_txt',

                        		'X(Centroid('.$this->dbConnection->quoteColumnName('geo_area').')) as lon',

                        		'Y(Centroid('.$this->dbConnection->quoteColumnName('geo_area').')) as lat',

                        		'Area('.$this->dbConnection->quoteColumnName('geo_area').') as area',

        		)

		);

		$this->dbCriteria->mergeWith($criteria);

	}


	private static $dbidx=0;


	private function toPolyExpression($varname) {

		$id=":area".self::$dbidx++;

		if(is_string($this->{$varname.'_txt'})) {

			$this->{$varname} = new CDbExpression(

				"GeomFromText($id,4236)",array($id=>$this->{$varname.'_txt'})

			);

		} elseif(is_a($this->{$varname.'_txt'},'CDbExpression')) {

			$dbexpr=new CDbExpression(

	        		"GeomFromText(".$this->{$varname.'_txt'}->expression.")",

			$this->{$varname.'_txt'}->params);

			$this->{$varname} = $dbexpr;

		}

	}


	protected function beforeSave() {

		$result = parent::beforeSave();

		// List of GeoArea field (I have more than one in the production table).

		$list=array('geo_area');

		foreach($list as $item) {

    		if(!isset($this->{$item.'_txt'})) {

			// If the '_txt' version of the are is not set, use the binary version.

                //$this->{$item}=$this->{'geo_area'};  // By default the reference area is used in my app.

    		} else {

			// Else, the '_txt' version is set, so use it (build the CDbExpression to convert it).

        		$this->toPolyExpression($item);

    		}

		}

		return $result;

	}


}