ThinkGeo.com    |     Documentation    |     Premium Support

Question about Sql2008 within WMS

#1

Hi,



I have a table with about 25000 rows (features) and I would like to know how I should build the WMS plugin to make sure that each request  will only target the feature within the BB of the request?  When I do execute a SQL request against my table I have a very good response time but the WMS plug-in is kinda slow and I cannot figure it out yet…



thanks a lot.



jm.



My plugin:




using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using ThinkGeo.MapSuite.Core;
using ThinkGeo.MapSuite.WmsServerEdition;
 
namespace WmsPlugin {
    internal class AqueductGlobalMapsAvailableBlueWater : WmsLayerPlugin {
        // This method is only called once per style and crs.  In it you should create your
        // layers and add them to the MapConfiguration.  If you want to use tile caching you
        // can also specif that in the MapConfiguration under the TileCache property.
        // If you have setup multiple styles or projections this method will get called for
        // each unique combination
 
        private const string PlugName = "AqueductGlobalMapsAvailableBlueWater";
        private const string AqueductGlobalMapsPath = @"K:\AqueductGlobalMaps\global_dl_20130620.shp";
        private const string WorldLayerFilePath = @"K:\DEPLOYMENT_SPHMCT\World\Human\cntry02.shp";
        private const string FileRoot = @"R:\AqueductGlobalMaps" + PlugName + @"";
        private const string BoundinboxFilePath = FileRoot + "BoundingBox.bin";
        private const string OutputShapeFilePath = FileRoot + PlugName + ".shp";
        private const NumberStyles Styles = NumberStyles.AllowExponent | NumberStyles.Number;
 
        protected override MapConfiguration GetMapConfigurationCore(string style, string crs) {
            var mapconf = new MapConfiguration();
            var bitmapTileCache = new FileBitmapTileCache {CacheDirectory = @"R:\Cache" + PlugName, CacheId = PlugName};
            mapconf.TileCache = bitmapTileCache;
            mapconf.TileCache.ImageFormat = TileImageFormat.Png;
 
            var backgroundLayer = new BackgroundLayer(new GeoSolidBrush(GeoColor.SimpleColors.White));
            var worldLayer = new ShapeFileFeatureLayer(WorldLayerFilePath, ShapeFileReadWriteMode.ReadOnly);
 
            worldLayer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle =
                AreaStyles.CreateSimpleAreaStyle(GeoColor.StandardColors.Transparent,
                    GeoColor.StandardColors.LightGray, 1);
            worldLayer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
            worldLayer.DrawingQuality = DrawingQuality.HighQuality;
 
            try {
 
                if (!Directory.Exists(FileRoot)) {
                    Directory.CreateDirectory(FileRoot);
                }
 
                MsSql2008FeatureLayer inputShapeFile = BuildSqlOverlay(
                    "sql",
                    @"data source=DEVSQL14\DEV2;initial catalog=DUSGEODATA;User Id=*****;Password=*****;Packet Size=16384;multipleactiveresultsets=True;connect timeout=120;App=EntityFramework",
                    "dbo.AqueductGlobalMapsAvailableBlueWater",
                    "geomFixed"
                    );
 
                #region Create Styles
 
                Collection<GeoColor> colorsInQualityFamily =
                    GeoColor.GetColorsInHueFamily(GeoColor.FromArgb(255, GeoColor.SimpleColors.LightBlue), 16);
 
                var classBreakStyle = new ClassBreakStyle("styleValue") {
                    BreakValueInclusion = BreakValueInclusion.IncludeValue
                };
 
                classBreakStyle.ClassBreaks.Add(new ClassBreak(0.0D, AreaStyles.NoData1));
 
                classBreakStyle.ClassBreaks.Add(new ClassBreak(1.0D, new AreaStyle {
                    FillSolidBrush = new GeoSolidBrush(colorsInQualityFamily[0]),
                    OutlinePen = new GeoPen(GeoColor.StandardColors.LightGray, 1)
                }));
                classBreakStyle.ClassBreaks.Add(new ClassBreak(2.0D, new AreaStyle {
                    FillSolidBrush = new GeoSolidBrush(colorsInQualityFamily[3]),
                    OutlinePen = new GeoPen(GeoColor.StandardColors.LightGray, 1)
                }));
                classBreakStyle.ClassBreaks.Add(new ClassBreak(3.0D, new AreaStyle {
                    FillSolidBrush = new GeoSolidBrush(colorsInQualityFamily[6]),
                    OutlinePen = new GeoPen(GeoColor.StandardColors.LightGray, 1)
                }));
                classBreakStyle.ClassBreaks.Add(new ClassBreak(4.0D, new AreaStyle {
                    FillSolidBrush = new GeoSolidBrush(colorsInQualityFamily[12]),
                    OutlinePen = new GeoPen(GeoColor.StandardColors.LightGray, 1)
                }));
                classBreakStyle.ClassBreaks.Add(new ClassBreak(5.0D, new AreaStyle {
                    FillSolidBrush = new GeoSolidBrush(colorsInQualityFamily[15]),
                    OutlinePen = new GeoPen(GeoColor.StandardColors.LightGray, 1)
                }));
 
                classBreakStyle.ClassBreaks.Add(new ClassBreak(6.0D, AreaStyles.NoData1));
 
                #endregion
 
                #region Apply Style to shape file
 
                inputShapeFile.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(
                    LineStyles.CreateSimpleLineStyle(GeoColor.StandardColors.LightGray, 1, LineDashStyle.Solid,
                        false));
                inputShapeFile.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(
                    PointStyles.CreateSimplePointStyle(PointSymbolType.Square, GeoColor.SimpleColors.Black,
                        GeoColor.SimpleColors.Black, 1, 10));
                inputShapeFile.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(classBreakStyle);
                inputShapeFile.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
 
                #endregion
 
                mapconf.Layers.Add("BackgroundLayer", backgroundLayer);
                mapconf.Layers.Add("WorldLayer", worldLayer);
                mapconf.Layers.Add(PlugName, inputShapeFile);
            }
            catch (Exception ex) {
                Trace("Exception", ex.Message);
                Trace("StackTrace", ex.StackTrace.ToString(CultureInfo.InvariantCulture));
                Trace("Source", ex.Source.ToString(CultureInfo.InvariantCulture));
            }
 
            BrowerCacheExpiration = new TimeSpan(0, 0, 0);
            WmsQueryMode = WmsQueryMode.Queryable;
 
            return mapconf;
        }
 
        // This method gets called on every amp request sent to the server.  In the parameters we pass the
        // map request which includes the bounding box, image size etc.  We also pass you the map configuration
        // which includes all the static layers.  In this method you can make any data changes or do anything
        // dynamic you want
        protected override Bitmap GetMapCore(GetMapRequest getMapRequest, MapConfiguration mapConfiguration,
            HttpContext context) {
            context.Response.Cache.SetCacheability(HttpCacheability.Public);
            context.Response.Expires = 1440*1000;
            return base.GetMapCore(getMapRequest, mapConfiguration, context);
        }
 
        // In this method you need to return the name of the Layer that WMS will expose.
        // You will use this name on the client to specify the layer you want to consume
        protected override string GetNameCore() {
            return PlugName;
        }
 
        protected override GeographyUnit GetGeographyUnitCore(string crs) {
            const GeographyUnit geographyUnit = GeographyUnit.Meter;
            return geographyUnit;
        }
 
        // In this method you need to return the projections that are supported by your data.
        // It is your responsability to project the data in the MapConfiguration for each projection
        // type you specify here.
        protected override Collection<string> GetProjectionsCore() {
            var projections = new Collection<string> {"EPSG:900913"};
            return projections;
        }
 
        // In this method you need to return the bounding box of the layer.
        protected override RectangleShape GetBoundingBoxCore(string crs) {
            var bb =
                new Feature(
                    "POLYGON((-20037397.9519 18429147.1567,-20037397.9519 -7540091.4503,20037507.0672 -7540091.4503,20037507.0672 18429147.1567,-20037397.9519 18429147.1567))");
            return (bb.GetShape().GetBoundingBox());
        }
 
        private static void Trace(string f, string m) {
            using (
                var writer =
                    new BinaryWriter(
                        File.Open(
                            FileRoot + f + ".dat",
                            FileMode.Create))) {
                writer.Write(m);
            }
        }
 
 
        private MsSql2008FeatureLayer BuildSqlOverlay(string featureLayerName,
            string connectionstring,
            string tableName, string columnName) {
            var inMemoryFeatureLayer = new MsSql2008FeatureLayer {
                Name = featureLayerName,
                ConnectionString = connectionstring,
                TableName = tableName,
                CustomGeometryColumnName = columnName,
                DatabaseConnectionMode = DatabaseConnectionMode.KeepOpen,
                Srid = 900913,
                DrawingQuality = DrawingQuality.HighQuality,
                FeatureIdColumn = "Id"
                //WhereClause = "where Id <= 4115"
            };
            return (inMemoryFeatureLayer);
        }
    }
}




#2

Hi Jm, 
  
 Yes, In wms serve, each tile request only targets the features within the bounding box of the request. 
  
 As for the performance enhancement, I guess we can use the Tile Cache Generator to cache all the tiles in advanced. More details please refer to thinkgeo.com/forums/MapSuite/tabid/143/aft/10446/Default.aspx 
 Once we have generated all the tiles including the layer from SQL, when the request is processed, it won’t access the SQL server and draw features on pictures, but get the cached pictures on your disk directly.  
  
 If any question, please feel free to let us know. 
 Thanks, 
 Johnny 
  
  


#3

Johnny, 
  
 Do you use the geometry column to query the data base?  Just want to make sure that you are not browsing the full table for each request? 
  
 Thanks. 
  
 Jm

#4

Hi Jm, 



Yes, we need and have to use the geometry to do the special data query in sql server. At the end of the sql server side, the T-sql statement will be generated like the below to do the query: 

"select … from … where (geometry::STGeomFromWKB(@Geometry,0).STIntersects(geom)=1: 



You can get the sql string for more details by registering the ExecutingSqlStatement event in the MsSql2008FeatureLayer. 



For each of the tile request, yes, we will search the whole table for the current tile extent. Actually, the same thing is applied in our other products like Web Edition and Mvc Edition. But not like the ordinary select command in sql server, for the geometry query, the Microsoft have used some kind of R-Tree mechanism to enhance such queries.



Hope it helps. 



Regards, 

Johnny