Bdtjtk

I have a data dump (csv) of place names from https://www.ordnancesurvey.co.uk/business-and-government/help-and-support/products/os-open-names.html

I need to import this into mysql however the geometry co-ordinates use BNG (OSGB36). Does Mysql have any functions to convert these co-ordinates to wgs84 lat/long or is there any other sql method to achieve this?

another option is perhaps loading it into postgis - does postgis have any funtion to transform BNG to lat/long? Perhaps I could do that and then export the data once transformed and load it into mysql?

In postgis I could do something as follows (not tested)

select AddGeometryColumn('locations', 'the_geom', 27700, 'POINT', 2);



-- X and Y are the BNG co-ordinates

UPDATE locations SET the_geom = ST_GeomFromText('POINT(' || x || ' ' || y || ')', 27700 );



alter table locations add column lat real;



alter table locations add column long real;



update locations set long=st_x(st_transform(the_geom,4326)), 

                      lat=st_y(st_transform(the_geom,4326));

Is it possible to do these type of function in mysql - basically what are the equivalent functions in mysql. I cant seem to figure the syntax out? The following doesnt work in mysql:

update locations set long=ST_X(ST_Transform(the_geom,4326)), 

                  lat=ST_Y(ST_Transform(the_geom,4326));

I get error function ST_Transform does not exist. I'm using mysql 5.7

* UPDATE *
Sample data:

 NAME1      LOCAL_TYPE  GEOMETRY_X  GEOMETRY_Y  DISTRICT_BOROUGH    REGION      COUNTRY 

 Southport  Town        333510      417225      Sefton              North West  England 

The answer is, if all places, here

Answered by Hartmut Holzgraefe in this comment.

So far the SRID property is just a dummy in MySQL, it is stored as
part of a geometries meta data but all actual calculations ignore it
and calculations are done assuming Euclidean (planar) geometry.

So ST_Transform would not really do anything at this point anyway.

I think the same is still true for MariaDB, at least the knowldege
base page for the SRID() function still says so:

This WorlLog discusses the progress of implementing ST_Transform.

MySQL 8.0 does seem to have it implemented: https://dev.mysql.com/doc/refman/8.0/en/spatial-operator-functions.html#function_st-transform

So, the solution may require upgrading to MySQL 8.0.

The changelog for the very recent 8.0.13 says:

----- 2018-10-22 8.0.13 General Availability -- -- -----

MySQL now implements the
ST_Transform()
spatial function for use in converting geometry values from one
spatial reference system (SRS) to another. Currently, it supports
conversion between geographic SRSs. For details, see Spatial Operator
Functions.