Import Google Maps polygons into MySQL

0 intro.txt

We have a client that ships from multiple warehouses using zones to determine
pricing. They maintain the zones in a Goole Maps document. We are creating a
front end that clients can enter their address and we'll show them what their
shipping options are. We will have the client export their zones as a KML
file and we'll import it into a MySQL database and use ST_Contains to find
the shipping zone(s) customers are in. (They can only be in one zone for any
given warehouse but may be within range of multiple warehouses.)

One thing to point out is there is confusion about the order of latitude and
longitude. Mathematically, it is naturally ordered "longitude, latitude" but
the tradition from navigation is "latitude, longitude". So, the KML file
orders it the former but MySQL expects the latter.

1 zones.sql

CREATE TABLE `zones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `warehouse` text NOT NULL,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `polygon` polygon DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2 example.kml

<?xml version="1.0" encoding="UTF-8"? >
<kml xmlns="http://www.opengis.net/kml/2.2">
  <Document>
    <name>Shipment Rates</name>
    <description/>
    ...
    <Folder>
      <name>Warehouse 1</name>
      <Placemark>
        <name>Warehouse 1</name>
        <description>Blah</description>
        <styleUrl>#icon-1899-FFD600</styleUrl>
        <Point>
          <coordinates>
            -73.985130,40.758896,0
          </coordinates>
        </Point>
      </Placemark>
      <Placemark>
        <name>Warehouse 1, Zone 1</name>
        <description>$2.80</description>
        <styleUrl>#poly-097138-1200-76</styleUrl>
        <Polygon>
          <outerBoundaryIs>
            <LinearRing>
              <tessellate>1</tessellate>
              <coordinates>
                -73.985130,40.758896,0
                -73.985700,40.758896,0
                -73.985700,40.758999,0
                -73.985130,40.758999,0
                -73.985130,40.758896,0
              </coordinates>
            </LinearRing>
          </outerBoundaryIs>
        </Polygon>
      </Placemark>
      ...
    </Folder>
  </Document>
</kml>

3 ImportKmlPolygons.php

<?php

// generate insert statements for zones

$dom = new DOMDocument();
$dom->loadXML(file_get_contents('zones.kml'));

foreach ($dom->getElementsByTagName('Folder') as $warehouse)
{
    $warehouseName = $warehouse->getElementsByTagName('name')[0]->nodeValue;

    foreach ($warehouse->getElementsByTagName('Placemark') as $placemark)
    {
        // in our case, a Placemark will either contain a Point, in which case it is the
        // location of the warehouse, or a Polygon, in which case it is a zone

        $polygons = $placemark->getElementsByTagName('Polygon');
        if ($polygons->length > 0)
        {
            $coordinates = $polygons[0]->getElementsByTagName('coordinates');
            if ($coordinates->length > 0)
            {
                $zoneName = $placemark->getElementsByTagName('name')[0]->nodeValue;
                $zoneDesc = $placemark->getElementsByTagName('description')[0]->nodeValue;
                $coordinates = explode("\n", $coordinates[0]->nodeValue);
                $coordinatesMysql = array();
                
                foreach ($coordinates as $coordinate)
                {
                    $coordinate = trim($coordinate);
                    if (!empty($coordinate))
                    {
                        list($lon, $lat, $elevation) = explode(",", trim($coordinate));
                        $coordinatesMysql[] = "$lat $lon";
                    }
                }

                echo 'insert into zones (warehouse, name, description, polygon) values '
                    ."('$warehouseName', '$zoneName', '$zoneDesc', ST_PolyFromText('Polygon(("
                    .implode(', ', $coordinatesMysql)
                    ."))'));\n";
            }
        }
    }
}

4 exampleQuery.sql

select * from zones where ST_Contains(polygon, GeomFromText('POINT(40.758896 -73.985130)'))