CREATE TABLE IF NOT EXISTS cache_geo_location (
id INT NOT NULL AUTO_INCREMENT,
AddressHash VARCHAR(64) NOT NULL,
OriginalAddress TEXT,
CleanAddress TEXT,
Lat DECIMAL(10,8),
Lng DECIMAL(11,8),
DisplayAddress TEXT,
Source VARCHAR(50),
Confidence DECIMAL(5,3),
Package VARCHAR(255),
Module VARCHAR(255),
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_hash (AddressHash, Package)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='Geocoding result cache — avoids redundant Nominatim calls'
CREATE TABLE IF NOT EXISTS data_postcode (
Guid VARCHAR(36) NOT NULL,
Postcode VARCHAR(20) NOT NULL,
Address TEXT,
Lat DECIMAL(10,7),
Lng DECIMAL(10,7),
GeoSource VARCHAR(50),
Confidence DECIMAL(4,3),
Package VARCHAR(255),
Module VARCHAR(255),
Createdate DATETIME,
PRIMARY KEY (Guid),
UNIQUE KEY idx_postcode (Postcode)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE={collation}
COMMENT='Shared postcode geocoding cache — reused across all datasets'