grimzy / laravel-mysql-spatial Goto Github PK
View Code? Open in Web Editor NEWMySQL Spatial Data Extension integration with Laravel.
License: Other
MySQL Spatial Data Extension integration with Laravel.
License: Other
Hi,
I have implemented this extension, and have managed to get most of it working. I would like to now use the Point object inside the Model. Essentially I would like to set an attribute value, so something like:
$this->attributes[ "location" ] = new Point( $lat, $lng );
However, the Point class is not available inside of the model. Is there a way to do this?
Clicking through the source code has me worried. It looks like this package facilitates SQL Injections.
For example the scopeDistance
function
public function scopeDistance($query, $geometryColumn, $geometry, $distance)
{
$query->whereRaw("st_distance(`{$geometryColumn}`, ST_GeomFromText('{$geometry->toWkt()}')) <= {$distance}");
return $query;
}
The user data is just executed raw, no bindings, no escaping. Doing a simple test below shows it allows for SQL Injection.
>>> Address::distance('location', new \Grimzy\LaravelMysqlSpatial\Types\Point(51.905737, 4.430866), "'' OR 1=1")->toSql()
=> "select * from `addresses` where st_distance(`location`, ST_GeomFromText('POINT(4.430866 51.905737)')) <= '' OR 1=1"
Route::get('test', function () {
$postData = collect(json_decode("[[-336.445313,82.118384],[580.429688,79.935918],[539.648438,-72.816074],[-281.601563,-76.840816],[-336.445313,82.118384]]"));
$multiplePoints = $postData->transform(function ($data) {
return new Point($data[1], $data[0]);
});
$polygon = new Polygon([new LineString($multiplePoints->toArray())]);
dd(Metadata::doesTouch('map_data', $polygon)->get());
});
MetadataFactory
$factory->define(Metadata::class, function (Faker $faker) {
$startPoint = new Point($faker->randomFloat(10, 14, 15), $faker->randomFloat(10, 121, 122));
$linestring = new LineString([
$startPoint,
new Point($faker->randomFloat(10, 14, 15), $faker->randomFloat(10, 121, 122)),
new Point($faker->randomFloat(10, 14, 15), $faker->randomFloat(10, 121, 122)),
$startPoint,
]);
return [
'level' => mt_rand(1, 4),
'map_data' => new Polygon([$linestring]),
];
});
mysql Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using EditLine wrapper
I could not make it work, I think I'm following the steps correctly.
I wanna put a simple point but it fails when saving
App\SportPlace.php
<?php
namespace App;
use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;
use Illuminate\Database\Eloquent\Model;
class SportPlace extends Model
{
use SpatialTrait;
protected $fillable = [
"status",
"name",
"latlng",
];
protected $spatialFields = [
'latlng',
];
$sp = new App\SportPlace();
>>> $point = new GeoJson\Geometry\Point([40.7484404, -73.9878441]);
=> GeoJson\Geometry\Point {#992}
>>> $sp->latlng = $point;
=> GeoJson\Geometry\Point {#992}
>>> $sp->save();
PHP Recoverable fatal error: Object of class GeoJson\Geometry\Point could not be converted to string in /vendor/laravel/framework/src/Illuminate/Support/Str.php on line 33
Mysql 5.7.17
laravel-mysql-spatial 2.0.2
laravel 5.5.4
I'm using 1.0.0
version of your package together with sofa/eloquence:^5.4
.
I thought the error in the title was fixed using:
use SpatialTrait { Eloquence::newEloquentBuilder insteadof SpatialTrait; SpatialTrait::newEloquentBuilder as newEloquentSpatialBuilder; }
because the Eloquence methods I'm using works well.
But now, I'm trying to update the location field of my model using the Point
class without success. The exeception is: SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field
.
Any idea to fix this collision without impact on the functionalities? I really need both packages.
I'm going to open an issue to Eloquence package too.
Hi, thanks by this awesome library. I'm trying it, but when I attempt to run a migration, this returns:
[Symfony\Component\Debug\Exception\FatalThrowableError]
Type error: Argument 1 passed to CreateStoresTable::{closure}() must be an instance of Grimzy\LaravelMysqlSpatial\Schema\Blueprint, instance of Ill
uminate\Database\Schema\Blueprint given, called in ...
I've imported the Grimzy\LaravelMysqlSpatial\Schema\Blueprint inside the migration, but this not runs.
Thanks by Help
Fix code quality reporting. Seems like something changed with codeclimate.
I have an issue to pass the polygon data from DB to Client.
I save the polygon like this:
// Save item
$item = new Item($data);
...
$item->polygon = $this->buildPolygonFromArray($data['polygon']);
$item->save();
// Build polygon from array
use Grimzy\LaravelMysqlSpatial\Types\LineString;
use Grimzy\LaravelMysqlSpatial\Types\Point;
use Grimzy\LaravelMysqlSpatial\Types\Polygon;
private function buildPolygonFromArray($polygon) {
$points = $polygon['coordinates'][0];
$arrayOfPoints = [];
foreach ($points as $point) {
$arrayOfPoints[] = new Point($point[0], $point[1]);
}
$lineString = new LineString($arrayOfPoints);
return new Polygon([$lineString]);
}
Now the polygon is been selected automatically by laravel and passed to the client BUT the problem is that the latitude and longitude are been switched in positions.
I send this array with lat&long points:
'polygon' =>
[
'coordinates' =>
[
0 => [
[34.783342, 32.075312],
[34.782876, 32.075323],
[34.782862, 32.074953],
[34.783407, 32.07494],
[34.783342, 32.075312],
],
]
]
On DB it's been saved on the opposite way (long&lat) because of the Point()->toPair()
function but it is totally OK. The problem is with the select and passing to client.
The response I get in the network is like this:
{"type":"Polygon","coordinates":[[[32.075312,34.783342],[32.075323,34.782876],[32.074953,34.782862],[32.07494,34.783407],[32.075312,34.783342]]]}
When I'm going over the array the points are in Long&Lat format which is not good, I need it in the same format I create the polygon: [Lat&Long]
I thought about working with public function getPolygonAttribute($value)
on the laravel side but still I need help here.
Thank you in advance,
Lior.
In what unit the value of parameter $distance is given in the available scopes in Spatial analysis functions?
distance($geometryColumn, $geometry, $distance)
I got
FatalThrowableErrorCall to undefined method Grimzy\LaravelMysqlSpatial\MysqlConnection::execute()
from using something like
DB::execute("update table set counter = counter + 1 where id = '?'", [$this->id]);
Further to #39, I've been trying to use a point field via latitude and longitude virtual properties, but I can't make it behave usefully at all.
In my model, I've declared some fillable & location properties, much as in the provided example code:
protected $fillable = [
'name',
'address',
'type',
'longitude',
'latitude',
];
protected $spatialFields = [
'location',
];
location
is defined in the database as a Laravel point
type. longitude
and latitude
do not exist in the database, so I implemented them with accessors and mutators in the model like this:
public function getLongitudeAttribute()
{
return $this->location->getLng();
}
public function getLatitudeAttribute()
{
return $this->location->getLat();
}
public function setLongitudeAttribute($longitude)
{
$this->location->setLng($longitude);
}
public function setLatitudeAttribute($latitude)
{
$this->location->setLat($latitude);
}
Then I send a PUT request to update a record containing a param string:
name=Smurf+city&latitude=21.21&longitude=150.15&type=account&address=123+main+st
However, this fails in the first mutator it runs:
"message": "Undefined index: location",
"exception": "ErrorException",
"file": "/Users/marcus/Sites/app/Location.php",
"line": 65,
If I try to access location
as a property in the mutator with $this->location->setLat($latitude);
, I get an error because location
is null
(even though it's not allowed to be declared as nullable in the DB):
"message": "Call to a member function setLat() on null",
"exception": "Symfony\\Component\\Debug\\Exception\\FatalThrowableError",
"file": "/Users/marcus/Sites/app/Location.php",
"line": 65,
What's even more confusing is that I get results back in a format that doesn't reflect my settings - I don't understand why this includes a location
property that doesn't appear in the fillable array, but not the latitude
and longitude
values that do:
{
"id": 1,
"created_at": "2018-03-16 16:39:47",
"updated_at": "2018-03-16 16:39:49",
"type": "account",
"name": "Bernier Group",
"address": "68914 Streich Springs Apt. 419\nVenabury, WI 39979-3229",
"location": {
"type": "Point",
"coordinates": [
-0.118092,
51.509865
]
}
},
This may be a broader Laravel question than something specific to this extension, but I don't know enough about either to tell. How should I be doing this?
I create places table with name and location column
but location is not point
its polygon and i store polygon of a places in table
now how can i check the point is on which column
I try :
$s9 = new Point(38.021996, 46.3799);
$spaa = Place::find(3);
$spac = $spaa->location;
$result=$spac->contains('location',$s9);
but not work
Since MySQL doesn't allow spatial indexes on nullable POINT type columns (and it also doesn't allow setting default value for POINT type columns), it would be handy if there is a constant defined in Point
class that represents null value. I'm currently using North Pole coordinates (lat: 90, lng: 0) as my null value. Can you define this as a CONST in Point
class so that we could assign it statically? How would you implement it?
(PHP doesn't support static constructor; neither does it support assigning function calls to const members. I'm not sure what other way do we have to define it statically.
I had a problem accessing latitude/longitude using $place->location->getLng()
.
I noticed that including location and area in $fillable
solved the problem.
So, while location and area are both listed in $spatialFields
, they should both also be listed in $fillable
.
Hi,
I´m building a application where the user can insert a point, line or polygon. Since i have to search by drawing a polygon on the map, i decided to save all of the above as polygons (because this way i only need one index, and the point or line can be null), as shown in the example below:
POINT(-8.534297 37.115448) -> POLYGON((-8.534297 37.115448,-8.534297 37.115448,-8.534297 37.115448,-8.534297 37.115448))
LINESTRING(-8.542011 37.117322,-8.540262 37.11674) -> POLYGON((-8.542011 37.117322,-8.540262 37.11674,-8.542011 37.117322,-8.542011 37.117322))
So, my question is: Is there a search method where i give the search (drawed) polygon to the function, and it returns all the polygons found inside (points and lines, converted to polygons be me).
I think i tried all of the functions you specify in the documentation and none of them seems to do what i need. Am i missing something?
Thanks in advance
Km, Meters, Miles ?
Hi there,
I ran into an issue when I try to push my data to the database, here is the error I'm receiving:
Symfony\Component\Debug\Exception\FatalThrowableError: Class 'App\Http\Controllers\Point' not found
Currently using:
Laravel Framework 5.5.28
MySQL Version : 5.6.36
Migration
use Grimzy\LaravelMysqlSpatial\Schema\Blueprint;
public function up()
{
Schema::create('pins', function (Blueprint $table) {
$table->point('_geoloc')->nullable();
}
Model.php
use Illuminate\Database\Eloquent\Model;
use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;
protected $spatialFields = [ '_geoloc', ];
Controller.php
namespace App\Http\Controllers;
use App\Pin;
use Illuminate\Http\Request;
class PinController extends Controller {
public function store(Request $request) {
$pin = new Pin();
$pin->_geoloc = new Point(-40.1134343, -43.76543);
$pin->save();
}
}
Please let me know any ideas you may have, thanks!
I have an array of coordinates, which I am passing to the controller and then parse it into an "array of Points" as follows:
Log::info($request->polygon);
$arrayOfPoints = [];
foreach( $request->polygon as $point ){
$arrayOfPoints[] = new Point($point[0], $point[1]);
}
Log::info(new Polygon($arrayOfPoints)); // Line 28
And immediately I get the following error:
[2017-08-22 20:44:51] local.INFO: array (
0 =>
array (
0 => '-99.16897058486938',
1 => '19.424870411471588',
),
1 =>
array (
0 => '-99.16744709014893',
1 => '19.424769230105948',
),
2 =>
array (
0 => '-99.16781187057495',
1 => '19.423433630174305',
),
3 =>
array (
0 => '-99.16882038116455',
1 => '19.4236764673421',
),
)
[2017-08-22 20:44:51] local.ERROR: exception 'InvalidArgumentException' with message '$linestrings must be an array of Points' in /Library/WebServer/Documents/private/private/vendor/grimzy/laravel-mysql-spatial/src/Types/MultiLineString.php:29
Stack trace:
#0 /Library/WebServer/Documents/private/private/app/Http/Controllers/LocationController.php(28): Grimzy\LaravelMysqlSpatial\Types\MultiLineString->__construct(Array)
#1 [internal function]: App\Http\Controllers\LocationController->create(Object(Illuminate\Http\Request))
I am having a terribly difficult time inserting a LineString. Points insert fine, but LineStrings are not being formatted corrected during the insert.
$route = new Route();
$route->path = new LineString([new Point(1, 1), new Point(2, 2), new Point(3, 3));
$route->save();
This is basically what I am doing. It actually appears that the LineString()
object is being created successfully, but I just can't get it to insert properly.
If i dump out the $route->path
property before the insert, this is what the LineString()
object looks like. (Am I maybe constructing the object wrong?), to me it looks like the object is constructed correct (there are Lat and Lng properties for each point in the list, I just didn't expand them all for brevity).
Using this code, the save() method throws a SQL error "Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field."
Basically MySQL is saying it can't figure out how to build the geometry object with the information it is being passed.
Here is the SQL statement being produced:
SQL: insert into `routes` (`name`, `type`, `departure_name`, `departure_id`, `arrival_name`, `arrival_id`, `distance`, `duration`, `route`, `polyline`, `updated_at`, `created_at`) values (Route Runner, 1, Leas, 3, Heldj, 2, 36, 44, 40.786159 -111.981999,40.782483 -111.991326,40.777533 -111.99107,40.763445 -111.978736,40.763865 -111.914582,40.782344 -111.908802,40.782438 -111.896401,40.771566 -111.892318,40.771572 -111.899574,40.782391 -111.899599,40.782109 -111.911055,40.760796 -111.914607,40.744019 -111.904332,40.698132 -111.901919,40.611625 -111.906225,40.529731 -111.891577,40.493677 -111.891283,40.458924 -111.914726,40.449055 -111.912245,40.432307 -111.893405, 1, 2017-09-26 21:14:36, 2017-09-26 21:14:36)
So the problem is clear to me that the format that the LineString is being inserted is incorrect. It should be lineString(lat lng, lat lng, lat lng)
instead of just lat lng, lat lng, lat lng
.
How am I using this package wrong?
I've created a point type field in my DB on a location
model, I can set its value explicitly by creating a Point
instance as per the examples, but what I've been unable to figure out is what format it wants on the input side, with values coming from a client. If I submit something like location=Point(51.509865,-0.118092)
in my HTTP params, I end up with an error when I pass it to $location = Location::create($request->all());
:
SQLSTATE[22003]: Numeric value out of range: 1416 Cannot get geometry object from data you send to the GEOMETRY field (SQL: insert into `locations` (`name`, `location`) values (Coffee Shop 1, Point(51.509865, -0.118092)))
It looks like this needs something like Point::toWKT()
applied to it, but I can't see how/where I'm meant to apply that automatically.
Do I need to handle some separate lat/long string values and convert them to a point on input? Perhaps with a mutator? Is there some input format that "just works"?
For the record (and which I've not seen documented anywhere), the JSON output format is:
"location":{"type":"Point","coordinates":[-0.118092,51.509865]}
To complete API with JsonSerializable
do you have example use this to draw and save polygon in database? laravel, sorrt im realy new to this kind
Hello!
I'm trying to get points by bounding box. My code below.
$sw = new Point($bbox['min_y'], $bbox['min_x']);
$nw = new Point($bbox['max_y'], $bbox['min_x']);
$ne = new Point($bbox['max_y'], $bbox['max_x']);
$se = new Point($bbox['min_y'], $bbox['max_x']);
$geometry = new Polygon([
new LineString([$sw, $nw]),
new LineString([$nw, $ne]),
new LineString([$ne, $se]),
new LineString([$se, $sw]),
]);
$userPoints = UserPoint::intersects('geom', $geometry)->toSql();
$userPoints
returns incorrect Polygon consisting of 4 LineStrings:
select * from `user_points` where st_intersects(`geom`, ST_GeomFromText('POLYGON((37.39128112793 55.523189221554,37.39128112793 55.936510173094),(37.39128112793 55.936510173094,37.976989746094 55.936510173094),(37.976989746094 55.936510173094,37.976989746094 55.523189221554),(37.976989746094 55.523189221554,37.39128112793 55.523189221554))'))
Of course, this sql can't find my POINT(37.72185 55.76129)
.
The right way to find my point is:
select * from `user_points` where st_intersects(`geom`, ST_GeomFromText('POLYGON((37.39128112793 55.523189221554, 37.39128112793 55.936510173094, 37.976989746094 55.936510173094, 37.976989746094 55.523189221554, 37.39128112793 55.523189221554))'))
I created this polygon from my code
$bbox = "POLYGON(($sw->toPair(), $nw->toPair(), $ne->toPair(), $se->toPair(), $sw->toPair()))";
But as I can see from your code, I can't insert WKT to scopes
$query->whereRaw("st_{$relationship}(`{$geometryColumn}`, ST_GeomFromText('{$geometry->toWkt()}'))");
What I want? I think, your Polygon is wrong, because it create 4 points, but not a polygon. You can test in with wicket.
Additional, I want to see bbox creation method and way to put in scopes my own WKT.
Thanks!
I followed the quickstart in the readme, but encountered an exception when running php artisan migrate
:
[Symfony\Component\Debug\Exception\FatalThrowableError]
Type error: Argument 1 passed to CreatePlacesTable::{closure}() must be an instance of Grimzy\LaravelMysqlSpatial\Schema\Blueprint, instance of Illuminate\Database
\Schema\Blueprint given, called in /home/vagrant/gaspis/vendor/laravel/framework/src/Illuminate/Database/Schema/Builder.php on line 163
I am using a Homestead instance with
I have installed the library with composer require grimzy/laravel-mysql-spatial
Hello,
When decoding geometry data field from MySQL it fails for some reason with following exception:
"exception": "GeoIO\\WKB\\Parser\\Exception\\ParserException",
"message": "Parsing failed: Bad endian byte value 230.",
"trace": "#0 /home/vagrant/Code/xxx/public_html/vendor/grimzy/laravel-mysql-spatial/src/Types/Geometry.php(63): GeoIO\\WKB\\Parser\\Parser->parse('\\xE6\\x10\\x00\\x00\\x01\\x06\\x00\\x00\\x00\\x01\\x00\\x00\\x00\\x01\\x03...')\n#1 /home/vagrant/Code/xxx/public_html/vendor/grimzy/laravel-mysql-spatial/src/Eloquent/SpatialTrait.php(76): Grimzy\\LaravelMysqlSpatial\\Types\\Geometry::fromWKB('\\xE6\\x10\\x00\\x00\\x01\\x06\\x00\\x00\\x00\\x01\\x00\\x00\\x00\\x01\\x03...')\n#2
This geometry contains multipolygon
, have tested with geometry that contains point
and worked fine.
Please see gist with all data required to reproduce issue: https://gist.github.com/deividaspetraitis/1d685fa1cdab0ad5719a07c4162edb57
I´m trying to test the within function to check if a point is inside a defined polygon.
Here´s the code i´m using:
$place = Place::first();
$polygon = new Polygon([new LineString([
new Point(40.74894149554006, -73.98615270853043),
new Point(40.74848633046773, -73.98648262023926),
new Point(40.747925497790725, -73.9851602911949),
new Point(40.74837050671544, -73.98482501506805),
new Point(40.74894149554006, -73.98615270853043)
])]);
return Place::within('location', $polygon) ;
'location' is the column on my table -> ($table->point('location');)
When i run the within function, it keeps giving me this error:
Object of class Grimzy\LaravelMysqlSpatial\Eloquent\Builder could not be converted to string
Am i doing something wrong?
Hi
We stored coordinates in Point(lat lng)
format. Now when I want to use this package the result of getLat()
and getLng()
is incorrect.
I think we have two solution :
Update
SQL query and use this package.Point.php
file methods.I know there are different standards/approaches (ISO 6709, MySQL Forum). But I don't know which solution is better.
Do you have any suggestion ?
Hi, I am having an issue with a geometry collection field . At some point, I have to update the field removing all points inside it. The problem is when I try to obtain an object from the DB and then get the field as an array.
It seems that the cast for an empty geometry collection is erroneous:
FatalThrowableError (E_ERROR) Call to a member function toArray() on string
The error occurs when calling:
$obj->waypoints->toArray()
A dump for the object returns the field as:
"waypoints" => "\x00\x00\x00\x00\x01\x07\x00\x00\x00\x00\x00\x00\x00"
The SQL query for the field, using ST_AsText(waypoints) returns:
GEOMETRYCOLLECTION()
I declare the field in the migration as:
$table->geometryCollection('waypoints');
and declare as a spatial field on the model class:
protected $spatialFields = [ 'waypoints' ];
Does this work on mariadb?
牛逼啊
Including PointCollection and GeometryCollection
I thought i'd put this here for anyone else that wanted it.
I wanted to orderByDistance and it was as simple as:
public function scopeOrderByDistance($query, $geometryColumn, $geometry)
{
$this->isColumnAllowed($geometryColumn);
$query->orderByRaw("st_distance(`$geometryColumn`, ST_GeomFromText(?))", [
$geometry->toWkt()
]);
return $query;
}
Presumably you can add these scopes for the other columns as well.
I've table like this:
CREATE TABLE `jovan_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`properties__name` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.name'))) VIRTUAL,
`properties__age` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.age'))) VIRTUAL,
`properties__height` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.height'))) VIRTUAL,
`properties__address__city` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.city'))) VIRTUAL,
`properties__address__zip_code` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.zip_code'))) VIRTUAL,
`properties__address__state` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.state'))) VIRTUAL,
`properties__address__country` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.country'))) VIRTUAL,
`properties__address__home_coordinate` point GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.home_coordinate')))) VIRTUAL,
`properties__address__routes` geometry GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.routes')))) VIRTUAL,
`__docs` json NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `jovan_test_properties__name_index` (`properties__name`),
KEY `jovan_test_properties__age_index` (`properties__age`),
KEY `jovan_test_properties__height_index` (`properties__height`),
KEY `jovan_test_properties__address__city_index` (`properties__address__city`),
KEY `jovan_test_properties__address__zip_code_index` (`properties__address__zip_code`),
KEY `jovan_test_properties__address__state_index` (`properties__address__state`),
KEY `jovan_test_properties__address__country_index` (`properties__address__country`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Insert new value like this:
insert into jovan_test set __docs = '{"properties": {"age": 29, "name": "Rahmat Awaludin", "height": 160, "address": {"city": "Bandung", "state": "Jawa Barat", "routes": {"type": "LineString", "coordinates": [[102, 0], [103, 1], [104, 0], [105, 1]]}, "country": "Indonesia", "zip_code": "43193", "home_coordinate": {"type": "Point", "coordinates": [30, 10]}}}}'
With resulting like this:
mysql root@localhost:intelligence> select id, st_astext(properties__address__home_coordinate) from jovan_test;
+----+-------------------------------------------------+
| id | st_astext(properties__address__home_coordinate) |
+----+-------------------------------------------------+
| 2 | POINT(30 10) |
+----+-------------------------------------------------+
The within
query generated query like this:
select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText("POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))"))
It silently error on mysql (I don't get error message in laravel), the message like this:
Binary geometry function st_within given two geometries of different srids: 4326 and 0, which should have been identical.
This because ST_GeomFromGeoJson generate SRID 4326 by default see here if none provided.
The srid argument, if given, must be a 32-bit unsigned integer. If not given, the geometry return value has an SRID of 4326.
While ST_GeomFromText generate SRID 0 by default if not provided see here.
This can be solved by using SRID to 0 on first ST_GeomFromGeoJson call. That is possible with this library.
But, I believe allowing set SRID on scopeWithin
will work best. So the resulting query would be
select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText("POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))",4326))
This sinergy with allowing SRID on creating column as you alredy done.
What do you think? I can prepare a PR if you want.
While writing PHPUnit tests, I ran into a couple of issues after running migrations.
First of all, something's wrong with the dependency injection. An instance of Illuminate\Database\Schema\Blueprint
is always passed, even if Grimzy\LaravelMysqlSpatial\Schema\Blueprint
is used instead. May not be related to this package, but I haven't yet found a solution.
Secondly, after bypassing the above issue by using $table->addColumn('point', 'column-name');
instead of $table->point('column-name')
, the following exception is thrown using an in-memory sqlite database for testing:
Error: Call to undefined method Illuminate\Database\Schema\Grammars\SQLiteGrammar::typePoint()
Any suggestions on how this could be fixed? If I'm doing something wrong, please let me know. It would be nice to document how to properly configure this package for testing.
exception
:
"Symfony\Component\Debug\Exception\FatalThrowableError"
file
:
"/var/www/pedro/vendor/grimzy/laravel-mysql-spatial/src/Connectors/ConnectionFactory.php"
line
:
27
message
:
"Undefined class constant 'PDO::FETCH_OBJ'"
So, right now the field parsed using code:
public static function fromWKB($wkb)
{
// mysql adds 4 NUL bytes at the start of the binary
$prefix = "\0\0\0\0";
if (substr($wkb, 0, strlen($prefix)) == $prefix) {
$wkb = substr($wkb, strlen($prefix));
}
$parser = new Parser(new Factory());
return $parser->parse($wkb);
}
but mysql field actually stored like
$srid . $wkb
where length of $srid is 4 bytes
if $srid != 0, there will be error like this
Parsing failed: Bad endian byte value 230
Well, for mysql <8.0 there no differences between any value of $srid,
but for Earth coordinates correct one is 4326 (EPSG:4326 - wgs-84)
and difference appears in mysql >=8
At the end, it's, probably, should looks like this:
public static function fromMysqlGeoField($value)
{
// $srid = substr($value,0,4); //
$wkb = substr($value, 4);
$parser = new Parser(new Factory());
return $parser->parse($wkb);
}
Streamline API (parameter order):
Rearrange parameters of spatial analysis Eloquent scopes to be in the same order as the in the generated SQL.
hi first thanks for amazing package i wish i reached here long time ago
as in readme
$table->point('location')->change();
where is this function change() decleared ?
what you do to make point column not null so i can use index on it?
is there is a way to set default value for point ? point(0,0) is ok for me
Classes targeted:
PointCollection
MultiLineString
MultiPolygon
GeometryCollection
Hi, I'd like to request this feature as I'd love to use this library but, due to restrictions with a legacy system, I can't change the order the coordinates are stored in the database. I need to find a way to allow this library to work with POINT()
s stored as (lat lng).
I came across #38 and don't quite understand your suggestion of extending the Point
class to customise the order. I assume you mean Grimzy\LaravelMysqlSpatial\Types\Point
, but I can't work out how to get the library to then use my extended class.
If an option (either by config or something to set in the model) could be provided to do this without needing to extend anything from the project, that would be great. I'd be happy to attempt this myself if I had some guidance.
Declaration of Grimzy\LaravelMysqlSpatial\Schema\Blueprint::point($column) should be compatible with Illuminate\Database\Schema\Blueprint::point($column, $srid = NULL)
Hi,
refere to laravel\framework
commit of Add support for defining a Spatial reference system for a Point column. (code: ddc4e05fc03a8f55a56be2afdce48ba891879d65)
, they add srid to declearation of point
method.
Due to what I mentioned above, it needs to make point method your Blueprint compatible.
check following error:
Declaration of Grimzy\LaravelMysqlSpatial\Schema\Blueprint::point($column) should be compatible with Illuminate\Database\Schema\Blueprint::point($column, $srid = NULL)
I am trying to use this package to store polygons in my database. When I set up new points for each point of my polygon and use them to create a LineString to save as a Polygon on my model, I am getting an error (title of issue)
The output of Collection is shown below along with the code to generate the collection.
I have added use SpatialTrait;
and protected $spatialFields = ['polygon'];
to my model.
I use the generated $polygons
variable in a relational model save()
method.
I have also set up the migration to use your Blueprint methods, even though Laravel now supports these fields.
Code:
$coords = explode(' ', $polygon);
$tempCoords = [];
foreach($coords as $coord) {
list($lat, $lon) = explode(',', $coord);
$tempCoords[] = new Point($lat, $lon);
}
$collection = new LineString($tempCoords);
$polygons[] = new MyModel([
'polygon' => new Polygon($collection)
]);
Dump of $collection;
Grimzy\LaravelMysqlSpatial\Types\LineString {#764
#points: array:7 [
0 => Grimzy\LaravelMysqlSpatial\Types\Point {#757
#lat: 34.39
#lng: -98.36
}
1 => Grimzy\LaravelMysqlSpatial\Types\Point {#758
#lat: 34.51
#lng: -98.46
}
2 => Grimzy\LaravelMysqlSpatial\Types\Point {#759
#lat: 34.51
#lng: -98.31
}
3 => Grimzy\LaravelMysqlSpatial\Types\Point {#760
#lat: 34.4
#lng: -98.23
}
4 => Grimzy\LaravelMysqlSpatial\Types\Point {#761
#lat: 34.21
#lng: -98.23
}
5 => Grimzy\LaravelMysqlSpatial\Types\Point {#762
#lat: 34.22
#lng: -98.37
}
6 => Grimzy\LaravelMysqlSpatial\Types\Point {#763
#lat: 34.39
#lng: -98.36
}
]
}
Have to do it because different MySQL versions have different spatial analysis function and their behavior is sometimes different.
Branches:
master
: MySQL 5.7 and 8.0mysql-5.6
: MySQL 5.6Versions:
1.x.x
: MySQL 5.6 (also supports MySQL 5.5 but not all spatial analysis functions)2.x.x
: MySQL 5.7 and 8.0Setup: Laravel 5.5.x
Let's consider following table:
<?php
$schema->create('classifieds_locations', function (Blueprint $table) {
/...
$table->geometry('location');
/...
$table->engine = 'InnoDB';
});
Model:
<?php
use App\Entities\MariaDB\Model;
use Grimzy\LaravelMysqlSpatial\Types\Point;
use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\SoftDeletes;
class Location extends Model implements LocationModelInterface
{
use SoftDeletes, SpatialTrait;
/**
* {@inheritdoc}
*/
protected $table = 'locations';
/**
* {@inheritdoc}
*/
public $timestamps = true;
/**
* {@inheritdoc}
*/
protected $fillable = [
0 => 'location'
];
/**
* {@inheritdoc}
*/
protected $spatialFields = [
'location',
];
/**
* @inheritdoc
*/
public function getLocation() : Point
{
return $this->getAttribute('location');
}
/**
* @inheritdoc
*/
public function setLocation(Point $point) : LocationModelInterface
{
$this->setAttribute('location', $point);
return $this;
}
}
When trying to update point values using following apporach is doesn't work because models both orinigal and attributes arrays are updated: $location->getLocation()->setLat(63.687515);
Any ideas why? It looks much nicer than workaround I've used to update values: $location->setLocation(63.687515, $location->getLocation()->getLng()));
Only a couple of the Relations between two geometries are implemented as scopes (distance, bounding).
Proposal is to add the remaining functions:
I can insert geometry records using the following code:
$location = new Point($datum->lat, $datum->lon);
MyModel::firstOrCreate([
'location' => DB::raw("ST_GeomFromText('" . $location->toWKT() . "')"),
]);
This seems a little verbose. Is there a shorter/cleaner way to do this?
laravel-mysql-spatial/src/Types/PointCollection.php
Lines 64 to 71 in e17f491
Hello,
Is there any way to validate that a POINT is included in a POLYGON ? or is within bounds (min/max * lat/lng) ?
Thank you !!
It would be nice to use the Laravel 5.5 spatial fields migrations instead of the package migrations:
https://laravel.com/docs/5.5/migrations#columns
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.