Conversion of MySQL binary GEOMETRY fields in PHP

Question

I’m working on an application integrating mapquest maps and using the mapquest API.

At this moment I want to achieve the following: the registered user has to input latitude and longitude information, and in the database this information has to be stored as geometry type field. Conversely, a user looking at their account should see the latitude and longitude information that was entered.

In an earlier simplified codebase I achieved this directly in the MySQL query, by using the MySQL functions AsText and GeomFromText. But now I’m on CodeIgniter and need to do the conversion in PHP

Is there anything equivalent in PHP to MySQL's AsText and GeomFromText functions?

Answer 1

I, too, am trying to extract geometry field data.I am using the MySQLi class to retrieve rows of data and display it according to field type.

While I haven't yet figured out how to decode the geometry field data, I can view it by using the bin2hex function. A point stored using GeomFromText('POINT(-73.91353 42.80611)') using bin2hex gives me a 50 character string value of

0000000001010000008d7a8846777a52c0417dcb9c2e674540

A partial list of field type numbers can be found here.

I hope that this helps you! If I discover more, I'll pass it on here.

Answer 2

It appears that the PHP unpack function is what is needed to extract the coordinate information.

MySQL stores the geometry fields in the WKB (Well Known Binary) format. The unpack function is able to extract that information when provided the proper format specifier. The following code is from a test script which successfully extracted the desired information.

Please note that my format for unpack differs slightly from that of the reference. This is because the WKB string wasn't retrieved from MySQL with the AsWKB() function, so it contains extra padding.

<?php
    $padding = 0;
    $order   = 1;
    $gtype   = 1;
    $lon     = -73.91353;
    $lat     = 42.80611;

    $bindata = pack('LcLd2', $padding, $order, $gtype, $lat, $lon);

    printf("Packed: %s\n\n", bin2hex($bindata));

    $result = unpack('Lpadding/corder/Lgtype/dlatitude/dlongitude', $bindata);
    var_dump($result);
?>

References MySQL and ESRI Network.