Inserting coordinates into MySQL - PolyFromText SQL syntax error / returning null

Question

I'm trying to insert geographic coordinates of a polygon into my MySQL database. I have a field named polygon of type POLYGON, and I've tried running all of these queries but continue to get SQL syntax errors:

SET @g = 'POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327))';
INSERT INTO 'zones' ('polygon') VALUES (PolyFromText(@g));

---

INSERT INTO 'zones' ('polygon') VALUES (PolyFromText('POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327))'));

---

INSERT INTO 'zones' ('polygon') VALUES (PolyFromText('POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327))', 0));

The last query was generated using phpmyadmins own geo spatial tools and return "Column 'polygon' cannot be null". Any help is appreciated!

Winegar

I tried PolygonFromText, PolyFromText and GeomFromText - neither of them work. – Winegar

Answer 1

The reason is because null exiting the last point is not equal to the first point, is a condition to meet the standard WKT of OSGeo, in some implementations this is permissible, but mysql is strict with it, in the SQL Server documentation better explain the conditions.

see this

SELECT  Dimension(GeomFromText('POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327,-74.13591384887695 40.93750722242824)))'));

doc of mysql

http://dev.mysql.com/doc/refman/5.0/en/gis-class-polygon.html

doc of sqlserver

http://msdn.microsoft.com/en-us/library/bb964739(v=sql.105).aspx

Cavour

Answer 2

As of 2020...

Your query should be changed to

INSERT INTO 'zones' ('polygon') VALUES (ST_PolygonFromText('POLYGON((-74.13591384887695 40.93750722242824,-74.13522720336914 40.929726129575016,-74.15102005004883 40.9329683629703,-74.14329528808594 40.94256444133327, -74.13591384887695 40.93750722242824))'));

Points to note:

1. Use the correct polygon conversion function ST_PolygonFromText
2. Correct use of nested parentheses to note inner polygons
3. The last point is equal to the first point - so, the polygon is closed

Destinee

Error Code: 3037. Invalid GIS data provided to function st_polygonfromtext. – Aguascalientes