Skip to main content

TIL how to find latitude and longitude of a geolocation stored in SQL Server

Updated 2 min readMarkdown
Part of seriesToday I Learnt...
TIL how to find latitude and longitude of a geolocation stored in SQL Server

I was recently working with an EF Core entity which contained a C# Point property representing a geolocation. This was stored in a single column in a SQL Server database. (I think this uses NetTopologySuite, judging by the EF Core Spatial Data documentation.)

I wanted to know where this geolocation actually is, and the easiest way I know is to convert it to latitude and longitude then look it up online. That raises the question, how can I get the latitude and longitude from the value in the SQL Server database?

If you look at the value of the geolocation in the database, it looks like this:

0xE6100000010C635A400D847555403847C263E4453440

That, of course, is gibberish to me, but happily there is an easy way to get the latitude and longitude from this value. Today I learnt it's built–in functionality within SQL Server!

SELECT TOP 1
    GeoLocation.Lat  AS Latitude,
    GeoLocation.Long AS Longitude,
    GeoLocation
FROM MyTable;
Latitude Longitude GeoLocation
85.8361848 20.2730162 0xE6100000010C635A400D847555403847C263E4453440

SSMS also has a Spatial Results tab which looks interesting. I'm not sure what to do with it, and I haven't looked into it because I didn't need it for what I was trying to do, but as always I like to know what tools exist that might be useful to me in the future.

As an aside, if you're struggling to find this geolocation on a map, that might be because it's in the Arctic Ocean. In the Northern hemisphere, latitude is a number from 0 (equator) to 90 (North Pole) so a latitude of 85 is pretty far North. This is why I ended up investigating this particular value in the database, because the location which this is intended to represent is, in fact, on land and nowhere near the North Pole.

No comments yet. Be the first to comment.