3

Are there any built in functions for dealing with geohashes in SQL Server? Either to go to/from polygon representations or to get the center point? I looked through the geography functions here but didn't see anything...

If there aren't built in functions is anyone aware of any definitions for user defined functions that might be floating around out there that do this? I know that the algorithm is publicly available but I'd rather not roll my own SQL implementation if it's already been done.

chaotic3quilibrium
  • 5,661
  • 8
  • 53
  • 86
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • 1
    There aren't any such functions native to sql server. This is the first thing I've read about geohashes but I've done similar things with Lat/Lng coordinates. You can do the same "proximity" thing with Lat/Lng by simply adding & substacting vales to create a range. – Jason A. Long Nov 14 '18 at 22:04

2 Answers2

4

After searching for a SQL Server TSQL implementation of Geohash types and conversion functions, I finally just went and wrote a whole open-source library myself:

I designed it for maximum accuracy, performance, and strong conversion consistency guarantees. It is enabling an average IT data warehouse analyst or report writer to efficiently use, process, and leverage simple GIS spatial proximity models and queries. And it accomplishes all this without requiring said analyst or report writer to engage in the steep learning curve of finding and adopting a full GIS style solution.

It provides two Geohash implementation types; Integer (BIGINT) and String (VARCHAR). It also provides two coordinate types; Longitude+Latitude and DMS. And then it provides the conversions to and from these various types. It also provides conversions to/from the SQL Server spatial geography Point type.

Please see the README.md for detailed information.

chaotic3quilibrium
  • 5,661
  • 8
  • 53
  • 86
2

I created a basic user sclarar function that accomplishes some of this:

https://github.com/Broham/SQL-Server-Geohash

Currently it will just take a geohash and return it's center lat/long but it could easily be modified to return a bounding box, get a geohash for a lat/long or any other number of use cases. Hope this is helpful to someone

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • Tysvm! This was helpful to me in converting a Geohash as a `VARCHAR` into a Long+Lat pair. However, I also needed the reverse function; i.e. converting a Long+Lat pair into a `VARCHAR`. And I also needed a version converting Geohash values to and from `BIGINT`. And then I needed a whole host of other supplementary functions. I wrote that up in the answer I provided. https://stackoverflow.com/a/66511156/501113 – chaotic3quilibrium May 22 '22 at 19:27