How to use a Point or Geometry type in Datamapper

Question

I'm using codeigniter and datamapper to build an API service (on mysql), and part of it involves geolocation - however I can't seem to find a workaround to use the point datatype. If I try to insert GeomFromText, the system treats it as a string.

Can anyone help?

Cheers, Mark.

Answer 1

Try

CREATE TABLE Points ( 
    ID INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
    location POINT NOT NULL, 
    SPATIAL INDEX(location) 
) ENGINE= MYISAM

In CodeIgniter:

$this->db->set("location",'geomfromtext("POINT(30.2 40.3)")',false);
$this->db->insert("Points");

In CodeIgniter with Datamaper (see help for "Using Formulas in Updates" in the documentation)...

$point = new Point();
$point->update('location','geomfromtext("POINT(30.2 40.3)")',FALSE);

Answer 2

As explained in the CodeIgniter User's Guide:

$this->db->set();

set function enables you to set values for inserts or updates.

set() will accepts a third parameter ($escape), that will prevent data from being escaped if set to FALSE.

$this->db->set('geo', "ST_GeomFromText('{$data['geo']}')", FALSE);
$this->db->insert('tableName');

return $this->db->insert_id() ? $this->db->insert_id() : FALSE;

Answer 3

Use FLOAT as your database datatype.