Mysql - Convert Point Geometry in British National Grid (OSGB36) to Latitude/Longitude (wgs84)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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
mysql postgis geocoding latitude-longitude
|
show 1 more comment
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
mysql postgis geocoding latitude-longitude
You are using mysql or postgis ?
– scaisEdge
Jan 4 at 16:25
@scaisEdge I need to use mysql in production
– adam78
Jan 4 at 16:26
in mysql there is a table for spatial system reference .. dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html and you could check for your SR .. (the SR are in EPSG)
– scaisEdge
Jan 4 at 16:32
@scaisEdge sorry but I'm not great with geocoding stuff. How would I use it to achieve the transformation?
– adam78
Jan 4 at 16:39
Does this cover only Britain? Can you provide a short sample of the CSV? (I may know how to transform it, but I can't tell from what you have said so far.)
– Rick James
Jan 4 at 23:37
|
show 1 more comment
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
mysql postgis geocoding latitude-longitude
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
mysql postgis geocoding latitude-longitude
mysql postgis geocoding latitude-longitude
edited Jan 5 at 14:10
adam78
asked Jan 4 at 15:57
adam78adam78
2,87373787
2,87373787
You are using mysql or postgis ?
– scaisEdge
Jan 4 at 16:25
@scaisEdge I need to use mysql in production
– adam78
Jan 4 at 16:26
in mysql there is a table for spatial system reference .. dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html and you could check for your SR .. (the SR are in EPSG)
– scaisEdge
Jan 4 at 16:32
@scaisEdge sorry but I'm not great with geocoding stuff. How would I use it to achieve the transformation?
– adam78
Jan 4 at 16:39
Does this cover only Britain? Can you provide a short sample of the CSV? (I may know how to transform it, but I can't tell from what you have said so far.)
– Rick James
Jan 4 at 23:37
|
show 1 more comment
You are using mysql or postgis ?
– scaisEdge
Jan 4 at 16:25
@scaisEdge I need to use mysql in production
– adam78
Jan 4 at 16:26
in mysql there is a table for spatial system reference .. dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html and you could check for your SR .. (the SR are in EPSG)
– scaisEdge
Jan 4 at 16:32
@scaisEdge sorry but I'm not great with geocoding stuff. How would I use it to achieve the transformation?
– adam78
Jan 4 at 16:39
Does this cover only Britain? Can you provide a short sample of the CSV? (I may know how to transform it, but I can't tell from what you have said so far.)
– Rick James
Jan 4 at 23:37
You are using mysql or postgis ?
– scaisEdge
Jan 4 at 16:25
You are using mysql or postgis ?
– scaisEdge
Jan 4 at 16:25
@scaisEdge I need to use mysql in production
– adam78
Jan 4 at 16:26
@scaisEdge I need to use mysql in production
– adam78
Jan 4 at 16:26
in mysql there is a table for spatial system reference .. dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html and you could check for your SR .. (the SR are in EPSG)
– scaisEdge
Jan 4 at 16:32
in mysql there is a table for spatial system reference .. dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html and you could check for your SR .. (the SR are in EPSG)
– scaisEdge
Jan 4 at 16:32
@scaisEdge sorry but I'm not great with geocoding stuff. How would I use it to achieve the transformation?
– adam78
Jan 4 at 16:39
@scaisEdge sorry but I'm not great with geocoding stuff. How would I use it to achieve the transformation?
– adam78
Jan 4 at 16:39
Does this cover only Britain? Can you provide a short sample of the CSV? (I may know how to transform it, but I can't tell from what you have said so far.)
– Rick James
Jan 4 at 23:37
Does this cover only Britain? Can you provide a short sample of the CSV? (I may know how to transform it, but I can't tell from what you have said so far.)
– Rick James
Jan 4 at 23:37
|
show 1 more comment
1 Answer
1
active
oldest
votes
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.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54042284%2fmysql-convert-point-geometry-in-british-national-grid-osgb36-to-latitude-lon%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
add a comment |
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.
add a comment |
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.
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.
answered Jan 5 at 23:44
Rick JamesRick James
71.3k567106
71.3k567106
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54042284%2fmysql-convert-point-geometry-in-british-national-grid-osgb36-to-latitude-lon%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You are using mysql or postgis ?
– scaisEdge
Jan 4 at 16:25
@scaisEdge I need to use mysql in production
– adam78
Jan 4 at 16:26
in mysql there is a table for spatial system reference .. dev.mysql.com/doc/refman/8.0/en/spatial-reference-systems.html and you could check for your SR .. (the SR are in EPSG)
– scaisEdge
Jan 4 at 16:32
@scaisEdge sorry but I'm not great with geocoding stuff. How would I use it to achieve the transformation?
– adam78
Jan 4 at 16:39
Does this cover only Britain? Can you provide a short sample of the CSV? (I may know how to transform it, but I can't tell from what you have said so far.)
– Rick James
Jan 4 at 23:37