How to insert point in mySQL table

user_7788402 0
Viewed 5413

I get an mySQL error saying 'ADDRESS_LONGI_LATIT_LOCATION' can not be null. Likely that 'geomfromtext/pointfromtext' is returning a null. Whats wrong here? Any help is appreciated.

INSERT INTO address (ADDRESS_NICKNAME,ADDRESS_LONGI_LATIT_LOCATION) 
VALUES ('Testing',geomfromtext('Point(10.20.45 34.23.79)'))

Another way 

INSERT INTO address (ADDRESS_NICKNAME,ADDRESS_LONGI_LATIT_LOCATION) 
VALUES ('Testing',pointfromtext('10.20.45 34.23.79'))
mysql-spatial point geometry mysql
1 Answers

As per the specification of ST_geomFromText() (geomfromtext() is a deprecated synonym, you should not use it):

If the geometry argument is NULL or not a syntactically well-formed geometry, or if the SRID argument is NULL, the return value is NULL.

Indeed your argument is not syntactically well-formed, as each coordinate contains two dots. You can check this with a simple:

select geomfromtext('Point(10.20.45 34.23.79)')

Result (try online):

(null)

With a single dot, it works:

select geomfromtext('Point(10.2045 34.2379)')

Result (true online)

AAAAAAEBAAAAYhBYObRoJED129eBcx5BQA==

The same applies to ST_PointFromText((), except you still need to use the WKT format so you need to use POINT() as well:

select pointfromtext('point(10.2045 34.2379)')

Result (try online):

AAAAAAEBAAAAYhBYObRoJED129eBcx5BQA==

Basically the two functions are the same, except pointfromtext() enforces a point result.

user_5250360
Related