CODEWITHSUNDEEP
phpmysql
user10986113
user10986113

Reputation:

Converting POLYGON binary to array of lat and long values

I need to convert POLYGON binary data to an array of lat and long values.

The reason why I decided not to use MySQL's function such as AsText is because I am having to take extra steps to parse information into correct format hence slowing down the whole process. AsText adds extra 0.4-0.5 seconds to the query time. i.e. 1338 results with AsText 0.422 seconds and without 0.078 seconds. Also, looping through an array of data, to put items into lat and long array, takes process time to 1.2 seconds. Due to which AsText is very slow.

I have created a small method to perform unpack based on the information here: https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html#gis-internal-format

The problem is that sometimes $unpack will return an extra array item due to which I am having to check if count($unpack) is odd or not. Note: ST_AsText returns correct number of elements excluding extra item.

I am also not confident about the correctness of the lat/long results, but I think it could be due to the issue above.

        $unpack = unpack("x4/corder/Ltype/d*", $polygon, 0);
        array_shift($unpack);
        array_shift($unpack);

        if (count($unpack)%2) {
            array_shift($unpack);
        }

        $data = array_chunk($unpack, 2);
        $return = [];

        for ($i = 0; $i < count($data); $i++) {
            $return[] = [
                'lat' => $data[$i][0],
                'lng' => $data[$i][1],
            ];
        }

        return $return;

In the MySQL my polygon is stored as GEOMETRY data type.

Example data in the DB:

0x01000000010300000001000000120000005EE31F2A74D219C0C6420701C3F24940A6ECDEC65DD219C09CD12A4CC3F249404374373157D219C0573E3C64C2F249409B434A0258D219C08127369EC1F249401D3C98D552D219C03ABFB0A4C0F24940B265A9BF50D219C000904B01BFF24940808F364F5ED219C05D8CBD93BEF249409359719566D219C059362A75BEF24940BAAC39A26AD219C0A40DF373BEF24940FAB94BE672D219C08BD0C591BEF249402D7E95B778D219C07CB8ABBBBEF249401FD891AA82D219C02B13AE26BFF249409FA6C02C87D219C0E91BDB6BBFF24940C133F78292D219C082DA76C7C0F24940ABF3CF2A92D219C0D765AA40C1F24940E29CAC7989D219C0E915AF3DC2F24940CA80EB0984D219C0F905EABDC2F249405EE31F2A74D219C0C6420701C3F24940

Upvotes: 0

Views: 973

Answers (1)

thunberg
thunberg

Reputation: 31

This was driving me nuts as well, here's the correct unpack:

unpack('VSRID/corder/ltype/Lnum_rings/Lnum_points/d*', $value);

https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html#gis-wkb-format

The MySQL docs cover the first 4 bytes being the SRID, next byte being the byte order, the following 4 bytes as the type info.

https://github.com/mysql/mysql-server/blob/ea7d2e2d16ac03afdd9cb72a972a95981107bf51/sql/gis/wkb.cc

What I had to dig through the MySQL source for was that before the coordinates there's an unsigned integer for the number of polygon rings, and another unsigned integer for the number of coordinate pairs.

Upvotes: 2

Related Questions