0

I saw this relevant question : How to convert Geometry type column to degree Minutes Second in SQL Server 2008 R2

But I'm after something else :

How to convert Geography type column to degree Minutes Second ?

The suggested solution for Geometry was: 

DECLARE @geom geometry;

SET @geom = geometry::STGeomFromText('POINT(-83.255 32.567477)', 4326);


SELECT CASE WHEN @geom.STX < 0 then '-' ELSE '' END +
    CAST(FLOOR(ABS(@geom.STX)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STX) * 3600) AS INT) % 60 AS VARCHAR) + '"',
  CASE WHEN @geom.STY < 0 then '-' ELSE '' END +
    CAST(FLOOR(ABS(@geom.STY)) as varchar) + ' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 60) AS INT) % 60 as varchar) + ''' ' +
    CAST(CAST(FLOOR(ABS(@geom.STY) * 3600) AS INT) % 60 AS VARCHAR) + '"'

NB I'm asking it because I know that geometry is more forgiving than geography

Question

How can I convert geography to deg/min/sec ? ,

Is it valid to change decimal to => deg/min/sec for geography , as the same way as for Geometry ? (would it be the same calculation?)

Community
  • 1
  • 1
Royi Namir
  • 144,742
  • 138
  • 468
  • 792
  • Did you think to maybe look at the properties of [`SqlGeometry`](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeometry_properties.aspx) and [`SqlGeography`](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlgeography_properties.aspx). Hmm, okay, yes, as the error says, the geography type has no `STX`/`STY`. But it does have `Lat` and `Long`... – Damien_The_Unbeliever Feb 04 '15 at 11:42
  • @Damien_The_Unbeliever I know this already. Is it valid to change deg/min/sec to decimal for geography the same as for Geometry ? ( geometry is more forgiving than geography...so i'm afraid to lose data) – Royi Namir Feb 04 '15 at 11:44
  • If afraid I don't know what you *mean* when you say "geometry is more forgiving" - do you have some articles/posts which talk about this topic? - since the main focus of your question seems to be "geography is missing the `STX` property, what do I do?" I thought I'd signpost you towards documentation that you should have been able to find about that issue, with a simple search. – Damien_The_Unbeliever Feb 04 '15 at 11:47
  • @Damien_The_Unbeliever http://i.imgur.com/DMI2x5b.png , from [here](http://stackoverflow.com/questions/279983/how-can-i-convert-geometry-data-into-a-geography-data-in-ms-sql-server-2008)... and so I ask - even if I change STX to LAT/LON : "_is it valid to change deg/min/sec to decimal for geography the same way as for Geometry_ " ? – Royi Namir Feb 04 '15 at 11:48
  • I've struggled to work out how to respond to that. You appear to have some strong opinion on something based on someone else's vague assertion in another answer, that in turn has very little to demonstrate an actual reason why one should be considered more "forgiving". That the two types have *different* uses shouldn't be a surprise - if they acted identically, we wouldn't have two types. Do you actually understand the code you've included in your question and understand why it's actually less appropriate for geometry than geography? – Damien_The_Unbeliever Feb 04 '15 at 19:47

1 Answers1

1

The short answer: yes. Both are just different representations of the same thing. One represents the mantissa in decimal and the other in sexagesimal.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68