CREATE TABLE `station_v1` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`IMEI` varchar(55) NOT NULL,
`BARCODE` varchar(55) NOT NULL,
`SIM_NUMBER` varchar(55) NOT NULL,
`SERVICE_PROVIDER` varchar(55) NOT NULL,
`STATE` varchar(100) NOT NULL DEFAULT '-',
`DIVISION_AGRICULTURE` varchar(55) DEFAULT NULL,
`DIVISION_REVENUE` varchar(55) DEFAULT NULL,
`DISTRICT` varchar(100) NOT NULL DEFAULT '-',
`TEHSIL` varchar(100) NOT NULL DEFAULT '-',
`REVENUE_CIRCLE` varchar(100) NOT NULL DEFAULT '-',
`STATION_NAME` varchar(100) NOT NULL,
`INSTALLATION_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`DATE_UPDATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`RECORDS_PER_DAY` int(11) DEFAULT NULL,
`ACTIVE` int(1) unsigned NOT NULL DEFAULT '1',
`Visible_To_Client` tinyint(1) NOT NULL DEFAULT '0',
`ADDRESS` text,
`INSTALLED_DATE` timestamp NULL DEFAULT NULL,
`INSTALLED_BY` varchar(255) DEFAULT NULL,
`PRE_QC_PERSON` text,
`QC_PERSON` text,
`SERVICE_ENGINEER` text,
`CLUSTER_CONTROLLER` text,
`LATITUDE` float DEFAULT NULL,
`LONGITUDE` float DEFAULT NULL,
`TEHSIL_ID` int(11) unsigned NOT NULL DEFAULT '0',
`ENABLE_FIRST_TIME_UPDATE` int(1) unsigned NOT NULL DEFAULT '0',
`LAST_UPDATED_DATA_DATE` date DEFAULT NULL,
`DATE_CREATED` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`RAIN_UPDATED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`ID`),
UNIQUE KEY `SV1_IMEI` (`IMEI`),
UNIQUE KEY `BARCODE` (`BARCODE`),
UNIQUE KEY `SIM_NUMBER` (`SIM_NUMBER`),
KEY `STATE` (`STATE`),
KEY `DISTRICT` (`DISTRICT`),
KEY `TEHSIL` (`TEHSIL`),
KEY `STATION_NAME` (`STATION_NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=2375 DEFAULT CHARSET=latin1;
DATA:
# ID, IMEI, BARCODE, SIM_NUMBER, SERVICE_PROVIDER, STATE, DIVISION_AGRICULTURE, DIVISION_REVENUE, DISTRICT, TEHSIL, REVENUE_CIRCLE, STATION_NAME, INSTALLATION_DATE, DATE_UPDATED, RECORDS_PER_DAY, ACTIVE, Visible_To_Client, ADDRESS, INSTALLED_DATE, INSTALLED_BY, PRE_QC_PERSON, QC_PERSON, SERVICE_ENGINEER, CLUSTER_CONTROLLER, LATITUDE, LONGITUDE, TEHSIL_ID, ENABLE_FIRST_TIME_UPDATE, LAST_UPDATED_DATA_DATE, DATE_CREATED, RAIN_UPDATED_DATE
'1', '868326023505555', 'MHAWS0194', '5755200604299', 'Vodafone', 'Maharashtra', 'Nagpur', 'Nagpur', 'Nagpur', 'Nagpur (R)', 'Khapari Re', 'Dongargaon', '0000-00-00 00:00:00', '2021-07-28 18:16:06', '144', '1', '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '20.9951', '79.0341', '364538', '0', '2021-08-15', '0000-00-00 00:00:00', '0000-00-00 00:00:00'
EX:2
CREATE TABLE `station_data_v1` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`IMEI_ID` int(11) unsigned NOT NULL,
`DATA_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`DATE_CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Temp_Max` double(8,3) NOT NULL DEFAULT '-11.110',
`Temp_Avg` double(8,3) NOT NULL DEFAULT '-11.110',
`Temp_Min` double(8,3) NOT NULL DEFAULT '-11.110',
`Hmdt_Max` double(8,3) NOT NULL DEFAULT '-11.110',
`Hmdt_Avg` double(8,3) NOT NULL DEFAULT '-11.110',
`Hmdt_Min` double(8,3) NOT NULL DEFAULT '-11.110',
`Rain_Max` double(8,3) NOT NULL DEFAULT '-11.110',
`Rain_Avg` double(8,3) NOT NULL DEFAULT '-11.110',
`Rain_Min` double(8,3) NOT NULL DEFAULT '-11.110',
`Anlg_1_Avg` double(8,3) DEFAULT '-11.110',
`Anlg_2_Avg` double(8,3) DEFAULT '-11.110',
`Anlg_3_Avg` double(8,3) DEFAULT '-11.110',
`Anlg_4_Avg` double(8,3) DEFAULT '-11.110',
`Wind_Max` double(8,3) unsigned NOT NULL DEFAULT '0.000',
`Wind_Avg` double(8,3) unsigned NOT NULL DEFAULT '0.000',
`Wind_Min` double(8,3) unsigned NOT NULL DEFAULT '0.000',
`Wind_Dir` double(8,3) NOT NULL DEFAULT '0.000',
`PV` double(8,3) NOT NULL DEFAULT '0.000',
`Battery` double(8,3) unsigned NOT NULL DEFAULT '0.000',
`SOURCE` varchar(25) NOT NULL DEFAULT 'unknown',
`IS_UPDATED` int(1) unsigned NOT NULL DEFAULT '0',
`IS_UPLOADED` int(1) unsigned NOT NULL DEFAULT '0',
`IS_HUMIDITY_UPDATED` tinyint(1) NOT NULL DEFAULT '0',
`Rainfall_Updated` tinyint(1) NOT NULL DEFAULT '0',
`RAIN_UPDATED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`RAIN_UPDATED_BY` int(11) unsigned DEFAULT NULL,
`DewPoint` double(5,2) DEFAULT '0.00',
`DATE_UPDATED` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`IS_INTERPOLATED` int(1) NOT NULL DEFAULT '0',
`USER_ID` int(11) unsigned DEFAULT NULL,
`Rainfall_Indicator` double(8,2) DEFAULT NULL,
`Solar_Radiation_Min` double(8,2) DEFAULT NULL,
`Solar_Radiation_Avg` double(8,2) DEFAULT NULL,
`Solar_Radiation_Max` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev1_Min` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev1_Avg` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev1_Max` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev2_Min` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev2_Avg` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev2_Max` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev3_Min` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev3_Avg` double(8,2) DEFAULT NULL,
`Soil_Moisture_lev3_Max` double(8,2) DEFAULT NULL,
`Soil_Temperature_Min` double(8,2) DEFAULT NULL,
`Soil_Temperature_Avg` double(8,2) DEFAULT NULL,
`Soil_Temperature_Max` double(8,2) DEFAULT NULL,
`Leaf_Wetness_Min` double(8,2) DEFAULT NULL,
`Leaf_Wetness_Avg` double(8,2) DEFAULT NULL,
`Leaf_Wetness_Max` double(8,2) DEFAULT NULL,
`Reserve1` double(8,2) DEFAULT NULL,
`Reserve2` double(8,2) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ImeiDataDateUnique_n511` (`IMEI_ID`,`DATA_DATE`),
KEY `DATA_DATE511` (`DATA_DATE`),
KEY `DATE_CREATED511` (`DATE_CREATED`),
KEY `IMEI_ID511` (`IMEI_ID`),
CONSTRAINT `STATION_DATA_V1_ibfk_511` FOREIGN KEY (`IMEI_ID`) REFERENCES `station_v1` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=482637970 DEFAULT CHARSET=latin1;
# ID, IMEI_ID, DATA_DATE, DATE_CREATED, Temp_Max, Temp_Avg, Temp_Min, Hmdt_Max, Hmdt_Avg, Hmdt_Min, Rain_Max, Rain_Avg, Rain_Min, Anlg_1_Avg, Anlg_2_Avg, Anlg_3_Avg, Anlg_4_Avg, Wind_Max, Wind_Avg, Wind_Min, Wind_Dir, PV, Battery, SOURCE, IS_UPDATED, IS_UPLOADED, IS_HUMIDITY_UPDATED, Rainfall_Updated, RAIN_UPDATED_DATE, RAIN_UPDATED_BY, DewPoint, DATE_UPDATED, IS_INTERPOLATED, USER_ID, Rainfall_Indicator, Solar_Radiation_Min, Solar_Radiation_Avg, Solar_Radiation_Max, Soil_Moisture_lev1_Min, Soil_Moisture_lev1_Avg, Soil_Moisture_lev1_Max, Soil_Moisture_lev2_Min, Soil_Moisture_lev2_Avg, Soil_Moisture_lev2_Max, Soil_Moisture_lev3_Min, Soil_Moisture_lev3_Avg, Soil_Moisture_lev3_Max, Soil_Temperature_Min, Soil_Temperature_Avg, Soil_Temperature_Max, Leaf_Wetness_Min, Leaf_Wetness_Avg, Leaf_Wetness_Max, Reserve1, Reserve2
'182689488', '2003', '2019-01-01 00:00:00', '2019-01-01 00:01:35', '8.290', '8.030', '7.720', '99.750', '99.350', '98.310', '-11.110', '0.000', '-11.110', '-11.110', '-11.110', '-11.110', '-11.110', '0.000', '0.000', '0.000', '0.000', '0.000', '12.380', 'mns', '2', '0', '0', '0', '0000-00-00 00:00:00', NULL, '0.00', '0000-00-00 00:00:00', '0', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
No comments:
Post a Comment