Moving lat/lon text columns into a 'point' type column

Question

I've got a table in my MySQL database called house.

Within the house table, there are a couple of text columns called latitude and longitude.

I've added a new column called coords, of type point - http://dev.mysql.com/doc/refman/5.0/en/gis-class-point.html

How would I move the latitude and longitude values into the new coords column?

Roth

This Question and some of the Answers are getting stale -- InnoDB now has SPATIAL and ST_DISTANCE_SPHERE(). – Pinkie

Answer 1

Assuming you want a SPATIAL index on this column:

ALTER TABLE mytable ADD coords Point;

UPDATE  mytable
SET     coords = Point(lon, lat);

ALTER TABLE mytable MODIFY coords POINT NOT NULL;

CREATE SPATIAL INDEX sx_mytable_coords ON mytable(coords);

If you don't, you can omit the last two steps.

Update:

In earlier versions of MySQL, you would need to populate Point columns using WKT:

UPDATE  mytable
SET     coords = GeomFromText(CONCAT('POINT (', lon, ' ', lat, ')'))

Howlett

The second command doesn't seem to move the values into coords. I get this error: Cannot get geometry object from data you send to the GEOMETRY field – Roth

@cannyboy: which version of MySQL are you using? Can lat and lon be NULL? – Howlett

MySQL 5.0.45. the latitude and longitude text columns are not null. – Roth

can't get the update to work either. maybe sqlbuddy isn't showing points properly? i'll have a look in phpmyadmin – Roth

@cannyboy: what do you mean by "can't get the update to work"? Please post the exact error text. – Howlett

@Howlett You have the order wrong. It is POINT(long, lat). See dev.mysql.com/doc/refman/5.7/en/gis-class-point.html. GeomFromText is redundant as well in the recent MySQL versions. – Engross

@GajusKuizinas: It does not really matter, as MySQL does not support geography functions anyway and you'll have to use conversion formulas. – Howlett

@GajusKuizinas - The order actually doesn't matter. Yes, technically longitude is your 'x' coordinate and latitude is your 'y', but as long as you're consistent in terms of which order you use when providing your latitude/longitude values to MySQL you can do so in either order. Given that the convention when working with lat/long pairs is to specify them as (latitude, longitude), I'd argue that Point(lon, lat) is counterintuitive and should therefore be avoided. – Potation

@Potation It does matter, at least from my anecdotal experience. Working with functions such as ST_Contains and ST_Distance produced odd results when I have stored point data as lat, long. – Engross

@GajusKuizinas - Fair enough. I've primarily only used the older MBRContains function, which in my experience works the same regardless of coordinate ordering (so long as it's always consistently one way or the other). Perhaps the newer ones are different? – Potation

@Howlett in the 4th line, where you are altering the table to make the field NOT NULL, you should also write the dataType, which is POINT in this case. So it should be like: ALTER TABLE mytable MODIFY coords POINT NOT NULL; – Kippy

I guess we've come to the point that the order has become important (LNG,, LAT) BUT if you use SRID 4326, then the order is the other way around. Very confusing. dba.stackexchange.com/questions/242001/… – Cave

Answer 2

MySQL Version 5.5.8

My latitude and longitude are of type float. To update existing rows...

UPDATE table_name SET coord = POINT(longitude_field, latitude_field);

Something to consider, if you are collecting data and need to save the latitude and longitude separately, in their respective columns, I suggest adding a trigger to your table

CREATE DEFINER=`username`@`localhost` TRIGGER `table_name`.`create_point_geom` 
BEFORE INSERT ON database_name.table_name FOR EACH ROW
BEGIN
    SET NEW.coord = POINT(NEW.longitude, NEW.latitude);
END;

I collect geo-tagged social media data and I use this method to add geometry to my tables.

Inveigle

Answer 3

Concisely:

UPDATE myTable SET coords = GeometryFromText( CONCAT( 'POINT(', lon, ' ', lat, ')' ) );

Note that answer from Quassnoi is in error since the proper input format is POINT(X Y), or in terms of earth POINT(lon lat).

Note you can show points via the X() and Y() functions like the following example:

SELECT X( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS x, Y( GeometryFromText( CONCAT( 'POINT(', 35, ' ', 60, ')' ) ) ) AS y;

Regale

Are you positive that it is lon-lat.. everything else on the net says the opposite – Electromagnet

@Jonathan It is POINT(x,y). See this proof SELECT X( POINT( 3, 2 ) ) AS x You'll get back 3. – Regale

Answer 4

FINALLY! I was able to fix these errors:

#3037 - Invalid GIS data provided to function st_geometryfromtext.
#1416 - Cannot get geometry object from data you send to the GEOMETRY field

By doing a custom SQL query, where I pointed the lat and long points. In my case, the SQL string that did it was:

UPDATE wp_wpgmza SET latlng = GeometryFromText( CONCAT( 'POINT(', 38.5775167, ' ', -121.4868583, ')' ) ) WHERE id = 63;

Maddie