Search code examples
phpsqlitebinarygeometry

decoding binary data from SQLite database in PHP Geometry Blob Binary

I'm currently facing an issue decoding binary data retrieved from an SQLite database in PHP. I'm attempting to extract geometry coordinates. The leading number of coordinates is a 32-bit unsigned integer and the ordinates are 32-bit floating point numbers (single precision).

enter image description here

Here's a simplified version of the relevant code:

<?php

function decodeCoordinatesFromByteArray($byteArray) {
    $coordinates = [];

    $offset = 0;
    $arraySize = count($byteArray) / 4; // Each coordinate is 4 bytes

    for ($i = 0; $i < $arraySize; $i++) {
        // Extract the 4 bytes for each coordinate
        $rawCoordinate = array_slice($byteArray, $offset, 4);

        // Unpack the bytes into a 32-bit unsigned integer
        $unsignedInt = ($rawCoordinate[0] << 24) + ($rawCoordinate[1] << 16) + ($rawCoordinate[2] << 8) + $rawCoordinate[3];

        // Interpret the 32-bit unsigned integer as a 32-bit floating point number
        $floatValue = unpack('f', pack('I', $unsignedInt))[1];

        // Add the floating point number to the coordinates array
        $coordinates[] = $floatValue;

        // Move the offset to the next 4 bytes
        $offset += 4;
    }

    // Group the coordinates into pairs (latitude and longitude)
    $coordinatesPairs = array_chunk($coordinates, 2);

    return $coordinatesPairs;
}



// Replace these with your actual database and table details
$dbFile = 'little_navmap_navigraph.sqlite';
$tableName = 'boundary';
$column = 'geometry';

// Filter condition based on ZNAME column
$filterColumnName = 'name';
$filterValue = 'BAGHDAD CTR WEST'; // Replace with the desired value

// Open the SQLite database
$db = new SQLite3($dbFile);

// Prepare and execute the query to retrieve the blob data with a filter
$query = $db->prepare("SELECT $column FROM $tableName WHERE $filterColumnName = :filterValue");
$query->bindValue(':filterValue', $filterValue, SQLITE3_TEXT); // Use SQLITE3_TEXT for string types
$result = $query->execute();

// Fetch and decode the result for all rows that meet the condition
$coordinatesArray = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
    $blobData = $row[$column];
    
    // Convert the blob data to a byte array
    $byteArray = unpack('C*', $blobData);

    // Debug: Output the byte array for inspection
    echo "Byte Array: ";
    var_dump($byteArray);

    // Decode the byte array into latitude and longitude pairs
    $decodedCoordinates = decodeCoordinatesFromByteArray($byteArray);

    // Debug: Output the decoded coordinates for inspection
    echo "Decoded Coordinates: ";
    var_dump($decodedCoordinates);

    // Add the coordinates to the array
    $coordinatesArray[] = $decodedCoordinates;
}


// Close the database connection
$db->close();

// Return the coordinates as JSON
echo json_encode($coordinatesArray, JSON_PRETTY_PRINT);
?>

data excepting is

 [
    [33.55443,44.56787],
    [33.23456,44.56985],
    [33.33333,44.43244],
    [33.67900,44.12342],
];

I've been referencing an approach used in the binary geometry.h file from the albar965/atools repository on GitHub (binarygeometry.h) and binarygeometry.cpp as a model for what I'm trying to achieve.

I've encountered challenges in replicating this functionality in PHP and need guidance. Specifically, I'm looking to interpret latitude and longitude pairs from the byte array retrieved from the database, similar to the way it's done in the referenced C++ file.

exmple :

62706c6973743030d401020304050607
0a582476657273696f6e592461726368
697665725424746f7058246f626a6563
747312000186a05f100f4e534b657965
644172636869766572d1080954726f6f
748001a50b0c1e222a55246e756c6cda
0d0e0f10111213141516171818191a1b
181b1c1d5624636c6173735f101c5549
42657a696572506174684c696e654a6f
696e5374796c654b65795f1023554942
657a696572506174684c696e65446173
685061747465726e436f756e744b6579
5f1019554942657a696572506174684d
697465724c696d69744b65795f101955
4942657a696572506174684347506174
68446174614b65795f101c554942657a
696572506174684c696e654461736850
686173654b65795f101b554942657a69
6572506174684c696e65436170537479
6c654b65795f1018554942657a696572
506174684c696e6557696474684b6579
5f1017554942657a6965725061746846
6c61746e6573734b65795f1022554942
657a6965725061746855736573457665
6e4f646446696c6c52756c654b657980
04100022412000008002220000000022
3f19999a08d21f0d2021574e532e6461
74614f1104380000000001000000562e
5043f063924401000000010000007d2e
5043f563924401000000010000005c28
5043b864924401000000010000008521
5043626592440100000001000000131a
5043f065924401000000010000002312
50435e6692440100000001000000d509
5043ad66924401000000010000004a01
5043d96692440100000001000000a4f8
4f43e4669244010000000100000004f0
4f43cc66924401000000010000008ce7
4f4392669244010000000100000060df
4f433766924401000000010000009ed7
4f43bc659244010000000100000065d0
4f43236592440100000001000000d3c9
4f436e649244010000000100000000c4
4f43a1639244010000000100000005bf
4f43bf6292440100000001000000f4ba
4f43ca6192440100000001000000dfb7
4f43c76092440100000001000000d0b5
4f43ba5f92440100000001000000d1b4
4f43a75e92440100000001000000e6b4
4f43925d924401000000010000000eb6
4f437f5c9244010000000100000044b8
4f43735b9244010000000100000080bb
4f43725a92440100000001000000b5bf
4f43805992440100000001000000d3c4
4f43a05892440100000001000000c4ca
4f43d6579244010000000100000073d1
4f43265792440100000001000000c3d8
4f4391569244010000000100000099e0
4f431b5692440100000001000000d5e8
4f43c5559244010000000100000056f1
4f43905592440100000001000000fbf9
4f4382559244010000000100000013fb
4f436b559244010000000100000042fd
4f43d6559244010000000100000069ff
4f434156924401000000010000008701
5043ae56924401000000010000009d03
50431b5792440100000001000000aa05
5043885792440100000001000000ae07
5043f65792440100000001000000aa09
50436558924401000000010000009d0b
5043d55892440100000001000000870d
5043455992440100000001000000690f
5043b659924401000000010000004111
5043275a924401000000010000001113
5043995a92440100000001000000d814
50430b5b924401000000010000009616
50437e5b924401000000010000004a18
5043f15b92440100000001000000f619
5043655c92440100000001000000981b
5043da5c92440100000001000000321d
50434f5d92440100000001000000c21e
5043c45d924401000000010000004920
50433a5e92440100000001000000c621
5043b15e924401000000010000003b23
5043285f92440100000001000000a624
50439f5f924401000000010000000726
50431760924401000000010000006027
50438f6092440100000001000000ae28
5043086192440100000001000000f429
50438161924401000000010000002f2b
5043fa6192440100000001000000622c
50437462924401000000010000008a2d
5043ee6292440100000001000000a92e
5043696392440100000001000000562e
5043f063924404000000000000008003
d2232425265a24636c6173736e616d65
5824636c61737365735d4e534d757461
626c6544617461a32728295d4e534d75
7461626c6544617461564e5344617461
584e534f626a656374d223242b2c5c55
4942657a69657250617468a22d295c55
4942657a696572506174680008001100
1a00240029003200370049004c005100
530059005f0074007b009a00c000dc00
f8011701350150016a018f0191019301
98019a019f01a401a501aa01b205ee05
f005f5060006090617061b0629063006
39063e064b064e000000000000020100
0000000000002e000000000000000000
0000000000065b
Solution

  • l have fixed by

    function decodeCoordinatesFromByteArray($byteArray) {
    $coordinates = [];
    
    // Extract the leading number of coordinates (4 bytes for the unsigned integer)
    $numCoordinatesBytes = array_slice($byteArray, 0, 4);

    // Convert bytes to little-endian representation for unpacking
    $numCoordinatesBytes = implode('', array_reverse(array_map('chr', $numCoordinatesBytes)));

    // Unpack the bytes into a 32-bit unsigned integer
    $numCoordinates = unpack('V', $numCoordinatesBytes)[1];

    for ($i = 0; $i < $numCoordinates; $i++) {
        // Extract the 8 bytes for each coordinate (4 bytes for each float)
        $startIndex = 4 + $i * 8; // Skip the first 4 bytes used for the number of coordinates
        if (isset($byteArray[$startIndex + 7])) {
            $rawCoordinates = array_slice($byteArray, $startIndex, 8);

            // Convert bytes to little-endian representation for unpacking
            $rawCoordinates = implode('', array_reverse(array_map('chr', $rawCoordinates)));

            // Unpack the bytes into two 32-bit floats (latitude and longitude)
            $floatValues = unpack('f2', $rawCoordinates);

            // Add the values to the coordinates array
            $coordinates[] = $floatValues[2]; // Longitude
            $coordinates[] = $floatValues[1]; // Latitude
        }
    }

    // Group the coordinates into pairs (longitude and latitude)
    $coordinatesPairs = array_chunk($coordinates, 2);

    return $coordinatesPairs;
}

    This modified function first unpacks the leading 4 bytes into a 32-bit unsigned integer to get the number of coordinates, then proceeds to unpack the rest of the bytes into 32-bit floats for the latitude and longitude values.