Developers Club geek daily blog

3 years, 1 month ago
I want to tell readers of habra-community as using CLR Microsoft.SqlServer.Types library it is possible to create tayla for the electronic card. In article the speech about generation of the list of cartographic tayl for their further rendering will go. The algorithm of generation of tayl on the geometry of objects which is stored in the MS SQL 2008 database will be described. All process of rendering will be step by step considered on example at the end of article.

We draw on tayla of the electronic card in MSSQL

Contents




Problem
Basic data
Solution
Storage of tayl
Stages of preparation of tayl
The used functions
Example with the broken line
Intersection check
Tables for storage of images of tayl
Placement of icon on the tayl
Association of tayl
Drawing of geometry on the tayl
Conclusion


Problem




When in the browser large number geo data in vector graphics is displayed (by means of SVG or CANVAS), sometimes it is necessary to wait not only so far data will be loaded on the client computer, but also drawing process which can be executed too long will be executed so far.
At display of large number of icons on the card in the browser it is possible to apply clustering, but for difficult geometrical objects it is necessary to use other approach.


Basic data:




The set of geometrical objects is stored in the table of database of Microsoft SQL 2008. Coordinates of nodes is width and longitude (EPSG:4326). The field with geo-data has the GEOMETRY type. Objects have to be displayed on the card in the form of icon, for Point geometry. In the form of the broken line of certain thickness for Polyline geometry. Geometry of Polygon have to be displayed in the form of one or several painted-over polygons with circuit. Tayla there have to correspond Web Mercator projections

Solution:




Instead of vector graphics we will display objects on the card in the form of raster layer, that is the same images (tayla) as well as the card. For this purpose it is necessary to prepare set of cartographic tayl for each scale with the image of objects. For forming of tayl we will use projection of Google Web Mercator, that is conversions of width and longitude to pixels of the card will be executed by means of the google code where the formulas describing Merkator's projection are used:
In more detail about projections it is possible to esteem here.
Starting with the SQL Server version 2008, for work with space data the data type of GEOMETRY and GEOGRAPHY is supported.
The existing cartographic services from Yandex, to Google or OpenStreetMap cartography as provided in the form of PNG of pictures, fixed size, usually 256x256 points. Though now there are services where tayla form by means of such technologies as SVG or CANVAS. Let's consider raster tayla in the PNG format (picture network graphic). The PNG format maintains transparency (it is specified in alpha the channel), thanks to it imposing of tayl at each other without absolute overlapping is possible, at display of several layers.

Storage of tayl




For each scale certain set of tayl is stored. For the scale of 0th level – 1 tayl:
image

For the scale of the 1st level four tayl 2 * 2:
We draw on tayla of the electronic card in MSSQL
for scale n it is stored 2n * 2n tayl. The quantity of tayl with increase in number of scale increases exponential.

Tayla are stored in file system of the Web server and go on the client's machine on http to request, for example:
someurl/layer {Z}/{X}/{ of Y } .png
where Z,X,Y respectively a scale, X position of tayl, Y position of tayl. For example, on the following url it is available tayl with the image of Troitsky Bridge in St. Petersburg:
b.tile.openstreetmap.org/15/19144/9524.png
In this url:
15 – number of scale;
19144– X position of tayl;
9524 – Y position of tayl.
It is natural that in different systems the URL format of request differs. Instead of numbers of tayl and scale, for request of tayl the QUAD key can be used. Files of tayl can be sent to the client by the server directly from file system or by means of http of processors. Let's consider option with X, Y, Z.

Stages of preparation of tayl




  • Forming of the list of tayl on geometry of each object;
  • Generation of tayl for each object;
  • Association of tayl, for receiving unique set;
  • Saving in file system.


The used functions




Implementation of task requires function of forming of geometry of tayl on X, Y positions of tayl and number of scale. The geometry of tayl in our case is the rectangle covering tayl with the coordinates of corners expressed in the width and longitude. Formations of such geometry can be implemented in SQL function or as SQL CLR. The time difference of execution of SQL CLR function and normal SQL function is not noticeable. The SQL CLR code of function is implemented in the class Coords2PixelConversion in the enclosed source codes.
The following geometry is circuit of this tayl, that is passes on its borders. Coordinates of tops is longitude and width here.
Geometry of border of tayl
'POLYGON ((30.322265625 59.955010262062061, 30.322265625 59.949509172252277, 30.333251953125 59.949509172252277, 30.333251953125 59.955010262062061, 30.322265625 59.955010262062061))'


Code of scalar SQL of the tile.GetTileBounds function ()
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


As will use this function it is told further in this article.

Let's consider methods of forming of the list of tayl. For different geometriya, it is possible to pick up different approaches to forming of the list of tayl.

Way 1:

Let's use such circumstance: If at one scale the object is not crossed with tayly, at scale with big number 4 of tayl which block checked tayl, are also not crossed with object. That is upon transition to the following scale we execute check of tayl, only if tayl the previous scale it is crossed with object geometry. Excess checks which are executed in way 2 are so excluded.

Way 2:

Actually this way – the scenario of the worst case. For each scale to determine subset of tayl by each object by the GEOMETRY function:: STEnvelope () and to check intersection of tayl from this subset with object. This way is less effective, especially for objects with big area or length as the bigger quantity of tayl is checked.

Way 3:

For each object to create geometry of taylovy grid, on intersection of grid with geometry of object to receive set of points. For everyone, received point to define two tayl and to add to the final list of tayl. For example, for the difficult geographical line passing through the continent it is possible to find crosspoints with the grid passing on borders of tayl and already to determine tayla for rendering by these points. The grid is created in borders of the rectangular area containing the line and represents set of vertical and horizontal lines. It much more effectively than to check everyone tayl in borders of rectangular area of object.
Let's describe the first way in more detail. For geometriya of objects of the tayl possessing the area set for check of intersection with object it can be limited extreme to tayla of rectangular area (bbox) blocking object.
On object geometry (except POINT geometry type) CLR rectangle the MSSQL GEOMETRY function forms:: STEnvelope (). For objects with POINT geometry as border of bbox the rectangular area blocking object icon on the card is used. The GetImageBound function returning geometry the blocking icon is implemented in the class GoogleProjection. Here methods of conversion of width and longitude to numbers of positions of pixels are implemented. Coordinates of salient points of rectangular area are expressed in the width and longitude. Further we receive subset of the tayl covering the received rectangle. For this purpose we need conversion function of geographical coordinates in number of tayl at the corresponding scale. For receiving we can use X and Y positions of tayl on longitude and width as SQL CLR function which will be given further, and the SQL functions given below:

tile.GetXTilePos((@Longitude FLOAT, @Zoom INT)
tile.GetYTilePos((@Latitude FLOAT, @Zoom INT)


After definition of positions of angular tayl, all tayla which are in rectangular area between the found angular tayla are checked for intersection with object geometry as tile.fn_FetchGeometryTilesZoomDepth ().

SQL Function of receiving the X position of tayl for longitude and number of scale
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



Function of receiving Y positions of tayl for width and number of scale
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


As tile.fn_FetchGeometryTilesZoomDepth () are calculated left upper and right lower tayl the minimum rectangular area covering geometry. Then for definition of intersection of figure with tayly in nested loop we use the tile.fn_GetTilesByTileNumZoomDepth function () for each tayl taking place in this area from left to right and from top to down from the left upper tayl to the right lower. Function returns the list of tayl for which intersection with object geometry has been defined.

Function of receiving set of tayl
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


Let's check intersection of geometry of tayl with object geometry the GEOMETRY function:: STIntersects (). If the geometry of object and geometry of tayl are crossed, we add entry in previously created table tile.TileOverlap and we cause the same function recursively for four tayl of the following scale, covering current, with the @Depth parameter reduced by unit. Check of intersection is implemented as tile.fn_FetchGeometryTilesZoomDepth ().

Obtaining the list of tayl on geometry for the specified tayl
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, d.X, d.Y FROM @currentTile c
	CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, c.X * 2, c.Y * 2, @Depth - 1) WHERE @Depth > 0) AS d
	INSERT INTO @retTiles	SELECT d.zoom, d.X, d.Y FROM @currentTile c
	CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, c.X * 2 + 1, c.Y * 2, @Depth - 1) WHERE @Depth > 0) AS d
	INSERT INTO @retTiles	SELECT d.zoom, d.X, d.Y FROM @currentTile c
	CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, c.X * 2, c.Y * 2 + 1, @Depth - 1) WHERE @Depth > 0) AS d
	INSERT INTO @retTiles	SELECT d.zoom, d.X, d.Y FROM @currentTile c
	CROSS APPLY (SELECT * FROM [tile].[fn_GetTilesForObjectByTileNumZoomDepth]( @GeoData , c.Zoom + 1, c.X * 2 + 1, c.Y * 2 + 1, @Depth - 1) WHERE @Depth > 0) AS d
	INSERT INTO @retTiles SELECT * FROM @currentTile
END
RETURN
END



If it is necessary to create tayla for one object, numbers of tayl can be written at once to the table tile.Tile as the set of tayl will be unique. To be necessary for forming of tayl with which are crossed geometry of several objects association of the tayl created for different objects and which are imposed at each other.
The tile.fn_GetTilesByTileNumZoomDepth function () executes check of intersection of geometry of object from tayla of scales, taking into account the set depth. The @Depth parameter specifies the check depth if for example @Zoom = 2 and @Depth = 1 that is checked tayl scale 2, and in case of availability of intersection 4 tayla of scale 3 will be checked. It is necessary to check these tayla as they block tayl from the previous scale. Check of intersection has to will be executed after type conversion of data of GEOMETRY to GEOGRAPHY, it is important as for GEOGRAPHY data type check is executed taking into account that all coordinates of points of geometry in projection 4326, that is is dealt with geometrical objects on the sphere.

Example with the broken line




Let's say that we want to receive tayla for broken line of Moscow connecting the center of St. Petersburg to the center. Extent is about 800 km. Lomanny will pass through settlements: Novgorod — Vyshny Volochyok – Tver.
Geometiya of the broken line from St. Petersburg to Moscow
'LINESTRING( 30.381113 59.971474, 31.26002 58.539215, 34.564158 57.591722, 35.915476 56.876838,37.622242 55.773125)'


For this geometry with 3 on 17 scale we will receive all of tayl 11076, distribution of quantity of the tayl which are crossed with geometry in terms of the scope of is given in table 1

Table 1 - Distribution of quantity of tayl on scale levels
Scale Quantity of tayl
3 1
4 2
5 3
6 4
7 7
8 12
9 23
10 45
11 88
12 174
13 347
14 692
15 1383
16 2766
17 5529


Tayla received for 3-oho and 4th scale are shown in figure 1:
We draw on tayla of the electronic card in MSSQLWe draw on tayla of the electronic card in MSSQL

Figure 1 – Tayla: 3/4/2 and 4/9/4

For each scale the subset of tayl forms and everyone is checked without exception tayl. At 4 - 5 scale quantity of the tayl getting to the rectangular area received by the GEOMETRY function:: STEnvelope () on geometry of object will be small. In total tayl at the 4th scale 2^4*2^4 = 256. But on 16 and the 17th scale it will be necessary to check one many more tayl. The exception of "excess" checks in the first way will accelerate work. For objects with geometry the point (POINT), both ways will have identical efficiency.


Intersection check




GEOMETRY function:: STIntersects () can not define intersection of geometry of object with geometry of tayl as the STIntersects function () for data type of GEOMETRY works with plane coordinates, and the width and longitude are not Cartesian coordinates. Therefore, for reliable definition of intersection we convert the GEOMETRY type in the GEOGRAPHY type. It is necessary to pay attention that unlike GEOMETRY data type the data type of GEOGRAPHY demands observance of orientation of rings of polygons. Coordinates of external rings have to go counterclockwise. For internal rings (emptiness) of coordinate have to be listed clockwise. To avoid error when forming geography we use GEOMETRY funtion:: MakeValid () and GEOMETRY:: STUnion () for receiving correct sequence of coordinates, at type conversion of GEOMETRY to the GEOGRAPHY type. At creation of geography we specify the SRID parameter = 4326, it means that all operations over coordinates are executed in spherical system.

At this stage when the list of tayl, that is the table with three columns is received: Z, X, Y; further work can be performed by means of the program for redering of mapnik which allows to execute forming of tayl with the image of objects. The organization of access of mapnik to database of Microsoft SQL Server demands certain efforts. Preparation for generation of tayl includes the following steps in mapnik:
• To declare style of objects for rendering;
• To describe data source of geometry of objects;
• To specify the table with the list of tayl for rendering not to generate all tayla in a row.
We will execute generation of tayl in the MS SQL Server database 2008. For this purpose it is necessary to implement some CLR functions for work with the geometry which is stored in database. Basic functions which will be necessary for us are given below:
  • tile.IconTile (),
  • tile.ShapeTile (),
  • tile.TileAgg (),
  • tile.SaveToFolderByZoomXY ()
.

Tables for storage of images of tayl




In figure 2 the structure of tables where the list of tayl for rendering remains is shown. In the field of Data in these tables PNG the picture with the image of the objects getting on tayl will remain. Storage and processing of large number of pictures in the table can affect productivity. For this task more suitable option is forming of images of tayl out of database on the list of tayl created in the table on each object and the subsequent saving in file system.

We draw on tayla of the electronic card in MSSQL
Figure 2 – Tables for storage of the list of tayl

Placement of icon on the tayl




Let's sort algorithms of positioning of icon on the tayl (POINT geometry type).
There is width and longitude of some object, there is list of tayl of the current scale on which the icon is imposed. Forming of the list of tayl has been described earlier. Calculation of position of icon on the tayl consists of the following actions:
1. At first we will transform the width and longitude to absolute pixel coordinates;
2. Then, for each tayl, from the available list, at the current scale we calculate absolute pixel coordinates of the upper left corner. coordinates of the left upper pixel of tayl (pixXtile, pixYtile) we calculate multiplication number x and y of position of tayl by its size, in our case it is 256 pixels;

3. Difference between absolute pixel coordinates of object and absolute pixel coordinates of the upper left corner of tayl are defined in the GetPixelXOnTile function () and GetPixelXOnTile (). This difference is relative pixel coordinates of the center of icon on the tayl;
4. To draw icon on the tayl it is necessary to receive drawing area borders on tayla in pixels in which there will be insert. Relative pixel coordinates of object on the tayl are received on the previous step. Now borders of rectangular area for insert are determined by the size of icon.
5. We execute drawing of icon on the tayl.

The CLR SQL function placing icon on the tayl
[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;
    }


We receive drawing area borders
 #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]


Copying of icon on tayl
/// <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));
}



Association of tayl




On the same tayl icons of several objects can be imposed. To receive tayla with all objects, it is possible to create at first tayla for each object, then to integrate them in one. Such solution can be implemented by means of grouping of lines of the table of database, CLR function the tile.TileAgg unit (), integrating tayla in one is for this purpose created. This solution has one minus as on each object which is stopped with tayly we will store separate record with BINARY the field storing the picture of tayl with izbrazheniye only of this object that demands large amount of memory. More correct solution – to use one copy of tayl, and consistently to display on it everything the icons of objects getting on it. Thus, we spend less memory. In this case there is nothing to gruppiroat. We want to use grouping.
Filling of the table with positions of tayl and images of tayl with the icons drawn on them
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


As source of objects we will use the table tile.Object with coordinates of objects and the identifier of image of the icon which is stored in the table tile.Image in the field of the Binary type.
Script of forming of tayl 3/4/2 and 4/9/4 with positioning of icon on longitude 30.381113 and width 59.971474
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)


Figure 3 – The received tayla with icon
We draw on tayla of the electronic card in MSSQLWe draw on tayla of the electronic card in MSSQL



Drawing of geometry on the tayl




For the broken line (POLYLINE, MULTIPOLYLINE) we integrate geometry of tayl with polyline geometry, thus the part of the broken line which is out of area of tayl is excluded. The algorithm of definition of circuit and the painted-over area, can be applied to the geometriya possessing the area, that is POLYGON, MULTIPOLYGON, GEOMETRYCOLLECTION containing POLYGON or MULTYPOLYGON. The algorithm is implemented in the class ShapeToTileRendering and includes the following stages:
1. Coordinates (width, longitude) of geometry will be transformed to pixel coordinates taking into account scale on formulas of conversion of width, longitude to PSG3857 pixels (Google projection) and is deductible from each coordinate of the received geometry of coordinate of the left upper pixel of target tayl. We receive, so-called geometry (A). These actions are implemented as ConvertToZoomedPixelZeroedByTileGeometry (poly, Z, X, Y)
2. The geometry (B) of tayl in pixel coordinates taking into account scale forms

3. The geometry received by intersection (C) (STIntersection) of pixel geometry of tayl (B) with geometry of object (A) forms
4. The geometry received as a result of intersection (D) circuit of geometry (C) and circuit of geometry of tayl (B) forms, we receive the lines passing on border of tayl and adjoining on the expected painted-over polygon area in tayl. The geometry (E) received as a result of subtraction by means of the .STDifference (other_geometry) function forms

5. The geometry (E) is also circuit for drawing which turns out subtraction from circuit (LINESTRING or MULTILINSTRING) of geometry (C) geometry (D) by means of function
6. The geometry polygon is filled in (C) – the filled-in area is received
7. The geometry (E) - polygon circuit after intersection exception with borders of tayl is drawn
8. We repeat steps with 1 on 7 for all tayl of the current object and we save them in the table tile.TileOverlap
Let's consider the first 3 stages in more detail on the example of tayl of the 15th scale in the X position with number 19144 and Y of position with number 9524. Let's execute above-mentioned operations with the help of T-SQL of scripts. For a start we receive geometry of borders of tayl, I use the following script:
 SELECT [tile].[GetTileBounds](15,19144,9524).ToString()

Have received the following result:
Geometry of border of tayl
 'POLYGON ((30.322265625 59.955010262062061, 30.322265625 59.949509172252277, 30.333251953125 59.949509172252277, 30.333251953125 59.955010262062061, 30.322265625 59.955010262062061))'


Further we will construct geometry in the form of rhombus for example which will be crossed with selected the tayly. Let the centers of geometry and tayl will match. For this purpose we will use function of creation of geographical sector on conditional surface of the earth. Function is implemented, proceeding from assumption that the earth has sphere form with radius of 6367 km. Function parameters are the longitude and width of the center of arc, azimuth (the direction of sector), arc turn corner, radius in meters and corner growth increment in degrees. Than the increment, especially exact arc more points in geometry is less. If to specify zero azimuth, corner of turn of equal 360 degrees and increment of 90 degrees, we will receive our rhombus. In function the cycle where with the help of formulas of spherical trigonometry increments to corner are executed is used and coordinates of points on arc of the circle lying on earth surface are defined. Function returns polygon geometry on the basis of these points:
SELECT [tile].[fn_GetCircleSegment](30.3277587890625, 59.952259717159905,0,360,440,90)


Function of receiving segment of circle on sphere surface
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



As an alternative, the CLR function executing the same calculations is written. It is possible to notice that considerable difference in runtime of these functions it is not observed.
CLR function of forming of segment of circle
/// <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;
}



Let's find intersection of geometry of tayl with geometry of our rhombus (quadrangle)
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)


Here 30.3277587890625, 59.952259717159905 – coordinates of the center of tayl;

Let's receive intersection geometry, for the present with the width and longitude in coordinates:
SELECT @bbox.STIntersection(@octagon)

We receive the following geometry:
Geometry of intersection of object with tayly with degrees in coordinates
'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))'



Let's use conversion functions of width and longitude in pixels to get sequence numbers of pixels on virtual flat cloth of the card:
Conversion of width and longitude to X and Y positions of pixels
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)


The got numbers of positions of pixels for object geometry
Longitude Width X pixel of the 15th scale Y pixel of the 15th scale
30.3253442162734 59.949509172234684 4900936 2438400
30.3301733618516 59.949509172234684 4901048 2438400
30.333251953125 59.9510505967796 4901120 2438328
30.333251953125 59.953468509045528 4901120 2438216
30.330173073498937 59.955010262085125 4901048 2438144
30.325344504626063 59.955010262085125 4900936 2438144
30.322265625 59.953468509045528 4900864 2438216
30.322265625 59.9510505967796 4900864 2438328
30.3253442162734 59.949509172234684 4900936 2438400


On the received pixel coordinates we will create geometry of circuit of intersection of tayl and geometry of object:
Geometry of intersection of object and tayl with pixels in coordinates
SELECT GEOMETRY::STGeomFromText('LINESTRING(4900936	2438400,	4901048	2438400,	4901120	2438328,	4901120	2438216,	4901048	2438144,	4900936	2438144,	4900864	2438216,	4900864	2438328,	4900936	2438400
)',0)



Geometry (C) created on step 3, the area which is subject to filling is selected green in figure 4 below.
We draw on tayla of the electronic card in MSSQL
Figure 4 – the general area of geometry of tayl and geometry of object

The geometry (D) is not especially necessary to us, more we are interested in set of lines of circuit for drawing on the tayl, it is received as it is described in step 5, subtraction from geometry circuit (C) geometry (D) that has turned out it is selected blue in figure 5.
Thus we have received area for filling and set of lines of circuit for this tayl.

The following geometry is geometry (E):
SELECT GEOMETRY::STGeomFromText('MULTILINESTRING((4901048	2438400,	4901120	2438328),(	4901120	2438216,	4901048	2438144),(	4900936	2438144,	4900864	2438216),	(4900864	2438328,	4900936	2438400)
)',0)


We draw on tayla of the electronic card in MSSQL
Figure 5 – Geometry circuit for rendering (it is selected blue)

The script following T-SQL creates images of tayl in the PNG format and saves in file system according to folders corresponding to Z/X/Y tree. In the absence of the folder are created.
Saving of ready tayl in file system
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)


The received PNG files in drawing are lower:
We draw on tayla of the electronic card in MSSQL
We draw on tayla of the electronic card in MSSQLWe draw on tayla of the electronic card in MSSQLWe draw on tayla of the electronic card in MSSQLWe draw on tayla of the electronic card in MSSQL

For creation of one tayl we call the DrawPartObjectShapeOnTile method () the class ShapeToTileRendering:
Drawing of part of geometry on the specified tayl
/// <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));
}


In PasteShapeOnTile metotda () drawing of the list of geometriya on the tayl is implemented.

private 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;
  }
}


Points 3-7, that is receiving the filled area and lines of circuit for the specified tayl, are implemented in the CutPolygonByZoomedPixelZeroTile method () where we use already pixels as coordinates.
/// <summary>
/// Формирует пересечение геометрии и полигона с контуром в виде границе тайла
/// </summary>
/// <param name="poly"></param>
/// <param name="X"></param>
/// <param name="Y"></param>
/// <param name="Z"></param>
/// <returns>Возвращает пересечение геометрии и полигона с контуром в виде границе тайла</returns>
private List<SqlGeometry> CutPolygonByZoomedPixelZeroTile(SqlGeometry poly, int X, int Y, int Z)
{
  return CutZoomedPixelPolygonByZeroTile(_parser.ConvertToZoomedPixelZeroedByTileGeometry(poly,Z,X,Y);
}

In the class GeometryParser methods of conversion of degrees to pixels of the card are implemented and then "zeroing" — obtaining relative pixel coordinates for tayl in the X, Y position is executed. That is pixel coordinates for zero tayl opredeyatsya, for this purpose we subtract pixel coordinates of the left upper pixel of target tayl from each coordinate of geometry:

/// <summary
///  Выполняем преобразование градусов в пиксели и смещаем координаты на нулевой  тайл    
/// </summary>
/// <param name="shape">геометрия с градусами в координатах</param>
/// <param name="zoom">номер уровня масштаба</param>
/// <param name="tileX"> Х позиция тайла</param>
/// <param name="tileY">Y позиция тайла</param>
/// <returns>Возвращает геометрию с пикселями в координатах на нулевом тайле</returns>
public SqlGeometry ConvertToZoomedPixelZeroedByTileGeometry(SqlGeometry shape,int zoom,
         int tileX,int tileY)
{
  return CreateGeometryFromZoomedPixelInfo
          (ConvertToGeometryZoomedPixelsZeroTileShiftedInfo( GetGeometryInfo(shape), zoom, tileX, tileY));
}

/// <summary
///  Выполняем преобразование градусов в пиксели и смещаем координаты на нулевой  тайл    
/// </summary>
/// <param name="info">Информация о экземпляре геометрии</param>
/// <param name="zoom">номер уровня масштаба</param>
/// <param name="x"> Х позиция тайла</param>
/// <param name="y">Y позиция тайла</param>
/// <returns>Возвращает информацию о пиксельной геометрии на нулевом тайле</returns>
private GeometryZoomedPixelsInfo ConvertToGeometryZoomedPixelsZeroTileShiftedInfo
(GeometryInstanceInfo info, int zoom, int x, int y)
{
  int tilezeroshiftX = x*TILE_SIZE;
  int tilezeroshiftY = y*TILE_SIZE;
  var result = new GeometryZoomedPixelsInfo();
  var pixelCoordsListList = new List<List<GeometryPixelCoords>>();
  var geomPixCoordsList = new List<GeometryPixelCoords>();
  var coords = new GeometryPixelCoords {InnerRing = false};
  OpenGisGeometryType type = info.ShapeType;
  result.ShapeType = type;
  switch (type)
  {
    case OpenGisGeometryType.Point:
         PointF[] geopoints = info.Points[0][0].PointList;
         coords.PixelCoordList = new[]
           {new Point{X = _conv.FromLongitudeToXPixel(geopoints[0].X, zoom) - tilezeroshiftX,
                      Y = _conv.FromLatitudeToYPixel(geopoints[0].Y, zoom) - tilezeroshiftY }
           };
         geomPixCoordsList.Add(coords);
         pixelCoordsListList.Add(geomPixCoordsList);
         break;
    case OpenGisGeometryType.LineString:
         coords.PixelCoordList = 
            GetPixelCoordsShifted(
              info.Points[0][0].PointList,
              zoom,
              tilezeroshiftX,
              tilezeroshiftY);
         geomPixCoordsList.Add(coords);
         pixelCoordsListList.Add(geomPixCoordsList);
         break;
    case OpenGisGeometryType.Polygon:
         foreach (var list in info.Points)
           foreach (GeometryPointSequence pointseq in list)
           {
             coords.PixelCoordList = 
               GetPixelCoordsShifted(pointseq.PointList, zoom, tilezeroshiftX, tilezeroshiftY);
               coords.InnerRing = pointseq.InnerRing;
               geomPixCoordsList.Add(coords);
           }
         pixelCoordsListList.Add(geomPixCoordsList);
         break;
    case OpenGisGeometryType.MultiPoint:
    case OpenGisGeometryType.MultiLineString:
    case OpenGisGeometryType.MultiPolygon:
         pixelCoordsListList = 
         GetGeometryPixelCoordsShifted(info.Points, zoom, tilezeroshiftX, tilezeroshiftY);
         break;
    case OpenGisGeometryType.GeometryCollection:
         GeometryInstanceInfo[] geomColl = info.GeometryInstanceInfoCollection;
         int n = info.GeometryInstanceInfoCollection.Length;
         var geomPixZoomInfoCollection = new GeometryZoomedPixelsInfo[n];
         for (int i = 0; i < n; i++)
         {
             var geom = new GeometryZoomedPixelsInfo();
             geom.ShapeType = geomColl[i].ShapeType;
             geom.Points = 
             GetGeometryPixelCoordsShifted(geomColl[i].Points, zoom, tilezeroshiftX, tilezeroshiftY);
             geomPixZoomInfoCollection[i] = geom;
         }
         result.GeometryInstanceInfoCollection = geomPixZoomInfoCollection;
         break;
    }
    if (type != OpenGisGeometryType.GeometryCollection) result.Points = pixelCoordsListList;
  return result;
}

In the class ShapeToTileRendering the CutZoomedPixelPolygonByZeroTile method (is implemented). In method we execute cutting of geometry on border of tayl. Let's pay attention that the geometry with the coordinates counted from the left upper pixel of target tayl is transferred in the poly parameter.

 private List<SqlGeometry> CutZoomedPixelPolygonByZeroTile(SqlGeometry poly, int X, int Y)
        {
            List<SqlGeometry> result = new List<SqlGeometry>();
            SqlGeometry stroke = null;
            SqlGeometry contour;
            SqlGeometry tileLineString;
            SqlGeometry tobecut;
            SqlGeometry tile = _conv.GetTilePixelBound(0, 0, 1);
            var tiled = poly.STIntersection(tile);
            result.Add(tiled);
            switch (GetOpenGisGeometryType(tiled))
            {
                case OpenGisGeometryType.Polygon:
                    // Получаем контур полигона и внутренние кольца в виде MULTILINESTRING
                    contour = PolygonToMultiLineString(tiled);
                    // удаляем линии среза геометрии по границе тайла
                    tileLineString = tile.ToLineString();
                    tobecut = contour.STIntersection(tileLineString);
                    stroke = contour.STDifference(tobecut);
                    break;
                case OpenGisGeometryType.MultiPolygon:
                    // Получаем контур полигона и внутренние кольца в виде MULTILINESTRING
                    contour = MultiPolygonToMultiLineString(tiled);
                    // удаляем линии среза геометрии по границе тайла
                    tileLineString = tile.ToLineString();
                    tobecut = contour.STIntersection(tileLineString);
                    stroke = contour.STDifference(tobecut);
                    break;
            }
            result.Add(stroke);
            return result;
        }


Now it was necessary to automate process of forming of tayl for objects according to the list. The tile.FillShapeTiles procedure creates the list of tayl for rendering, on the geometry transferred in the @GeoData parameter and saves the created tayla in file system on the way specified in the @FolderPath parameter.
In procedure the following CLR functions imported to DB are used:
.
Functions are implemented in the class BitmapFunctions SQL CLR of SqlBitmapOperation library:
The ShapeTile function () returns PNG the picture of tayl with the image of the part of object getting on tayl in the specified position xTile, yTile:

ShapeTile ()
[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;
            }
        }            
    }



The SqlBitmapOperation library uses the methods implemented in TileRendering library.
The .NET TileRendering library uses the following .NET of library:
  • System
  • Microsoft.SqlServer.Types
  • System.Drawing


msdn .microsoft.com/en-us/library/ms345099.aspx
After compilation of SqlBitmapOperation and TileRendering libraries, they need to be imported to database from file system, previously having imported the libraries used by them:
Import of libraries to database from file system
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


SqlBitmapOperation uses nQuant.Core library in the research purposes. This library allows to create picture PNG in format of 8 bits per pixel with use of palette.

As we use the SqlGeometry type and other methods of Microsoft.SqlServer.Types library in our functions, Microsoft.SqlServer.Types has to be also imported to DB.
System.Drawing is wrapper for GDI+ c library uncontrollable code, it is possible therefore it is necessary to use EXTERNAL_ACCESS permission access level for the imported libraries using methods from Sytem.Drawing. At such permission access level for library it is necessary to create asymmetric key, or to allow to import assemblies with access rights of EXTERNAL_ACCESS or UNSAFE having executed the script following T-SQL:
ALTER DATABASE [dataBaseName] SET TRUSTWORTHY ON; 


After import of CLR of assemblies to database, it is possible to declare functions that they could be caused in stored procedures:
Declaration of functions on the party of database
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


The class ShapeToTileRendering is intended for display of geometry on the tayl. Before drawing the geometry from spherical coordinates of projection 4326 will be transformed to pixel coordinates of the corresponding scale. These conversions are implemented in the class GeometryParser which creates geometry already in PSG3857 projection, ready to display on the tayl. The PastShapeOnTile method places the geometry transferred in the geom parameter on the image of tayl. Image size in our case of 256 pixels at all levels of scale.
Placement of the prepared geometry on the tayl
     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;
            }                    
        }


The following stored procedure of tile.FillShapeTiles can be used only for forming of tayl of one object.
Stored procedure of tile.FillShapeTiles
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



If it is some objects, more than 100 000 for example, on one tayl it is necessary to project some objects. That is it will be necessary to impose tayla for different objects at each other. Let's give this task to database server, that is we will use CLR function the unit.

The tile.FillShapeTilesIntersection procedure () uses CLR the tile.ShapeTile function () for forming of image in the .PNG format on half-scientific geometry, coordinates of tayl and style of drawing. Availability of this CLR function in database is supposed. For association of tayl, the CLR tile.TileAgg unit (@Data VARBINARY(MAX)) accepting as parameter binary image of tayl in .PNG format is used, tayla are stored in VABINARY (MAX) fields.
At any CLR function of aggregation there have to be following methods:
  • Init ();
  • Accumulate(value);
  • Merge(Agg);
  • Terminate ()

Implementation of the SQL CLR unit
//------------------------------------------------------------------------------
// <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; 
    }
}


The stored procedure of tile.FillShapeTilesIntersection as basic data uses the tables tile.Shape. In the @StartZoom parameter – the initial scale, @EndZoom — final scale. Fill color and color of circuit is stored in the tile.Shapes.fillArgb and tile.Shapes.strokeArgb fields. We use the following format: AARRGGBB,
where AA – alpha the channel (transparency), RR – red component, GG — green kompolnent, BB – blue component in hexadecimal representation. For example: DDDDFFDD.

Forming of tayl for set of objects
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



Conclusion




Summing up the result, it should be noted that the library of forming of tayl described here is only prototype. Of course generation of tayl on the party of database — quite strange approach, also exists many pedpochtitelny alternatives. But if someone has read up up to the end this article, now has idea of the device of taylovy model of the electronic card and knows as to use space data types of the SQL Server.
We draw on tayla of the electronic card in MSSQL The source code of library on github

Source code of library and backup of database 3.5mb

This article is a translation of the original post at habrahabr.ru/post/266363/
If you have any questions regarding the material covered in the article above, please, contact the original author of the post.
If you have any complaints about this article or you want this article to be deleted, please, drop an email here: sysmagazine.com@gmail.com.

We believe that the knowledge, which is available at the most popular Russian IT blog habrahabr.ru, should be accessed by everyone, even though it is poorly translated.
Shared knowledge makes the world better.
Best wishes.

comments powered by Disqus