You can give this package a go. It is specifically made to work with spatial data in laravel https://github.com/grimzy/laravel-mysql-spatial/blob/master/README.md
Error for Valid MySQL Query!
Hello everyone,
I'm working on a project that needs to work with MySql spatial features in Laravel, I currently have a model that has a location field (POINT type in mysql) which represents its location on map (earth). In a part of app, I want to select all models that are inside a Polygon, so I'm trying this code:
Laravel Code (a part of code - some WHEREs and a ORDER_BY are added before this code and a SELECT is added after this):
// Bounds are actually generated from request inputs...
$bounds = "-71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957";
$properties->whereRaw("ST_Within(`location`, ST_GeomFromText('POLYGON(( ? ))', 4326))", [$bounds]);
Which produces this query:
Produced Query:
select `id`, `address`, `bedrooms`, `bathrooms`, `price`, `offer_type` from `properties` where `offer_type` = Lease and ST_Within(`location`, ST_GeomFromText('POLYGON(( -71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957 ))', 4326)) and `properties`.`deleted_at` is null order by `days_on_market` asc
Now, when I run this query inside MySql Workbench, it runs the query successfully with no error... BUT when I run this by Laravel (by calling ->get() appended to that piece of code in controller to get the results), I get this error as response:
Error:
{
"message": "SQLSTATE[22023]: Invalid parameter value: 3037 Invalid GIS data provided to function st_geomfromtext. (SQL: select `id`, `address`, `bedrooms`, `bathrooms`, `price`, `offer_type` from `properties` where `offer_type` = Lease and ST_Within(`location`, ST_GeomFromText('POLYGON((-71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957))', 4326)) and `properties`.`deleted_at` is null order by `days_on_market` asc)",
"exception": "Illuminate\Database\QueryException",
"file": "C:\Users\abgha\Documents\Projects\MSS\vendor\laravel\framework\src\Illuminate\Database\Connection.php",
"line": 669,
"trace": [...]
}
Environment:
- Laravel 7.0
- PHP 7.2 and 7.4.3 (both tested)
- MySQL 8.0
I'm not sure if it's a Laravel bug, or Database Driver or something else. so, any help is appreciated :)
Can you test this:
// Bounds are actually generated from request inputs...
$bounds = "-71.177658505292 42.390290973957,-71.177682026887 42.390370174324,-71.17760630126 42.390382566075,-71.177582658308 42.390303365353,-71.177658505292 42.390290973957";
$properties->whereRaw("ST_Within(`location`, ST_GeomFromText('POLYGON(( {$bounds} ))', 4326))");
I guess when using binding, PDO is adding quotes around the coordinates, at least when I tried that was the reason.
If that solves your problem, you have an issue on sanitizing user input before interpolating as it could led to a SQL injection attack.
Maybe the package recommended by @sinnbeck would be a better way to go it, among other features, abstracts away sanitizing user input
Please or to participate in this conversation.