How to parse ogc geometry(wkb) from mysql with copymemory method (visual basic)

I need to parse a wkb with copymemory. The wkb is described here:

http://mysql.mirror.netmonic.com/doc/mysql/en/GIS_WKB_format.html

rs.Open "SELECT AsBinary(address_loc) as address_loc from address where (address = 'street a')", conn

The data is then in rs.Fields(0).Value.

How can I parse the data to get x,y into doubles? (I just tried it without copymemory and converting the stuff into hex but that's not really fast)

thx red


redtiger889Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Erick37Commented:
I cannot test this because I do not have MySql, but this should get you started.  
Using CopyMemory will only only work if the ByteOrder is little-endian (reverse byte order).
Save your work before using CopyMemory!

Not sure if you can pass  rs.Fields(0).Value to CopyMemory, you may need to copy it to a byte array as in the example below or try:
ByVal VarPtr(rs.Fields(0).Value)

Option Explicit

'The WKB type to split out our array
Private Type WellKnownBinary
    'ByteOrder As Byte '(cant use single bytes because CopyMemory wants to be word aligned)
    Type As Long
    X As Double
    Y As Double
End Type

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Private Sub Command1_Click()

    Dim a(20) As Byte 'This is the WKB array
    Dim i As Long 'just a counter
    Dim wkb As WellKnownBinary
   
    For i = 0 To 20
        a(i) = i 'Fill in dummy data
    Next
   
    'Start reading from a(1) because we are skipping the ByteOrder byte
    Call CopyMemory(wkb, ByVal VarPtr(a(1)), LenB(wkb))
   
    Debug.Print Hex(wkb.Type)

End Sub
0
redtiger889Author Commented:
Unfortunately it's not working - the output is not correct  -  my code looks like that (without copymemory):

'strStream = rs.fields(0).value

Dim k As Long
Dim hexString As String

    For k = 1 To Len(strStream)
        hexString = hexString & Hex(Asc(Mid(strStream, k, 1)))
    Next k

    Debug.Print hexString
   
Dim X As Double
Dim Y As Double


'Debug.Print Left(hexString, 1) & "/" & Mid(hexString, 2, 4) 'order and type
'Debug.Print Mid(hexString, 6, 10) & "/" & Mid(hexString, 16, 10) 'x and y

0
redtiger889Author Commented:
Here's what I tried : I get 100 instead of 1000 (near). But the lenght of b() after b = rs.Fields(0).Value is 42. I think there's just a problem with the postion of x,y .... (???)

   Dim b() As Byte
       
    Dim wkb As WellKnownBinary
   
    Dim rs As New ADODB.Recordset
   
    conn.Open
   
    rs.Open "SELECT AsBinary(address_loc) as address_loc from address where (address = 'lindauergasse')", conn

    b = rs.Fields(0).Value
       
    conn.Close
   
    Call CopyMemory(wkb, ByVal VarPtr(b(1)), LenB(wkb))

    Debug.Print Hex(wkb.Type)

thx and greetings red
0
Erick37Commented:
If rs.Fields(0).Value is a string value, then when you assign to a byte array it will be twice as long because strings in VB are unicode (1 character = 2 bytes).  If you cannot get the data directly from the database as binary data, then you can convert the string to a byte array like this:

b = StrConv(rs.Fields(0).Value, vbFromUnicode)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
redtiger889Author Commented:
Thx a lot - you saved my evening.

red
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.