How to Decode and Interpret Spatial Data Encoded in Hexadecimal Format (SRID=4326) Using PHP

I'm working with spatial data stored in a SQLite database, and I need help parsing and interpreting the data encoded in hexadecimal format with SRID=4326. The geometry type is described as [POLYGON, XY, SRID=4326].

Here's a sample hexadecimal representation of the spatial data: 0001e61000006acf313a5111464092eae373eb9640405fbb039ef0254640353a492fb9ab40407c030000000100000022000000d0b02b40ee194640353a492fb9ab40405fbb039ef02546404c39fd643d994040ea9d859fcf244640d957040691984040a2fc5e159a234640eb79d71100984040b4fc82a953224640fec95f3f8c97404067779d3800214640001545ed369740405fb273c6a31f4640e34ed01d01974040b871d371421e464092eae373eb96404072933368e01c46409ed41231f69640406b272ad9811b4640219cdb3421974040c260dce92a1a464094050afd6b974040b5cc8ca8df18464012ec3da7d5974040e2ef6b00a41746409b0293f35c984040a4c5cead7b164640d5bd614800994040cd9ded326a154640157a0eb7bd994040ce7c4ccd72144640c8d7d801939a40402471ec6b981346408d529aa27d9b4040a44963a6dd1246409b4d60d27a9c40403cbbf4b444124640bc39cb91879d4040aa56c569cf114640381e1ab2a09e404060b13c2b7f1146401bb6c7dec29f404013dfa8ef541146402f849ca7eaa040406acf313a51114640abc1178b14a24040d35d2719741146405be20f013da34040c5e8b025bd114640349b6a8560a44040b223e1842b124640c4e8cba27ba540400c982ceabd1246404c861bfd8aa6404006fa3c9b72134640659dc05b8ba740407124177547144640743274b379a840404a4a85f2391546403df38b2f53a9404025c5b233471646403a90a03a15aa40409fede2066c1746405eac7486bdaa404020ae28f2a4184640e5ab03134aab4040d0b02b40ee194640353a492fb9ab4040fe

I would appreciate guidance on how to decode and interpret this hexadecimal data using PHP, considering the spatial reference system and units in degrees (SRID=4326). Specifically, I need to understand how to extract meaningful spatial information from this encoded data. Any code examples or insights would be extremely helpful!

<?php
require_once('vendor\geoPHP\geoPHP.inc');

$hexData = '0001e61000006acf313a5111464092eae373eb9640405fbb039ef0254640353a492fb9ab40407c030000000100000022000000d0b02b40ee194640353a492fb9ab40405fbb039ef02546404c39fd643d994040ea9d859fcf244640d957040691984040a2fc5e159a234640eb79d71100984040b4fc82a953224640fec95f3f8c97404067779d3800214640001545ed369740405fb273c6a31f4640e34ed01d01974040b871d371421e464092eae373eb96404072933368e01c46409ed41231f69640406b272ad9811b4640219cdb3421974040c260dce92a1a464094050afd6b974040b5cc8ca8df18464012ec3da7d5974040e2ef6b00a41746409b0293f35c984040a4c5cead7b164640d5bd614800994040cd9ded326a154640157a0eb7bd994040ce7c4ccd72144640c8d7d801939a40402471ec6b981346408d529aa27d9b4040a44963a6dd1246409b4d60d27a9c40403cbbf4b444124640bc39cb91879d4040aa56c569cf114640381e1ab2a09e404060b13c2b7f1146401bb6c7dec29f404013dfa8ef541146402f849ca7eaa040406acf313a51114640abc1178b14a24040d35d2719741146405be20f013da34040c5e8b025bd114640349b6a8560a44040b223e1842b124640c4e8cba27ba540400c982ceabd1246404c861bfd8aa6404006fa3c9b72134640659dc05b8ba740407124177547144640743274b379a840404a4a85f2391546403df38b2f53a9404025c5b233471646403a90a03a15aa40409fede2066c1746405eac7486bdaa404020ae28f2a4184640e5ab03134aab4040d0b02b40ee194640353a492fb9ab4040fe';


    $hexData = str_replace(["\n", "\r", " "], '', $hexData);
    
    // Convert the hex to binary
    $binaryData = pack('H*', $hexData);
    
    // Parse the WKB data
    $geom = geoPHP::load($binaryData, 'wkb');
    
    // Get the coordinates
    $coordinates = $geom->getCoordinates();
    
    // Print the coordinates
    print_r($coordinates);
    
    ?>

return

Fatal error: Uncaught Exception: Only NDR (little endian) SKB format is supported at the moment in C:\xampp\htdocs\vendor\geoPHP\lib\adapters\WKB.class.php:52 Stack trace: #0 C:\xampp\htdocs\vendor\geoPHP\lib\adapters\WKB.class.php(44): WKB->getGeometry(Resource id #24) #1 C:\xampp\htdocs\vendor\geoPHP\geoPHP.inc(78): WKB->read('\x00\x01\xE6\x10\x00\x00j\xCF1:Q\x11F@\x92...') #2 C:\xampp\htdocs\notam.php(13): geoPHP::load('\x00\x01\xE6\x10\x00\x00j\xCF1:Q\x11F@\x92...', 'wkb') #3 {main} thrown in C:\xampp\htdocs\vendor\geoPHP\lib\adapters\WKB.class.php on line 52

Solution

You are accessing an SQLite table which contains spatial data. The table was created with SpatiaLite (an SQLite spatial extension).

In theory, you should manipulate the data through specialized SQL functions. For example, to retrieve the exterior of a polygon:

SELECT ExteriorRing(geometry) FROM ff_controlled_airspace

However, your PHP setup probably doesn't have SpatiaLite enabled, so you can't use those functions and you need to deal with the raw binary data.

Fortunately, the data format is described here. The first paragraph says:

SpatiaLite internally stores geometry values using ordinary SQLite's BLOB columns in a format that is very closely related to WKB format, but not exactly identical.

Since the format is not exactly WKB, you can't use a WKB parser. However, it's not very hard to write your own parser. Here is a way to do it:

function getPolygonCoords($data)
{
    // Convert from hex to binary
    $data = hex2bin($data);

    // Some validity checks
    if($data[0] != "\x00")
        throw new Exception('Invalid data');
    if($data[1] != "\x01")
        throw new Exception('Unsupported endianness');
    if($data[38] != "\x7C")
        throw new Exception('Invalid MBR_END');

    // Check that the class type is POLYGON
    $a = unpack('Vclass', $data, 39);
    if($a['class'] != 3)
        throw new Exception('No polygon found');

    // Number of points
    $a = unpack('Vlen', $data, 43 + 4);
    $len = $a['len'];

    // Point coordinates
    $coords = [];
    for($i=0;$i<$len;$i++)
        $coords[] = unpack('elon/elat', $data, 43 + 8 + 16*$i);

    return $coords;
}

$data = '0001e61000006acf313a5111464092...';
$coords = getPolygonCoords($data);
var_export($coords);

Output:

array (
  0 => 
  array (
    'lon' => 44.20258333333334,
    'lat' => 33.341588888888886,
  ),
  1 => 
  array (
    'lon' => 44.29640555555556,
    'lat' => 33.19718611111111,
  ),

[...]

  32 => 
  array (
    'lon' => 44.192533750403754,
    'lat' => 33.33819806746741,
  ),
  33 => 
  array (
    'lon' => 44.20258333333334,
    'lat' => 33.341588888888886,
  ),
)