MsSql2008FeatureLayer / using SQL server 2008 to store shape file data

bentaylortheonly · March 22, 2016, 7:12am

Hello,

We are trying to implement mapping using some shape files which represent data for a large area of the UK.

These are bigger than the files for the samples, for instance our file for the minor roads is 47MB - consequently the refreshing, panning and zooming of the map is fairly slow.

I have tried an approach of storing the data in SQL server 2008, and indexing the Xmin, Ymin, Xmax and Ymax (the bounding box) of each record.

The map responsiveness for the shape files which I have regenerated by pulling out a small subset of this data from SQL server for a small area of the country (which are obviously much smaller files) is fast, but the process of pulling the data out itself and regenerating the small shape files is much too slow to be done on the fly.

I am investigating alternative ways round this, however I have noticed that you have included a class called 'MsSql2008FeatureLayer', so I was wondering if this enables me to tell a MapSuite map to get its shape file data from SQL Server? If it would, do you have any guidelines on what format the database would need to be in and some sample code for connecting this layer to the database? Many thanks

Ben · March 22, 2016, 8:09am

ShapeFile is not supposed to be that slow. Can you double check if you have the index files (.idx and .ids) for the shape and the Layer.RequireIndex is set to true (be default)? 47M is not a huge file and I’m sure we can deal with it pretty well with indexes.  Here is the way how to generate it.

ShapeFileFeatureLayer.BuildIndexFile(“shapeFile.shp”);

About SQL2008FeatureLayer, we have a sample in “Data Providers”->LoadMsSql2008FeatureLayer. It doesn’t generate shape file on the fly, instead it gets WKB (Well Known Binary) directly from the server. The table should have a FeatureIdColumn which holds the unique id of every record.

I think your shape file will work fine and sure go ahead to try SQL2008FeatureLayer is you are interested.

Ben

bentaylortheonly · March 22, 2016, 8:09am

Ah ok, thank you for the information about how to generate the indexes, I will try that out to test the performance increase.

I see the sample code to consume a database, thanks for that, however - what format does the database need to be in? Do you have for example a SQL script or a sample program that initiates the database from a shape file?

Ben · March 22, 2016, 8:09am

There is a tool converts a shape file into SQL Server 2008 table. Can you try it as following as that’s exactly what we did for the test?

Step 1: get the tool here
Step 2: open the tool with the following settings. The shape file used for that sample is attached below. For more info about the settings please see the docs here.

Step 3: try “select * from States”, here should be the result

Any queries just let us know.

Kimberly · March 22, 2016, 8:09am

I tried to download the tool to import a shape file to SQL Server 2008, but the link doesn't work. Can you repost the link?

Thank you!

Kimberly

Ben · March 22, 2016, 8:09am

Kimberly,

You can download it from the following link where you can find shape2Sql.exe inside.
sharpgis.net/file.axd?file=SqlSpatialTools_build3413.zip

Thanks for reminding, I will update the link in the above post too.

Ben

kkuang · March 22, 2016, 8:09am

Hi Ben,

I have imported states, counties,  tracts shape files into a sql server 2008 database.  I am able to query the tables and preview the maps by using MS Management Studio.  But I got an “invalid geometry instance” error when I run my MapSuite Desktop test app.  Do you know what cause this error?

The detail error message:
“There’re some invalid geometry instance in table not valid. About invalid geometry please have aThe  look at msdn.microsoft.com/en-us/library/bb933890.aspx website. To solve this problem, we provide MakeAllGeometryValid function to make the invalid geometry to be valid, because of the function updating your data, you need to backup your data before using this function.”

The following is my code:

Private Sub frmTGSQL_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         LoadSQL2008Layer()
     End Sub

     Private Sub LoadSQL2008Layer()
         Try
             winformsMap1.MapUnit = GeographyUnit.DecimalDegree

             winformsMap1.CurrentExtent = New RectangleShape(-171.399778, 73.033923, -65.969831, 17.286137)
             winformsMap1.BackgroundOverlay.BackgroundBrush = New GeoSolidBrush(GeoColor.GeographicColors.ShallowOcean)

             Dim connectString As String = “Data Source=MSSRND1;Initial Catalog=CHATGeoDB;Persist Security Info=True;Integrated Security=SSPI”
             Dim sql2008Layer As New MsSql2008FeatureLayer(connectString, “states”, “recid”)
             sql2008Layer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1
             sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20

             Dim staticOverlay As New LayerOverlay()
             staticOverlay.Layers.Add(“Sql2008Layer”, sql2008Layer)
             winformsMap1.Overlays.Add(staticOverlay)

             winformsMap1.Refresh()
         Catch ex As Exception
             MsgBox(ex.Message)
         End Try
     End Sub

ThinkGeo · March 22, 2016, 8:09am

Kevin,

I think you did right except one statement missing.

MsSql2008FeatureLayer sql2008Layer = new MsSql2008FeatureLayer(connectString, "States", "recid"); sql2008Layer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1; sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20; sql2008Layer.Srid = 4326;

Any more questions just let me know.

Thanks.

Yale

kkuang · March 22, 2016, 8:09am

Thanks, Yale.

I added sql2008Layer.Srid = 4326 in my code.  The error has gone.  But the map is blank.

Thanks.
Kevin

ThinkGeo · March 22, 2016, 8:09am

Kevin,

I am very strange why your map is blank? I was using the same codes and it can show exactly the same with ShapeFileFeatureLayer. I am wondering if there is some potential problem with your data in the SQL Server2008.

It seems that you also imports the data from area type ShapeFile into SQL server2008 by the tool shape2Sql.exe. Can you verify the following codes to see if the features in the table are 51 area–type features?
string connectString = "Data Source=192.168.0.58,1041;Initial Catalog=DatabaseName;Persist Security Info=True;User ID=username;Password=password"; MsSql2008FeatureLayer sql2008Layer = new MsSql2008FeatureLayer(connectString, "states", "recid"); sql2008Layer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1; sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20; sql2008Layer.Open(); Collection<Feature> features = sql2008Layer.QueryTools.GetAllFeatures(ReturningColumnsType.AllColumns); sql2008Layer.Close();

Any more questions just let me know.

Thanks.

Yale

kkuang · March 22, 2016, 8:09am

The features in the table are 53.

But no map display.

Thanks.

The following is my code

Imports System
Imports System.Windows.Forms

Imports ThinkGeo.MapSuite.Core
Imports ThinkGeo.MapSuite.DesktopEdition
Imports System.Collections.ObjectModel

Public Class frmTGSQL

     Private Sub frmTGSQL_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         LoadSQL2008Layer()
     End Sub

     Private Sub LoadSQL2008Layer()
         Try
             winformsMap1.MapUnit = GeographyUnit.DecimalDegree

             winformsMap1.CurrentExtent = New RectangleShape(-171.399778, 73.033923, -65.969831, 17.286137)
             winformsMap1.BackgroundOverlay.BackgroundBrush = New GeoSolidBrush(GeoColor.GeographicColors.ShallowOcean)

             Dim connectString As String = "Data Source=MSSRND1;Initial Catalog=CHATGeoDB;Persist Security Info=True;Integrated Security=SSPI"
             Dim sql2008Layer As New MsSql2008FeatureLayer(connectString, "states", "recid")
             sql2008Layer.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1
             sql2008Layer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20
             sql2008Layer.Srid = 4326

             sql2008Layer.Open()
             Dim features As Collection(Of Feature)
             features = sql2008Layer.QueryTools.GetAllFeatures(ReturningColumnsType.AllColumns)
             sql2008Layer.Close()


             Dim staticOverlay As New LayerOverlay()
             staticOverlay.Layers.Add("Sql2008Layer", sql2008Layer)
             winformsMap1.Overlays.Add(staticOverlay)

             winformsMap1.Refresh()
         Catch ex As Exception
             MsgBox(ex.Message)
         End Try
     End Sub
End Class

ThinkGeo · March 22, 2016, 8:09am

Kevin,

Your code is exactly the same with mine!

I am wondering your imported data into SqlServer2008 is correct or not, because we use the same shape file data but getting different feature count. I verified the shape file in the attachment again, it should be 51 features instead of 53.Can you following Ben’ instructions to import the data again and try it? Besides, it seems you also imported some other data? Can you try that again too?

Besides, can you verify the version you are using:

string versionString = WinformsMap.GetVersion();

Sorry for the inconvenience!

Any more questions just let me know.

Thanks.

Yale

Yuri · March 22, 2016, 8:09am

I had the same problem, a blank map, I also used the Shape2SQL tool to load my data in SQL Server. But after hours of testing the problem solves using.
sql2008Layer.Srid = 0

Hope this help.

ThinkGeo · March 22, 2016, 8:09am

Yuri,

Thanks for your sharing solution!

The default SRID value of MsSql2008FeatureLayer is 4326 in the latest public release version, but when you convert a shape file into SQL Server 2008 table using the tool the default SRID value is 0. So you’d better make the “Set SRID 4326” check box checked shown in the following screenshot when you import the data to Sql Server using the tool:

Any more questions please let me know.

Thanks,

Sun

Yuri · March 22, 2016, 8:09am

Thanks Sun I can see that is very importat to use the proper SRID, as I say I used first the 0 SRID but this cause irregularities on the geometry. In my case the correct SRID for my data is the default 4326. Thanks for the tip.

ThinkGeo · March 22, 2016, 8:09am

You are welcome, Yuri

Any more questions please let me know.

Thanks,

sun

Hassan · March 22, 2016, 8:09am

I'm having the same problem, cannot download shape2sql.exe because that link is broken. when I click on: Download SqlSpatialTools v1.3.0 (348 kb) (build 3759) this link, it redirects me to: sharpgis.net/error404.aspx

Without this tool I'm not able to work any further. Please make this tool available.

Thanks:

Hassan Ali,

Web Engineer

ThinkGeo · March 22, 2016, 8:09am

Hassan,

Take it easy, please contact our Support by email support@thinkgeo.com to request the SqlSpatail tool.

Thanks
James

Ryan · March 22, 2016, 8:09am

As this link is for a third party site we cannot claim responsibility for its contents being available. However you access from this post.

SqlSpatialTools.zip (334 KB)

John · March 22, 2016, 8:09am

We store all spatial data for our product in MS SQL Server 2008 geometry format. We have notice poor performance, until we learn how to properly index the data. Note, proper indexing make a significant different.

We have a SQL Server database wihl about 1GB of spatial data. Street level data for several counties in the U.S. Performance seemed kind of slow until we realized the default indexing scheme from some data loading programs don’t work very well.

Unlike other GIS indexing processes, SQL starts with a bounding box and then grid densities at 4 levels. What we realized is that is we had a “medium” grid scheme at each level, we had so many grids that performance took a big hit. At a “medium” density grid scheme, our 4th level index grid was 2 meters in size. Ridiculous for our data.

We changed the grid density in SQL Management Studio to “low” for all 4 levels and now we have draw times of 0.3 seconds at street level, on a large monitor, on a modest desktop computer.

Further, to tune this properly, you may also want to make the bounding rectangle defined during indexing to be larger than your data so the 4th level grid is a better “fit” for your data.

2 additional items, 1. ArcSDE tuning white paper suggest taking the most “common” map scale that your data is drawn and dividing the screen display into 9 grid. This would be the “optimal” grid size. 2. Although SQL Management Studio allows you to set grid density to “none” in the GUI, “none” is NOT a valid value and will get set to “medium” when executed. I guess it’s a bug.

Try modifying the index and you will be amazed at the performance. SQL can be the same as Shapefile! MS SQL Server is high performance, so it should be very fast.

To learn more about SQL indexing visit Isaac at MSDN:
blogs.msdn.com/b/isaac/archive/2007/11/24/spatial-indexing-part-1-why-a-spatial-index.aspx

John