1

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.

tereško
  • 58,060
  • 25
  • 98
  • 150
Mark Peace
  • 11
  • 3

3 Answers3

1

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;
Juancho Rossi
  • 36
  • 4
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);
Alfonso Rubalcava
  • 2,237
  • 17
  • 26
0

Use FLOAT as your database datatype.

toopay
  • 1,635
  • 11
  • 18
  • [MySQL Manual](http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html).-MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001. If you can not use Point, is preferable to use decimal. – Alfonso Rubalcava Jul 24 '11 at 22:16
  • Well, i've done crate a proxy library which support for geolocation(using google map service) in Codeigniter : http://codeigniter.com/forums/viewthread/186250/, and works fine with my float datatype on my database. – toopay Jul 25 '11 at 05:05
  • It is not the same country as mailing addresses, and failure precision in MySQL is a design feature. In the manual refer to these fields as "Floating-Point (Approximate-Value) Types"... [MySQL Manual](http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html) – Alfonso Rubalcava Jul 25 '11 at 13:55