ST_WITHIN or ST_CONTAINS with POLYGON across IDL or Greenwich
I'm having a question regarding spatial functions with MySQL 8.0.13 server.
I try to determine if a polygon contains a point, using ST_Contains function.
In my tests, both spherical objects (polygon and point) are defined with the SGID 4326 (WGS84 projection).
Northeastern quarter of the earth polygon:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((0 0,0 180,90 180,90 0,0 0))',4326),
ST_GeomFromText( 'POINT(10 10)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in France croosing the Greenwich meridian
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 -2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in Pacific Ocean crossing the International Date Line:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 -179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
World clockwise: SW,SE,NE,NW,SW
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',4326),
ST_GeomFromText( 'POINT(0 0)',4326 )
))
... returns 0. KO, the point is not detected as part of the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
I think POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180)) is not relevant using spatial WGS84 coordinates; all points used into this polygon are north|south poles!
When using a flat projection (SGID 0), it works well.
I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too.
My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes) so I can use a generic SQL query that works on every cases.
Christophe
mysql gis spatial
asked Nov 15 '18 at 16:06
ChrisChris
1
add a comment |
I'm having a question regarding spatial functions with MySQL 8.0.13 server.
I try to determine if a polygon contains a point, using ST_Contains function.
In my tests, both spherical objects (polygon and point) are defined with the SGID 4326 (WGS84 projection).
Northeastern quarter of the earth polygon:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((0 0,0 180,90 180,90 0,0 0))',4326),
ST_GeomFromText( 'POINT(10 10)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in France croosing the Greenwich meridian
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 -2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in Pacific Ocean crossing the International Date Line:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 -179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
World clockwise: SW,SE,NE,NW,SW
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',4326),
ST_GeomFromText( 'POINT(0 0)',4326 )
))
... returns 0. KO, the point is not detected as part of the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
I think POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180)) is not relevant using spatial WGS84 coordinates; all points used into this polygon are north|south poles!
When using a flat projection (SGID 0), it works well.
I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too.
My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes) so I can use a generic SQL query that works on every cases.
Christophe
mysql gis spatial
asked Nov 15 '18 at 16:06
ChrisChris
1
Oups; I forgot to replace the SRID in the last command (using 89.99°). SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',4326), ST_GeomFromText( 'POINT(0 0)',4326) )) ... returns 0. KO, the point is not detected as part of the polygon.
– Chris
Nov 15 '18 at 16:33
"it works well. I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too." "My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes)" For what kind of application do you need this?
– Raymond Nijland
Nov 15 '18 at 16:38
Another test, add points when crossing Equator, Greenwich meridian and the IDL meridian: SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-90 -180,-90 0,-90 180,0 180,90 180,90 0, 90 -180,0 -180,-90 -180))',4326), ST_GeomFromText( 'POINT(1 1)',4326) )) This time, MySQL returns 1. But is it a valid workaround ?
– Chris
Nov 15 '18 at 16:43
@Raymond Nijland A weatherforecast service app. I have setup a list of GRIB models with boundaries defined by polygons in a MySQL table; then I'm looking for all available GRIB models that fit my locations.
– Chris
Nov 15 '18 at 16:46
MySQL 8.0 isn't that old i believe it was released somewhere around April this year. MySQL 8.0 is the first MySQL database which implements Spatial Reference Systems (SRS) calculations it might still contain a "bug or two" for some grids.. I advice you to post this on the MySQL dev forums..
– Raymond Nijland
Nov 15 '18 at 17:07
add a comment |
I'm having a question regarding spatial functions with MySQL 8.0.13 server.
I try to determine if a polygon contains a point, using ST_Contains function.
In my tests, both spherical objects (polygon and point) are defined with the SGID 4326 (WGS84 projection).
Northeastern quarter of the earth polygon:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((0 0,0 180,90 180,90 0,0 0))',4326),
ST_GeomFromText( 'POINT(10 10)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in France croosing the Greenwich meridian
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 -2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in Pacific Ocean crossing the International Date Line:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 -179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
World clockwise: SW,SE,NE,NW,SW
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',4326),
ST_GeomFromText( 'POINT(0 0)',4326 )
))
... returns 0. KO, the point is not detected as part of the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
I think POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180)) is not relevant using spatial WGS84 coordinates; all points used into this polygon are north|south poles!
When using a flat projection (SGID 0), it works well.
I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too.
My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes) so I can use a generic SQL query that works on every cases.
Christophe
mysql gis spatial
asked Nov 15 '18 at 16:06
ChrisChris
1
I'm having a question regarding spatial functions with MySQL 8.0.13 server.
I try to determine if a polygon contains a point, using ST_Contains function.
In my tests, both spherical objects (polygon and point) are defined with the SGID 4326 (WGS84 projection).
Northeastern quarter of the earth polygon:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((0 0,0 180,90 180,90 0,0 0))',4326),
ST_GeomFromText( 'POINT(10 10)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in France croosing the Greenwich meridian
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((41 -6,41 10.5,51.5 10.5,51.5 -6,41 -6))',4326),
ST_GeomFromText( 'POINT(45 -2)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
Polygon in Pacific Ocean crossing the International Date Line:
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-45 100,-45 -100,45 -100, 45 100, -45 100))',4326),
ST_GeomFromText( 'POINT(0 -179)',4326 )
))
... returns 1: OK, the point is contained by the polygon.
World clockwise: SW,SE,NE,NW,SW
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',4326),
ST_GeomFromText( 'POINT(0 0)',4326 )
))
... returns 0. KO, the point is not detected as part of the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
SELECT (ST_Contains(
ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',0),
ST_GeomFromText( 'POINT(0 0)',0)
))
... returns 1: OK, the point is contained by the polygon.
I think POLYGON((-90 -180,-90 180,90 180,90 -180,-90 -180)) is not relevant using spatial WGS84 coordinates; all points used into this polygon are north|south poles!
When using a flat projection (SGID 0), it works well.
I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too.
My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes) so I can use a generic SQL query that works on every cases.
Christophe
mysql gis spatial
mysql gis spatial
asked Nov 15 '18 at 16:06
ChrisChris
1
asked Nov 15 '18 at 16:06
ChrisChris
1
asked Nov 15 '18 at 16:06
ChrisChris
1
asked Nov 15 '18 at 16:06
ChrisChris
1
asked Nov 15 '18 at 16:06
ChrisChris
1
1
Oups; I forgot to replace the SRID in the last command (using 89.99°). SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',4326), ST_GeomFromText( 'POINT(0 0)',4326) )) ... returns 0. KO, the point is not detected as part of the polygon.
– Chris
Nov 15 '18 at 16:33
"it works well. I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too." "My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes)" For what kind of application do you need this?
– Raymond Nijland
Nov 15 '18 at 16:38
Another test, add points when crossing Equator, Greenwich meridian and the IDL meridian: SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-90 -180,-90 0,-90 180,0 180,90 180,90 0, 90 -180,0 -180,-90 -180))',4326), ST_GeomFromText( 'POINT(1 1)',4326) )) This time, MySQL returns 1. But is it a valid workaround ?
– Chris
Nov 15 '18 at 16:43
@Raymond Nijland A weatherforecast service app. I have setup a list of GRIB models with boundaries defined by polygons in a MySQL table; then I'm looking for all available GRIB models that fit my locations.
– Chris
Nov 15 '18 at 16:46
MySQL 8.0 isn't that old i believe it was released somewhere around April this year. MySQL 8.0 is the first MySQL database which implements Spatial Reference Systems (SRS) calculations it might still contain a "bug or two" for some grids.. I advice you to post this on the MySQL dev forums..
– Raymond Nijland
Nov 15 '18 at 17:07
add a comment |
Oups; I forgot to replace the SRID in the last command (using 89.99°). SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',4326), ST_GeomFromText( 'POINT(0 0)',4326) )) ... returns 0. KO, the point is not detected as part of the polygon.
– Chris
Nov 15 '18 at 16:33
"it works well. I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too." "My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes)" For what kind of application do you need this?
– Raymond Nijland
Nov 15 '18 at 16:38
Another test, add points when crossing Equator, Greenwich meridian and the IDL meridian: SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-90 -180,-90 0,-90 180,0 180,90 180,90 0, 90 -180,0 -180,-90 -180))',4326), ST_GeomFromText( 'POINT(1 1)',4326) )) This time, MySQL returns 1. But is it a valid workaround ?
– Chris
Nov 15 '18 at 16:43
@Raymond Nijland A weatherforecast service app. I have setup a list of GRIB models with boundaries defined by polygons in a MySQL table; then I'm looking for all available GRIB models that fit my locations.
– Chris
Nov 15 '18 at 16:46
MySQL 8.0 isn't that old i believe it was released somewhere around April this year. MySQL 8.0 is the first MySQL database which implements Spatial Reference Systems (SRS) calculations it might still contain a "bug or two" for some grids.. I advice you to post this on the MySQL dev forums..
– Raymond Nijland
Nov 15 '18 at 17:07
Oups; I forgot to replace the SRID in the last command (using 89.99°). SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',4326), ST_GeomFromText( 'POINT(0 0)',4326) )) ... returns 0. KO, the point is not detected as part of the polygon.
– Chris
Nov 15 '18 at 16:33
Oups; I forgot to replace the SRID in the last command (using 89.99°). SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',4326), ST_GeomFromText( 'POINT(0 0)',4326) )) ... returns 0. KO, the point is not detected as part of the polygon.
– Chris
Nov 15 '18 at 16:33
"it works well. I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too." "My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes)" For what kind of application do you need this?
– Raymond Nijland
Nov 15 '18 at 16:38
"it works well. I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too." "My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes)" For what kind of application do you need this?
– Raymond Nijland
Nov 15 '18 at 16:38
Another test, add points when crossing Equator, Greenwich meridian and the IDL meridian: SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-90 -180,-90 0,-90 180,0 180,90 180,90 0, 90 -180,0 -180,-90 -180))',4326), ST_GeomFromText( 'POINT(1 1)',4326) )) This time, MySQL returns 1. But is it a valid workaround ?
– Chris
Nov 15 '18 at 16:43
Another test, add points when crossing Equator, Greenwich meridian and the IDL meridian: SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-90 -180,-90 0,-90 180,0 180,90 180,90 0, 90 -180,0 -180,-90 -180))',4326), ST_GeomFromText( 'POINT(1 1)',4326) )) This time, MySQL returns 1. But is it a valid workaround ?
– Chris
Nov 15 '18 at 16:43
@Raymond Nijland A weatherforecast service app. I have setup a list of GRIB models with boundaries defined by polygons in a MySQL table; then I'm looking for all available GRIB models that fit my locations.
– Chris
Nov 15 '18 at 16:46
@Raymond Nijland A weatherforecast service app. I have setup a list of GRIB models with boundaries defined by polygons in a MySQL table; then I'm looking for all available GRIB models that fit my locations.
– Chris
Nov 15 '18 at 16:46
MySQL 8.0 isn't that old i believe it was released somewhere around April this year. MySQL 8.0 is the first MySQL database which implements Spatial Reference Systems (SRS) calculations it might still contain a "bug or two" for some grids.. I advice you to post this on the MySQL dev forums..
– Raymond Nijland
Nov 15 '18 at 17:07
MySQL 8.0 isn't that old i believe it was released somewhere around April this year. MySQL 8.0 is the first MySQL database which implements Spatial Reference Systems (SRS) calculations it might still contain a "bug or two" for some grids.. I advice you to post this on the MySQL dev forums..
– Raymond Nijland
Nov 15 '18 at 17:07
add a comment |
0
active
oldest
votes
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%2f53323439%2fst-within-or-st-contains-with-polygon-across-idl-or-greenwich%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53323439%2fst-within-or-st-contains-with-polygon-across-idl-or-greenwich%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
Oups; I forgot to replace the SRID in the last command (using 89.99°). SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-89.99 -180,-89.99 180,89.99 180,89.99 -180,-89.99 -180))',4326), ST_GeomFromText( 'POINT(0 0)',4326) )) ... returns 0. KO, the point is not detected as part of the polygon.
– Chris
Nov 15 '18 at 16:33
"it works well. I also tried to reduce latitudes to avoid using poles; with 89.99° instead of 90°, it works too." "My question is then, is there a workaround/other solutioin to define a full earth 'polygon' (using 90° latitudes)" For what kind of application do you need this?
– Raymond Nijland
Nov 15 '18 at 16:38
Another test, add points when crossing Equator, Greenwich meridian and the IDL meridian: SELECT (ST_Contains( ST_GeomFromText( 'POLYGON((-90 -180,-90 0,-90 180,0 180,90 180,90 0, 90 -180,0 -180,-90 -180))',4326), ST_GeomFromText( 'POINT(1 1)',4326) )) This time, MySQL returns 1. But is it a valid workaround ?
– Chris
Nov 15 '18 at 16:43
@Raymond Nijland A weatherforecast service app. I have setup a list of GRIB models with boundaries defined by polygons in a MySQL table; then I'm looking for all available GRIB models that fit my locations.
– Chris
Nov 15 '18 at 16:46
MySQL 8.0 isn't that old i believe it was released somewhere around April this year. MySQL 8.0 is the first MySQL database which implements Spatial Reference Systems (SRS) calculations it might still contain a "bug or two" for some grids.. I advice you to post this on the MySQL dev forums..
– Raymond Nijland
Nov 15 '18 at 17:07