What is causing MySQL Error #1416 - Cannot get geometry object from data you send to the GEOMETRY field

I started exploring new data types in MySQL that I've never used on a real project. It seems that is quite convenient to store the coordinates of a location into a Point type column of a table in the database as MySQL supports Spatial data types. After doing something so simple as inserting a new row using the PHPMyAdmin insert form (I wanted to test how it looks like), where it's possible to insert the points of the Point type column with a form that looks like this:

After filling the form and trying to insert the record, this error appears with the following generated query:

INSERT INTO `tableName`
    (`id`, `name`, `coordinates`) 
        VALUES 
    (NULL, 'My First Record', '\'POINT(40.71727401 -74.00898606)\',0')

This query will throw the mentioned error: #1416 - Cannot get geometry object from data you send to the GEOMETRY field. This happens because the query is invalid. To prevent this exception from appearing, you need to fix your query to match the current version of MySQL or MariaDB.

Correcting query syntax

Data in spatial columns need to be stored as spatial data. The values should be stored in internal geometry format, but you can convert them to that format from either the Well-Known Text (WKT) or Well-Known Binary (WKB) format.

In newer versions of MySQL, inserting the query with Point will work:

INSERT INTO `tableName`
    (`id`, `name`, `coordinates`) 
        VALUES 
    (NULL, 'My First Record', POINT(40.71727401 -74.00898606))

As the Point(X,Y) function is basically a constructor for numbers with precision and does not require converting first to text, making it way faster. It's also guaranteed to RETURN A POINT OR FAIL.

However, for older versions of MySQL and MariaDB, you will be forced to use the slower Well-Known text (WKT) constructors. In older versions these functions could be found without the ST_ prefix, so whenever it's available, be sure to use the version with the ST_ prefix.

• ST_GeomFromText(wkt, srid) can return ANY spatial type that is supported by MySQL and can be represented by WKT. This makes it loosely typed if you want to think of it like that.
• ST_PointFromText(wkt, srid) a strongly-typed POINT-constructor from Well-known text.

The following example demonstrates how to insert the same data geometry values into a table by converting WKT values to its internal geometry format (if ST_GeomFromText isn't available, use GeomFromText instead):

INSERT INTO `tableName`
    (`id`, `name`, `coordinates`) 
        VALUES 
    (NULL, 'My First Record', ST_GeomFromText('POINT(40.71727401 -74.00898606)', 0));

Even if you don't use the SRID (Spatial Reference System ID), you can as well insert the point without this value:

INSERT INTO `tableName`
    (`id`, `name`, `coordinates`) 
        VALUES 
    (NULL, 'My First Record', ST_GeomFromText('POINT(40.71727401 -74.00898606)'))

Happy coding ❤️!