Multipolygon Support?

[Dherlou]

Hey there,

we are using this API to save/retrieve geo data, especially multipolygons.

I noticed that the API fails when working with multipolygons:

• GETing results in a blank 200 response
• POSTing/PUTting results in an sql error SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field

Upon further debugging I found out that the multipolygon field is not detected as a geometric field, hence the value is treated as a typical scalar value, doesn't undergo the WKT-parsing and then fails the syntax check upon inserting.

When I add 'multipolygon' => 'geometry' to the $toJdbc array

php-crud-api/api.php

Line 7356 in 9307ae2

private $toJdbc = [

, it seems to work fine (at least from my POV - we are using MariaDB).

Almost all database types (MySQL/MariaDB, PostgreSQL and SQL Server), probably except SQLite (or at least I couldn't find reliable information) seem to support it and the type support for multipolygons seems to be already implemented in

php-crud-api/api.php

Line 7748 in 9307ae2

"MultiPolygon",

and

php-crud-api/api.php

Line 10660 in 9307ae2

return '^(POINT|LINESTRING|POLYGON|MULTIPOINT|MULTILINESTRING|MULTIPOLYGON)\s*\(.*$';

.

Is there a specific reason why the multipolygon type is 'deactivated'? Are there any concerns in enabling it (at least in our installation)?

[mevdschee]

Hi Lucas Kinne,

Thank you for reporting this issue.

Is there a specific reason why the multipolygon type is 'deactivated'?

No, I can't think of any. Note that the 'multipolygon' column type is a 'geometry' column type with a constraint (must be of geometry type 'multipolygon').

Are there any concerns in enabling it (at least in our installation)?

No, I think it was left out by accident. Feel free to do a PR.

Kind regards,

Maurits van der Schee

[mevdschee]

I checked some more and Postgresql could be improved with more types:

https://www.postgresql.org/docs/current/datatype-geometric.html

point | 16 bytes | Point on a plane | (x,y)
line | 32 bytes | Infinite line | {A,B,C}
lseg | 32 bytes | Finite line segment | ((x1,y1),(x2,y2))
box | 32 bytes | Rectangular box | ((x1,y1),(x2,y2))
path | 16+16n bytes | Closed path (similar to polygon) | ((x1,y1),...)
path | 16+16n bytes | Open path | [(x1,y1),...]
polygon | 40+16n bytes | Polygon (similar to closed path) | ((x1,y1),...)
circle | 24 bytes | Circle | <(x,y),r> (center point and radius)

I don't see multipolygon there.

MariaDB could also be improved:

https://www.postgresql.org/docs/current/datatype-geometric.html

POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
GEOMETRY

It seems it can be made more complete.

In the class Geometry.php I see:

    "Point",
    "MultiPoint",
    "LineString",
    "MultiLineString",
    "Polygon",
    "MultiPolygon",
    //"GeometryCollection",

see: https://github.com/mevdschee/php-crud-api/blob/main/src/Tqdev/PhpCrudApi/GeoJson/Geometry.php

I expect that apart from "GeometryCollection" the WKT types can be represented (so not circle for instance) and I'm not sure about 'box'.

[Dherlou]

Ah, you are right. It seems that some other bits might be missing as well. We just use POINT and MULTIPOLYGON, so I didn't notice the rest. :)

While MariaDB supports multipolygons natively, it seems that you need the "PostGIS" extension for PostgreSQL, which among others adds Multipolygon. The same might be true for SpatiaLite for SQLite, but I am only familiar with MySQL/MariaDB, and the documentation seems hard to understand.

[mevdschee]

I think most databases use an underlying "geometry" column type, which is what I rely on.

I think the following lines:

'polygon' => 'geometry',
'point' => 'geometry',

should be:

'point' => 'geometry',
'linestring' => 'geometry',
'polygon' => 'geometry',
'multipoint' => 'geometry',
'multilinestring' => 'geometry',
'multipolygon' => 'geometry',

In the file TypeConverter.php. Agree?

see: https://github.com/mevdschee/php-crud-api/blob/main/src/Tqdev/PhpCrudApi/Database/TypeConverter.php#L69

[Dherlou]

I think so. At least that is what got us up and running. :)

[mevdschee]

I'll create a new release later today. Thank you!

[mevdschee]

Released in v2.14.21