CODEWITHSUNDEEP
mysqlpostgisgeocodinglatitude-longitude
adam78
adam78

Reputation: 10078

Mysql - Convert Point Geometry in British National Grid (OSGB36) to Latitude/Longitude (wgs84)

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

Upvotes: 0

Views: 771

Answers (1)

Rick James
Rick James

Reputation: 142503

The answer is, of 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 knowledge [sic] base page for the SRID() function still says so:

This WorkLog 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.

Upvotes: 2

Related Questions