Saving POINT() geometries in SilverStripe DataObjects

I encountered an issue while trying to set up a POINT field with a spatial index.

A spatial index in MySQL allows us to do some very fast searches based on locations of ‘objects’ in the database. However, the requirement is that Geometry fields on the table are ‘not null,’ so to save a record we must also include a valid Geometry – in this case a POINT field.

The problem then comes when a new record is created.

In the write() method in SilverStripe’s DataObject class, after a few checks, it’ll try to create a new record if it doesn’t exist (around line 990). This is so that the record ID can be retrieved, then other data is saved to that record.

Since this initial record creation does not include any values other than a ‘Created’ field, the constraint on the Geometry field fails and MySQL returns an error. Therefore, nothing is saved to the database.

The resolution is fairly simple; we need to save the initial record in a valid way and assign the new ID to the object, thus overriding the default record creation.

To do this we use the onBeforeWrite() method on the object. Note that my field name is called ‘Location.’

public function onBeforeWrite () {
// Is this a new record?
if(!$this->ID) {
// If we want a proper geo field with spatial index, the record must always have some value for location
// We need to save some default values else MySQL returns an error and nothing gets saved
// See around DataObject.php:984-994 for this bit
$baseTable = $this->baseTable();
DB::query("INSERT INTO \"".$baseTable."\" (\"Created\", \"Location\") VALUES (" . DB::getConn()->now() . ", GeomFromText('POINT(0 0)'))");
$this->record['ID'] = DB::getGeneratedID($baseTable);
return parent::onBeforeWrite();

That’s it! We save the initial record with the required value for Location and get back the ID.

This entry was posted in Silverstripe and tagged . Bookmark the permalink.