Loading Aerial Photos with ECW or TIF files

Hi

I have to load aerial photos onto my map. Previously I used Mr Sid files that were quite easy to load in MapObjects - I would read a database file that held 4 records with all the info to load the 4 Mr Sid files.
The client now uses ECW or TIF Files. I assume the ECW files are quicker to load as they are smaller.

I tried to use the ECW files but am getting the same error (It may be caused by Global Setting GdalPlugin Path pointing to wrong folder) that others have been getting. Is this still being worked on? I also tried it in MapWindow5 and got the same error. I then tried the tiff files - but even though I loop through the files, it only actually seems to load 1. The TIFF folder has tif files and tfw files - I assume these somehow indicate to the Map where the Aerial Photo tif files goes as it seems to put the one that is loaded in the correct place. Am I doing it correctly looping through all the files and how to I get more than 1 to load. I was also wondering if there is a way to only load files that are in the area you are viewing. I need the aerial photo to load quickly. The MrSid files would load in about 3 secs for the whole Map.

This is the code that I was using for both the ECW (that did not work) and tif files

Dim folderName As String
Dim FileName As String
Dim FSOLibrary As FileSystemObject
Dim FSOFolder As Object
Dim FSOFile As Object

folderName = “\w12efmgisphoto\AerialPhotography\2019\TIFF”

'Set all the references to the FSO Library
Set FSOLibrary = New FileSystemObject
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

For Each FSOFile In FSOFile
Dim h As Integer
Dim img As New MapWinGIS.Image
FileName = FSOFile.Name
h = -1
If right(FileName, 3) = “tif” Then
If img.Open(folderName & FileName, ImageType.TIFF_FILE, False) = True Then
h = Map1.AddLayer(img, True)
Else
MsgBox img.ErrorMsg(img.LastErrorCode)
End If
End If
Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing
Map1.Redraw

Thanks for any help

Regards

Colleen Crawford

Hello Colleen.

Unless you are using a Tile Server or some sort, only one image is loaded at a time. I presume the one image you are seeing is the last one in the loop. And yes, it should use the World file to help position the image.

Theoretically, MWGis supports the MrSid file format (although I’ve never tried it). Have you tried that? I believe you have to specify when installing the OCX that you want MrSid support (it may not be installed by default).

I’ll have to look into the ECW issue that you make mention of. I don’t know the status off-hand.

Jerry.

As far as I can tell, there has been no change on the ECW problem. I have suggested a potential solution on that ticket, and if someone happens to follow-up on it, there could be some motion toward a solution.

Thanks for this info. I can’t use MrSid, unfortunately, as the client has stopped using this. They would like me to use ECW, but they also have TIF files.

I will have to research tile servers as I don’t know anything about this. I found what I thought may be a TileServer so tried the following but it is coming back false with no error

Me.Map1.Projection = tkMapProjection.PROJECTION_NONE
Me.Map1.GrabProjectionFromData = True
Dim tProvider As New MapWinGIS.Tiles
Me.Map1.TileProvider = MapWinGIS.tkTileProvider.ProviderNone
Set tProvider = Me.Map1.Tiles
tProvider.DelayRequestTimeout = 600
If tProvider.Providers.Add(1030, “AerialPhoto”, “http://w8vmgis01.durban.gov.za/arcgis/services/WebViewers/AerialPhotography2019/ImageServer/{zoom}/{x}/{y}.png”, tkTileProjection.SphericalMercator, 0, 19) = False Then
MsgBox “Cannot Load Aerial Photo”
MsgBox tProvider.ErrorMsg(tProvider.LastErrorCode)
End If
tProvider.ProviderId = 1030

I will try and get more info about this from the client.

Is the above the correct way to load an aerial photo? It seems the {zoom}/{x}/{y}.png are required and {switch:a,b,c}.tile. is required if it switches between servers. I assume I find this out of the client.

When I look up ArcGIS I get the following

'http://{subDomain}.tile.opencyclemap.org/cycle/{level}/{col}/{row}.png

Now I am not sure if I am supposed to use {zoom}/{x}/{y}.png or {level}/{col}/{row}.png

Regards

Colleen Crawford

Hello Colleen.

I think you want to use the zoom/x/y, and the ocx substitutes the values before submitting it to the provider. Also, if you’re using a web-based tile service, you may also need to specify the projection as Google Mercator, since otherwise the tiles will not align properly. You can read more here.

What was the error you got back?

Hi

Thanks for your reply. With the above code it makes no difference to change the projection. The error is always ‘No Error’. I think there must be some error with the code as it did this even when I tried to open the OpenStreetMap tile server.

The following opens OpenStreetMap correctly for South Africa

Map1.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR
Map1.KnownExtents = tkKnownExtents.keSouth_Africa
Map1.ZoomBehavior = tkZoomBehavior.zbUseTileLevels
Map1.Tiles.Provider = tkTileProvider.OpenStreetMap

I then tried to open the OpenStreetMap tile server using the code from the help to test opening a custom provider.

Me.Map1.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR
Dim providers As New MapWinGIS.TileProviders
Dim providerID As Integer
Me.Map1.TileProvider = MapWinGIS.tkTileProvider.ProviderCustom
providerID = tkTileProvider.ProviderCustom + 1 ’ // (1024 + 1) should be unique across application runs in case disk caching is used
If providers.Add(providerID, “Custom TMS provider”, “https://{switch:a,b,c}.tile.openstreetmap.org/{zoom}/{x}/{y}.png”, tkTileProjection.SphericalMercator, 0, 18) = False Then
'If providers.Add(providerID, “AerialPhoto”, “http://w8vmgis01.durban.gov.za/arcgis/services/WebViewers/AerialPhotography2019/ImageServer/{zoom}/{x}/{y}.png”, tkTileProjection.SphericalMercator, 0, 18) = False Then
MsgBox “Cannot Load Aerial Photo”
MsgBox tProvider.ErrorMsg(tProvider.LastErrorCode)
End If
Map1.Redraw

Whether I used “Custom TMS provider” or “AerialPhoto” I get the same result, As soon as the app gets to the Me.Map1.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR line, it would load the American OpenStreetMaps map. I would search to 1 of my Roads and no OpenStreetMaps is loaded there. The same happened with PROJECTION_WGS84… When it reach the providers.add line, the American Map was already added and there was no error.

Not sure what to do from here

Regards

Colleen Crawford

Hello Colleen.

The primary problem is that you shouldn’t be creating a new TileProviders class, but using the one from the Map, as follows:

    Dim providers As TileProviders = AxMap1.Tiles.Providers

A secondary problem is that you’re setting a custom providerID (ProviderCustom + 1), but then never assigning the custom provider to the map. So after adding the custom provider, you need to do this:

    AxMap1.Tiles.ProviderId = providerID

The Open Street Map tiles work in this case. It’s admittedly confusing that the Map seems to load OSM by default if you assign an invalid provider, and that’s why OSM kept on displaying even though you weren’t assigning OSM as the provider.

As you may know, you can generally test a provider right from your browser, to see if it finds anything. I tried this with the OSM provider as follows (you have to guess a zoom level, x, and y; and x and y are screen coordinates, so their generally in the hundreds):

I tried a few things with the ‘durban’ provider and could not get any valid results. If I start back at ‘durban.gov.za’, I can get their website. From there, you may need to work your way down to the image server URL, and see if you can get a valid image in the browser.

Regards,
Jerry.

Hi

Thanks so much for the info. It is working now for OpenStreetMaps. I had put the AxMap1.Tiles.ProviderId = providerID in after I sent the previous mail, as I saw it in the documentation, but was still getting the USA map. I suppose nothing was going to work as I needed the first line.
I have tried to get information from the client but it seems they are all on leave. I suspect that the path is not correct as I did try it. Anyway, at least I have been able to load OpenStreetMaps, so once I get the correct info, it should work hopefully…

Regards

Colleen Crawford

Hi Jerry

Please could you look at the code again. I am flipping between 2 machines and I think I was looking at the one where I am using KnownExtents when I thought the SA map was showing.

Me.Map1.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR
Dim providers As TileProviders
Set providers = Me.Map1.Tiles.providers
Dim providerID As Integer
'Me.Map1.TileProvider = mapWinGIS.tkTileProvider.ProviderCustom
providerID = tkTileProvider.ProviderCustom + 1 ’ // (1024 + 1) should be unique across application runs in case disk caching is used
If providers.Add(providerID, “Custom TMS provider”, “https://{switch:a,b,c}.tile.openstreetmap.org/{zoom}/{x}/{y}.png”, tkTileProjection.SphericalMercator, 0, 18) = False Then
'If providers.Add(providerID, “AerialPhoto”, “http://w8vmgis01.durban.gov.za/arcgis/services/WebViewers/AerialPhotography2019/ImageServer/{zoom}/{x}/{y}.png”, tkTileProjection.SphericalMercator, 0, 18) = False Then
MsgBox “Cannot Load Aerial Photo”
MsgBox providers.ErrorMsg(providers.LastErrorCode)
End If
Me.Map1.Tiles.providerID = providerID

Me.Map1.Redraw

If I step through the code, this line -Me.Map1.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR - causes the USA map to be loaded before I have even setup the TileProviders

Thanks

Colleen

Good morning.

I looked through the OCX code, and it is not setting any predefined Extents (it sets it to keNone). It could be that the tile cache still has that area from an earlier run? You could try clearing the cache (but only do it one time, because you don’t want to rebuild the cache every time you run).

AxMap1.Tiles.ClearCache(tkCacheType.Disk)

You could also, at startup, deliberately set no provider, and it will hopefully wait until you specifically set things up.

AxMap1.TileProvider = tkTileProvider.ProviderNone

Regards.

Hi

I have done both of the above and it still displays the USA Map. There is only 1 shape file loaded at the time I try - Areas of eThekwini- I tried to display the projection using shp.GepProjection.Name and ProjectionName - both were blank. When this shape file is loaded all the x/y co-ordinates are displaying as expected.

This is code in my Map Load - had to do this to get all layers to show X/Y coords as some were loading and it changed to Lat and long. If I use PROJECTION_GOOGLE_MERCATOR it immediately loads the USA map on the load of the Map, even before I select to display OpenStreetMaps
.

’ Have to do this to make sure that Layers that have a different projection are converted to the same projection as OpEntity
With Map1
.Projection = tkMapProjection.PROJECTION_NONE
.GeoProjection = shpOpEntity.GeoProjection.Clone
'.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR ’ although .PROJECTION_NONE also worked - This causes USA map to load
.GrabProjectionFromData = False ’ you should set True if using PROJECTION_NONE - If I set to true it does not work
.SendMouseDown = True
.CursorMode = tkCursorMode.cmNone
End With
’ these are important for mismatched projections
Dim gs As GlobalSettings
Set gs = New GlobalSettings
gs.AllowProjectionMismatch = True
gs.ReprojectLayersOnAdding = True

I took out all the above code out and it made no difference to loading the OpenStreetMaps - I assume that I need at least 1 shape file loaded as it will use the position of this shape file to detect where to load the OpenStreetMaps.

I have also tried the following
If I put the Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR with the 2 lines you suggested as the very first thing when the Map loads. My Area shape file then loads in the correct area but it is about half the size as what it normally is - normally takes up whole Map area. NB: when I did this it was no longer automatically displaying the USA Map
If I put the Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR after the load of the Area shape file - a blank screen is displayed as it is at the wrong lat/long - it is at the USA lat and long. At this stage if I click to load the OpenStreetMaps, it opens the USA Map -If I click full extent then my Areas come into focus - If at this stage I click the selection to add OpenStreetMaps - I now get a plain blue background loading. I zoom out to find my Areas in the middle of the sea to the left of Africa at approx Lat -28, long -0.137 when OpenStreetMaps shows Durban far to the right at approx lat -30, lng +31.
Lat/Long of Durban if I search on the web show as -29.8579 31.0292 - so OpenStreetMaps is in correct position.

So, it really seems that there is some problem with my layer, but don;t know what it is as it shows the X/Y Coords correctly. I have checked against the app using Map Objects and the x/y coords are the same. This is very puzzling to me and I hope it can be sorted out as one of the main requirements is to display an aerial photo as the Users require this in order to do their work.

Here is a screen shot of the Map with my tiny Area map far to the left of where it should be (Durban) in the sea

Does anyone have any idea what I can do to solve this.

Thanks

Colleen Crawford

Hi

I have managed to get 1 step forward. None of the shape files have .prj files. I copied a .prj file over and now if I set the Projection to Google _mercator before loading the first shape file, it displays the areas at the correct lat/long and the OpenStreetMaps is in the correct place. However, all my drawing options- showing different colours for different areas just disappear and all areas are 1 randon colour. It is puzzling to me why changing the projection would result in the drawing options/categories not working.

Regards

Colleen Crawford

Hello Colleen.

MapWinGIS differs from ESRI in that ESRI allowed multiple projections in the same map, allowing each layer to maintain its projection. MapWinGIS puts everything into the same projection, being the assigned map projection.

You need your map projection to be Google Mercator because otherwise the imagery will not align properly (most all Tile services distribute tiles in the Google Mercator projection). So you want to set that first, and keep the settings that say AllowProjectionMismatch and ReprojectLayersOnAdding.

However, to properly reproject, it needs to know the projection of the layers, since the layers have coordinates that are in some projection. So you need to make sure the PRJ files are appropriate for the layers you are adding. Then once the layers are loaded, all layers (in memory) will be in the Google Mercator projection, but they should align properly.

As far as the rendering is concerned, are you re-applying the categories, etc, after the layers are reprojected? When using layers from an OGR datasource, I found that if any edits were made, and a layer had to be reloaded from the database, all rendering categories would have to be re-applied on the newly added layer.

Sorry, I was up too early with our sick old dog, and maybe shouldn’t have tried to answer.

To clarify, when a OGR Layer or Shapefile are reprojected, they effectively become a new Shapefile (in memory). So if you have a Shapefile variable sf, that you open and load, and it gets reprojected, then the recommended procedure is to re-assign sf, as follows:

sf = AxMap.Shapefile(lHandle)

Then use that Shapefile reference to set up your rendering categories and labels.

See section F of the Getting Started section. This c# example shows this condition.

new GlobalSettings() { AllowProjectionMismatch = false, ReprojectLayersOnAdding = true };
var sf = new Shapefile();
if (sf.Open(filename, null))
{
    //sf.GenerateLabels(0, tkLabelPositioning.lpCentroid); // don't call it here as labels may be lost
    int layerHandle = axMap1.AddLayer(sf, true);
    sf = axMap1.get_Shapefile(layerHandle); // grab the reprojected version of shapefile
    sf.GenerateLabels(0, tkLabelPositioning.lpCentroid); // now it's ok to generate labels
}

See if that is the issue.

Regards,
Jerry.

Hi Jerry

Thanks for your help. Hope your dog is OK.

I did the above as you said and I now get the different colours with the Area, but I am struggling with other shapefiles.

CODE TO LOAD BACKGROUND AREAS - this is done after I set the projection

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)
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

DIFFERENT THINGS I TRIED WITH PROJECTION

  1. With Map1
    .Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR ’ although .PROJECTION_NONE also worked - This causes USA map to load

.GrabProjectionFromData = False
End With
’ these are important for mismatched projections
Dim gs As GlobalSettings
Set gs = New GlobalSettings
gs.AllowProjectionMismatch = True
gs.ReprojectLayersOnAdding = True

Areas load to correct lat/long - OpenStreetMaps loads to correct place
Other layers will not load, and the gdal error displays a No Error. I get the same result if I change GrabProjectionFromData = true
Showing lat/long

2.With Map1

.Projection = tkMapProjection.PROJECTION_GOOGLE_MERCATOR ’ although .PROJECTION_NONE also worked - This causes USA map to load

.GrabProjectionFromData = True
End With
’ these are important for mismatched projections
Dim gs As GlobalSettings
Set gs = New GlobalSettings
gs.AllowProjectionMismatch = False
gs.ReprojectLayersOnAdding = True

Areas layer does not load ,but goes to correct lat/long - my Roads, and other shape files seem to load correctly at correct lat/long. However, ogrlayers do not load. I get an error as I am doing the AddLayerFromDatabase - returns -1 and the following error ;Latitude or longitude exceeded limits

’ LOADING OGR LAYER

’ Gets the Traffic Signal Shape Details
strSQL = “Select * from CablePtsShapeView where CPType = ‘T’”
Dim gs As New GlobalSettings
ConnStr = “MSSQL:server=” & TempVars(“ServerName”).Value & “;DATABASE=” & TrafSigCablesDatabase & _
“;UID=” & TrafSigCablesUserName & “;PWD=” & TrafSigCablesPasswrd
’ Adds an Ogr Layer from database
hndSignals = Map1.AddLayerFromDatabase(ConnStr, strSQL, True)
If hndSignals = -1 Then
MsgBox ("Failed to open layer: " + Map1.FileManager.ErrorMsg(Map1.FileManager.LastErrorCode))
’ in case the reason of failure is still unclear, get GDAL details
MsgBox ("Last GDAL error: " + gs.GdalLastErrorMsg)
SignalsInd = “I”
MsgBox (“This Signal layer is invalid. Please speak to your System Supervisor”)
Me.DispSignals = False
Exit Sub
End If
’ Move the Layer to a Shape file so you can use Shapefile Categories
Set OgrSignalsLyr = Map1.OgrLayer(hndSignals)
Set shpSignals = OgrSignalsLyr.GetBuffer()

Set shpSignals = Map1.ShapeFile(hndSignals) ’ Not sure if this is required again, is the above line OK?

  1. Same as above but I set .GrabProjectionFromData = false - Areas does not load, goes to wrong lat/long. No other shape files load, ogr layers don’t load and get gdal - no error. When loading OpenStreetMaps get USA

RE: OGR Layer - I am not sure where to get the projection for a shape file stored on an sql database - I would assume it must be stored in the shape value somewhere.

Regards

Colleen Crawford

Hello Colleen.

When you created/added Spatial capabilities to your SQL database, how did you do it? Did you do it through GDAL, perhaps ogr2ogr, or just add the geometry field? The reason I ask is that in my case, since the database already existed, I just needed to add the ogr_fid and ogr_geometry fields. But there are metadata tables that are also required, and I needed to add and populate these tables myself.

If you read this article, it describes the 2 metatables that are expected by a GDAL datasource, and this includes the projection that is applied to the geometry for each table. If your tables exist, then this is where you can check out what projection the database is applying to your geometry.

Here is a partial screenshot of the dbo.geometry_columns table for one of our databases. I would guess that you likely have these tables since I’m not sure if GDAL will work without them. But check it out.
image

I will look more at your code and reply separately.

Regards,
Jerry.

Hello again.

I think this is the setup you want.

  1. You want Google Mercator so that the imagery will align properly.
  2. Once you 've set the projection, I think it does not matter whether GrabProjectionFromData is True or False since that setting will only apply for the first layer, if there is not already a projection. But you are already setting the projection to Google Mercator.
  3. You want to allow projection mismatch in order to get all layers added. You should also consider AllowLayersWithIncompleteReprojection = True, which allow ‘some’ records to fail reprojection, but still load the layer. Otherwise the entire layer would be rejected even for 1 bad record.
  4. You don’t need to do the following, and in fact, I think that call would fail. If my recollection is correct, an OGR layer remains an OGR layer in the map. When you call GetBuffer, it creates a Shapefile version of the OGR layer, in memory, which you can use for custom rendering purposes, as you are doing. But I think that if you try to fetch the layer from the map, by LayerHandle, as a Shapefile, it would fail since an OGR layer does not support the IShapefile interface. So you need two distinct references, as you have, OgrSignalsLay, and shpSignals.

So is it just the OGR layers now that are failing to load? Let me know what you discover about their projection. Theoretically, they should reproject on-the-fly, just as Shapefile would.

Regards,
Jerry.

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

Hello Colleen.

I don’t have time right now to address everything here, but we’ll start with the SQL tables.

In your first case, you say that you don’t have the spatial metatables, correct? If so, I’m quite confident that that’s the reason you get the Lat/Long exceeding limits. It would normally reproject the data, but since it has no spatial reference, it is instead trying to use the shapes as Google Mercator, and the coordinates are inappropriate. I would say that you need to add those tables and specify that the coordinates are in SRID 32735. I had written a conversion program for a customer that creates and populates the metatables, and converts the X, Y coordinates to geometry fields. I can’t share much of this code because it is proprietary, but I may be able to glean some of it for you, if that would help.

Regarding your second case, it doesn’t surprise me that ESRI does things in a more proprietary way. I can’t say for sure if the table name of SDE_geometry_columns would result in the GDAL OGR library being unable to load them, unless you specify that your OGR datasource is an ESRI datasource. Even so, I am perplexed by the field values for SRID, which appear to be only 2-digit values, and many different values, whereas I would expect to see something like 32735. I don’t know if that is why they are not loading in the correct place. What I have to do in these cases is step through the debugger. I have set up a debug build of both GDAL and the OCX, so that I can try to figure out where things are breaking down. I had to do this when first setting up SQL Server as my datasource. I realize, of course, that this may not be an option for you.

Regards, for now.
Jerry.