I'm trying to build a geolocation addon and I'm running into a little problem. Much of what I have read, for the best performance, I should save a location in a geometry field, specifically a point, and then run my query's from this field. The problem is that I must save to this field using the mysql POINT() function. I've been able to get around this problem using select statements, but I'm having real problems inserting this field. What happens is that everything appears to look good in the query except the apostrophes around the function.
Problem query:
INSERT INTO `modx_sekgl_locations` (`ref_id`, `address`, `lat`, `lng`, `loc`, `createdon`, `editedon`) VALUES ('2', '1600 Pennsylvania Avenue, Washington, NC 27889, USA', '35.5402684', '-77.0339597', 'POINT(35.5402684,-77.0339597)', '2013-09-13 21:03:40', '0000-00-00 00:00:00')
If I remove the apostrophes and run the query manually, everything works great.
My schema:
<object class="sekglLocations" table="sekgl_locations" extends="xPDOSimpleObject">
<field key="ref_id" dbtype="varchar" precision="10" phptype="string" null="false" index="unique" default="" />
<field key="address" dbtype="text" phptype="string" null="false" default=""/>
<field key="lat" dbtype="decimal" precision="10,6" phptype="float" null="true" default="0.000000" index="index" indexgrp="LATLNG" />
<field key="lng" dbtype="decimal" precision="10,6" phptype="float" null="true" default="0.000000" index="index" indexgrp="LATLNG" />
<field key="loc" dbtype="point" phptype="string" null="false" index="spatial"/>
<field key="createdon" dbtype="timestamp" phptype="timestamp" null="false" default="0000-00-00 00:00:00" index="index" indexgrp="EDITSTAMP" />
<field key="editedon" dbtype="timestamp" phptype="timestamp" null="false" default="0000-00-00 00:00:00" index="index" indexgrp="EDITSTAMP" />
<index alias="REF" name="REF" primary="false" unique="false" type="BTREE">
<column key="ref_id" length="" collation="A" null="false" />
</index>
<index alias="LOCATION" name="LOCATION" primary="false" unique="false" type="BTREE">
<column key="loc" length="" collation="A" null="false" />
</index>
<index alias="LATLNG" name="LATLNG" primary="false" unique="false" type="BTREE">
<column key="lat" length="" collation="A" null="false" />
<column key="lng" length="" collation="A" null="false" />
</index>
<index alias="EDITSTAMP" name="EDITSTAMP" primary="false" unique="false" type="BTREE">
<column key="createdon" length="" collation="A" null="false" />
<column key="editedon" length="" collation="A" null="false" />
</index>
</object>
My snippet places the lat and lng into the object,then in my class I have:
class sekglLocations extends xPDOSimpleObject {
public function save($cacheFlag= null) {
if ($this->isNew()) {
$this->set('createdon','NOW');
} else {
$this->set('editedon','NOW');
}
$this->set('loc','POINT('.$this->get('lat').','.$this->get('lng').')');
$saved= parent :: save($cacheFlag);
return $saved;
}
}
Note: I did try to change the phptype in the loc field to something other than string, the source of the apostrophes I'm sure. Neither integer, array, nor object work, which I did not expect them to fix my problem. Also slightly annoying, setting index to 'spatial' does not work, it changes automatically to 'index'.
