Support ST_CENTROID spatial function

[leandrocaplan]

I'm using Laravel Eloquent Spatial in my project.
Despite I've could used it pretty well when selecting Point objects within a Polygon geometry, I had to create a raw SQL request to use those MySQL Spatial methods.

Here's my code of a method in my app, used to return a Point geometry with the centroid of a polygon:

public function centroide(array $featurePoligono){

  error_log(json_encode($featurePoligono)); //{"type":"Feature" ...}
  error_log(json_encode($featurePoligono['geometry'])); //{"type":"Polygon" ...}
  
  $dbQuery=DB::select('SELECT ST_AsGeoJSON(ST_Centroid(ST_GeomFromGeoJSON(\''. json_encode($featurePoligono) .'\')))');
  
  $centroide=array_values((array)$dbQuery[0])[0];
  
  error_log($centroide); //{"type": "Point", "coordinates": [-58.424712150445174, -34.605689506111084]}
  error_log(gettype($centroide)); //string
  error_log(get_class(json_decode($centroide))); //stdClass
  
  return (array)json_decode($centroide);
}

Is there any way to achieve this using Laravel Eloquent Spatial?

[MatanYadaev]

@leandrocaplan Hi, let me know if it works for you: https://github.com/MatanYadaev/laravel-eloquent-spatial/blob/master/API.md#withcentroid
Example:

laravel-eloquent-spatial/tests/SpatialBuilderTest.php

Lines 375 to 407 in 30e0a85

	it('calculates geometry centroid', function (): void {
	// Arrange
	$polygon = Polygon::fromJson('{"type":"Polygon","coordinates":[[[-1,-1],[1,-1],[1,1],[-1,1],[-1,-1]]]}');
	TestPlace::factory()->create(['polygon' => $polygon]);
	// Act
	/** @var TestPlace $testPlace */
	$testPlace = TestPlace::query()
	->withCentroid('polygon')
	->withCasts(['centroid' => Point::class])
	->firstOrFail();
	// Assert
	$expectedCentroid = new Point(0, 0);
	expect($testPlace->centroid)->toEqual($expectedCentroid);
	});
	it('calculates geometry centroid with alias', function (): void {
	// Arrange
	$polygon = Polygon::fromJson('{"type":"Polygon","coordinates":[[[-1,-1],[1,-1],[1,1],[-1,1],[-1,-1]]]}');
	TestPlace::factory()->create(['polygon' => $polygon]);
	// Act
	/** @var TestPlace $testPlace */
	$testPlace = TestPlace::query()
	->withCentroid('polygon', 'centroid_alias')
	->withCasts(['centroid_alias' => Point::class])
	->firstOrFail();
	// Assert
	$expectedCentroid = new Point(0, 0);
	expect($testPlace->centroid_alias)->toEqual($expectedCentroid);
	});

[leandrocaplan]

Hi Matan!

Sorry for the delay of my answer. I've had a few difficult and busy days, and I'm just now able to give you a proper feedback.

First of all, I saw that you updated the package with the new 'withCentroid' method, just after I've posted this issue. I really appreciate that goodwill!

Answering you question, asking if the code shown in 'API.md', as an example of usage of the new 'withCentroid' method, worked for me, I can tell you my answer is:
It worked on a %25, since I got many errors while testing it in my app.

The main ones were:

-When applying 'withCast()' in the chain after 'withCentroid()':

"Call to undefined method MatanYadaev\EloquentSpatial\SpatialBuilder::withCast()" (I couldn't even find the definition of that method anywhere)

-When removing that method in the chain, but just executing the query with 'first()' or 'get()':

"Call to a member function hydrate() on null"

We should take into consideration that, since Eloquent is a ORM, every Eloquent model and accordingly, every Eloquent Query Builder instance, it's related to a table in our database. However, these kind of functions of MySQL Spatial, such as 'ST_Centroid' or 'ST_GeomFromGeoJSON', doesn't always retrieve data from a table to perform the needed query, since they just return a geometry object based on the values of another geometry, which may be coded directly in the query text.

For example:

"select ST_CENTROID(ST_GeomFromText('POLYGON((-58.420486450195 -34.619216934912, ... , -58.420486450195 -34.619216934912))', 0)) AS centroid"

In this way, we may need an instance of 'Illuminate\Database\Query\Builder' (Laravel Query Builder) instead of an instance of 'Illuminate\Database\Eloquent\Builder' (Eloquent Query Builder), which 'MatanYadaev\EloquentSpatial\SpatialBuilder' inherits from, to perform those kind of queries properly, since we don't want to generate those queries from a given Eloquent model.

So, in the example of API.md, we have the line:

Place::create(['polygon' => $polygon]); //After '$polygon = Polygon::fromJson('{"type":"Polygon",...'

Being 'Place' an Eloquent ORM model, which creates a new record into our database just for calculating the centroid of a given polygon. This is something we don't want and no need to.

So, I'm sharing you here, the section of my code that at least could use, somewhere in it, the new 'withCentroid()' method to achieve its purpose, with the lines which throwed error commented and describing the error:

    public function centroide(array $featurePoligono){

        //Code of the previous version:

        error_log(json_encode($featurePoligono)); //{"type":"Feature" ...}
        error_log(json_encode($featurePoligono['geometry'])); //{"type":"Polygon" ...}
        
        //We generate and run a raw SQL query:
        $dbQuery=DB::select('SELECT ST_AsGeoJSON(ST_Centroid(ST_GeomFromGeoJSON(\''. json_encode($featurePoligono) .'\')))');
        
        $centroide=array_values((array)$dbQuery[0])[0]; //En $centroide nos queda un GeoJSON como string

        error_log($centroide); //{"type": "Point", "coordinates": [-58.424712150445174, -34.605689506111084]}
        error_log(gettype($centroide)); //string
        error_log(get_class(json_decode($centroide))); //stdClass
       
        


        //Code using new 'withCentroid' method:

        $polygon = Polygon::fromJson(json_encode($featurePoligono['geometry']));

        //Place::create(['polygon' => $polygon]); //Unwanted sentence

        //We create a new SpatialBuilder instance which is not related to any model, just passing to it constructor, an instance of
        //a Laravel (not Eloquent) query builder, by calling 'DB::query' without arguments:
        $query=new SpatialBuilder(DB::query()); 
        //$query=new SpatialBuilder(new Builder(DB::connection())); //Same result as line above

        //Applying 'withCentroid' to new instance of SpatialBuilder:
        $placeWithCentroid = $query
            ->withCentroid($polygon);
            //->withCast('centroid', Point::class); //Call to undefined method MatanYadaev\EloquentSpatial\SpatialBuilder::withCast()
            //->first(); //Call to a member function hydrate() on null

        error_log($placeWithCentroid->toSql()); //// select ST_CENTROID(ST_GeomFromText('POLYGON((...
        //error_log($placeWithCentroid->get()); //Call to a member function hydrate() on null

        //Executing the query as raw SQL, but with text generated from SpatialBuilder instance
        $dbQuery2=DB::select($placeWithCentroid->toSql())[0];
 
        error_log($dbQuery2->centroid);
        
        $centroide2=Point::fromWkb($dbQuery2->centroid)->toJson();
       
        error_log($centroide2); //{"type": "Point", "coordinates": [-58.424712150445174, -34.605689506111084]}

        

        
        //These two lines have the same result (the second one returning the GeoJSON generated using 'withCentroid', and the first one using just raw SQL):
        return (array)json_decode($centroide);
        //return (array)json_decode($centroide2);
    }

May you tell me if there's a more proper way to achieve this same result by using this package?

Thanks a lot!
Leandro

[MatanYadaev]

@leandrocaplan can you please fork this repo and reproduce this issue with a failing test? Or even a fresh new Laravel project?
A failing test will be the best way for me to fix it.

[leandrocaplan]

@MatanYadaev I'll create a new branch of the project in my own repo with the failure, and last version of laravel-eloquent-spatial defined into 'composer.json', that would be OK?

[leandrocaplan]

The link of the project with the new testing branch is:
https://gitlab.com/leandrocaplan/laravel-10-leaflet-example/-/commit/2ec3d8e0b7cc2a58d4591c62f049220eedd413c0

I've defined the link just to the file where the issue happens.

Here's a link just for easily testing the issue (you may want add some outlets in the app before testing it, when you first install it locally):
http://localhost:8000/api/centroide?json=%7B%22type%22%3A%22Feature%22%2C%22properties%22%3A%7B%22name%22%3A%22zona%22%7D%2C%22geometry%22%3A%7B%22type%22%3A%22Polygon%22%2C%22coordinates%22%3A%5B%5B%5B-58.42048645019532%2C-34.61921693491219%5D%2C%5B-58.388385772705085%2C-34.612011953145455%5D%2C%5B-58.39920043945313%2C-34.58827347289634%5D%2C%5B-58.40915679931641%2C-34.578097761700334%5D%2C%5B-58.421344757080085%2C-34.574281548799924%5D%2C%5B-58.445549011230476%2C-34.5814898038672%5D%2C%5B-58.46065521240235%2C-34.60169785184796%5D%2C%5B-58.4549903869629%2C-34.61554388509679%5D%2C%5B-58.42048645019532%2C-34.61921693491219%5D%5D%5D%7D%7D

[MatanYadaev]

@leandrocaplan Hi, there are two issues:

1. You used withCast instead of withCasts - it's an issue in my docs, I fixed it.
2. withCasts works only when you have a model, and in this case, you don't have one. You use Laravel's Query Builder, and Not Eloquent (the ORM). This package isn't meant for the Query Builder, unfortunately.

There is a hacky workaround, if you want:

$emptyModel = new class extends Model {};
$query = (new SpatialBuilder(DB::query()))->setModel($emptyModel);
$centroid = $query
    ->withCentroid($polygon)
    ->withCasts(['centroid' => Point::class])
    ->from(null)
    ->first()['centroid'];

[leandrocaplan]

@MatanYadaev That worked pretty fine!

Just notice this: if you check the API doc, at 'withCentroid' example, it shows:

$placeWithCentroid = Place::query()
    ->withCentroid('polygon')
    ->withCast('centroid', Point:class) // This is important, otherwise the centroid will be returned as a binary string.
    ->first();

Instead of:

$placeWithCentroid = Place::query()
    ->withCentroid('polygon')
    ->withCasts(['centroid' => Point:class]) 
    ->first();

Besides we should have 'withCasts' instead of 'withCast', we must pass it an array, instead of two arguments like the doc is currently showing.
There's also any documentation of the 'withCasts' method at the moment in the docs.

Thanks a lot!
Leandro

[MatanYadaev]

@leandrocaplan I forgot to push a commit :) Now it's live. Thanks!