CODEWITHSUNDEEP
mysqllaravelspatial-indexgenerated-columns
Erich
Erich

Reputation: 2616

Spatial index on generated (virtual/stored) column?

I have a generated (storedAs) column in my Laravel database that takes two decimal columns lat and lng and returns a point geospatial type. I'm using a generated column so as to avoid a scenario where the decimal lat/lng columns somehow get updated and the spatial latlng column doesn't (or vice versa).

I'd like to also index this column so as to perform speedy lookups, but running into a slight hitch. I'm using the Laravel migration builder to generate the table schema. Although the source columns are not nullable and default to 0, it won't run if I also add a spatial index, citing all parts must be not null. I'm assuming that "all parts" refers to just the latlng column.

The migration below uses the spatialIndex() method from Grimzy's Laravel MySQL Spatial extension, but I don't think that's the issue, as the standard Laravel Blueprint class also returns the same result.

public function up()
{
    Schema::table('locations', function (Blueprint $table) {
        $table->decimal('lat', 8, 6)->default(0);
        $table->decimal('lng', 9, 6)->default(0);
        $table->point('latlng')->storedAs('POINT(lat, lng)');
        $table->spatialIndex('latlng');
    });
}

Upon migration:

Illuminate\Database\QueryException  : SQLSTATE[42000]: Syntax error or access violation: 1252 All parts of a SPATIAL index must be NOT NULL (SQL: alter table `locations` add spatial `locations_latlng_spatial`(`latlng`))

My only guess is that MySQL doesn't support spatial indexes on generated columns. Or am I doing something wrong?

UPDATE: seems I'm using MariaDB and not MySQL, which doesn't currently support them.

Upvotes: 1

Views: 792

Answers (1)

yuliyy__
yuliyy__

Reputation: 61

A workaround for MariaDB is to use triggers.

Table:

CREATE TABLE `geoms` (
    `lat` DECIMAL(20,6) NOT NULL,
    `lng` DECIMAL(20,6) NOT NULL,
    `latlng` POINT NOT NULL,
    SPATIAL INDEX `latlng` (`latlng`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

Insert trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `geoms_before_insert`
BEFORE INSERT ON `geoms`
FOR EACH ROW BEGIN
    set NEW.latlng = POINT(NEW.lat, NEW.lng);
END

Update trigger:

CREATE DEFINER=`root`@`localhost` TRIGGER `geoms_before_update`
BEFORE UPDATE ON `geoms`
FOR EACH ROW BEGIN
    SET NEW.latlng = POINT(NEW.lat, NEW.lng);
END

A drawback with this method is once you changed how the value of the "virtual column" is derived, you would have to re-render all existing data manually.

Upvotes: 0

Related Questions