Mixing
STDistance() return weird result
I am finding the distance between two points of geometries and following is my query
DECLARE @g geometry;
SET @g = geometry::STPolyFromText('POLYGON ((5.177074447274207 60.32819571126778,
5.177074447274207 60.32533671620816,
5.172660537064075 60.32533671620816,
5.172660537064075 60.32819571126778,
5.177074447274207 60.32819571126778)
)', 4326).MakeValid();
DECLARE @h geometry;
SET @h = geometry::Point(5.1752474, 60.3290297, 4326)
Select @g.STDistance(@h)
and the following is the result I get
0.000833988732217961
But when I find the distance between points on Google Map and Bing Map, I get 100mtr.
I have also checked the documentation related to SRID and as I use SRID 4326 it uses the meter as the measurement. So, if I consider the return result as the meters then there is a vastly different result.
So is there any issue with the function STDistance or should I consider this as Km instead of meters or something?
sql-server sqlgeometry edited Jan 2 at 8:59
Dale Burrell
3,39052655
asked Jan 2 at 8:47
sandipsandip
858
add a comment |
I am finding the distance between two points of geometries and following is my query
DECLARE @g geometry;
SET @g = geometry::STPolyFromText('POLYGON ((5.177074447274207 60.32819571126778,
5.177074447274207 60.32533671620816,
5.172660537064075 60.32533671620816,
5.172660537064075 60.32819571126778,
5.177074447274207 60.32819571126778)
)', 4326).MakeValid();
DECLARE @h geometry;
SET @h = geometry::Point(5.1752474, 60.3290297, 4326)
Select @g.STDistance(@h)
and the following is the result I get
0.000833988732217961
But when I find the distance between points on Google Map and Bing Map, I get 100mtr.
I have also checked the documentation related to SRID and as I use SRID 4326 it uses the meter as the measurement. So, if I consider the return result as the meters then there is a vastly different result.
So is there any issue with the function STDistance or should I consider this as Km instead of meters or something?
sql-server sqlgeometry edited Jan 2 at 8:59
Dale Burrell
3,39052655
asked Jan 2 at 8:47
sandipsandip
858
add a comment |
I am finding the distance between two points of geometries and following is my query
DECLARE @g geometry;
SET @g = geometry::STPolyFromText('POLYGON ((5.177074447274207 60.32819571126778,
5.177074447274207 60.32533671620816,
5.172660537064075 60.32533671620816,
5.172660537064075 60.32819571126778,
5.177074447274207 60.32819571126778)
)', 4326).MakeValid();
DECLARE @h geometry;
SET @h = geometry::Point(5.1752474, 60.3290297, 4326)
Select @g.STDistance(@h)
and the following is the result I get
0.000833988732217961
But when I find the distance between points on Google Map and Bing Map, I get 100mtr.
I have also checked the documentation related to SRID and as I use SRID 4326 it uses the meter as the measurement. So, if I consider the return result as the meters then there is a vastly different result.
So is there any issue with the function STDistance or should I consider this as Km instead of meters or something?
sql-server sqlgeometry edited Jan 2 at 8:59
Dale Burrell
3,39052655
asked Jan 2 at 8:47
sandipsandip
858
I am finding the distance between two points of geometries and following is my query
DECLARE @g geometry;
SET @g = geometry::STPolyFromText('POLYGON ((5.177074447274207 60.32819571126778,
5.177074447274207 60.32533671620816,
5.172660537064075 60.32533671620816,
5.172660537064075 60.32819571126778,
5.177074447274207 60.32819571126778)
)', 4326).MakeValid();
DECLARE @h geometry;
SET @h = geometry::Point(5.1752474, 60.3290297, 4326)
Select @g.STDistance(@h)
and the following is the result I get
0.000833988732217961
But when I find the distance between points on Google Map and Bing Map, I get 100mtr.
I have also checked the documentation related to SRID and as I use SRID 4326 it uses the meter as the measurement. So, if I consider the return result as the meters then there is a vastly different result.
So is there any issue with the function STDistance or should I consider this as Km instead of meters or something?
sql-server sqlgeometry sql-server sqlgeometry edited Jan 2 at 8:59
Dale Burrell
3,39052655
asked Jan 2 at 8:47
sandipsandip
858
edited Jan 2 at 8:59
Dale Burrell
3,39052655
asked Jan 2 at 8:47
sandipsandip
858
edited Jan 2 at 8:59
Dale Burrell
3,39052655
edited Jan 2 at 8:59
Dale Burrell
3,39052655
edited Jan 2 at 8:59
Dale Burrell
3,39052655
3,39052655
asked Jan 2 at 8:47
sandipsandip
858
asked Jan 2 at 8:47
sandipsandip
858
asked Jan 2 at 8:47
sandipsandip
858
858
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
First: Since Earth is not flat, use geography types instead:
--Closest point from polygon
DECLARE @g geography = geography::Point(60.32819571126778, 5.1752474, 4326)
--Reference point
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9212347595042 [meters] which seems to be correct.
Second: To use polygon you must take care of orientation. There is distinction between 'inside polygon' and 'outside polygon' in geography type. If polygon covers half of Earth - which half should be selected? This is determined by orientation. I switched points #2 and #4. See following example:
DECLARE @g geography = geography::STPolyFromText('POLYGON ((
5.177074447274207 60.32819571126778,
5.172660537064075 60.32819571126778,
5.172660537064075 60.32533671620816,
5.177074447274207 60.32533671620816,
5.177074447274207 60.32819571126778))', 4326);
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9192581745513 [meters] which seems to be correct.
Third: Make sure your latitude and longitude coordinates are not switched: see Point vs STPolyFromText.
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
I think you meantgeographyat the first line.
– EzLo
Jan 2 at 10:49
Yes, corrected, 'weird`...
– Paweł Dyl
Jan 2 at 10:56
1
Well, yes. Point is specified here: docs.microsoft.com/en-us/sql/t-sql/spatial-geography/…. I left POLYGON coordinates unchanged.
– Paweł Dyl
Jan 2 at 14:30
1
As a simple rule:geometrybetter fits flat areas (Cartesian),geographybetter fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declarationPoint ( Lat, Long, SRID ).Latis Latitude,Longis Longtitude.
– Paweł Dyl
Jan 3 at 8:41
1
According to specification: 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list).
– Paweł Dyl
Jan 3 at 10:28
|
show 3 more comments
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%2f54003440%2fstdistance-return-weird-result%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
First: Since Earth is not flat, use geography types instead:
--Closest point from polygon
DECLARE @g geography = geography::Point(60.32819571126778, 5.1752474, 4326)
--Reference point
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9212347595042 [meters] which seems to be correct.
Second: To use polygon you must take care of orientation. There is distinction between 'inside polygon' and 'outside polygon' in geography type. If polygon covers half of Earth - which half should be selected? This is determined by orientation. I switched points #2 and #4. See following example:
DECLARE @g geography = geography::STPolyFromText('POLYGON ((
5.177074447274207 60.32819571126778,
5.172660537064075 60.32819571126778,
5.172660537064075 60.32533671620816,
5.177074447274207 60.32533671620816,
5.177074447274207 60.32819571126778))', 4326);
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9192581745513 [meters] which seems to be correct.
Third: Make sure your latitude and longitude coordinates are not switched: see Point vs STPolyFromText.
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
I think you meantgeographyat the first line.
– EzLo
Jan 2 at 10:49
Yes, corrected, 'weird`...
– Paweł Dyl
Jan 2 at 10:56
1
Well, yes. Point is specified here: docs.microsoft.com/en-us/sql/t-sql/spatial-geography/…. I left POLYGON coordinates unchanged.
– Paweł Dyl
Jan 2 at 14:30
1
As a simple rule:geometrybetter fits flat areas (Cartesian),geographybetter fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declarationPoint ( Lat, Long, SRID ).Latis Latitude,Longis Longtitude.
– Paweł Dyl
Jan 3 at 8:41
1
According to specification: 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list).
– Paweł Dyl
Jan 3 at 10:28
|
show 3 more comments
First: Since Earth is not flat, use geography types instead:
--Closest point from polygon
DECLARE @g geography = geography::Point(60.32819571126778, 5.1752474, 4326)
--Reference point
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9212347595042 [meters] which seems to be correct.
Second: To use polygon you must take care of orientation. There is distinction between 'inside polygon' and 'outside polygon' in geography type. If polygon covers half of Earth - which half should be selected? This is determined by orientation. I switched points #2 and #4. See following example:
DECLARE @g geography = geography::STPolyFromText('POLYGON ((
5.177074447274207 60.32819571126778,
5.172660537064075 60.32819571126778,
5.172660537064075 60.32533671620816,
5.177074447274207 60.32533671620816,
5.177074447274207 60.32819571126778))', 4326);
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9192581745513 [meters] which seems to be correct.
Third: Make sure your latitude and longitude coordinates are not switched: see Point vs STPolyFromText.
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
I think you meantgeographyat the first line.
– EzLo
Jan 2 at 10:49
Yes, corrected, 'weird`...
– Paweł Dyl
Jan 2 at 10:56
1
Well, yes. Point is specified here: docs.microsoft.com/en-us/sql/t-sql/spatial-geography/…. I left POLYGON coordinates unchanged.
– Paweł Dyl
Jan 2 at 14:30
1
As a simple rule:geometrybetter fits flat areas (Cartesian),geographybetter fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declarationPoint ( Lat, Long, SRID ).Latis Latitude,Longis Longtitude.
– Paweł Dyl
Jan 3 at 8:41
1
According to specification: 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list).
– Paweł Dyl
Jan 3 at 10:28
|
show 3 more comments
First: Since Earth is not flat, use geography types instead:
--Closest point from polygon
DECLARE @g geography = geography::Point(60.32819571126778, 5.1752474, 4326)
--Reference point
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9212347595042 [meters] which seems to be correct.
Second: To use polygon you must take care of orientation. There is distinction between 'inside polygon' and 'outside polygon' in geography type. If polygon covers half of Earth - which half should be selected? This is determined by orientation. I switched points #2 and #4. See following example:
DECLARE @g geography = geography::STPolyFromText('POLYGON ((
5.177074447274207 60.32819571126778,
5.172660537064075 60.32819571126778,
5.172660537064075 60.32533671620816,
5.177074447274207 60.32533671620816,
5.177074447274207 60.32819571126778))', 4326);
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9192581745513 [meters] which seems to be correct.
Third: Make sure your latitude and longitude coordinates are not switched: see Point vs STPolyFromText.
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
First: Since Earth is not flat, use geography types instead:
--Closest point from polygon
DECLARE @g geography = geography::Point(60.32819571126778, 5.1752474, 4326)
--Reference point
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9212347595042 [meters] which seems to be correct.
Second: To use polygon you must take care of orientation. There is distinction between 'inside polygon' and 'outside polygon' in geography type. If polygon covers half of Earth - which half should be selected? This is determined by orientation. I switched points #2 and #4. See following example:
DECLARE @g geography = geography::STPolyFromText('POLYGON ((
5.177074447274207 60.32819571126778,
5.172660537064075 60.32819571126778,
5.172660537064075 60.32533671620816,
5.177074447274207 60.32533671620816,
5.177074447274207 60.32819571126778))', 4326);
DECLARE @h geography = geography::Point(60.3290297, 5.1752474, 4326)
SELECT @g.STDistance(@h)
It returns 92,9192581745513 [meters] which seems to be correct.
Third: Make sure your latitude and longitude coordinates are not switched: see Point vs STPolyFromText.
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
edited Jan 2 at 10:56
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
answered Jan 2 at 10:38
Paweł DylPaweł Dyl
7,5171524
7,5171524
I think you meantgeographyat the first line.
– EzLo
Jan 2 at 10:49
Yes, corrected, 'weird`...
– Paweł Dyl
Jan 2 at 10:56
1
Well, yes. Point is specified here: docs.microsoft.com/en-us/sql/t-sql/spatial-geography/…. I left POLYGON coordinates unchanged.
– Paweł Dyl
Jan 2 at 14:30
1
As a simple rule:geometrybetter fits flat areas (Cartesian),geographybetter fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declarationPoint ( Lat, Long, SRID ).Latis Latitude,Longis Longtitude.
– Paweł Dyl
Jan 3 at 8:41
1
According to specification: 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list).
– Paweł Dyl
Jan 3 at 10:28
|
show 3 more comments
I think you meantgeographyat the first line.
– EzLo
Jan 2 at 10:49
Yes, corrected, 'weird`...
– Paweł Dyl
Jan 2 at 10:56
1
Well, yes. Point is specified here: docs.microsoft.com/en-us/sql/t-sql/spatial-geography/…. I left POLYGON coordinates unchanged.
– Paweł Dyl
Jan 2 at 14:30
1
As a simple rule:geometrybetter fits flat areas (Cartesian),geographybetter fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declarationPoint ( Lat, Long, SRID ).Latis Latitude,Longis Longtitude.
– Paweł Dyl
Jan 3 at 8:41
1
According to specification: 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list).
– Paweł Dyl
Jan 3 at 10:28
I think you meant
geography at the first line.– EzLo
Jan 2 at 10:49
I think you meant
geography at the first line.– EzLo
Jan 2 at 10:49
Yes, corrected, 'weird`...
– Paweł Dyl
Jan 2 at 10:56
Yes, corrected, 'weird`...
– Paweł Dyl
Jan 2 at 10:56
1
1
Well, yes. Point is specified here: docs.microsoft.com/en-us/sql/t-sql/spatial-geography/…. I left POLYGON coordinates unchanged.
– Paweł Dyl
Jan 2 at 14:30
Well, yes. Point is specified here: docs.microsoft.com/en-us/sql/t-sql/spatial-geography/…. I left POLYGON coordinates unchanged.
– Paweł Dyl
Jan 2 at 14:30
1
1
As a simple rule:
geometry better fits flat areas (Cartesian), geography better fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declaration Point ( Lat, Long, SRID ). Lat is Latitude, Long is Longtitude.– Paweł Dyl
Jan 3 at 8:41
As a simple rule:
geometry better fits flat areas (Cartesian), geography better fits 'globe' calculations (geodetic). If you click link in my previous comment, you should see following declaration Point ( Lat, Long, SRID ). Lat is Latitude, Long is Longtitude.– Paweł Dyl
Jan 3 at 8:41
1
1
According to specification: 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list).
– Paweł Dyl
Jan 3 at 10:28
According to specification: 'The interior of the polygon in an ellipsoidal system is defined by the left-hand rule'. You can: 1. ask another service to respect rules. 2. If rule is always broken, reverse points. 3. If there is no rule - select points forming minimal area from list a) received from service, b) reversed (one set should likely cover almost whole world, switching #2 and #4 is reversing 5-element list).
– Paweł Dyl
Jan 3 at 10:28
|
show 3 more comments
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%2f54003440%2fstdistance-return-weird-result%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
