There are a few gotchas when trying to add a spatial column to an existing MySQL table. Especially if you want to have a spatial index, which is kind of the point.
This document goes through the process I had to endure to get spatial working the way I want in MySQL. Hint: do not execute scripts on the way through – scroll to the bottom to get the one that actually works. I’ve put it here so I have a record of what I had to do to get things working.
Why MySQL and not Postgres/PostGIS?
One word – legacy. The existing MySQL 8 database has too many applications accessing data from it, and too many business processes that rely on the existing structure. Yes, PostGIS has better spatial functionality, but we’ll save that for use on fresh projects.
Engine Types
The existing database has been running for many years. The first issue is that to use spatial features reference systems in MySQL, you can’t use MyISAM. You should use InnoDB.
In case you’re on MyISAM, the upgrade is painless.
ALTER TABLE `mydatabase`.`locations` ENGINE=InnoDB;
I already have existing columns for latitude and longitude, in WGS84. I obviously want to use those in the new spatial column.
Create Spatial Column
The new spatial column is going to be called geom.
The simplest way to add the column with an SRID is like this:
ALTER TABLE `mydatabase`.`locations` ADD COLUMN `geom` GEOMETRY SRID 4326;
All good. To check that the SRID has been added, we can query the INFORMATION_SCHEMA:
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=4326;
With any luck, there will be one row displayed.
+---------------+--------------+--------------+-------------+----------+--------+--------------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | SRS_NAME | SRS_ID | GEOMETRY_TYPE_NAME | +---------------+--------------+--------------+-------------+----------+--------+--------------------+ | def | mydatabase | locations | geom | WGS 84 | 4326 | geometry | +---------------+--------------+--------------+-------------+----------+--------+--------------------+ 1 row in set (0.11 sec)
Spatial Index
To use a spatial index, there has to be an SRID and the column must be defined as NOT NULL.
But before we can addNOT NULL, we need to populate geom with a default value.
If using Workbench, add the Default value of ST_SRID(POINT(0,0),4326) and ticking NOT NULL leads to this script for review:
ALTER TABLE `mydatabase`.`locations` CHANGE COLUMN `geom` `geom` GEOMETRY NOT NULL SRID 4326 DEFAULT ST_SRID(POINT(0,0),4326);
If you try to run that as-is, you’ll see the following error:
[box title=”Gotcha #2″ style=”default” box_color=”orangered” title_color=”#FFFFFF” radius=”3″]The default value that Workbench generates needs to be wrapped in brackets “(“,”)”.[/box]ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ST_SRID(POINT(0,0),4326)’ at line 2
Edit the script to be:
ALTER TABLE `mydatabase`.`locations` CHANGE COLUMN `geom` `geom` GEOMETRY NOT NULL DEFAULT (ST_SRID(POINT(0,0),4326));
This also generates an error:
ERROR 1138: Invalid use of NULL value
That’s weird, you think. I’ll remove the NOT NULL and add it separately.
ALTER TABLE `mydatabase`.`locations` CHANGE COLUMN `geom` `geom` GEOMETRY NULL DEFAULT (ST_SRID(POINT(0,0),4326));
Yay, you think. We now have a default value (of POINT(0,0)) and can apply the NOT NULL requirement.
ALTER TABLE `mydatabase`.`locations` CHANGE COLUMN `geom` `geom` GEOMETRY NOT NULL DEFAULT (ST_SRID(POINT(0,0),4326));
But this too generates an error:
ERROR 1138: Invalid use of NULL value
What is going on? There are no NULLS in the database, and the script looks correct.
If you run the INFORMATION_SCHEMA query again, you will see that there are now no tables with an SRID attribute.
[box title=”Gotcha #3″ style=”default” box_color=”orangered” title_color=”#FFFFFF” radius=”3″]MySQL deletes the SRID attribute when changing a column definition.[/box]Investigations found that you MUST restate the full column definition, otherwise undeclared attributes will go back to default settings. For example, not restating the DEFAULT clause will unset the default value. And not restating the SRID 4326 attribute will also delete it.
Fair enough. Let’s include that SRID attribute and run it again.
ALTER TABLE `mydatabase`.`locations` CHANGE COLUMN `geom` `geom` GEOMETRY SRID 4326 NOT NULL DEFAULT (ST_SRID(POINT(0,0),4326));
But alas, this won’t even be able to be executed. Are we having fun yet?
The next trick is to look at the CREATE syntax for the table locations, which reveals the following:
... `geom` geometry /*!80003 SRID 4326 */ DEFAULT (st_srid(point(0,0),4326)), ...
Whoah – what is all that extra syntax around the SRID?
[box title=”Gotcha #4″ style=”default” box_color=”orangered” title_color=”#FFFFFF” radius=”3″]To specify the SRID attribute, wrap it in /*!80003 and */.[/box]Here are the final SQL scripts that we need to add a new spatial column to MySQL before we can add a spatial index:
ALTER TABLE `mydatabase`.`locations` ADD COLUMN `geom` GEOMETRY SRID 4326 NOT NULL DEFAULT (st_srid(point(0,0),4326)) AFTER `latitude`;
and
ALTER TABLE `mydatabase`.`locations` CHANGE COLUMN `geom` `geom` GEOMETRY /*!80003 SRID 4326 */ NOT NULL DEFAULT (ST_SRID(POINT(0,0),4326));
I wasn’t able to run it as one query and at this point, I’m too frustrated to try.
One more check of INFORMATION_SCHEMA should show one row. Finally getting somewhere.
Add Spatial Index
ALTER TABLE locations ADD SPATIAL INDEX(geom);
The script runs and a quick check of the database looks promising.
select id, name, longitude, latitude, ST_AsText(geom), geom from locations where geom is not null limit 10;
Result:
+----+----------------------------------+------------+------------+------------------------------+------------------------------------------------------+ | id | name | longitude | latitude | ST_AsText(geom) | geom | +----+----------------------------------+------------+------------+------------------------------+------------------------------------------------------+ | 2 | Andrews Arcade | 149.140507 | -35.315635 | POINT(-35.315635 149.140507) | 0xE610000001010000007E3B89087FA462408A3C49BA66A841C0 | | 4 | Aranda Shopping Centre | 149.078599 | -35.256382 | POINT(-35.256382 149.078599) | 0xE61000000101000000F2CF0CE283A262403CA41820D1A041C0 | | 5 | Australian Defence Force Academy | 149.164327 | -35.291379 | POINT(-35.291379 149.164327) | 0xE61000000101000000315BB22A42A56240DFDE35E84BA541C0 | | 6 | Australian Institute of Sport | 149.103103 | -35.245551 | POINT(-35.245551 149.103103) | 0xE61000000101000000D3A3A99E4CA36240034015376E9F41C0 | | 7 | Australian National University | 149.120682 | -35.277761 | POINT(-35.277761 149.120682) | 0xE61000000101000000E8667FA0DCA36240588D25AC8DA341C0 | | 9 | Bailey's Corner | 149.130848 | -35.279591 | POINT(-35.279591 149.130848) | 0xE61000000101000000E71725E82FA46240C3A04CA3C9A341C0 | | 15 | Brassey Hotel | 149.135194 | -35.308597 | POINT(-35.308597 149.135194) | 0xE61000000101000000B2135E8253A462406552431B80A741C0 | | 16 | Domayne - Auburn | 151.041257 | -33.843607 | POINT(-33.843607 151.041257) | 0xE61000000101000000653733FA51E162409DD66D50FBEB40C0 | | 17 | Domayne - Campbelltown | 150.822678 | -34.054113 | POINT(-34.054113 150.822678) | 0xE610000001010000007024D06053DA6240ECA4BE2CED0641C0 | | 18 | Domayne - Liverpool | 150.919307 | -33.906700 | POINT(-33.9067 150.919307) | 0xE610000001010000007C7F83F66ADD624040A4DFBE0EF440C0 | +----+----------------------------------+------------+------------+------------------------------+------------------------------------------------------+ 10 rows in set (0.07 sec)
Populating spatial column with existing latitude and longitude
You’ve got this far, and just want to know how to update with existing values. (I added the extra LOCK/UNLOCK to try to get around timeout issues):
LOCK TABLES locations WRITE; UPDATE locations l SET geom = ST_SRID(POINT(l.longitude,l.latitude),4326) where l.id>0; UNLOCK TABLES;
I hope this helps somebody out there.
Recent Comments