ThinkGeo.com    |     Documentation    |     Premium Support

ThinkGeo Layers MsSQL - Geography

#1

We are trying to load a MsSQlFeatureLayer from a SQL Server 2017 Table with a Geography data type column. We are using MapSuite.Layers.MsSql.10.6.3 but the code only works with Geometry columns and throws the following error for Geography. We have also updated Microsoft.SqlServer.Types to attempt to resolve the issue.

Operand type clash: geography is incompatible with geometry Could not find method ‘STEnvelope’ for type ‘Microsoft.SqlServer.Types.SqlGeography’ in assembly ‘Microsoft.SqlServer.Types’

#2

Thanks Brandon,
Could you send us the sample code you are trying to load the data. And a small SQL Server 2017 backup database file that will be very helpful for us to dig into more detail. I think it may need some adjustment for the MsSQlFeatureLayer to suport the Geography data type. We will figure out once we get the sample project and data.

Thanks

Frank

#3

Thanks Frank and I am working with my team to slim down a copy of the database and do you have preferred transfer method when they are done?

I have also provided the code and DB scripts that were used.

VIsual Studio 2017 Code

    private void DBLayer_LoadTest()
    {
        //Clear Map Layers
        mapLayerTest.HighlightOverlay.Features.Clear();
        mapLayerTest.Popups.Clear();
        mapLayerTest.StaticOverlay.Layers.Clear();
        mapLayerTest.CustomOverlays.Clear();
        //Set Background
        mapLayerTest.MapBackground = new GeoSolidBrush(GeoColor.StandardColors.White);
        
        //DecimalDegrees Works
        mapLayerTest.MapUnit = GeographyUnit.DecimalDegree;
        //mapLayerTest.MapUnit = GeographyUnit.Feet;

        //Database Layer
        LayerOverlay layerOverlay = new LayerOverlay();
        MsSqlFeatureLayer sqlLayer = new MsSqlFeatureLayer();
        sqlLayer.ConnectionString = "Data Source=<DBNAME>;User ID=<USERID>;Initial Catalog=<CATALOG>;Persist Security Info=False;Password=<PASSWORD>";
        sqlLayer.TableName = "gis.CampusAreaPointLocation_Test";
        sqlLayer.FeatureIdColumn = "FeatureID";
        sqlLayer.Srid = 4269;
        sqlLayer.DatabaseConnectionMode = DatabaseConnectionMode.AutoClose;

        //Error with GEOG_Data - geography Data Type
        //sqlLayer.CustomGeometryColumnName = "GEOG_Data";
        sqlLayer.CustomGeometryColumnName = "GEOM_Data";

        GeoSolidBrush symbolBrush = new GeoSolidBrush(GeoColor.SimpleColors.DarkRed);
        sqlLayer.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(TextStyles.CreateSimpleTextStyle("AreaName", "Arial", 7, DrawingFontStyles.Regular, GeoColor.StandardColors.Black, GeoColor.StandardColors.White, 5));
        sqlLayer.ZoomLevelSet.ZoomLevel01.CustomStyles.Add(new PointStyle(PointSymbolType.Star, symbolBrush, 15));
        sqlLayer.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20;
        //Set CUrrent Extents
        sqlLayer.Open();
        mapLayerTest.CurrentExtent = sqlLayer.GetBoundingBox();
        sqlLayer.Close();
        //Add to Map Viewer
        layerOverlay.Layers.Add("layerTest", sqlLayer);
        mapLayerTest.CustomOverlays.Add(layerOverlay);
    }

SQL Server 2017 Table Script and Sample Data Insert (Lat/Lon Point)

CREATE TABLE gis.CampusAreaPointLocation_Test
(
FeatureID VARCHAR(15) NOT NULL,
AreaName VARCHAR(45) NOT NULL,
GEOG_Data GEOGRAPHY,
GEOM_Data GEOMETRY
CONSTRAINT pk_CampusAreaPointLocation_Test PRIMARY KEY (FeatureID)
)

–Delete Records
–DELETE gis.CampusAreaPointLocation_Test

–Insert Records
INSERT INTO gis.CampusAreaPointLocation_Test
(
FeatureID,
AreaName,
GEOG_Data,
GEOM_Data
)
SELECT ‘NI1109’, ‘Texas Tech Polo Grounds’, geography::STGeomFromText(‘POINT(-101.824 33.4768)’, 4269), geometry::STGeomFromText(‘POINT(-101.824 33.4768)’, 4269) UNION ALL
SELECT ‘S0377’, ‘S (0377) SYSTEM OFFICE BUILDING’, geography::STGeomFromText(‘POINT(-101.893 33.5812)’, 4269), geometry::STGeomFromText(‘POINT(-101.893 33.5812)’, 4269) UNION ALL
SELECT ‘T0003’, ‘T (0003) MECHANICAL ENGINEERING SOUTH’, geography::STGeomFromText(‘POINT(-101.875 33.588)’, 4269), geometry::STGeomFromText(‘POINT(-101.875 33.588)’, 4269) UNION ALL
SELECT ‘T0004’, ‘T (0004) ELECTRICAL ENGINEERING’, geography::STGeomFromText(‘POINT(-101.876 33.5866)’, 4269), geometry::STGeomFromText(‘POINT(-101.876 33.5866)’, 4269) UNION ALL
SELECT ‘T0005’, ‘T (0005) CHEMISTRY’, geography::STGeomFromText(‘POINT(-101.876 33.5837)’, 4269), geometry::STGeomFromText(‘POINT(-101.876 33.5837)’, 4269) UNION ALL
SELECT ‘T0405’, ‘T (0405) TEXAS TECH PLAZA’, geography::STGeomFromText(‘POINT(-101.87 33.5775)’, 4269), geometry::STGeomFromText(‘POINT(-101.87 33.5775)’, 4269)

–Make Geography/Geometry Data Valid
UPDATE gis.CampusAreaPointLocation_Test
SET
GEOG_Data = GEOG_Data.MakeValid(),
GEOM_Data = GEOM_Data.MakeValid()

#4

Thanks Brandon,
You could drop it to dropbox and send the share link to support@thinkgeo.com. But I think your script should be good for us to re-create the database. I will let you know if we need the backup file.

Thanks

Frank

#5

Thanks Brandon,
We could re-produce this issue. We don’t need the backup file. We are working on it.

Thanks

Frank

#6

Brandon,
I looked into the detail. We do support both types. But there are some issue if the table have both geometry and geography column. So you could remove one of them in you database table without update the mapsuite NuGet library.

If you have to keep both columns in your table. You may need update the mapsuite NuGet library to the latest beta one to get the fix for this issue.

Thanks

Frank

#7

Frank,

We appreciate your responsiveness and we would prefer to use GEOGRAPHY so we recreated the table with the GEOGRAPHY column and excluded the GEOGRAPHY column but encountered the same error.

What is the version number of the beta NuGet library?

Thanks,
Brandon Hennington

#8

Thanks Brandon,
Here is the one need update
https://www.nuget.org/packages/ThinkGeo.MapSuite.Layers.MsSql/11.0.0-beta016

Thanks

Frank

#9

Frank,

Would this require us to upgrade from 10.0 to 11.0? We do not have a Subscription License.

Thanks,
Brandon

#10

Thanks Brandon,
11.0.0-beta*** will go into the V10 prod. You don’t need the separate License. It use the same license as V10.

Thanks

Frank

#11

Frank,

We have updated to the following libraries in Visual Studio and the same error occurs. I wanted to check and see if there are additional references that need to be included or anything on the server side?

Assembly ThinkGeo.MapSuite
ThinkGeo.MapSuite.11.0.0-beta148\lib\net45\ThinkGeo.MapSuite.dll

Assembly ThinkGeo.MapSuite.Layers.MsSql
ThinkGeo.MapSuite.Layers.MsSql.11.0.0-beta016\lib\net45\ThinkGeo.MapSuite.Layers.MsSql.dll

#12

Thanks Brandon,
The latest mapsuite 11 beta is
ThinkGeo.MapSuite.11.0.0-beta149. I attached you the full sample. WindowsFormsApp3.zip (12.5 KB)
Here is all references

image
image1159×1195 86.1 KB

Here is the video show you how it get work.

Thanks

Frank

#13

Frank,

Thanks and I appreciate the additional information. I noticed that you your example is a Windows Form and the application that we are working on is ASP.NET so I was not sure if there were any differences that need to be considered?

Thanks,
Brandon Hennington

#14

Thanks Brandon,
I don’t think there any differences for that. Are you using Web Api or Web Mvc Edition. I can create a demo for you.

Thanks

Frank

#15

Frank,

We are using the ThinkGeo UI Web for WebForms (.NET native GIS Control)/AJAX.

Thanks,
Brandon Hennington

#16

Thanks Brandon,
I created a web sample for you. Also I send you the backup file for the database.

QuickStartSample.zip (3.0 MB)
gis.zip (3.6 MB)

Thanks

Frank

#17

Frank,

We appreciate your teams assistance and this issue has been resolved after making the following changes.

Update WebForms.dll
Assembly ThinkGeo.MapSuite.WebForms
WebForWebForms-Standard.11.0.0-beta066\lib\net45\ThinkGeo.MapSuite.WebForms.dll

Explicitly setting the SchemaName instead of including it in the TableName:
sqlLayer.SchemaName = “gis”;
sqlLayer.TableName = “CampusAreaPointLocation_Test”;

If the SchemaName is not set then it will throw the same error.

Thanks,
Brandon Hennington

#18

Thanks Brandon,
Good to know this works. I am going to close this one. Go ahead let us know if you have any more question.

Thanks

Frank