How to handle external and internal DNS on windows 2012

Question

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

Solution

It's strongly recommended that you create separate internal and external DNS zones to avoid disclosing every detail about your topology to the entire internet. The risk is best mitigated by keeping the data separate. As well, by isolating public-facing DNS from AD and its auto-update functionality, you can prevent yet another mechanism that could potentially be used to hijack your records.

The way to do this without completely splitting DNS is to use a view, or split-horizon DNS, which windows doesn't support. So, either way, you'll have to manage a second set of DNS servers.

As well, you can use the same domain inside and out with different data (basically split-horizon), or a subdomain. It's your choice. For more on that, read Windows Active Directory naming best practices?.

Comments

[Paul Spiegel]

"AsText adds extra 0.4-0.5 seconds to the query time!" - For how many rows?

[Admin]

@PaulSpiegel 1338 results with AsText = 0.422 sec and without 0.078 sec

[Paul Spiegel]

Please provide an MCVE. Include (hardcoded) values for $polygon (something like $polygon = hex2bin('0000..')). Include both cases (even and odd count). Also post the result you'd like to get from the code. In best case someone would just need to C&P your code and fix it.

[Admin]

@PaulSpiegel I have added an example

[Paul Spiegel]

Seems they don't store it as WKB. There is an extra byte at the beginning (see the difference). Also see this bug report. Do you have an example without that byte?

[Admin]

@PaulSpiegel I dont have an example without extra byte(s) while using Geometry data type. Polygon data type does not have the same problem. I have tried removing extra bytes using PHP, but it caused decoded data to have one lat or long to be inaccurate from time to time.

[Admin]

The issue is to do with SRID, had to make it 0 when inserting data.

[kralyk]

To assist this answer, read all of this (shameless plug): serverfault.com/questions/76715/…

[mahmudy-91]

thanks for sharing your ideas, it really helped a lot