SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Geography datatype problem in updation


Geography datatype problem in updation

Author
Message
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 1330

DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1

UPDATE [dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT('+CAST(@Latitude AS Varchar)+' '+CAST(@Longitude AS Varchar)+' )', 4326)
WHERE LocationID = @ID


SELECT CAST(LocationGeography AS varchar(max)) FROM dbo.LocationCopy
GO


DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1

UPDATE [dbo].[LocationCopy]
SET
LocationGeography = geography:Tongueoint(@Latitude, @Longitude, 4326)
WHERE LocationID = @ID

SELECT CAST(LocationGeography AS varchar(max)) FROM dbo.LocationCopy
GO




After executing the updation with geography::STGeomFromText method
there is no change in the latitude,longitude order in the result
POINT (8.4827 76.9192)

but when i use geography:Tongueoint method there is a change in the latitude,longitude order in the result
POINT (76.9192 8.4827)

Why this happen?I am planning to use this to find nearby places, if the order get change doesn't it affect the distance.

One more question isn't the POINT method is fastest as there is no conversion..
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23880 Visits: 6519
The clue is in the spatial function STGeomFromText requires a text imput.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 1330
dilipd006 (8/14/2012)
[code="sql"]


After executing the updation with geography::STGeomFromText method
there is no change in the latitude,longitude order in the result
POINT (8.4827 76.9192)

but when i use geography:Tongueoint method there is a change in the latitude,longitude order in the result
POINT (76.9192 8.4827)

Why this happen?I am planning to use this to find nearby places, if the order get change doesn't it affect the distance.



The order of latitude and langitude is important as found from this site
http://www.mssqltips.com/sqlservertip/1965/sql-server-geography-data-type/

Finally, and perhaps most important, is the order in which the coordinates are entered in the data type. The functions used for the geography data types view coordinates as {X,Y} coordinates. In order to properly document the locations of the earthquakes I will need to present the data as longitude first, then latitude, which is different from how most people think of earth-related coordinates.


Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16866 Visits: 7413
Read up on the geography:Tongueoint, especially regarding the X (Latitude) and Y (Longitude) coordinates.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 1330
Stewart "Arturius" Campbell (8/14/2012)
Read up on the geography:Tongueoint, especially regarding the X (Latitude) and Y (Longitude) coordinates.


In this article it is written X=latitude, y= longitude..so when updating
i am passing the lattitude and langitude in (X,Y) format.

But when i am retrieving this the order changed
it return as (Y,X) format
Does'nt it should return in the same format as (x,y).?
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16866 Visits: 7413
It seems there is a bit of confusion with this.
Please see page http://msdn.microsoft.com/en-us/library/bb933811(v=sql.105).aspx, Community Content section.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
SQL006
SQL006
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 Visits: 1330
So i can choose any method (Point or STPointFromText ) method for the calculation .
It doesn't affect the result in calculating nearby places. right?
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16866 Visits: 7413
Yes, any one can be used, just bear in mind the location of X & Y between the two options.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search