MSSQLで電子マップタイルに描画する

'POLYGON ((30.322265625 59.955010262062061, 30.322265625 59.949509172252277, 30.333251953125 59.949509172252277, 30.333251953125 59.955010262062061, 30.322265625 59.955010262062061))' 

 tile.GetTileBounds(@level int, @x int, @y int) CREATE FUNCTION [tile].[GetTileBounds] (@level int, @x int, @y int) RETURNS geometry AS BEGIN DECLARE @res GEOMETRY = NULL IF @level IS NOT NULL AND @x IS NOT NULL AND @y IS NOT NULL BEGIN DECLARE @n1 FLOAT = PI() - 2.0 * PI() * @y / POWER(2.0, @level); DECLARE @n2 FLOAT = PI() - 2.0 * PI() * (@y + 1) / POWER(2.0, @level); DECLARE @top FLOAT = (180.0 / PI() * ATAN(0.5 * (EXP(@n1) - EXP(-@n1)))); DECLARE @bottom FLOAT = (180.0 / PI() * ATAN(0.5 * (EXP(@n2) - EXP(-@n2)))); DECLARE @tileWidth FLOAT = 360 / CONVERT(float, POWER(2, @level)) DECLARE @left FLOAT = @tileWidth * @x - 180, @right FLOAT = @tileWidth * (@x + 1) - 180 SET @res = geometry::STPolyFromText('POLYGON ((' + LTRIM(STR(@left, 25, 16)) + ' ' + LTRIM(STR(@top, 25, 16)) + ', ' + LTRIM(STR(@left, 25, 16)) + ' ' + LTRIM(STR(@bottom, 25, 16)) + ', ' + LTRIM(STR(@right, 25, 16)) + ' ' + LTRIM(STR(@bottom, 25, 16)) + ', ' + LTRIM(STR(@right, 25, 16)) + ' ' + LTRIM(STR(@top, 25, 16)) + ', ' + LTRIM(STR(@left, 25, 16)) + ' ' + LTRIM(STR(@top, 25, 16)) + '))', 0) END RETURN @res END 

 CREATE FUNCTION tile.GetXTilePos(@Longitude FLOAT, @Zoom INT) RETURNS INT AS BEGIN DECLARE @D FLOAT,@E FLOAT,@F FLOAT,@G FLOAT, @tileY INT, @tileX INT SET @D = 128 * POWER(2, @Zoom) SET @E = ROUND(@D + @Longitude * 256 / 360 * POWER(2, @Zoom), 0) SET @tileX = Floor(@E / 256); RETURN @tileX END 

 CREATE FUNCTION tile.GetYTilePos(@Latitude FLOAT, @Zoom INT) RETURNS INT AS BEGIN DECLARE @A FLOAT, @B FLOAT, @C FLOAT, @D FLOAT, @E FLOAT, @F FLOAT, @G FLOAT, @tileY INT SET @D = 128 * POWER(2, @Zoom) SET @A = Sin(PI() / 180 * @Latitude) SET @B = -0.9999 SET @C = 0.9999 IF @A < @B SET @A = @B IF @A > @C SET @A = @C SET @F = @A SET @G = Round(@D + 0.5 * Log((1.0 + @F) / (1.0 - @F)) * (-256) * POWER(2, @Zoom) / (2 * PI()),0) SET @tileY = Floor(@G / 256) RETURN @tileY END 

 CREATE FUNCTION tile.fn_FetchGeometryTilesZoomDepth ( @GeoData GEOMETRY, @Zoom INT, @Depth INT) RETURNS @retTiles TABLE ( Zoom INT, TileX INT, TileY INT) AS BEGIN DECLARE @Left FLOAT, @Right FLOAT, @Top FLOAT, @Bottom FLOAT DECLARE @CurrentXTile INT, @CurrentYTile INT, @Quanttiles INT DECLARE @Envelope GEOMETRY, @RightTop GEOMETRY, @LeftBottom GEOMETRY DECLARE @CurTileGeom GEOMETRY, @res GEOMETRY DECLARE @tiletop FLOAT,@tilebottom FLOAT,@tileleft FLOAT, @tileright FLOAT DECLARE @LeftTilePos INT,@RightTilePos INT,@TopTilePos INT DECLARE @BottomTilePos INT SET @envelope = @GeoData.STEnvelope() SET @RightTop = @envelope.STPointN(3) SET @LeftBottom = @envelope.STPointN(1) SET @Right = @RightTop.STX SET @Left = @LeftBottom.STX SET @Top = @RightTop.STY SET @Bottom = @LeftBottom.STY SET @LeftTilePos = tile.GetXTilePos( @Left,@Zoom) SET @RightTilePos = tile.GetXTilePos( @Right,@Zoom) SET @TopTilePos = tile.GetYTilePos( @Top,@Zoom) SET @BottomTilePos = tile.GetYTilePos( @Bottom,@Zoom) SET @CurrentXTile = @LeftTilePos WHILE @CurrentXTile <= @RightTilePos BEGIN SET @currentYTile = @TopTilePos WHILE @CurrentYTile <= @BottomTilePos BEGIN INSERT INTO @retTiles (Zoom, TileX, TileY) SELECT * FROM tile.fn_GetTilesByTileNumZoomDepth ( @GeoData, @Zoom, @CurrentXTile, @CurrentYTile, @Depth ) SET @CurrentYTile = @CurrentYTile + 1 END SET @CurrentXTile =@CurrentXTile + 1 END RETURN END 

 CREATE FUNCTION tile.fn_GetTilesByTileNumZoomDepth ( @GeoData GEOMETRY, @Zoom INT, @tileX INT, @tileY INT, @Depth INT) RETURNS @retTiles TABLE ( Zoom INT, X INT, Y INT) AS BEGIN DECLARE @currentTile TABLE ( Zoom INT, X INT, Y INT) IF GEOGRAPHY::STGeomFromWKB([tile].[GetTileBounds](@Zoom, @tileX, @tileY).STAsBinary(),4326).STIntersects(GEOGRAPHY::STGeomFromWKB(@GeoData.MakeValid().STUnion(@GeoData.STStartPoint()).STAsBinary(),4326)) = 1 BEGIN INSERT INTO @currentTile SELECT @Zoom , @tileX , @tileY INSERT INTO @retTiles SELECT d.zoom, dX, dY FROM @currentTile c CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, cX * 2, cY * 2, @Depth - 1) WHERE @Depth > 0) AS d INSERT INTO @retTiles SELECT d.zoom, dX, dY FROM @currentTile c CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, cX * 2 + 1, cY * 2, @Depth - 1) WHERE @Depth > 0) AS d INSERT INTO @retTiles SELECT d.zoom, dX, dY FROM @currentTile c CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, cX * 2, cY * 2 + 1, @Depth - 1) WHERE @Depth > 0) AS d INSERT INTO @retTiles SELECT d.zoom, dX, dY FROM @currentTile c CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, cX * 2 + 1, cY * 2 + 1, @Depth - 1) WHERE @Depth > 0) AS d INSERT INTO @retTiles SELECT * FROM @currentTile END RETURN END 

 'LINESTRING( 30.381113 59.971474, 31.26002 58.539215, 34.564158 57.591722, 35.915476 56.876838,37.622242 55.773125)' 

 [Microsoft.SqlServer.Server.SqlFunction] public static SqlBinary IconTile(SqlBinary image, SqlInt32 zoom, SqlDouble Lon, SqlDouble Lat, SqlInt32 xTile, SqlInt32 yTile, SqlDouble scale) { SqlBinary result = null; using (Icon2TileRendering paster = new Icon2TileRendering()) { using (MemoryStream ms = new MemoryStream()) { ms.Write(image.Value, 0, image.Length); SetBeginPosition(ms); paster.PasteFromStreamScaledImageToTile((int)zoom, (double)Lon, (double)Lat, (int)xTile, (int)yTile, (double)scale, ms); result = paster.GetBytes(); } } return result; } 

  #region [Pixel Position Calculation] Rectangle GetTargetBound(int zoom, double Lon, double Lat, int xTile, int yTile, int width, int height) { int xPix = _conv.FromLongitudeToXPixel(Lon, zoom); int yPix = _conv.FromLatitudeToYPixel(Lat, zoom); int xPos = xPix - xTile * TILE_SIZE; int yPos = yPix - yTile * TILE_SIZE; int halfWidth = width / 2; int halfHeight = height / 2; return new Rectangle(xPos - halfWidth, yPos - halfHeight, width, height } int GetPixelXOnTile(int zoom, double Lon, int xTile) { return _conv.FromLongitudeToXPixel(Lon, zoom) - xTile * TILE_SIZE; } int GetPixelYOnTile(int zoom, double Lat, int yTile) { return _conv.FromLatitudeToYPixel(Lat, zoom) - yTile * TILE_SIZE; } #endregion [Pixel Position Calculation] 

 /// <summary> ///     /// </summary> /// <param name="zoom"></param> /// <param name="Lon"></param> /// <param name="Lat"></param> /// <param name="xTile"></param> /// <param name="yTile"></param> /// <param name="iconImage"></param> public void PasteImageToTileByLatLon(int zoom, double Lon, double Lat, int xTile, int yTile, Bitmap iconImage) { int width = iconImage.Width; int height = iconImage.Height; CopyRegionIntoImage(iconImage, new Rectangle(0, 0, width, height), GetTargetBound(zoom, Lon, Lat, xTile, yTile, width, height)); } 

 CREATE PROC [tile].[FillObjectTilesIntersection]( @StartZoom INT, @EndZoom INT) AS BEGIN DECLARE @CurrentZoom INT SET @CurrentZoom = @StartZoom WHILE @CurrentZoom <= @EndZoom BEGIN INSERT INTO tile.Tile (X,Y,Data,Zoom) SELECT t.TileX,t.TileY, [tile].[TileAgg] (tile.IconTile(i.Data, @CurrentZoom,o.Longitude,o.Latitude,t.tileX,t.tileY, 1.0) ),@CurrentZoom AS Zoom FROM tile.Shape o INNER JOIN tile.[Image] i ON i.ImageID = o.ImageID CROSS APPLY tile.fn_FetchObjectTiles(tile.GetIconBoundForZoom(o.Longitude, o.Latitude, 64, 64, @CurrentZoom, 0),@CurrentZoom) t WHERE o.TypeID = @TypeID GROUP BY t.TileX,t.TileY SET @CurrentZoom = @CurrentZoom + 1 END END 

 DECLARE @Image VARBINARY(MAX) SELECT TOP (1) @image = ( SELECT * FROM OPENROWSET(BULK N'd:\media\icons\pin_orange.png', SINGLE_BLOB) As tile) SELECT [tile].[SaveToFolderByZoomXY]([tile].[IconTile](@image, 3,30.381113, 59.971474, 4, 2, 1.0), N'D:\Tiles\',3,4,2) SELECT [tile].[SaveToFolderByZoomXY]([tile].[IconTile](@image, 4,30.381113, 59.971474, 9, 4, 1.0), N'D:\Tiles\',4,9,4) 

  'POLYGON ((30.322265625 59.955010262062061, 30.322265625 59.949509172252277, 30.333251953125 59.949509172252277, 30.333251953125 59.955010262062061, 30.322265625 59.955010262062061))' 

 CREATE FUNCTION [tile].[fn_ GetCircleSegment] (@X float, @Y float, @azimuth float, @angle float, @distance float, @step FLOAT) RETURNS geometry WITH EXEC AS CALLER AS BEGIN IF @X IS NULL OR @Y IS NULL OR @azimuth IS NULL OR ISNULL(@angle, 0) = 0 OR ISNULL(@distance, 0) <= 0 RETURN NULL DECLARE @sectorStepAngle FLOAT SET @sectorStepAngle = @step IF ABS(@angle) > 360 SET @angle = 360 DECLARE @pointsStr VARCHAR(MAX) DECLARE @firstPointsStr VARCHAR(MAX) DECLARE @earthRadius FLOAT DECLARE @lat FLOAT DECLARE @lon FLOAT DECLARE @d FLOAT IF ABS(@angle) < 360 SET @pointsStr = LTRIM(STR(@X, 25, 16)) + ' ' + LTRIM(STR(@Y, 25, 16)) ELSE SET @pointsStr = '' SET @earthRadius = 6367 SET @lat = RADIANS(@Y) SET @lon = RADIANS(@X) SET @d = (@distance / 1000) / @earthRadius DECLARE @angleStart FLOAT DECLARE @angleEnd FLOAT SET @angleStart = @azimuth - @angle / 2; SET @angleEnd = @azimuth + @angle / 2; DECLARE @pointsCount INT SET @pointsCount = FLOOR(@angle / @sectorStepAngle) DECLARE @brng FLOAT DECLARE @latRadians FLOAT DECLARE @lngRadians FLOAT DECLARE @ptX FLOAT DECLARE @ptY FLOAT DECLARE @i INT SET @i = 0 DECLARE @addPrefix TINYINT IF ABS(@angle) < 360 SET @addPrefix = 1 ELSE SET @addPrefix = 0 WHILE @i <= @pointsCount BEGIN SET @brng = RADIANS(@angleStart + @i * @sectorStepAngle); SET @latRadians = ASIN(SIN(@lat) * COS(@d) + COS(@lat) * SIN(@d) * COS(@brng)); SET @lngRadians = @lon + ATN2(SIN(@brng) * SIN(@d) * COS(@lat), COS(@d) - SIN(@lat) * SIN(@latRadians)); SET @ptX = 180.0 * @lngRadians / PI(); SET @ptY = 180.0 * @latRadians / PI(); IF @addPrefix = 1 BEGIN SET @pointsStr += ', ' + LTRIM(STR(@ptX, 25, 16)) + ' ' + LTRIM(STR(@ptY, 25, 16)) END ELSE BEGIN SET @pointsStr += LTRIM(STR(@ptX, 25, 16)) + ' ' + LTRIM(STR(@ptY, 25, 16)) SET @addPrefix = 1 END IF @i = 0 SET @firstPointsStr = LTRIM(STR(@ptX, 25, 16)) + ' ' + LTRIM(STR(@ptY, 25, 16)) IF @i = @pointsCount AND (@angleStart + @pointsCount * @sectorStepAngle) < @angleEnd BEGIN SET @brng = RADIANS(@angleEnd); SET @latRadians = ASIN(SIN(@lat) * COS(@d) + COS(@lat) * SIN(@d) * COS(@brng)); SET @lngRadians = @lon + ATN2(SIN(@brng) * SIN(@d) * COS(@lat), COS(@d) - SIN(@lat) * SIN(@latRadians)); SET @ptX = 180.0 * @lngRadians / PI(); SET @ptY = 180.0 * @latRadians / PI(); SET @pointsStr = @pointsStr + ', ' + LTRIM(STR(@ptX, 25, 16)) + ' ' + LTRIM(STR(@ptY, 25, 16)) END SET @i = @i + 1 END IF ABS(@angle) < 360 SET @pointsStr += ', ' + LTRIM(STR(@X, 25, 16)) + ' ' + LTRIM(STR(@Y, 25, 16)) ELSE SET @pointsStr += ', ' + @firstPointsStr RETURN geometry::STPolyFromText('POLYGON ((' + @pointsStr + '))', 0) END GO 

 /// <summary> ///    /// </summary> /// <param name="longitude"></param> /// <param name="latitude"></param> /// <param name="azimuth"></param> /// <param name="angle"></param> /// <param name="radius"></param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static SqlGeometry DrawGeoSpatialSectorVarAngle(SqlDouble longitude, SqlDouble latitude, SqlDouble azimuth, SqlDouble angle, SqlDouble radius, SqlDouble stepAngle) { if (longitude == SqlDouble.Null || latitude == SqlDouble.Null || azimuth == SqlDouble.Null || angle == SqlDouble.Null || radius == SqlDouble.Null || radius == 0 || angle == 0) return SqlGeometry.Parse("GEOMETRYCOLLECTION EMPTY"); SqlGeometryBuilder builder = new SqlGeometryBuilder(); builder.SetSrid(0); builder.BeginGeometry(OpenGisGeometryType.Polygon); double firstPointLon; double firstPointLat; double sectorStepAngle = (double) stepAngle; const double earthRadius = 6367.0; double lat = (double) latitude; double lon = (double) longitude; double azim = (double) azimuth; double ang = (double) angle; double piRad = (Math.PI/180.0); double tLat = piRad*lat; double tLon = piRad*lon; double distkm = ((double) radius/1000)/earthRadius; double angleStart = azim - ang/2; double angleEnd = azim + ang/2; var _angle = Math.Abs(ang); if (_angle > 360.0) { angle = 360.0; } int pointCount = (int) Math.Floor(ang/sectorStepAngle); double brng; double latRadians; double lngRadians; double ptX; double ptY; int i = 0; if (angle < 360.0) { builder.BeginFigure(lon, lat); firstPointLon = lon; firstPointLat = lat; } else { brng = piRad*(angleStart); latRadians = Math.Asin(Math.Sin(tLat)*Math.Cos(distkm) + Math.Cos(tLat)*Math.Sin(distkm)*Math.Cos(brng)); lngRadians = tLon + Math.Atan2(Math.Sin(brng)*Math.Sin(distkm)*Math.Cos(tLat), Math.Cos(distkm) - Math.Sin(tLat)*Math.Sin(latRadians)); ptX = 180.0*lngRadians/Math.PI; ptY = 180.0*latRadians/Math.PI; builder.BeginFigure(ptX, ptY); firstPointLon = ptX; firstPointLat = ptY; } while (i <= pointCount) { brng = piRad*(angleStart + i*sectorStepAngle); latRadians = Math.Asin(Math.Sin(tLat)*Math.Cos(distkm) + Math.Cos(tLat)*Math.Sin(distkm)*Math.Cos(brng)); lngRadians = tLon + Math.Atan2(Math.Sin(brng)*Math.Sin(distkm)*Math.Cos(tLat), Math.Cos(distkm) - Math.Sin(tLat)*Math.Sin(latRadians)); ptX = 180.0*lngRadians/Math.PI; ptY = 180.0*latRadians/Math.PI; builder.AddLine(ptX, ptY); i = i + 1; } if (((angleStart + pointCount * sectorStepAngle) < angleEnd)) { brng = piRad * (angleEnd); latRadians = Math.Asin(Math.Sin(tLat) * Math.Cos(distkm) + Math.Cos(tLat) * Math.Sin(distkm) * Math.Cos(brng)); lngRadians = tLon + Math.Atan2(Math.Sin(brng) * Math.Sin(distkm) * Math.Cos(tLat), Math.Cos(distkm) - Math.Sin(tLat) * Math.Sin(latRadians)); ptX = 180.0 * lngRadians / Math.PI; ptY = 180.0 * latRadians / Math.PI; builder.AddLine(ptX, ptY); } builder.AddLine(firstPointLon, firstPointLat); builder.EndFigure(); builder.EndGeometry(); return builder.ConstructedGeometry; } 

 DECLARE @bbox GEOMETRY DECLARE @octagon GEOMETRY SELECT @bbox = [tile].[GetTileBounds](15,19144,9524), @octagon = [tile].[fn_GetCircleSegment](30.3277587890625, 59.952259717159905,0,360,440,90) 

 'POLYGON ((30.3253442162734 59.949509172234684, 30.3301733618516 59.949509172234684, 30.333251953125 59.9510505967796, 30.333251953125 59.953468509045528, 30.330173073498937 59.955010262085125, 30.325344504626063 59.955010262085125, 30.322265625 59.953468509045528, 30.322265625 59.9510505967796, 30.3253442162734 59.949509172234684))' 

 SELECT [tile].[GetPixelXPosFromLongitude](30.3253442162734,15), [tile].[GetPixelYPosFromLatitude](59.949509172234684,15) , [tile].[GetPixelXPosFromLongitude](30.3301733618516,15), [tile].[GetPixelYPosFromLatitude]( 59.949509172234684,15) , [tile].[GetPixelXPosFromLongitude](30.333251953125,15), [tile].[GetPixelYPosFromLatitude]( 59.9510505967796,15) , [tile].[GetPixelXPosFromLongitude](30.333251953125,15), [tile].[GetPixelYPosFromLatitude]( 59.953468509045528,15) , [tile].[GetPixelXPosFromLongitude](30.330173073498937,15), [tile].[GetPixelYPosFromLatitude]( 59.955010262085125,15) , [tile].[GetPixelXPosFromLongitude](30.325344504626063,15), [tile].[GetPixelYPosFromLatitude]( 59.955010262085125,15) ,[tile].[GetPixelXPosFromLongitude](30.322265625,15), [tile].[GetPixelYPosFromLatitude]( 59.953468509045528,15) , [tile].[GetPixelXPosFromLongitude](30.322265625,15), [tile].[GetPixelYPosFromLatitude]( 59.9510505967796,15) , [tile].[GetPixelXPosFromLongitude](30.3253442162734,15), [tile].[GetPixelYPosFromLatitude]( 59.949509172234684,15) 

 SELECT GEOMETRY::STGeomFromText('LINESTRING(4900936 2438400, 4901048 2438400, 4901120 2438328, 4901120 2438216, 4901048 2438144, 4900936 2438144, 4900864 2438216, 4900864 2438328, 4900936 2438400 )',0) 

 DECLARE @bbox GEOMETRY DECLARE @rhomb GEOMETRY DECLARE @image VARBINARY(MAX) SELECT @bbox = [tile].[GetTileBounds](15,19144,9524), @rhomb = [tile].[fn_GetSector](30.3277587890625, 59.952259717159905,0,360,440,90) SET @image = [tile].[ShapeTile]( @octagon,15,19144,9524,'4400B050','9601B41E',3) SELECT[tile].[SaveToFolderByZoomXY](@image,'d:/tiles',15,19144,9524) SET @image = [tile].[ShapeTile]( @octagon,15,19143,9524,'4400B050','9601B41E',3) SELECT[tile].[SaveToFolderByZoomXY](@image,'d:/tiles',15,19143,9524) SET @image = [tile].[ShapeTile]( @octagon,15,19145,9524,'4400B050','9601B41E',3) SELECT[tile].[SaveToFolderByZoomXY](@image,'d:/tiles',15,19145,9524) SET @image = [tile].[ShapeTile]( @octagon,15,19144,9523,'4400B050','9601B41E',3) SELECT[tile].[SaveToFolderByZoomXY](@image,'d:/tiles',15,19144,9523) SET @image = [tile].[ShapeTile]( @octagon,15,19144,9525,'4400B050','9601B41E',3) SELECT[tile].[SaveToFolderByZoomXY](@image,'d:/tiles',15,19144,9525) 

 /// <summary> ///      /// </summary> /// <param name="shape">       </param> /// <param name="X">X  </param> /// <param name="Y">Y  </param> /// <param name="Zoom">  </param> /// <param name="argbFill">    ARGB</param> /// <param name="argbStroke"> </param> /// <param name="strokeWidth"> </param> public void DrawPartObjectShapeOnTile(SqlGeometry shape, int X, int Y, int Zoom, string argbFill, string argbStroke, int strokeWidth) { PasteShapeOnTile(CreateColor(argbFill), CreateColor(argbStroke), strokeWidth, CutPolygonByZoomedPixelZeroTile(shape, X, Y, Zoom)); } 

 [SqlFunction] public static SqlBinary ShapeTile(SqlGeometry shape, SqlInt32 zoom, SqlInt32 xTile, SqlInt32 yTile, SqlString argbFill,SqlString argbStroke,SqlInt32 strokeWidth) { SqlBinary result = null; using (ShapeToTileRendering paster = new ShapeToTileRendering()) { using (MemoryStream ms = new MemoryStream()) { try { paster.DrawPartObjectShapeOnTile(shape, (int) xTile, (int) yTile, (int) zoom, argbFill.ToString(), argbStroke.ToString(), (int) strokeWidth); result = paster.GetBytes(); } catch (System.Exception ex) { string innerMessage = ex.InnerException.Message; throw new Exception(string.Format("zoom: {1}; X:{2}; Y:{3} {0} , inner: {4}", shape, zoom, xTile,yTile, innerMessage)); } return result; } } } 

 CREATE ASSEMBLY [Microsoft.SqlServer.Types] AUTHORIZATION [dbo] FROM 'd:\SQLCLR\BIN\TileRendering\Microsoft.SqlServer.Types.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY [System.Drawing] AUTHORIZATION [dbo] FROM 'd:\SQLCLR\BIN\TileRendering\ System.Drawing.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY [TileRendering] AUTHORIZATION [dbo] FROM 'd:\SQLCLR\BIN\TileRendering\TileRendering.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY nQuant.Core FROM 'd:\SQLCLR\BIN\TileRendering\ nQuant.Core.dll' WITH PERMISSION_SET = UNSAFE GO CREATE ASSEMBLY SqlBitmapOperation FROM 'd:\SQLCLR\BIN\TileRendering\SqlBitmapOperation.dll' WITH PERMISSION_SET = UNSAFE GO 

 CREATE AGGREGATE [tile].[TileAgg] (@Value [varbinary](max)) RETURNS[varbinary](max) EXTERNAL NAME [SqlBitmapOperation].[TileAgg] GO CREATE AGGREGATE [tile].[IconTileAgg] (@Value [varbinary](max), @PixelX [int], @PixelY [int]) RETURNS[varbinary](max) EXTERNAL NAME [SqlBitmapOperation].[IconTileAgg] GO CREATE FUNCTION [tile].[IconTile](@image [varbinary](max), @zoom [int], @Lon [float], @Lat [float], @xTile [int], @yTile [int], @scale [float]) RETURNS [varbinary](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlBitmapOperation].[BitmapFunctions].[IconTile] GO --ShapeTile(SqlGeometry shape, SqlInt32 zoom, SqlInt32 xTile, SqlInt32 yTile, SqlString argbFill,SqlString argbStroke,SqlInt32 strokeWidth) CREATE FUNCTION [tile].[ShapeTile](@shape GEOMETRY, @zoom [int], @xTile [int], @yTile [int], @argbFill NVARCHAR(10),@argbStroke NVARCHAR(10), @strokeWidth INT) RETURNS [varbinary](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlBitmapOperation].[BitmapFunctions].[ShapeTile] GO --SaveToFolderByZoomXY(SqlBinary image, SqlString rootFolderPath, SqlInt32 Zoom, SqlInt32 X,SqlInt32 Y) CREATE FUNCTION tile.SaveToFolderByZoomXY(@image VARBINARY(MAX),@rootFolderPat NVARCHAR(512) , @Zoom [int], @xTile [int], @yTile [int]) RETURNS BIT WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlBitmapOperation].[BitmapFunctions].[SaveToFolderByZoomXY] GO 

  void PasteShapeOnTile(Color fillcolor,Color strokecolor, int width, List<SqlGeometry> geom) { SqlGeometry shape = geom[0]; int geomnum = (int)shape.STNumGeometries(); SqlGeometry stroke = null; SqlGeometry ring; int intnum; if (geom != null) switch (GetOpenGisGeometryType(shape)) { case OpenGisGeometryType.LineString: case OpenGisGeometryType.MultiLineString: DrawMultiLineStringBordered2(shape, fillcolor, strokecolor, width, 1); break; case OpenGisGeometryType.Polygon: intnum = (int)shape.STNumInteriorRing(); ring = shape.STExteriorRing(); // 1.      FillPolygonOnTile(fillcolor, ring.ToPointsArray()); // 2.    if (geomnum >= 1) stroke = geom[1]; for (int i = 1; i <= intnum; i++) { FillTransparentPolygonOnTile(shape.STInteriorRingN(i).ToPointsArray()); } // 3.   if (geom.Count > 1) { stroke = geom[1]; DrawContourOnTile(stroke, strokecolor, width); } break; case OpenGisGeometryType.MultiPolygon: break; } } 

 CREATE PROC tile.FillShapeTiles @GeoData GEOMETRY, @fillArgb VARCHAR(20),@strokeArgb VARCHAR(20), @FolderPath NVARCHAR(20), @EndZoom INT = 17, @StartZoom INT = 4, @Thickness INT = 2 AS BEGIN IF @EndZoom < @StartZoom OR @GeoData IS NULL RETURN INSERT INTO tile.tile (Zoom, X,Y,Data) SELECT t.Zoom, t.TileX AS X,t.TileY AS Y, tile.ShapeTile(@GeoData, t.Zoom, t.TileX, t.TileY, @fillArgb, @strokeArgb ,@Thickness) AS Data FROM (SELECT * FROM tile.fn_FetchGeometryTilesZoomDepth(@GeoData,@StartZoom, @EndZoom - @StartZoom)) t SELECT tile.SaveToFolderByZoomXY (Data, @FolderPath ,Zoom,X,Y) FROM tile.Tile END 

 //------------------------------------------------------------------------------ // <copyright file="CSSqlAggregate.cs" company="Microsoft"> // Copyright (c) Microsoft Corporation. All rights reserved. // </copyright> //------------------------------------------------------------------------------ using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using TileRendering; using System.IO; using System.Drawing; using System.Drawing.Imaging; [Serializable] [SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = false, IsNullIfEmpty = false, MaxByteSize = -1)] public struct TileAgg : IBinarySerialize { Bitmap _bitmap; ImageFormat _format; Graphics _graphics; ImageCodecInfo _codecInfo; const int TILE_SIZE = 256; Bitmap GetInitialTile() { Bitmap DrawArea = new Bitmap(TILE_SIZE, TILE_SIZE); using (Graphics xGraph = Graphics.FromImage(DrawArea)) { xGraph.FillRectangle(Brushes.Transparent, 0, 0, TILE_SIZE, TILE_SIZE); _graphics = Graphics.FromImage(DrawArea); return DrawArea; } } #region [Aggregate artifacts] public void Init() { _codecInfo = GetEncoderInfo("image/png"); _bitmap = GetInitialTile(); DetectFormat(); } public void Accumulate(SqlBinary Value) { using (MemoryStream ms = new MemoryStream()) { ms.Write(Value.Value, 0, Value.Length); ms.Seek(0, SeekOrigin.Begin); ms.Position = 0; PasteFromStreamImageToTile( ms); } } public void Merge(TileAgg Group) { PasteGroup(Group.Terminate()); } public SqlBinary Terminate() { return GetBytes(); } #endregion [Aggregate artifacts] void PasteFromStreamImageToTile( Stream stream) { using (Bitmap iconImage = new Bitmap(stream, false)) { DetectFormat(); int width = iconImage.Width; int height = iconImage.Height; var area = new Rectangle(0, 0, width, height); CopyRegionIntoImage(iconImage,area, area); } } void CopyRegionIntoImage(Bitmap srcBitmap, Rectangle srcRegion, Rectangle destRegion) { _graphics.DrawImage(srcBitmap, destRegion, srcRegion, GraphicsUnit.Pixel); srcBitmap.Dispose(); } void PasteGroup(SqlBinary Value) { using (MemoryStream ms = new MemoryStream()) { ms.Write(Value.Value, 0, Value.Length); ms.Seek(0, SeekOrigin.Begin); ms.Position = 0; PasteTile(ms); } } void PasteTile(Stream stream) { Rectangle bounds = new Rectangle(0, 0, TILE_SIZE, TILE_SIZE); CopyRegionIntoImage(new Bitmap(stream), bounds, bounds); } byte[] GetBytes() { return _bitmap.ToByteArray(ImageFormat.Png); } #region [IBinarySerialize] public void Read(BinaryReader reader) { _bitmap = new Bitmap(new MemoryStream(reader.ReadBytes((int)reader.BaseStream.Length))); DetectFormat(); } public void Write(BinaryWriter writer) { EncoderParameters encodeParams = new EncoderParameters(1); encodeParams.Param[0] = new EncoderParameter(System.Drawing.Imaging.Encoder.Quality, 100); _bitmap.Save(writer.BaseStream, _codecInfo, encodeParams); } #endregion [IBinarySerialize] /// <summary> ///    /// </summary> void DetectFormat() { _format = _bitmap.GetImageFormat(); } ImageCodecInfo GetEncoderInfo(string mimeType) { //     string lookupKey = mimeType.ToLower(); ImageCodecInfo foundCodec = null; Dictionary<string, ImageCodecInfo> encoders = Encoders(); if (encoders.ContainsKey(lookupKey)) { //    foundCodec = encoders[lookupKey]; } return foundCodec; } private Dictionary<string, ImageCodecInfo> Encoders() { Dictionary<string, ImageCodecInfo> encoders = new Dictionary<string, ImageCodecInfo>(); foreach (ImageCodecInfo codec in ImageCodecInfo.GetImageEncoders()) { encoders.Add(codec.MimeType.ToLower(), codec); } return encoders; } } 

 CREATE PROC tile.FillShapeTilesIntersection( @StartZoom INT, @EndZoom INT) AS BEGIN DECLARE @Shape GEOMETRY DECLARE @CurrentZoom INT DECLARE @ObjectTypeID INT DECLARE @fillArgb NVARCHAR(10), @strokeArgb NVARCHAR(10) IF @ObjectTypeID IS NOT NULL BEGIN SET @CurrentZoom = @StartZoom DECLARE shape_cursor CURSOR FOR SELECT o.Shape, fillARGB, strokeARGB FROM tile.Shape o OPEN shape_cursor FETCH NEXT FROM shape_cursor INTO @Shape, @fillArgb, @strokeArgb WHILE @@FETCH_STATUS = 0 BEGIN SET @CurrentZoom = @StartZoom WHILE @CurrentZoom <= @EndZoom BEGIN INSERT INTO tile.tileOverlap (Zoom, X,Y,Data) SELECT t.Zoom, t.TileX AS X,t.TileY AS Y, tile.ShapeTile(@Shape, t.Zoom, t.TileX, t.TileY, @fillArgb, @strokeArgb ,2) AS Data FROM (SELECT * FROM tile.fn_FetchGeometryTiles(@Shape,@CurrentZoom)) t SET @CurrentZoom = @CurrentZoom + 1 END FETCH NEXT FROM shape_cursor INTO @Shape, @fillArgb, @strokeArgb END CLOSE shape_cursor; DEALLOCATE shape_cursor; DELETE tile.TileOverlap END END