Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Geography datatype problem in updation Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:12 PM
Points: 186, Visits: 1,017
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::Point(@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::Point 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..
Post #1344583
Posted Tuesday, August 14, 2012 3:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1344585
Posted Tuesday, August 14, 2012 4:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:12 PM
Points: 186, Visits: 1,017
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::Point 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.

Post #1344595
Posted Tuesday, August 14, 2012 4:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 4,158, Visits: 5,555
Read up on the geography::Point, 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”
Post #1344596
Posted Tuesday, August 14, 2012 5:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:12 PM
Points: 186, Visits: 1,017
Stewart "Arturius" Campbell (8/14/2012)
Read up on the geography::Point, 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).?

Post #1344614
Posted Tuesday, August 14, 2012 5:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 4,158, Visits: 5,555
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”
Post #1344622
Posted Wednesday, August 15, 2012 6:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:12 PM
Points: 186, Visits: 1,017
So i can choose any method (Point or STPointFromText ) method for the calculation .
It doesn't affect the result in calculating nearby places. right?
Post #1345236
Posted Wednesday, August 15, 2012 6:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:04 PM
Points: 4,158, Visits: 5,555
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”
Post #1345239
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse