Longitude and Latitude order with ST_X, and ST_Y on SRID 4326
I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.
Nothing in the docs other than the primitive Point() suggests the input as "x,y" or "long, lat" but if I run the following query:
INSERT INTO organization
(loc, spots, name, phone, website, account_id, category_id)
VALUES
(
ST_PointFromText("POINT(-113.532000 54.733000)", 4326),
10,
"Baptiste",
" ",
null,
null,
null
);
SQL Error (3617): Latitude -113.532000 is out of range in function st_pointfromtext. [It must be within -90 > +90]
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
SELECT
*,
ST_Y(loc) as lat,
ST_X(loc) as lng
FROM
organization;
What am I not understanding about how these functions and coordinates work?
Seems the POINT() actually accepts LAT, LON on input - but when querying one must remember that Y is latitude, and X is longitude???
TIA
mysql spatial mysql-8.0
edited 1 min ago
Evan Carroll
32.2k970219
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.
Nothing in the docs other than the primitive Point() suggests the input as "x,y" or "long, lat" but if I run the following query:
INSERT INTO organization
(loc, spots, name, phone, website, account_id, category_id)
VALUES
(
ST_PointFromText("POINT(-113.532000 54.733000)", 4326),
10,
"Baptiste",
" ",
null,
null,
null
);
SQL Error (3617): Latitude -113.532000 is out of range in function st_pointfromtext. [It must be within -90 > +90]
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
SELECT
*,
ST_Y(loc) as lat,
ST_X(loc) as lng
FROM
organization;
What am I not understanding about how these functions and coordinates work?
Seems the POINT() actually accepts LAT, LON on input - but when querying one must remember that Y is latitude, and X is longitude???
TIA
mysql spatial mysql-8.0
edited 1 min ago
Evan Carroll
32.2k970219
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.
Nothing in the docs other than the primitive Point() suggests the input as "x,y" or "long, lat" but if I run the following query:
INSERT INTO organization
(loc, spots, name, phone, website, account_id, category_id)
VALUES
(
ST_PointFromText("POINT(-113.532000 54.733000)", 4326),
10,
"Baptiste",
" ",
null,
null,
null
);
SQL Error (3617): Latitude -113.532000 is out of range in function st_pointfromtext. [It must be within -90 > +90]
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
SELECT
*,
ST_Y(loc) as lat,
ST_X(loc) as lng
FROM
organization;
What am I not understanding about how these functions and coordinates work?
Seems the POINT() actually accepts LAT, LON on input - but when querying one must remember that Y is latitude, and X is longitude???
TIA
mysql spatial mysql-8.0
edited 1 min ago
Evan Carroll
32.2k970219
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a MySQL 8.0.15 and I am experimenting with the geo-spatial location functions. I have applied SRID 4326 to the "loc" column.
Nothing in the docs other than the primitive Point() suggests the input as "x,y" or "long, lat" but if I run the following query:
INSERT INTO organization
(loc, spots, name, phone, website, account_id, category_id)
VALUES
(
ST_PointFromText("POINT(-113.532000 54.733000)", 4326),
10,
"Baptiste",
" ",
null,
null,
null
);
SQL Error (3617): Latitude -113.532000 is out of range in function st_pointfromtext. [It must be within -90 > +90]
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
SELECT
*,
ST_Y(loc) as lat,
ST_X(loc) as lng
FROM
organization;
What am I not understanding about how these functions and coordinates work?
Seems the POINT() actually accepts LAT, LON on input - but when querying one must remember that Y is latitude, and X is longitude???
TIA
mysql spatial mysql-8.0
mysql spatial mysql-8.0
edited 1 min ago
Evan Carroll
32.2k970219
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 1 min ago
Evan Carroll
32.2k970219
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 1 min ago
Evan Carroll
32.2k970219
edited 1 min ago
Evan Carroll
32.2k970219
edited 1 min ago
Evan Carroll
32.2k970219
32.2k970219
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
asked 8 hours ago
Alex.BarylskiAlex.Barylski
1061
1061
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Alex.Barylski is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says (look . Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing
ST_Longitude()ST_Latitude()
For more information check out Paul Ramsey's blog entry, "Let's Call the Whole Thing Off..."
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
Alex.Barylski is a new contributor. Be nice, and check out our Code of Conduct.
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%2fdba.stackexchange.com%2fquestions%2f229647%2flongitude-and-latitude-order-with-st-x-and-st-y-on-srid-4326%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
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says (look . Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing
ST_Longitude()ST_Latitude()
For more information check out Paul Ramsey's blog entry, "Let's Call the Whole Thing Off..."
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
add a comment |
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says (look . Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing
ST_Longitude()ST_Latitude()
For more information check out Paul Ramsey's blog entry, "Let's Call the Whole Thing Off..."
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
add a comment |
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says (look . Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing
ST_Longitude()ST_Latitude()
For more information check out Paul Ramsey's blog entry, "Let's Call the Whole Thing Off..."
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.
That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says (look . Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing
ST_Longitude()ST_Latitude()
For more information check out Paul Ramsey's blog entry, "Let's Call the Whole Thing Off..."
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
edited 49 secs ago
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
answered 9 mins ago
Evan CarrollEvan Carroll
32.2k970219
32.2k970219
add a comment |
add a comment |
Alex.Barylski is a new contributor. Be nice, and check out our Code of Conduct.
Alex.Barylski is a new contributor. Be nice, and check out our Code of Conduct.
Alex.Barylski is a new contributor. Be nice, and check out our Code of Conduct.
Alex.Barylski is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f229647%2flongitude-and-latitude-order-with-st-x-and-st-y-on-srid-4326%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
