יום שבת, 25 במאי 2013

The route way points table

Not long ago I had a mission to normalize a Routes Waypoint table .
The current RoutePoints  table should be split into 2 tables:Route table and a Waypoints table. 

Capture18

The following  is used to create the table demo sample for the post 

IF EXISTS ( SELECT * 
    FROM sys.tables 
    WHERE name = 'RoutesPoints' ) 
   DROP TABLE RoutesPoints; 
   
CREATE TABLE RoutesPoints
   ( RouteName VARCHAR(255), 
      WayPointName VARCHAR(255), 
      WayPointlocation GEOGRAPHY , 
      WayPointType VARCHAR(6),  
   ); 
INSERT INTO RoutesPoints
   (  RouteName, WayPointName,WayPointlocation, WayPointType ) 
VALUES 
(  'Route1', 'WayPoint1', geography::STGeomFromText('POINT(32.34900 36.65150)', 4326) , 'Start' ),
(  'Route2', 'WayPoint2', geography::STGeomFromText('POINT(32.34900 36.65260)', 4326) , 'Start' ),
(  'Route1', 'WayPoint3', geography::STGeomFromText('POINT(32.34900 36.65600)', 4326) , 'Middle' ),
(  'Route3', 'WayPoint4', geography::STGeomFromText('POINT(32.34900 36.65666)', 4326) , 'End' ),
(  'Route3', 'WayPoint5', geography::STGeomFromText('POINT(32.34800 36.65160)', 4326) , 'Middle' ),
(  'Route2', 'WayPoint6', geography::STGeomFromText('POINT(32.34980 36.65290)', 4326) , 'Start' ),
(  'Route1', 'WayPoint7', geography::STGeomFromText('POINT(32.34908 36.61100)', 4326) , 'Start' ),
(  'Route2', 'WayPoint8', geography::STGeomFromText('POINT(32.34950 36.65150)', 4326) , 'Middle' ),
(  'Route1', 'WayPoint9', geography::STGeomFromText('POINT(32.34900 36.65540)', 4326) , 'Start' ),
(  'Route2', 'WayPoint10', geography::STGeomFromText('POINT(32.34900 36.65550)', 4326) , 'Start' ),
(  'Route1', 'WayPoint11', geography::STGeomFromText('POINT(32.34900 36.65230)', 4326) , 'End' ),
(  'Route2', 'WayPoint12', geography::STGeomFromText('POINT(32.34940 36.65150)', 4326) , 'Middle' ),
(  'Route1', 'WayPoint13', geography::STGeomFromText('POINT(32.34600 36.65270)', 4326) , 'Start' ),
(  'Route4', 'WayPoint16', geography::STGeomFromText('POINT(32.34500 36.65340)', 4326) , 'Start' ),
(  'Route1', 'WayPoint14', geography::STGeomFromText('POINT(32.34500 36.65160)', 4326) , 'Middle' ),
(  'Route2', 'WayPoint17', geography::STGeomFromText('POINT(32.34400 36.65150)', 4326) , 'Middle' ),
(  'Route3', 'WayPoint18', geography::STGeomFromText('POINT(32.34200 36.65140)', 4326) , 'Start' ),
(  'Route3', 'WayPoint19', geography::STGeomFromText('POINT(32.34200 36.65150)', 4326) , 'Start' ),
(  'Route1', 'WayPoint20', geography::STGeomFromText('POINT(32.34100 36.65140)', 4326) , 'Middle' ),
(  'Route2', 'WayPoint21', geography::STGeomFromText('POINT(32.34100 36.65150)', 4326) , 'End' ),
(  'Route1', 'WayPoint22', geography::STGeomFromText('POINT(32.34910 36.65160)', 4326) , 'Middle' ),
(  'Route2', 'WayPoint23', geography::STGeomFromText('POINT(32.34430 36.65100)', 4326) , 'Start' ),
(  'Route2', 'WayPoint24', geography::STGeomFromText('POINT(32.34440 36.65180)', 4326) , 'Start' ),
(  'Route2', 'WayPoint25', geography::STGeomFromText('POINT(32.34530 36.65140)', 4326) , 'Middle' ),
(  'Route1', 'WayPoint26', geography::STGeomFromText('POINT(32.34340 36.65800)', 4326) , 'Start' ),
(  'Route3', 'WayPoint27', geography::STGeomFromText('POINT(32.34670 36.65700)', 4326) , 'Start' ),
(  'Route2', 'WayPoint28', geography::STGeomFromText('POINT(32.34540 36.65600)', 4326) , 'End' ),
(  'Route3', 'WayPoint29', geography::STGeomFromText('POINT(32.34650 36.65500)', 4326) , 'Middle' ),
(  'Route1', 'WayPoint30', geography::STGeomFromText('POINT(32.34345 36.65140)', 4326) , 'Start' ),
(  'Route4', 'WayPoint31', geography::STGeomFromText('POINT(32.34584 36.65130)', 4326) , 'Start' ),
(  'Route5', 'WayPoint31', geography::STGeomFromText('POINT(32.34888 36.65200)', 4326) , 'Middle' ),
(  'Route4', 'WayPoint32', geography::STGeomFromText('POINT(32.34900 36.65100)', 4326) , 'Middle' )
 


The rolls for the normalization process  :
Every route should contains one start waypoint , one end way point and 0..n middle waypoints. if no start or end drop the points drop the route data if more then one end or start points exists to the same route take the first occurrence. 

The T-SQL for the normalized tables 

 IF EXISTS ( SELECT * 
    FROM sys.tables 
    WHERE name = 'RoutePoints' ) 
   DROP TABLE RoutePoints; 
   
CREATE TABLE RoutePoints
   ( RouteID int, 
      WayPointName VARCHAR(255), 
      WayPointlocation GEOGRAPHY , 
      WayPointType VARCHAR(6), 	 
   ); 
 IF EXISTS ( SELECT * 
    FROM sys.tables 
    WHERE name = 'Routes' ) 
   DROP TABLE Routes; 
   
CREATE TABLE Routes
   ( RouteID int , 
      [Name] VARCHAR(255), 
	 CONSTRAINT PK_Routes_RoutesID PRIMARY KEY CLUSTERED (RouteID)   
   ); 
go 
ALTER TABLE RoutePoints
  ADD CONSTRAINT RouteKey FOREIGN KEY (RouteID)
      REFERENCES Routes (RouteID)
      ON DELETE CASCADE
GO

The resolution for this problem is based on this article.
  











ב-






















תוויות:


















אין תגובות:















הוסף רשומת תגובה


















        

      









הירשם ל-
תגובות לפרסום (Atom)