H3.net: A port of H3.net to SQL Server via C# and SQLClr
This is a port of H3.net to SQL Server via SQL CLR, with most of the polygon functionality based on SqlGeography for native SQL Server Geography use. It supports net481 and uses the latest release of H3.net.
H3 is a geospatial indexing system using a hexagonal grid that can be (approximately) subdivided into finer and finer hexagonal grids, combining the benefits of a hexagonal grid with S2's hierarchical subdivisions.
You will need to build a DLL for SQL Server to import as an assembly, and then run SQL commands to install the functions that you can use.
While I have developed this project on macOS Sonoma and it should build on Windows and Linux, please tell me if you run into problems.
exec sp_configure 'clr enabled', 1
reconfigure
GOdotnet build -c Releasecp bin/Release/net481/H3.net.SqlClr.dll bin/Release/net481/H3.net.SqlClr.pdb bin/Release/net481/Microsoft.SqlServer.Server.dll bin/Release/net481/Microsoft.SqlServer.Types.dll /some-directory/mssql-can-access/use database_name_to_use;
CREATE ASSEMBLY H3NetClr FROM '/path/from/step-3/H3.net.SqlClr.dll' WITH PERMISSION_SET = SAFE; GOCREATE FUNCTION dbo.LatLongToH3Index(@latitude FLOAT, @longitude FLOAT, @resolution INT) RETURNS BIGINT AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].LatLongToH3Index; GO
CREATE FUNCTION dbo.PointToH3Index(@point GEOGRAPHY, @resolution INT) RETURNS BIGINT AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].PointToH3Index; GO
CREATE FUNCTION dbo.H3IndexToPoint(@index BIGINT) RETURNS GEOGRAPHY AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].H3IndexToPoint; GO
CREATE FUNCTION dbo.H3IndexToBoundary(@index BIGINT) RETURNS GEOGRAPHY AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].H3IndexToBoundary; GO
CREATE FUNCTION dbo.H3PolyFill(@polygon GEOGRAPHY, @res INT) RETURNS TABLE(cell_id BIGINT) AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].PolyFill; GO
CREATE FUNCTION dbo.H3PolyFillOverlapping(@polygon GEOGRAPHY, @res INT) RETURNS TABLE(cell_id BIGINT) AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].PolyFillOverlapping; GO
CREATE FUNCTION dbo.H3PolyFillInside(@polygon GEOGRAPHY, @res INT) RETURNS TABLE(cell_id BIGINT) AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].PolyFillInside; GO
CREATE FUNCTION dbo.H3PolyFillCompact(@polygon GEOGRAPHY, @res INT) RETURNS TABLE(cell_id BIGINT) AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].PolyFillCompact; GO
CREATE FUNCTION dbo.H3PolyFillToMultiPolygon(@polygon GEOGRAPHY, @res INT) RETURNS GEOGRAPHY AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].PolyFillToMultiPolygon; GO
CREATE FUNCTION dbo.H3GridDistance(@start BIGINT, @end BIGINT) RETURNS INT AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].H3GridDistance; GO
CREATE FUNCTION dbo.H3IndexRange(@index BIGINT, @k INT) RETURNS TABLE(cell_id BIGINT) AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].H3IndexRange; GO
CREATE FUNCTION dbo.H3IndexRangeDistance(@index BIGINT, @k INT) RETURNS TABLE(cell_id BIGINT, distance INT) AS EXTERNAL NAME H3NetClr.[H3.SqlClrWrapper].H3IndexRangeDistance; GOSELECT dbo.LatLongToH3Index(51.50136599999999, -0.14189, 10);
-- returns BIGINT 621942988316803071 DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(51.50136599999999, -0.14189, 4326);
SELECT dbo.PointToH3Index(@point, 9);
-- returns BIGINT 617439388689498111SELECT dbo.H3IndexToPoint(617439388689498111);
-- returns Geography type, with STAsText() -> POINT (-0.14287445856532638 51.501744706664091)SELECT dbo.H3IndexToBoundary(617439388689498111);
-- returns Geography type, with STAsText() ->
-- POLYGON ((-0.1433086747759113 51.503491954526822, -0.14539999922415278 51.502511203725028, -0.1449657187320165 51.500763934493264, -0.14244027052338126 51.499997436569508, -0.14034904040487972 51.500978166766039, -0.1407831641656424 51.502725415490147, -0.1433086747759113 51.503491954526822))Using H3.net's vertex modes
DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((-122.40898669969356 37.81331899988944, -122.47987669969707 37.81515719990604, -122.52471869969825 37.783587199903444, -122.51234369969448 37.70761319990403, -122.35447369969584 37.719806199904276, -122.38054369969613 37.78663019990699, -122.40898669969356 37.81331899988944))', 4326);
-- resolution 7, VertexTestMode.Center
SELECT cell_id FROM dbo.H3PolyFill(@polygon, 7);
-- resolution 7, VertexTestMode.Any
SELECT cell_id FROM dbo.H3PolyFillOverlapping(@polygon, 7);
-- resolution 7, VertexTestMode.All
SELECT cell_id FROM dbo.H3PolyFillInside(@polygon, 7);
DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((-122.40898669969356 37.81331899988944, -122.47987669969707 37.81515719990604, -122.52471869969825 37.783587199903444, -122.51234369969448 37.70761319990403, -122.35447369969584 37.719806199904276, -122.38054369969613 37.78663019990699, -122.40898669969356 37.81331899988944))', 4326);
SELECT cell_id FROM dbo.H3PolyFillCompact(@polygon, 9); -- smallest hexagon resolution 9 DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((-122.40898669969356 37.81331899988944, -122.47987669969707 37.81515719990604, -122.52471869969825 37.783587199903444, -122.51234369969448 37.70761319990403, -122.35447369969584 37.719806199904276, -122.38054369969613 37.78663019990699, -122.40898669969356 37.81331899988944))', 4326);
SELECT dbo.H3PolyFillToMultiPolygon(@polygon, 7); -- nb! returns scalar value, NOT table-valued
--- STAsText() ->
--- MULTIPOLYGON (((-122.4332650748479 37.749955385153285, -122.42576908738397 37.76173573392154, -122.43458610784572 37.772267349851525, -122.45089906210502 37.77101747326509, -122.45839165007875 37.759237120299929, -122.44957468417971 37.74870664801545, -122.4332650748479 37.749955385153285)), ((-122.45839165007875 37.759237120299929, -122.45089906210502 37.77101747326509, [...] , -122.38563455403627 37.776004200673846)))SELECT dbo.H3GridDistance(608430891680661503,608430896109846527); -- returns 6SELECT cell_id FROM dbo.H3IndexRange(608430891680661503, 6); -- k-ring 6SELECT cell_id, distance FROM dbo.H3IndexRangeDistance(608430891680661503, 6); -- k-ring 6After copying across your DLLs, you can refresh SQL Server's copy by running
ALTER ASSEMBLY H3NetClr FROM '/path/from/step-3/H3.net.SqlClr.dll'; GODROP FUNCTION IF EXISTS dbo.LatLongToH3Index; GO
DROP FUNCTION IF EXISTS dbo.PointToH3Index; GO
DROP FUNCTION IF EXISTS dbo.H3IndexToPoint; GO
DROP FUNCTION IF EXISTS dbo.H3IndexToBoundary; GO
DROP FUNCTION IF EXISTS dbo.H3PolyFill; GO
DROP FUNCTION IF EXISTS dbo.H3PolyFillOverlapping; GO
DROP FUNCTION IF EXISTS dbo.H3PolyFillInside; GO
DROP FUNCTION IF EXISTS dbo.H3PolyFillCompact; GO
DROP FUNCTION IF EXISTS dbo.H3PolyFillToMultiPolygon; GO
DROP FUNCTION IF EXISTS dbo.H3GridDistance; GO
DROP FUNCTION IF EXISTS dbo.H3IndexRange; GO
DROP FUNCTION IF EXISTS dbo.H3IndexRangeDistance; GODROP ASSEMBLY IF EXISTS H3NetClr; GO