CODEWITHSUNDEEP
mysqlsqlindexingspatialsrid
andyde
andyde

Reputation: 21

Why is MySQL Database not using my spatial index?

In my setup I want to use a spatial index to search efficiently through a database, in particular to get all points in a radius around a second point. However, I'm kind of confused, why MySQL (version 8.0.29) is refusing to use my index. My query is the following:

SELECT * FROM `StationTable` WHERE
  st_contains( 
      st_makeEnvelope (
          point(53.6, 10),
          point(53.5, 9.9)
      ),
      Location
  )
AND
ST_Distance_Sphere(Location, ST_GeomFromText('POINT(9.95 53.55)')) < 1000;

I expected the table to use my spatial index on Location to find the 4-5 points inside this envelope and then to calculate the distance from my point, but my database (InnoDB) is performing the following: PHPMyAdmin - SQL Explain on my query Why doesn't it use my spatial index? SRID of the data is 0, but I also tested data with SRID = 4326 with the following error:

#3618 - st_makeenvelope(POINT, POINT) has not been implemented for geographic spatial reference systems.

I also tested to query with WHERE ST_X(Location) BETWEEN, but it also didn't used my index. How can I write my query, to use my index?

Upvotes: 1

Views: 436

Answers (1)

andyde
andyde

Reputation: 21

Found the answer myself: The column needs to be SRID restricted (see dev.mysql.com for details). So the correct query would be

CREATE TABLE `StationTable` (
    `UUID` VARCHAR(32),
    `Location` POINT NOT NULL SRID 4326,
    PRIMARY KEY(`UUID`),
    SPATIAL INDEX(`Location`)
);

SELECT UUID, Location FROM `StationTable` WHERE
  ST_Distance_Sphere(ST_GeomFromText('POINT(9.95 53.55)', 4326), Location) < 1000;

Upvotes: 1

Related Questions