[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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


0
redtiger889
Asked:
redtiger889
  • 3
  • 2
1 Solution
 
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
 
redtiger889Author Commented:
Thx a lot - you saved my evening.

red
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now