Convert latitude/longitude fields into Geospatial Points

6
votes

I have a table with with a latitude, longitude and coordinates column. There are over 500,000 rows. The coordinates field is currently empty on every row. What I need to do is convert the latitude and longitude into a Geospatial POINT() and put it in the coordinates column. Is this possible?

I know that POINTs can be inserted like this:

INSERT INTO `myTable` (`coordinates`) VALUES (GeomFromText('POINT(50.2 51.6)'));

I essentially need to do exactly this but by pulling the value from latitude and longitude from each row.

3
mysqlsqlgeospatial
Is the latitude 50.2, and the longitude 51.6? - ganders
@ganders No that's just an example. There are 500,000 different latitude and longitude points as FLOAT(10,6) type - jskidd3
What I'm asking is, does the 50.2 map to the latitude column, and the 51.6 maps to the longitude column? (and there are 500,000 entries, like you said) - ganders
@ganders Yes that's correct (sorry), latitude - longitude - jskidd3
What version of Mysql are you using? - Ray

3 Answers

5
votes

A GeomFromText needs a string, you've got to lot of concatenate 

INSERT INTO myTable (coordinates)  
   SELECT GeomFromText(CONCAT('POINT(',ot.latitude, ' ', ot.longitude,')'))
    FROM otherTable ot;

If it's an update to an existing table with latitude and longitude columns into a new column coordinates do this:

UPDATE  myTable 
 SET coordinates =  GeomFromText(CONCAT('POINT(',latitude, ' ', longitude,')'));
5
votes

This is a older question but I have remarked that all answers are wrong (at least in 2019) latitude and longitude must be opposite as the answers

instead: POINT(latitude,longitude)

Must be: POINT(longitude,latitude)

In Mysql 5.6 and later can use :

Update myTable
Set coordinates = POINT(longitude, latitude);

If we try some like:

SELECT ST_Distance_Sphere(
    POINT(13.1500000,99.9666700), 
    POINT(48.861105, 120.335337)
);

got error:

ERROR 1210 (HY000): Incorrect arguments to st_distance_sphere

Because I have used like in the answers POINT(latitude,longitude)

But if we try POINT(longitude,latitude):

SELECT ST_Distance_Sphere(
    POINT(99.9666700, 13.1500000), 
    POINT(120.335337, 48.861105)
);

The result is:

+--------------------------------------------+
| ST_Distance_Sphere(
        POINT(99.9666700, 13.1500000),
        POINT(120.335337, 48.861105)
    ) |
+--------------------------------------------+
|                                                                                      4389299.754585881 |
+--------------------------------------------+
1 row in set (0.00 sec)
0
votes

Would something like this work?

Update myTable
Set coordinates = GeomFromText('POINT(' + latitude + ' ' + longitude + ')')

This is assuming your coordinates column exists in the same table as your lat and long.

(Keep in mind this is SqlServer/Tsql syntax)...