Skip to content

Instantly share code, notes, and snippets.

@nklatt nklatt/0 intro.txt
Last active Sep 30, 2019

Embed
What would you like to do?
Import Google Maps polygons into MySQL
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.
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;
<?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>
<?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";
}
}
}
}
select * from zones where ST_Contains(polygon, GeomFromText('POINT(40.758896 -73.985130)'))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.