How to store Google Map Circle data in MySql Database

Question

I am trying to store Google Map Circle data in MySql Database. I choose text data type to store Center Coordinate and int to store radius of the center. Now I am using below code to fetch data from those column.

$zone = RestaurantZones::whereRaw( "ST_Distance(circle_coordinates, ?) <= radius", [$point] )->pluck( 'id' );

I am getting below error.

Illuminate\Database\QueryException: SQLSTATE[22023]: Invalid parameter value: 3037 Invalid GIS data provided to function st_distance. 

How can I store Google Map Circle data in MySql Database ?

Answer 1

It's pretty clear from the error shown, to store and retrieve this kind of data, you should use MySQL's spatial data types and functions.

"(Just for revision) Imagine you have a map with circles on it, like the Google Map circles. Each circle has a center (the middle point) and a radius (how big it is).

• When you want to save this information in a computer, you can't just use regular numbers. You need a special way, like using a special code for these circles. In computer language, this is called "spatial data.
• In this table, you tell the computer that one column is for the center of the circle, and another column is for the radius.

Having a table that uses spatial data types

CREATE TABLE restaurant_zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    circle_coordinates POINT, -- for storing the center coordinates
    radius INT,
    SPATIAL INDEX(circle_coordinates) -- create a spatial index for better performance
);

• Use MySQL's ST_GeomFromText function to create a point from the center coordinates.

INSERT INTO restaurant_zones (circle_coordinates, radius) VALUES (ST_GeomFromText('POINT(11.1111.111)', 1000);

Answer 2

Ensure that your circle_coordinates column in the RestaurantZones table is defined as a spatial data type into your Model. if you are using this package

grimzy/laravel-mysql-spatial

then model should looks like this.

use Grimzy\LaravelMysqlSpatial\Eloquent\SpatialTrait;

    class RestaurantZones extends Model
    {
        use SpatialTrait;
    
        protected $fillable = ['name', 'coordinates', 'radius'];
    
        protected $spatialFields = [
            'coordinates',
        ];
    }

In your migration field type should be in the point

$table->point('circle_coordinates');

And

use Grimzy\LaravelMysqlSpatial\Types\Point;

$point = new Point($latitude, $longitude);

If you've ensured that the circle_coordinates and radius columns are correctly defined, and you're passing a valid Point object for the $point variable, you should be able to use ST_Distance without encountering the "Invalid GIS data" error. If the issue persists, you may want to check the actual data in your database to make sure there are no anomalies or inconsistencies in the spatial data.