fleetbase / fleetbase/fleetops

ST_MakeEnvelope breaks MariaDB and fails on MySQL with SRID 4326 spatial columns

Open
#247 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
PHP
Stars
30
Forks
62
Avg merge
2d 49m
Merged PRs (30d)
26

Description

## Summary

`LiveController` builds the map-viewport spatial filter for the `/v1/internal/live/{drivers,vehicles,places}` endpoints using MySQL's `ST_MakeEnvelope`. This breaks the application in two ways:

1. **MariaDB does not implement `ST_MakeEnvelope`.** It is a MySQL-only convenience function (5.7.6+). Calling these endpoints on any MariaDB-backed deployment returns a 500. MariaDB is otherwise a first-class target — recent migrations gate driver-specific branches on `in_array(DB::getDriverName(), ['mysql', 'mariadb'])` (e.g. `2025_08_28_045009_noramlize_uuid_foreign_key_columns.php`, `2025_08_28_054925_create_devices_table.php`), and the bundled spatial library (`fleetbase/laravel-mysql-spatial`) advertises MariaDB support — so this is the one outlier blocking MariaDB.

2. **On MySQL, `ST_MakeEnvelope` only accepts SRID 0 geometries.** Fleetbase's `Point`, `Polygon`, and `MultiPolygon` casts (`server/src/Casts/`) feed geometries through `Utils::createSpatialExpressionFromGeoJson` → `\Fleetbase\LaravelMysqlSpatial\Types\Geometry::fromJson(...)`, which emits SRID 4326. Recent migrations such as `2025_10_27_171322_fix_device_column_names.php` explicitly normalize positions to `ST_SRID(POINT(0, 0), 4326)`. Mixing a SRID-0 envelope with SRID-4326 column data on MySQL 8 raises `ER_GIS_DIFFERENT_SRIDS` (3618) or `ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS` (3680) the moment the bounds filter actually meets a populated row, so this is a latent correctness bug on plain MySQL too.

## Affected code

`server/src/Http/Controllers/Internal/v1/LiveController.php`, three identical call sites:

- `drivers()` — line 160
- `vehicles()` — line 202
- `places()` — line 245

Each is:

```php
$query->whereRaw(
'ST_Within(location, ST_MakeEnvelope(POINT(?, ?), POINT(?, ?)))',
[$west, $south, $east, $north]
);
```

## User-facing impact

- Calling `GET /v1/internal/live/drivers?bounds[]=…&bounds[]=…&bounds[]=…&bounds[]=…` (or the vehicles / places equivalents) returns **500** on any MariaDB deployment, and on MySQL once the spatial column has any SRID-4326 data — which it always does, because the casts and recent migrations write SRID 4326.
- This is the live map viewport filter used by the console map, so it is the primary spatial query a fresh install will hit.

## Repro (documentation references)

- MariaDB miscellaneous GIS functions reference omits `ST_MakeEnvelope`: .
- MySQL `ST_MakeEnvelope` documentation states it is defined for SRID 0 only and raises `ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS` / `ER_GIS_DIFFERENT_SRIDS` on non-zero-SRID inputs.
- Fleetbase storage SRID is 4326: see `Fleetbase\LaravelMysqlSpatial\Types\Geometry::fromJson` (used by `Utils::createSpatialExpressionFromGeoJson`) and the explicit `ST_SRID(POINT(0, 0), 4326)` in `2025_10_27_171322_fix_device_column_names.php`.

## Proposed fix

Replace `ST_MakeEnvelope(POINT(?, ?), POINT(?, ?))` with an equivalent WKT polygon passed through `ST_GeomFromText(?, 4326)`. This preserves the `ST_Within(location, …)` form (so a future `SPATIAL INDEX` on `location` remains usable), pins the envelope SRID to 4326 to match the column, and runs unchanged on MySQL 8 / 8.4 and MariaDB 10.5+ / 11.x.

PR following this issue.

## Environment

- Repro confirmed by reading current `main` (commit `9394d4f`); call sites unchanged from the lines cited above.
- Reproduces on any MariaDB version; on MySQL 8.0 / 8.4 with the default cast pipeline writing SRID 4326.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.