Cakephp 4 and datatype 'geometry'

1

I’m trying to migrate a sql-query to work in cakephp 4:

SELECT name FROM streets WHERE ST_CONTAINS(@area,linestring);

This query is giving the expected results when run in a terminal.

According to https://book.cakephp.org/4/en/orm/database-basics.html I’m using the ‘execute’ command:

execute(‘SELECT name FROM streets WHERE ST_CONTAINS(:area,linestring)’,[‘area’ => $polygon])

The ‘execute’ takes the $polygon as a string, so I have to bind the wanted datatype, which shoud be ‘geometry’ I think.

execute(‘SELECT name FROM streets WHERE ST_CONTAINS(:area,linestring)’,[‘area’ => $polygon][‘polygon’ => ‘geometry’])

‘Geometry’ isn’t a supported datatype (https://book.cakephp.org/4/en/orm/database-basics.html#data-types).

Searching the github, there are some closed issues concerning geospatial datatypes. They end up ‘closed’ with a reference to https://book.cakephp.org/4/en/orm/database-basics.html#adding-custom-types.

Before even starting to try to get a custom data type ‘geometry’:

  • Is the line of thought correct, or do I have to search in another direction?
  • Did anybody else develope the geometry-datatype, code I can copy?
  • Although it looks a bit complicated to me, is developing a custom-datatype straightforward following the ‘json’-example in the cookbook?
2

Straightforward? Probably, if you have enough experience. I was able to get a custom data type working in my code.

Was it pretty? Um… I wouldn’t want to publish my code :crazy_face: but at least it’s encapsulated and doesn’t trouble the rest of my code base.

At some point I’ll want to revisit the tumor and refactor it.

But with the custom data type in place, working with entities that contain columns that use it has been smooth, so I would recommend giving that strategy a try.

3

Ok, won’t even try.

Thinking about other solution, calling a stored procedure.

4

hahaha! I hear that.

But in reality, my ‘alternate’ approaches are always more complicated (and more importantly, harder to maintain) than the built in solutions.

Still, through some perversity of my mind, I find it hard to understand pre-built solutions until I have worked through my own implementation and really understand the problem.

Good luck

5

Sort of same here, and for some reason, don’t know exactly why, the cakephp-docs don’t support me very much in really understanding the implementations (choices made, alternatives with pro’s and con’s etc.).