Hi Jerry
In my SQL 2014 database I do not have those tables. I just created a geometry column for the shape and used the X/Y Coords to run SQL code to populate it
Update LocationInfo Set LocnPtShape = geometry::STPointFromText(‘POINT(’+ str([x_Coord]) + ’ ’ + str([y_Coord]) + ‘)’, 32735)
I found somewhere that our SRID is 32735
This used to work well before I changed the Projection to Mercator
Now, when all the shape files are in the correct position and I try to load a layer from the SQL 2014 database I get the gdal error - Latitide or Longitude exceeded limits
This is an eg of what is in the LocnPtShape field
0xDF7F0000010C0000000000A893C000000080891649C1
With regards to the shape files I have the following
With Map1
.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR ’ although .PROJECTION_NONE also worked - This causes USA map to load
.GrabProjectionFromData = True ’ you should set True if using PROJECTION_NONE - If I set to true it does not work
.SendMouseDown = True
.SendMouseMove = True
.CursorMode = tkCursorMode.cmNone
End With
’ these are important for mismatched projections
Dim gs As GlobalSettings
Set gs = New GlobalSettings
With gs
.AllowProjectionMismatch = False
.ReprojectLayersOnAdding = True
.AllowLayersWithIncompleteReprojection = True
End With
Dim shpOpEntity As New mapwingis.Shapefile
’ Routine that returns the shape file
Set shpOpEntity = GetShapeFile(“OperationalEntitySQL”, hndOpEntity, “”)
If ValidLayer = True Then
’ Indicates Valid, I = invalid
OpEntityInd = “V”
Dim fieldIndex As Long
fieldIndex = shpOpEntity.Table.FieldIndexByName(OpEntityName)
If hndOpEntity = 0 Then
hndOpEntity = Map1.AddLayer(shpOpEntity, True)
If hndOpEntity = -1 Then
MsgBox gs.GdalLastErrorMsg
End If
End If
Set shpOpEntity = Me.Map1.Shapefile(hndOpEntity)
shpOpEntity.Categories.Generate fieldIndex, tkClassificationType.ctUniqueValues, 7
Dim scheme As New ColorScheme
scheme.SetColors2 tkMapColor.LightBlue, tkMapColor.LightPink
shpOpEntity.Categories.ApplyColorScheme tkColorSchemeType.ctSchemeGraduated, scheme
shpOpEntity.DefaultDrawingOptions.Visible = True
Map1.ZoomToLayer (hndOpEntity)
Map1.Redraw
End If
This first background OpEntity does not load correctly, but all shape files after this load correctly in the correct place with correct x/y Coords when I convert using PixelToProj. The Sql Database layers still do not load. I think it is something to do with the first layer loaded as I change this to be another Layer and it also does not load, but of I click a checkbox to load the layer it loads fine.
As soon as I change .AllowProjectionMismatch = True - the opentity layer loads fine as well as most other shapefiles, but the x/y coords are very strange - The SQL Database layers still do not load.
If I change .GrabProjectionFromData = False and .AllowProjectionMismatch = False, then USA map loads
DATABASE
I have 2 different scenarios - On my TEST machine I use shape files to load most Layers but have my own shape files that I create on an SQL 2014 database as above
Then on the LIVE machine instead of loading the other layers from shape files, I am loading from an ESRI type SQL database (SDE) It has 2 different databases sde and cgis. The sde database has the files I need to load as well as the dbo.geometry_columns table. but it is called sde.SDE_geometry_columns . The layer tables are prefixed by ldr.
I have been loading in the normal way using OGR and the layers load but seem to be slower than the shape files with the same data. However, I cannot get them to load in the correct place like the shape files as it is obviously not finding the projection data as it does with the .prj file
Dim gs As New GlobalSettings
Select Case DbaseType
Case “SQL”
ConnStr = “MSSQL:server=” & TempVars(“ServerName”).Value & “;DATABASE=” & CurrentDatabaseName & _
“;UID=” & AccSuiteUpdateUserName & “;PWD=” & AccSuitePasswrd
strSQL = "Select * from " & rs!LayerFileName
Case “sde”
ConnStr = “MSSQL:server=” & GISServer & “;DATABASE=” & GISDatabase & _
“;UID=” & GISUser & “;PWD=” & GISPassword
strSQL = “Select * from ldr.” & rs!LayerFileName
End Select
’ Adds an Ogr Layer from database
hnd = Forms!DisplayGisMap.Map1.AddLayerFromDatabase(ConnStr, strSQL, True)
If hnd = -1 Then
MsgBox ("Failed to open layer: " + Forms!DisplayGisMap.Map1.FileManager.ErrorMsg(Forms!DisplayGisMap.Map1.FileManager.LastErrorCode))
’ in case the reason of failure is still unclear, get GDAL details
MsgBox ("Last GDAL error: " + gs.GdalLastErrorMsg)
ValidLayer = False
MsgBox (“This Layer is invalid. Please speak to your System Supervisor”)
GoTo Exit_GetShapeFile
End If
Set ogrLyr = Forms!DisplayGisMap.Map1.OgrLayer(hnd)
’ Move the Layer to a Shape file so you can use Shapefile Categories
Set ShpFile = ogrLyr.GetBuffer()
’ Don’t display this layer until the drawing options have been applied
ShpFile.DefaultDrawingOptions.Visible = False
’ Return the shape file
Set GetShapeFile = ShpFile
ValidLayer = True
So the following happens on the live machine
.AllowProjectionMismatch = True and .AllowProjectionMismatch = False - can’t load first layer (I am doing this automatically on Form_load event and wonder if this might not be the problem, maybe the Map is not yet initialised or something - I will look at this as well) - sde layers all load with correct x/y coords but in the incorrect place for OpenStreetMaps.Sql 2014 layers load correctly
.AllowProjectionMismatch = False and .AllowProjectionMismatch = True and .AllowProjectionMismatch = True and .AllowProjectionMismatch = True - Does not load first layer,all other sde layers load and SQL 2014 layers load, all with correct x/y coords but in wrong place - Open Street Maps loads but far to the right
I’m assuming I get different results here as it cannot do a proper reprojection as there is no projection data
Regards
Colleen