MySQL 8 inserting spatial POINT data (gps) using placeholders, not possible?

66 Views Asked by At

I'm trying co insert rows into a table, one column of which is GPS coordinates. Rows will later be selected based on their distance from a given set of coordinates, so I want to use the POINT data type to make use of the inbuilt function to do that.

My program builds up the query string and correct number of '%s' placeholders for the values correctly for all the other data, but when I try to do the same with the spatial data I can't get it to work, giving

Error #1416 - Cannot get geometry object from data you send to the GEOMETRY field". 
Spatial data string is: "ST_GeomFromText('POINT(2 2)', 4326)"

If I put the exact string directly into the query it runs correctly, but if I think put that string into a variable and insert with %s I get the above error.

point2 = "ST_GeomFromText('POINT(2 2)', 4326)"
my_name = "Test1"
point = "POINT(3 3)"

# Placeholder for "name" string, no placeholder for POINT. This works correctly:
mycursor.execute(
    "INSERT INTO person (name, gps) VALUES (%s, ST_GeomFromText('POINT(2 3)', 4326));",
    (my_name,),
)

# Placeholder for POINT string, works correctly:
mycursor.execute(
    "INSERT INTO person (name, gps) VALUES (%s, ST_GeomFromText(%s, 4326));",
    (my_name, point),
)

# This is what I want to do, but won't work:

mycursor.execute(
    "INSERT INTO person (name, gps) VALUES (%s, %s);",
    (my_name3, point2),
)

It seems as soon as I try to use a placeholder for the ST_GeomFromText section, it doesn't work. However I do thet the error message that shows it's trying to interpret the data as spatial, so it is recognising it.

I feel like I must be missing something obvious, but I've spent several hours trying different combinations and can't get it to work.

If anyone is willing to put a second pair of eyes on this I would appreciate it enormously!

Thank you.

EDIT:

I'm using mysql.connector version 8.1.0

From the logs I can see that the working query is submitted as:

INSERT INTO person (name, gps) VALUES ('Test11asd', ST_GeomFromText('POINT(3 3)', 4326))

Whereas the non working query is submitted as:

INSERT INTO person (name, gps) VALUES ('Test3', 'ST_GeomFromText(\'POINT(2 2)\', 4326)')

The difference being that using the placeholder it as added additional quotes around the 'ST_GeomFromText' section. I presume because it is trying to insert a string into the query string. However I can see no way to remove those, as I have to save it as a string in the python variable, ie

point2 = "ST_Geom..." 

I can't do :

point2 = ST_Geom...
0

There are 0 best solutions below