MYSQL-how to insert geometry data

Question

I'm trying to insert geometry data using MYSQL, here is a code-example:

CREATE TABLE CARTESIAN
(
ROW_ID INT NOT NULL,
G GEOMETRY,
PRIMARY KEY(ROW_ID)
)

INSERT INTO CARTESIAN
VALUES (0,'POINT(1 1)'), 
       (1,'LINESTRING(2 1, 6 6)'), 
       (2,'POLYGON((0 5, 2 5, 2 7, 0 7, 0 5))')

When I run the INSERT I receive the message "Cannot get geometry object from data you send to the GEOMETRY field". Can you explain me where I'm wrong?

Answer 1

You need to convert the text representations into GEOMETRY before you can insert them using the ST_GeomFromText function. Try this:

CREATE TABLE CARTESIAN
(
ROW_ID INT NOT NULL,
G GEOMETRY,
PRIMARY KEY(ROW_ID)
);
INSERT INTO CARTESIAN
VALUES (0,ST_GeomFromText('POINT(1 1)')), 
       (1,ST_GeomFromText('LINESTRING(2 1, 6 6)')), 
       (2,ST_GeomFromText('POLYGON((0 5, 2 5, 2 7, 0 7, 0 5))'));
SELECT * FROM CARTESIAN

Output:

ROW_ID  G
0       [GEOMETRY - 25 B]
1       [GEOMETRY - 45 B]
2       [GEOMETRY - 97 B]