{"id":1679,"date":"2025-02-27T15:17:02","date_gmt":"2025-02-27T15:17:02","guid":{"rendered":"https:\/\/editjournal.redakt.eu\/faxmodem\/?p=1679"},"modified":"2025-02-27T15:17:35","modified_gmt":"2025-02-27T15:17:35","slug":"read-write-geospatial-point","status":"publish","type":"post","link":"https:\/\/editjournal.redakt.eu\/faxmodem\/blog\/development\/cakephp\/read-write-geospatial-point\/","title":{"rendered":"Reading and writing geospatial in CakePHP 5.1+"},"content":{"rendered":"

Limited support for SQL geospatial types was added<\/a> in CakePHP 5.1.0. To quote the doc<\/a>:<\/p>\n

\n

The geometry<\/code>, point<\/code>, linestring<\/code>, and polygon<\/code> types are also known as the \u201cgeospatial types\u201d. CakePHP offers limited support for geospatial columns. Currently they can be defined in migrations, read in schema reflection, and have values set as text.<\/p>\n<\/blockquote>\n

Creating a POINT column<\/h3>\n

Using point<\/code> as an example, your database table MyTable<\/code> needs to have a column position<\/code> with type POINT<\/code>. You will then have to have some additional processing for reading and writing while the built-in support is limited.<\/p>\n

Here's how you doublecheck if CakePHP understands your column type correctly:<\/p>\n

debug($myTable->getSchema()->getColumn('position'));<\/code><\/pre>\n
[\n  'type' => 'point',\n  'length' => null,\n  'null' => true,\n  'default' => null,\n  'comment' => '',\n  'precision' => null,\n  'srid' => null,\n]<\/code><\/pre>\n
If you just created the column, you may have to do bin\/cake cache clear_all<\/code>.<\/p>\n
Reading POINT<\/h3>\n
As per https:\/\/stackoverflow.com\/questions\/52742813\/how-to-unpack-mysql-multipoint-geometry-data-in-php<\/a>: <\/p>\n
$position = unpack('x\/x\/x\/x\/corder\/Ltype\/dx\/dy', $entity->position);\ndebug($position);<\/code><\/pre>\n
[\n    'order' => (int) 1,\n    'type' => (int) 1,\n    'x' => (float) 12.34,\n    'y' => (float) -56.78,\n]<\/code><\/pre>\n
You may want to do this post-processing in a Virtual Field<\/a> on your Entity. This way the geospatial data is already parsed whenever you need it.<\/p>\n
Writing POINT<\/h3>\n
To save our POINT, we need to pass it through the database function ST_GeomFromText()<\/code><\/a>. Because CakePHP doesn't come with all possible database functions built in, we're going to use a query expression with a custom function<\/a> which is a magic wrapper.<\/p>\n
\/\/ Take the $entity we have and patch it with request data\n$entity = $myTable->patchEntity($entity, $this->getRequest()->getData());\n\/\/ Specifically set the position column using query expression\n$entity->position = $myTable->query()->func()->ST_GeomFromText([\n    "'POINT(0.123 -20.00)'" => 'literal', \/\/ single quotes are important\n]);\n\/\/ Save the data\n$result = $myTable->save($entity);<\/code><\/pre>\n