The best ideas are SO simple

Topics: Data Access, SharpMap v0.9 / v1.x, WinForms Controls
Mar 11, 2014 at 1:15 PM
I have just started SharpMap, 3 working days ago, and i can say that a map with a layer that is originating from System.Data.Entity.DbContext is displaying much faster than the same table accessed via SharpMap.Data.Providers.SqlServer2008. This is a first frugal impression on the run, but i like it very much. SharpMap is an incredibly nice project. thanks a lot !
Coordinator
Mar 11, 2014 at 1:30 PM
That's really interesting. I Wonder if that has to due with the fact that EF6 is using SqlServerSpatialTypes and not the STAsBinary() as the SqlServer2008 provider is using. We could probably upgrade the plain provider to use them as well to get the same performance-gain also on the sql2008 provider without EF6 which would be nice.

Thanks for sharing
Coordinator
Mar 11, 2014 at 1:40 PM
Glad you like it.

Do you mind sharing a bit more information or even code to display your map?

Have you tried the SqlServer provider that resides in the SharpMap.SqlServerSpatialObjects project. It might offer a performance benefit over the usual SqlServer provider, as it does not rely on the SqlServer translating its geometries to WKB that SharpMap then parses, instead it parses the SqlGeometry/SqlGeography directly.
Editor
Mar 11, 2014 at 1:45 PM
I have also used EF to good effect combined with sharpmap.

Although to get decent performance I had to write a stored proc that did the bounding box query as EF cannot be configured to use a spatial index as far as I know.

The only bottle neck I found was then parsing the returned entity types into the geometries along with styling info. But with some clever caching this was again minimised.

the performance of this did diminish as the geometry count increased (obviously) but this is due to bottle necks in the GDI+ rendering, which hopefully we can refactor out in the future and replace with a better graphics render.
Mar 12, 2014 at 8:50 AM
This is my 4th day (and a shorter one) of SharpMap diving for me, so my SharpMap-coding is laughing-stock, esteemed coordinators.

using ...;
using GeoPoint = GeoAPI.Geometries.Coordinate;
using sql = Microsoft.SqlServer.Types;
using sqlconv = SharpMap.Converters.SqlServer2008SpatialObjects;
...
public partial class Form1 : Form
{
...
    Collection<GeoAPI.Geometries.IGeometry> geomColl = null;
    SharpMap.Layers.VectorLayer vectV = null;
...
    private void EF6Data_Click(object sender, EventArgs e)
    {
...
        using(geodbEntities db = new geodbEntities())
        {
            string SelLayer = "yellow_submarine";
            if (geomColl == null)
            {
                geomColl = new Collection<GeoAPI.Geometries.IGeometry>();
            }

            var d22 = db.soluris;
            foreach(soluri unsol in d22)
            { 
                sql.SqlGeometry sqlM = sql.SqlGeometry.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(unsol.ogr_geom2.AsBinary()),0);
                GeoAPI.Geometries.IGeometry geoM =   sqlconv.SqlGeometryConverter.ToSharpMapGeometry(sqlM);
                geomColl.Add(geoM);
            }

            if (vectV == null)
            {
                vectV = new VectorLayer(SelLayer);
                vectV.Style.Fill = new SolidBrush(Color.DarkCyan); 
                vectV.DataSource = new SharpMap.Data.Providers.GeometryFeatureProvider(geomColl); 
                mapBox1.Map.Layers.Add(vectV);  
                mapBox1.Map.ZoomToExtents();
                mapBox1.Refresh(); 
            }
            }
    }

Even if the map.redrawing speed was bad, the comfort of EntityFramework6 programming will be enough for me, but think, it is faster ! I will try to get the SharpMap.SqlServerSpatialObjects a try, just for speed sake, to see how fast the map redraws.
Coordinator
Mar 12, 2014 at 10:05 AM
Ok you are pulling all geometries into memory and render that.

You may want to consider using
Marked as answer by costeakai on 3/12/2014 at 3:34 AM
Editor
Mar 12, 2014 at 11:32 AM
I created an actual entity provider that used the dbContext to pull assets from the database directly.

The performance was adequate for small amounts of geometries, but to get it really perform a spatial index, with a stored proc was required. After this the speed was on par with the normal SQL provider.
Marked as answer by costeakai on 3/12/2014 at 3:34 AM
Mar 12, 2014 at 1:06 PM
Edited Mar 12, 2014 at 1:16 PM
I hope i can manage with as little digging in sharpmap as it takes to to the job. everything else, i must do it using entityframerork.6 and sqlserver.
Editor
Mar 12, 2014 at 1:30 PM
I think the way you have done it is fine, as long as the data you are looking at is static. With that method any new or edited data will not be displayed unless the load process is repeated. Using an entity or sql data provider you are getting the data from the source every time you need it so it will always be current.
Mar 12, 2014 at 2:14 PM
Edited Mar 13, 2014 at 10:50 AM
well, as long as i am just discussing...i just wonder how i'll see things in 40 days, not 4...
i would say that sharpmap fits perfectly with the top ms-techniques, among which i love code-first-table-per-hierarchy; sharpmap authors have made a great choice by using c#, that's to say nothing about sharpmap itself, a masterpiece of multithreading and that's all i dare say.
now poor me, instead of using
wlayer.DataSource.ExecuteIntersectionQuery(infoPnt_Envelope, ds);
i can use
ogr_geom2.STIntersects('POINT(123.321 321.123)') = 1
but instead of the sql server syntax i will use the entityframework syntax.
i prefer using entityframework spatial capabilities.
using an in-memory-collection as datasource for the map, i believe that map.redrawing is faster than using directly a database.table as datasource for the map; i think it is needed just to take a one by one approach: replace the modified geometry in the collection; add the new geometry to the same collection, and finally to remove and add the layer based on the in-memory-collection and refresh the map; that's what i have already done (for new geometries, i haven't yet dealed with modified geometries) and it's working just fine; you don't have to repeat the whole load process. but one can do it all, the hard way by NOT using entityframework, or the easy way by using it. the ONLY downside is the 10 gb limit for sqlexpress. ok, it is a dealbreaker mr. Gates.
P.S. ok, this does it:
i have opened 2 windows , on 2 different monitors. on monitor.1 i'm selecting features using SharpMap.Data.Providers.GeometryFeatureProvider filled by sqlexpress2012 via entityframework.6 , on monitor.2 i use SharpMap.Data.Providers.SqlServer2008 filled by sqlexpress2012 via SharpMap.Data.Providers.SqlServer2008 .
i have measured the code-time to select a feature by clicking on it: 0.06 secs for SharpMap.Data.Providers.SqlServer2008
and 0.25 secs for SharpMap.Data.Providers.GeometryFeatureProvider . i have clicked first on the the map with SqlServer2008(0.06 secs) and I have clicked afterwards on the map with GeometryFeatureProvider(0.25 secs) . which of them , would you believe, showed up first by a huge margin on the screen? now what do you believe matters for the client? the code time or the final time to see all things up and running completely? The winner, ladies and gentlemen is GeometryFeatureProvider , by a decisive margin, although it was handicapped by the time necessary for me to move the mouse from one monitor to the other and click (and select) the same feature.
Editor
Mar 13, 2014 at 10:49 AM
This is what is good about programming with sharpmap. You can code the way you are comfortable with. As you get more fluent with the code you can do more advanced stuff!
Mar 13, 2014 at 10:53 AM
Edited Mar 13, 2014 at 10:56 AM
yes, but is hugely different , not for me the programmer, but for the final client, if i use an in memory collection instead of a database table. please read the "P.S" from my previous post.
Now i don't want to be a pain , but i believe that the impression upon the client is what matters, NOT our nasty programming habits.
Is it so? Are we just discussing the SharpMap by itself ?
Coordinator
Mar 13, 2014 at 11:52 AM
Edited Mar 13, 2014 at 6:07 PM
The performance of the SqlServer2008 provider relies heavily on the way you have configured the usage of the spatial index.
One could imagine a GeometryFeatureProvider backed CachingProvider... .Overall I agree, performance is what essentially counts.

Nonetheless, I have played a bit with the BusinessObjectProvider mentioned above and added an void ExecuteQuery(IQueryable<T> query, FeatureDataSet fds) method on the provider and the underlying repositories.
        [Test]
        public void ProviderTest()
        {
            BusinessObjectProvider<University> p = null;
            Assert.DoesNotThrow(() => p= new BusinessObjectProvider<University>("XYZ",
                new EF6BusinessObjectRepository<University>(() => new UniversityContext())));

            Assert.IsNotNull(p);
            Assert.AreEqual(2, p.GetFeatureCount());

            Envelope env = null;
            Assert.DoesNotThrow(() => env = p.GetExtents());
            Assert.IsNotNull(env);
            Assert.IsFalse(env.IsNull);

            var fds = new FeatureDataSet();
            Assert.DoesNotThrow(() => p.ExecuteIntersectionQuery(env, fds));
            Assert.AreEqual(1, fds.Tables.Count);
            Assert.AreEqual(2, fds.Tables[0].Rows.Count);

            fds = new FeatureDataSet();
            var f = NetTopologySuite.NtsGeometryServices.Instance.CreateGeometryFactory(0);
            var pBuf = f.CreatePoint(new Coordinate(-122.296623, 47.640405)).Buffer(0.001);
            Assert.DoesNotThrow(() => p.ExecuteIntersectionQuery(pBuf, fds));
            Assert.AreEqual(1, fds.Tables.Count);
            Assert.GreaterOrEqual(1, fds.Tables[0].Rows.Count);

            fds = new FeatureDataSet();
            var r = p.Source as EF6BusinessObjectRepository<University>;
            using (var c = r.Context)
            {
                var pDbBuf = pBuf.ToDbGeometry();
                var q = from u in c.Set<University>()
                    where u.Name == "Graphic Design Institute" || u.DbGeometry.Intersects(pDbBuf)
                    orderby u.Name
                    select u;

                Assert.DoesNotThrow(() => p.ExecuteQueryable(q, fds));
            }
            Assert.AreEqual(1, fds.Tables.Count);
            Assert.GreaterOrEqual(2, fds.Tables[0].Rows.Count);
        }
The test passes alright, the full source is here. Enjoy
Mar 14, 2014 at 7:45 AM
I make no promises, as no country for old men could try just a little. But I will try to do what a god named FObermaier is telling me to do. No joking, I am an just an old mortal, with little time left for digging arround. As far as I can see there's a spatial index with a bounding box, and default cells per object and grids , for my geometrycolumn. First of all I will try the SqlServer provider that resides in the SharpMap.SqlServerSpatialObjects , I don't know if I will reach the BusinessObjectProvider . I strongly believe that my coding habits for SharpMap aren't mature enaugh, so don't mind me for half a year or smthing like that. I'll show up when I will have smthing to say. You've given me stuff for at least half a year to deal with. Thanks.
Coordinator
Mar 14, 2014 at 9:09 AM
Especially look for the following properties:
  • ExtentsMode,
  • ForceIndex,
  • ForceSeekHint
  • MaxDop,
  • NoLockHint
Mar 14, 2014 at 12:26 PM
I'm starting to get the hang of USING it, not programming it; it seems to me that the less geometry-items to show, the faster the (async)rendering. If you get over 30,000 items to show'em ALL, you have to wait a little. Or is it my little-sloppy-code is completely lousy? should i upload my latest sick-code ?
Coordinator
Mar 14, 2014 at 1:01 PM
The rendering speed depends on many factors like
  • amount of items to render,
  • complexity of items
  • speed of provider
  • use of themes
  • use of VectorStyle.EnableOutline
  • ...
Only the TileAsyncLayer is rendered async on a MapBox control. Feel free to show us your code.
Mar 15, 2014 at 8:27 AM
Edited Mar 17, 2014 at 7:19 PM
//...
using GeoPoint = GeoAPI.Geometries.Coordinate;
using sql = Microsoft.SqlServer.Types;
using sqlconv = SharpMap.Converters.SqlServer2008SpatialObjects;
//...
public partial class Form1 : Form
    {
  Collection<GeoAPI.Geometries.IGeometry> geomColl = null;
  SharpMap.Layers.VectorLayer vectV = null;

//...
  

     private void mapBox1_MouseUp(GeoPoint worldPos,  System.Windows.Forms.MouseEventArgs imagePos)
  {
  if(geomColl == null)
      geomColl = new Collection<GeoAPI.Geometries.IGeometry>();

  if(vectV == null)
  {
      vectV = new VectorLayer("yellow_submarine");
      vectV.Style.Fill = new SolidBrush(Color.Yellow);  //all.yellow
      vectV.DataSource = new SharpMap.Data.Providers.GeometryFeatureProvider(geomColl); 
      mapBox1.Map.Layers.Add(vectV); 
  }

  using (geodbEntities db = new geodbEntities())
  {
    sql.SqlGeometryBuilder worldPos_M_blder = new sql.SqlGeometryBuilder();
  worldPos_M_blder.SetSrid(0);    worldPos_M_blder.BeginGeometry(sql.OpenGisGeometryType.Point);
  worldPos_M_blder.BeginFigure(worldPos.X  , worldPos.Y);//  x  y
  worldPos_M_blder.EndFigure();    worldPos_M_blder.EndGeometry();
  
  sql.SqlGeometry sqlM = worldPos_M_blder.ConstructedGeometry;
  System.Data.Entity.Spatial.DbGeometry dbM = System.Data.Entity.Spatial.DbGeometry.FromBinary(sqlM.STAsBinary().Buffer);
  System.Linq.IQueryable<SharpMapForm.soluri> d33 = db.soluris.Where(x => x.ogr_geom2.Intersects(dbM));
  SharpMapForm.soluri dbvBIG = d33.FirstOrDefault();
  System.Data.Entity.Spatial.DbGeometry dbv = dbvBIG.ogr_geom2;
  sql.SqlGeometry sqlM2 = sql.SqlGeometry.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(dbv.AsBinary()), 0);
  GeoAPI.Geometries.IGeometry geoM2 = sqlconv.SqlGeometryConverter.ToSharpMapGeometry(sqlM2);
  geomColl.Add(geoM2);      int CiteGeomsSelectate = geomColl.Count();
    }

    vectV.DataSource = new SharpMap.Data.Providers.GeometryFeatureProvider(geomColl);     
  mapBox1.Map.Layers.RemoveAt(1);
  mapBox1.Map.Layers.Add(vectV);

  //mapBox1.Map.Layers[0].Enabled = false;
  mapBox1.Refresh();
  }
if i uncomment
mapBox1.Map.Layers[0].Enabled = false;
it means that i only render Map.Layers[1], which has very few features and it renders very quickly, but I also need Map.Layers[0] to be enabled, but , having tens of thousands of features , Map.Layers[0] renders very-very-slowly when zoomedToExtents;

now I have read (https://sharpmap.codeplex.com/discussions/302254) that it is faster if one draws the layer (with few features) on the picture box, but , although i have scanned quite a lot of the discussions, I cannot seem to find out how to draw on the picture box. Is this the fastest method to refresh a map like mine? Could you , kindly , point me how to redraw only the small layer and NOT to redraw the large one?
Coordinator
Mar 15, 2014 at 9:09 PM
Edited Mar 15, 2014 at 9:11 PM
Please use CTRL+K (Insert Code) when inserting code. It improves readability a lot.
Where is the location where you have performance problems?
Mar 17, 2014 at 1:16 PM
Edited Mar 17, 2014 at 3:00 PM
I have edited my previous post, perhaps it is more useful now, i hope.
Editor
Mar 17, 2014 at 4:03 PM
you could try setting the minVisible and maxVisible properties of the layer so that the one with many features is only drawn at closer zoom levels, then most of the features will be culled and not drawn, so it will speed up the drawing.
Marked as answer by costeakai on 3/17/2014 at 11:07 AM
Coordinator
Mar 17, 2014 at 4:23 PM
The default mechansim to get the extents of a layer is to go through the whole table and get the extent of each geometry.
It is much faster to get the extents from the spatial index, so you should add a spatial index and set the ExtentsMode to either SpatialIndex or EnvelopeAggregate if you are on SqlServer2012.

I don't know if all your tens of thousands of geometries are really necessary at every zoomlevel, maybe there is an easy way to filter them for the zoomlevel? In that case you could add a layergroup with a bunch layers based on your datasource all with a different Style.MinZoom, Style.MaxZoom value.
Marked as answer by costeakai on 3/17/2014 at 11:07 AM
Mar 17, 2014 at 7:10 PM
Edited Dec 7, 2014 at 11:49 AM
Thanks, f.obermaier
Apr 10, 2014 at 7:18 AM
Edited Dec 7, 2014 at 11:49 AM
...