CREATE TABLE CanadianAddresses
(
    PostalCode CHAR(7) NOT NULL DEFAULT "",
    City VARCHAR(45) DEFAULT NULL,
    Province CHAR(2) DEFAULT NULL,
    ProvinceName CHAR(45) DEFAULT NULL,
    AreaCode CHAR(3) DEFAULT NULL,
    CityFlag CHAR(1) DEFAULT NULL,
    TimeZone INT DEFAULT NULL,
    DST CHAR(1) DEFAULT NULL,
    Latitude DECIMAL(12,8) DEFAULT NULL,
    Longitude DECIMAL(12,8) DEFAULT NULL,
    Elevation INT DEFAULT NULL,
    Population INT DEFAULT NULL,
    Dwellings INT DEFAULT NULL,
    AreaName VARCHAR(45) DEFAULT NULL,
    StreetName VARCHAR(45) NOT NULL DEFAULT "",
    StreetTypeCode CHAR(6) DEFAULT NULL,
    StreetDirCode CHAR(2) DEFAULT NULL,
    StreetSeqCode CHAR(1) NOT NULL DEFAULT "",
    StreetFromNo VARCHAR(6) NOT NULL DEFAULT "",
    StreetFromSuffix CHAR(1) NOT NULL DEFAULT "",
    StreetToNo VARCHAR(6) NOT NULL DEFAULT "",
    StreetToSuffix CHAR(1) NOT NULL DEFAULT "",
    CityMixedCase VARCHAR(45) DEFAULT NULL,
    AreaNameMixedCase VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY ( PostalCode,StreetName,StreetSeqCode,StreetFromNo,StreetFromSuffix,StreetToNo,StreetToSuffix ),
    KEY PostalCode ( PostalCode ),
    KEY Province ( Province,City,StreetName ),
    KEY StreetTypeCode ( StreetTypeCode )

) ENGINE=INNODB CHARSET=UTF8 COMMENT="All canadian postal codes by City, Province, StreetName etc.";
