ArcSDE and SharpMap

Topics: Data Access
Apr 19, 2007 at 3:48 PM
Is there a way to query ArcSDE with SharpMap?
Apr 20, 2007 at 1:58 PM
I don't think there's anything already out there but the architecture of SharpMap is such that it should be fairly easy to plug in a new data provider. Check out the SharpMap\Data\Providers directory for examples.

-Abe
Programmer for hire.
abe@digital-pulp.com
http://code.google.com/p/ziggis/
May 3, 2007 at 1:29 PM
I think that a good choice is with OGR. You need to try with GDAL compiled with the ArcSDE pluggin. Another choice is to develop a new data provider using the ArcSDE c api.

Carlos
May 4, 2007 at 3:47 AM
A better approach is just to use the MsSql or Oracle namespaces, read the SDE docs on ESRIs website and rewrite the MsSqlSpatial/PostGIS dataprovider to an SDE provider. It would a lot more efficient than going through OGR, pure managed code, and the fact that SDE uses compressed geometry would most likely make it faster than MsSqlSpatial and PostGIS (there's actually a lot of overhead in extracting binary datastreams, compared to having to uncompress them)
Coordinator
Jun 20, 2007 at 12:08 AM
Edited Jun 20, 2007 at 12:08 AM
Odegaard -

That sounds great. Are you volunteering a contrib? ;)
Aug 22, 2007 at 7:58 PM
I've started looking into this a little bit and found something interesting. I added a vb function below that decodes ArcSDE binary into an object with point count and x array, y array. I'm not up to speed on the internals of SharpMap just yet but it might help someone make a new data converter. I've also added the SQL select statement you need to access the parameters you need to pass in. It should work for ArcSDE 9.X

SQL to get layer info:

"SELECT SDElayers.*, SDEgeometrycolumns.gtable_name, " & _
"SDEspatialreferences.falsex, SDEspatialreferences.falsey, " & _
"SDEspatialreferences.xyunits " & _
"FROM SDElayers INNER JOIN (SDEgeometrycolumns INNER JOIN SDEspatial_references " & _
"ON SDEgeometrycolumns.srid = SDEspatialreferences.srid) ON SDElayers.tablename = " & _
"SDEgeometrycolumns.ftablename WHERE table_name = 'LayerName'"

Then select the records you want and decode

Public Class SDEBinaryObject
Public CoordCount As Integer = 0
Public X() As Double
Public Y() As Double
End Class

Public Function ReadBinary(ByVal FalseX As Double, ByVal FalseY As Double, ByVal XYUnit As Double, ByVal SDEBinary() As Byte, ByVal PointCount As Integer) As SDEBinaryObject
Dim i As Integer
Dim BitData As String
Dim TempBitData As String
Dim StoredInt As UInt64

'create new object
ReadBinary = New SDEBinaryObject
ReDim ReadBinary.X(PointCount - 1)
ReDim ReadBinary.Y(PointCount - 1)

'parse co-ordinates
BitData = ""
Dim ByteCount As Integer = 0
Dim Sign As Integer = 1

Dim X As Double
Dim Y As Double
Dim LastX As Double
Dim LastY As Double
Dim ProcessX As Boolean = True
Dim StartCoord As Boolean = True

For i = 8 To SDEBinary.Length - 1
If ReadBinary.CoordCount >= PointCount Then
'oops? there is more binary data than points. exit
Exit For
End If

ByteCount += 1

'convert bit data to string array
TempBitData = Convert.ToString(CInt(SDEBinary(i)), 2).PadLeft(8, "0")

If ByteCount = 1 Then
'check sign bit
If Mid(TempBitData, 2, 1) = "1" Then Sign = -1 Else Sign = 1
BitData = Right(TempBitData, 6) & BitData
Else
BitData = Right(TempBitData, 7) & BitData
End If

'check control bit
If TempBitData.StartsWith("0") Then
'convert bit data back to integer
StoredInt = Convert.ToInt64(BitData, 2)

'convert integer back to world coordinates
If ProcessX = True Then
'check if it's the start coordinate
If StartCoord = True Then
X = (StoredInt / XYUnit) + FalseX
Else
X = ((StoredInt / XYUnit) * Sign) + LastX
End If
ReadBinary.X(ReadBinary.CoordCount) = X
LastX = X
Else
'check if it's the start coordinate
If StartCoord = True Then
Y = (StoredInt / XYUnit) + FalseY
StartCoord = False
Else
Y = ((StoredInt / XYUnit) * Sign) + LastY
End If
ReadBinary.Y(ReadBinary.CoordCount) = Y
LastY = Y
ReadBinary.CoordCount += 1
End If

'reset count
ByteCount = 0
BitData = ""
If ProcessX = True Then ProcessX = False Else ProcessX = True
End If
Next
End Function
Aug 29, 2007 at 12:46 AM
You could also look at using the FDO provider for ArcSDE as a basis for a SharpMap data provider. http://fdo.osgeo.org/

Bill Dollins
bill@zekiah.com
Mar 1, 2008 at 8:23 PM
Okay, I've created an alpha version of a provider (implements IProvider) for ArcSde Compressed Binary format. Is there still any interest here for that? If so, I'll tidy it up and submit it.
Mar 1, 2008 at 9:50 PM
Absolutely, an ArcSDE provider would be a great addition.

Thanks for your effort.
Developer
Mar 2, 2008 at 7:01 AM
Definitely. We are way behind on the patch reviews though, but please submit it so that it won't be lost.
Mar 3, 2008 at 1:44 PM
I have a definite need for an SDE provider.
Mar 11, 2008 at 5:13 AM
I'll admit that the level of complexity here is a little more than what I was expecting.

At this point I have an ArcSDE IProvider, or the beginning of one. Actually, I've extended the SharpMap.Converters by adding a Compressed Binary object, and so far it's only one-way: CompressedBinary to Geometry. I've got it doing a good job of decoding multipolygon layers with- and without donut holes. I've also tested these against SDE 8.3 and 9.1. I'm assuming that the byte stream format won't change in 9.2 and beyond.

So right now, my main problem is performance: it's abysmal. I don't yet know where the bottlenecks are, but I suspect my Oracle connection. Once that's tackled, I need to test with the remaining geometry types. Then make the Conversion going the other way (geometry into Sde).

I'm also challenged about the *best way to handle all the Sde metadata and associated lookups. I don't want to compile schema, table, column names, but I'm also very lazy and don't want to be forced to create all those objects for each call. So I'm toying with an idea for handling that in a fairly efficient way.

I suppose there's no burning desire or need for this thing outside of my own little Esri-hating bubble; just wanted to check in and let you (3) guys know that I'm still working on this thing full-time.

Cheers,
Scott
Coordinator
Mar 11, 2008 at 4:07 PM
Great update Scott...

Your effort is definately appreciated by all.

Is anyone going to the ESRI Dev Conference? Perhaps we could get a bit more information about the byte stream format of upcoming server versions there.
Developer
Apr 7, 2008 at 2:21 PM
i'm interested to help you to developing an SDE provider for SharpMap: could you share some code, so I could start to take a look?
May 22, 2008 at 12:24 AM
I've looked at the code above but am wondering if anything happened with this? Searching around the site I was hoping to see some other reference to arcsde and sharpmap but this is the only thread?