GIS MySQL Schema

#Table structure for table region_detail

CREATE TABLE IF NOT EXISTS `region_detail` (
  `region_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `geocoord` varchar(200) DEFAULT NULL,
  `mid_point_lat` varchar(30) DEFAULT NULL,
  `mid_point_lng` varchar(30) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `create_id` int(11) DEFAULT NULL,
  `remarks` varchar(25) DEFAULT NULL,
  `edit_id` int(11) DEFAULT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

#Table structure for table road_intersection

CREATE TABLE IF NOT EXISTS `road_intersection` (
  `road_intersection_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(35) NOT NULL,
  `lat` varchar(30) DEFAULT NULL,
  `lng` varchar(30) DEFAULT NULL,
  `max_speed` varchar(5) DEFAULT NULL,
  `region_id` int(11) NOT NULL,
  `create_date` datetime DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `create_id` int(11) DEFAULT NULL,
  `edit_id` int(11) DEFAULT NULL,
  `remarks` varchar(25) DEFAULT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (`road_intersection_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#Table structure for table route_intersection_assignment

CREATE TABLE IF NOT EXISTS `route_intersection_assignment` (
  `sno` int(11) NOT NULL AUTO_INCREMENT,
  `route_path_id` int(11) NOT NULL,
  `road_intersection_id` int(11) NOT NULL,
  `create_id` int(11) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `edit_id` int(11) DEFAULT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

#Table structure for table route_leg

CREATE TABLE IF NOT EXISTS `route_leg` (
  `route_leg_id` int(11) NOT NULL AUTO_INCREMENT,
  `route_path_id` int(11) NOT NULL,
  `region_id` int(11) NOT NULL,
  `lat` varchar(30) DEFAULT NULL,
  `lng` varchar(30) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `create_id` int(11) DEFAULT NULL,
  `edit_id` int(11) DEFAULT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (`route_leg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

#Table structure for table route_path

CREATE TABLE IF NOT EXISTS `route_path` (
  `route_path_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `geocoord` blob,
  `remarks` varchar(25) DEFAULT NULL,
  `region_id` int(11) NOT NULL,
  `create_date` datetime DEFAULT NULL,
  `edit_date` datetime DEFAULT NULL,
  `create_id` int(11) DEFAULT NULL,
  `edit_id` int(11) DEFAULT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (`route_path_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;