CREATE TABLE IF NOT EXISTS def_geo_zone (
id INT NOT NULL AUTO_INCREMENT,
ZoneCode VARCHAR(100) NOT NULL,
ZoneName VARCHAR(255) NOT NULL,
ZoneType VARCHAR(50) NOT NULL,
CenterLat DECIMAL(10,8) DEFAULT NULL,
CenterLng DECIMAL(11,8) DEFAULT NULL,
RadiusKm DECIMAL(12,3) DEFAULT NULL,
MinLat DECIMAL(10,8) DEFAULT NULL,
MinLng DECIMAL(11,8) DEFAULT NULL,
MaxLat DECIMAL(10,8) DEFAULT NULL,
MaxLng DECIMAL(11,8) DEFAULT NULL,
Package VARCHAR(255) DEFAULT NULL,
Module VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_zone (ZoneCode, Package)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='Geographic zone master record (circle, polygon, or bbox)'
CREATE TABLE IF NOT EXISTS def_geo_zone_points (
id INT NOT NULL AUTO_INCREMENT,
ZoneCode VARCHAR(100) NOT NULL,
Package VARCHAR(255) DEFAULT NULL,
PointOrder INT NOT NULL,
Lat DECIMAL(10,8) NOT NULL,
Lng DECIMAL(11,8) NOT NULL,
PRIMARY KEY (id),
KEY idx_zone (ZoneCode, Package)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='Ordered polygon vertices for GeoZoneType.POLYGON zones'
INSERT INTO def_geo_zone
(ZoneCode, ZoneName, ZoneType,
CenterLat, CenterLng, RadiusKm,
MinLat, MinLng, MaxLat, MaxLng,
Package, Module)
VALUES
('{zone_code}', '{zone_name}', '{zone_type}',
{center_lat}, {center_lng}, {radius_km},
{min_lat}, {min_lng}, {max_lat}, {max_lng},
'{package}', 'ObjGeoZone')
ON DUPLICATE KEY UPDATE
ZoneName = VALUES(ZoneName),
ZoneType = VALUES(ZoneType),
CenterLat = VALUES(CenterLat),
CenterLng = VALUES(CenterLng),
RadiusKm = VALUES(RadiusKm),
MinLat = VALUES(MinLat),
MinLng = VALUES(MinLng),
MaxLat = VALUES(MaxLat),
MaxLng = VALUES(MaxLng)
DELETE FROM def_geo_zone_points
WHERE ZoneCode = '{zone_code}'
AND Package = '{package}'
INSERT INTO def_geo_zone_points
(ZoneCode, Package, PointOrder, Lat, Lng)
VALUES
('{zone_code}', '{package}', {order}, {lat}, {lng})
SELECT ZoneType,
CenterLat, CenterLng, RadiusKm,
MinLat, MinLng, MaxLat, MaxLng,
ZoneName
FROM def_geo_zone
WHERE ZoneCode = '{zone_code}'
AND if(ifnull(Package,'')='','CORE',Package)
IN ('{package}','CORE','ADHOC')
LIMIT 1
SELECT Lat, Lng
FROM def_geo_zone_points
WHERE ZoneCode = '{zone_code}'
AND Package = '{package}'
ORDER BY PointOrder