Hi there,
How can we retrieve geom Point objects from a MySQL 5.7 table for our models? At the moment I just get a garbled string? In raw sql I would use something like 'select ASTEXT(location) as location' so I'm also unsure how we would use the 'as' in the SQL statement in models.
Do we have to use raw sql? If so do I need to populate the model objects automatically? Should I create a static function my object to do this? e.g static function findWithGeom()
Also with regards to inserts for geom data I've seen it written that we need to use DB\RawValue but that the docs also specifiy we shouldn't use this for user entered data to avoid sql injection attacks. If I'm allowing users to enter locations how can we get over this? It looks like I won't be able to use the save() method of a model but again have to use raw sql? Something like
insert into location(location) VALUES(Point(:lon, :lat)) and bind the values that way?
Has anyone got a full working example of this? I really want to keep my code tidy and not have raw sql/PHQL in my controllers to retrieve this items purely because they have geom data. With mobile apps and apis these days it strikes me as something people would want to do more of, so I would really appreciate a fuller example.
I tried using the column map to explicitly create lat/lon properties as below but it doesn't work even though it doesn't error
public function columnMap()
{
// Keys are the real names in the table and
// the values their names in the application
return array(
'id' => 'id',
'location' => 'location',
'X(location)' => 'lon',
'Y(location)' => 'lat'
);
}
thanks