Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 487
Yev Avatar asked Mar 16 '13 18:03

Yev


1 Answers

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

like image 200
phipex Avatar answered Oct 23 '22 15:10

phipex