MySQL different approaches to store coordinates-CodePudding

I have some SQL for MySQL that inserts both a point and a polygon into a database. This code works. However I’m seeing multiple ways to do this online and it’s not clear what is the right approach.

This is my current way: http://sqlfiddle.com/#!9/df0e51/1

CREATE TABLE `Grid` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `point` POINT DEFAULT NULL,
  `polygon` polygon DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

INSERT INTO `Grid`(point, polygon) 
VALUES(POINTFROMTEXT('POINT(49.227239 17.564932)'),POLYGONFROMTEXT('POLYGON((0 5, 2 5, 2 7, 0 7, 0 5))'));

However Ive also seen tutorials using the geometry type with insert statements containing ST_GeomFromText to achieve exactly the same thing.

My question is, is there a right way to do this, or at least, how do I know when to use one or the other?

CodePudding user response：

There are a couple of reasons to prefer ST_GeomFromText:

It is more general than the MySQL POINTFROM/POLYGONFROM functions because it looks at the text definition and determines which kind of geometry it is, making your code potentially cleaner.
Functions with prefix ST_ conform to the behavior defined by Open Geospatial Consortium for geospatial SQL, and so are portable across different database types. The MySQL aliases without ST_ will probably just work on MySQL.