Description:
I found lots of duplicate records in my database with a unique key defined. The unique key I'm expecting to take care of this is 'report'.
I think I have narrowed it down to the float (lat, lon) fields. I'm guessing this is due to like values (41.85) not creating the same floating point representation. I would like conformation.
How to repeat:
Create this table and run the SQL INSERT statements (more then once if you would like). The second INSERT should create an Duplicate error but it never does.
CREATE TABLE `test_storm_reports` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ref_id` int(11) DEFAULT NULL,
`site` char(8) NOT NULL,
`date_issued` datetime NOT NULL,
`event_date` datetime NOT NULL,
`event` enum('HAIL','RAIN','HEAVY RAIN','FREEZING RAIN','SNOW','HEAVY SNOW','BLIZZARD','TORNADO','WATER SPOUT','WIND','HIGH SUST WINDS','DOWNBURST','TSTM WND DMG','TSTM WND GST','NON-TSTM WND GST','NON-TSTM WND DMG','LIGHTNING','FLOOD','FLASH FLOOD','FUNNEL CLOUD','MARINE TSTM WIND') DEFAULT NULL,
`location` varchar(40) DEFAULT NULL,
`county` varchar(40) DEFAULT NULL,
`state` char(2) DEFAULT NULL,
`lat` float(6,2) NOT NULL,
`lon` float(6,2) NOT NULL,
`meas_est` enum('M','E') DEFAULT NULL,
`mag` float(4,2) DEFAULT NULL,
`units` enum('INCH','F','MPH') DEFAULT NULL,
`source` enum('PUBLIC','AMATEUR RADIO','LAW ENFORCEMENT','TRAINED SPOTTER','FIRE DEPT/RESCUE','EMERGENCY MNGR','BROADCAST MEDIA','POST OFFICE','ASOS','NWS EMPLOYEE','OFFICIAL NWS OBS','MESONET','CO-OP OBSERVER','STORM CHASER','NWS STORM SURVEY','AWOS','UTILITY COMPANY','DEPT OF HIGHWAYS','NEWSPAPER','COUNTY OFFICIAL','OTHER FEDERAL','PARK/FOREST SRVC','911 CENTER','SKYWARN SPOTTER','C-MAN STATION','STATE POLICE','COCORAHS','BUOY','AIRPLANE PILOT','OK HIGHWAY PTRL','COAST GUARD','INSURANCE CO','UNKNOWN') DEFAULT NULL,
`comments` varchar(256) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `report` (`site`,`event_date`,`event`,`lat`,`lon`,`source`,`comments`),
KEY `event_date` (`event_date`)
) ENGINE=MyISAM AUTO_INCREMENT=24363 DEFAULT CHARSET=latin1
INSERT INTO `test_storm_reports` (`ref_id`, `site`, `date_issued`, `event_date`, `event`, `location`, `county`, `state`, `lat`, `lon`, `meas_est`, `mag`, `units`, `source`, `comments`) VALUES (NULL,'KDMX','2010-08-11 06:15:00','2010-08-11 05:53:00','TSTM WND GST','2 W WOODWARD','DALLAS','IA',41.85,-93.96,'E',60,'MPH','EMERGENCY MNGR',NULL);
INSERT INTO `test_storm_reports` (`ref_id`, `site`, `date_issued`, `event_date`, `event`, `location`, `county`, `state`, `lat`, `lon`, `meas_est`, `mag`, `units`, `source`, `comments`) VALUES (NULL,'KDMX','2010-08-11 10:05:00','2010-08-11 05:53:00','TSTM WND GST','2 W WOODWARD','DALLAS','IA',41.85,-93.96,'E',60,'MPH','EMERGENCY MNGR',NULL);
Suggested fix:
What is the best way to store latitude and Longitude? DECIMAL(4,6)?
Description: I found lots of duplicate records in my database with a unique key defined. The unique key I'm expecting to take care of this is 'report'. I think I have narrowed it down to the float (lat, lon) fields. I'm guessing this is due to like values (41.85) not creating the same floating point representation. I would like conformation. How to repeat: Create this table and run the SQL INSERT statements (more then once if you would like). The second INSERT should create an Duplicate error but it never does. CREATE TABLE `test_storm_reports` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ref_id` int(11) DEFAULT NULL, `site` char(8) NOT NULL, `date_issued` datetime NOT NULL, `event_date` datetime NOT NULL, `event` enum('HAIL','RAIN','HEAVY RAIN','FREEZING RAIN','SNOW','HEAVY SNOW','BLIZZARD','TORNADO','WATER SPOUT','WIND','HIGH SUST WINDS','DOWNBURST','TSTM WND DMG','TSTM WND GST','NON-TSTM WND GST','NON-TSTM WND DMG','LIGHTNING','FLOOD','FLASH FLOOD','FUNNEL CLOUD','MARINE TSTM WIND') DEFAULT NULL, `location` varchar(40) DEFAULT NULL, `county` varchar(40) DEFAULT NULL, `state` char(2) DEFAULT NULL, `lat` float(6,2) NOT NULL, `lon` float(6,2) NOT NULL, `meas_est` enum('M','E') DEFAULT NULL, `mag` float(4,2) DEFAULT NULL, `units` enum('INCH','F','MPH') DEFAULT NULL, `source` enum('PUBLIC','AMATEUR RADIO','LAW ENFORCEMENT','TRAINED SPOTTER','FIRE DEPT/RESCUE','EMERGENCY MNGR','BROADCAST MEDIA','POST OFFICE','ASOS','NWS EMPLOYEE','OFFICIAL NWS OBS','MESONET','CO-OP OBSERVER','STORM CHASER','NWS STORM SURVEY','AWOS','UTILITY COMPANY','DEPT OF HIGHWAYS','NEWSPAPER','COUNTY OFFICIAL','OTHER FEDERAL','PARK/FOREST SRVC','911 CENTER','SKYWARN SPOTTER','C-MAN STATION','STATE POLICE','COCORAHS','BUOY','AIRPLANE PILOT','OK HIGHWAY PTRL','COAST GUARD','INSURANCE CO','UNKNOWN') DEFAULT NULL, `comments` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `report` (`site`,`event_date`,`event`,`lat`,`lon`,`source`,`comments`), KEY `event_date` (`event_date`) ) ENGINE=MyISAM AUTO_INCREMENT=24363 DEFAULT CHARSET=latin1 INSERT INTO `test_storm_reports` (`ref_id`, `site`, `date_issued`, `event_date`, `event`, `location`, `county`, `state`, `lat`, `lon`, `meas_est`, `mag`, `units`, `source`, `comments`) VALUES (NULL,'KDMX','2010-08-11 06:15:00','2010-08-11 05:53:00','TSTM WND GST','2 W WOODWARD','DALLAS','IA',41.85,-93.96,'E',60,'MPH','EMERGENCY MNGR',NULL); INSERT INTO `test_storm_reports` (`ref_id`, `site`, `date_issued`, `event_date`, `event`, `location`, `county`, `state`, `lat`, `lon`, `meas_est`, `mag`, `units`, `source`, `comments`) VALUES (NULL,'KDMX','2010-08-11 10:05:00','2010-08-11 05:53:00','TSTM WND GST','2 W WOODWARD','DALLAS','IA',41.85,-93.96,'E',60,'MPH','EMERGENCY MNGR',NULL); Suggested fix: What is the best way to store latitude and Longitude? DECIMAL(4,6)?