Spatial index will not be used 'cause it has no SRID attribute

Question

I am very confused now, because I cannot get this to work. I have a tbl_address which already has longitude and latitude. I want to add a spatial index for my search. So I want to add a location POINT column in my database. I tried this one:

alter table tbl_address
add column location POINT GENERATED ALWAYS AS (ST_SRID(POINT(longitude, latitude), 4326)) STORED NOT NULL;

But when I then try to add my SI, I receive this warning back:

CREATE SPATIAL INDEX idx_location ON tbl_address (location);
0 row(s) affected, 1 warning(s): 3674 The spatial index on column 'location' will not be used by the query optimizer since the column does not have an SRID attribute. Consider adding an SRID attribute to the column. Records: 0  Duplicates: 0  Warnings: 1

I get that just my entries do have the 4326 srid, but if I try to add it to the column itself (POINT SRID 4326) I am receiving a syntax error on GENERATED ALWAYS AS.

How can I make it work? It would be great to have location updated when longitude/latitude changes. Do I really need to implement a trigger? I am using MySQL 8.

Thanks!

Edit: My mysql version is 8.0.43-0ubuntu0.22.04.1 and this is the test table I am using:

CREATE TABLE IF NOT EXISTS tbl_address
(
address_id int not null auto_increment,
latitude DECIMAL(9,2) NOT NULL default 0,
longitude DECIMAL(9,2) NOT NULL default 0,
location POINT not null SRID 4326,
Primary Key (address_id)
);

Not sure why it is working for you guys, maybe it is an issue with this specific version?

I tested your example on MySQL 9.4.0, 8.4.0, and 8.0 and I got no warning 3674. Can you please confirm the version you are using? Use SELECT VERSION(); Also show the complete error message for the syntax error you got. — Bill Karwin
– Bill Karwin, Commented Oct 26 at 13:24
Also please show the data types for your current table. I tested with DECIMAL(9,2) for the longitude and latitude columns, but this was a guess. It helps people find the right answer if you show your reproducible test case. — Bill Karwin
– Bill Karwin, Commented Oct 26 at 14:39
I really don't know of GIS on MySQL, but wouldn't an st_geom instead of a point allow you to build your index, as explictly told in this comment (found through a less interesting one). — Guillaume Outters
– Guillaume Outters, Commented Oct 27 at 6:13
Hi, my version is: '.0.43-0ubuntu0.22.04.1' I will update for my details later today, thanks! — Sylber
– Sylber, Commented Oct 27 at 14:11

Guillaume Outters · Accepted Answer · 2025-10-28 06:27:02Z

1

Although the "natural" combination POINT SRID 4326 followed by GENERATED results in syntax errors,
putting the SRID tag at the very end of the column definition is the MySQL accepted way to express it, after the GENERATED and the NOT NULL:

alter table tbl_address
add column location POINT GENERATED ALWAYS AS … STORED NOT NULL SRID 4326;
-- Or with hint notation:
alter table tbl_address
add column location POINT GENERATED ALWAYS AS … STORED NOT NULL /*!80003 SRID 4326 */;

(hint notation found on this Percona blog)

Although I couldn't reproduce the same warning as you (even using VARCHAR as the type for latitude and longitude) on the SPATIAL INDEX' creation, thanks to EXPLAIN's possible_keys we get an idea of its usability;
as can be seen on this db<>fiddle:

your SPATIAL INDEX on the non-SRIDed POINT did not show in EXPLAIN
(= what the warning alerted you of)
2 attempts with SRID right where we think it belong (next to the POINT) fail with a syntax error
(= what you got with your attempts)
the SPATIAL INDEX on the column SRIDed at the end is now considered a possible_keys.

edited Oct 28 at 6:27

answered Oct 27 at 6:54

Guillaume Outters

7,5041 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sylber Oct 27 at 19:07

Hm I am confused, I have checked the fiddle and it is indeed working there. I am using version 8.0.43 and it is not working for me. Maybe I will upgrade to 8.4 and try again?

Sylber Oct 27 at 19:14

Btw, moving it to the end did work as well! Thanks a lot!

Guillaume Outters Oct 28 at 6:28

Your shared feedback makes it precious for the community: I included it to the answer. Thanks! (as well as for the question: I learned a lot about MySQL and GIS in one day thanks to you)

Collectives™ on Stack Overflow

Spatial index will not be used 'cause it has no SRID attribute

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related