Pages

Friday, October 21, 2011

Google Maps Eight Waypoints Limitation

Google maps DirectionsService (Google Maps API) has a eight waypoint limitation, so I needed to find how to map directions that had more than eight waypoints.

JQuery AJAX call that hit MS SQL Database, which returned a JSON string of waypoints.

Initialized a variable indicating the max waypoints allowed by Google DirectionsService: maxWayPts = 8;

A for loop handled the number of divisions, where an inner loop handled the maximum waypoints. The variable wpLength is the number of waypoints that was returned from the database.

for (var i = 0; i < wpLength; i++) {
    for (var j = i * maxWayPts; j < maxWayPts * (i + 1) - 1; j++)
 
    }
}

Initialized a oBounds object, which has two properties: origin and destination. The origin property holds the first waypoint and destination property holds the last waypoint.

Returned from database: JSON string – Array of objects put into oWayPts variable:



[
{
"title":"Starting Tour at BCT, Berea",
"descrip":"Official Starting Point: Berea Coffee and Tea, Bera KY",
"wayPoints":
[
{
"location":"Berea Coffee and Tea, Berea, KY",
"latLng": {"lat":37.572542,"lng":-84.288762},
"marker":null,
"zDate":"\/Date(1317960000000)\/"
},
{
"location":"Moran Mill Rd, KY",
"latLng":{"lat":37.603667,"lng":-84.347126},
"marker":null,"zDate":"\/Date(1317960000000)\/"
}
]


Initially, origin property is acquired by splicing from the waypoint array (oWayPts). oWayPts is an array of sting objects that was returned from the database.



var oBounds = {};
oBounds.origin = oWayPts.splice(0, 1);

Looping through, waypoints are pushed onto the wayPoints array and its elements are of type google.maps.DirectionsWaypoint. A forced break in the loop when at the end of the waypoints array (oWayPts).


for (var j = i * maxWayPts; j < maxWayPts * (i + 1) - 1; j++) {
    if (typeof oWayPts[j] === "undefined") {
        break;
    } else {
        wayPoints.push(
        {
            location: new google.maps.LatLng(oWayPts[j].latLng.lat, oWayPts[j].latLng.lng),
            stopover: false
        });
    }
}

After the looping through the inner for loop, the oBounds.destination property is set by splicing off the wayPoints array at its end. The oBounds.origin is set to the destination, which causes an overlapping of waypoints.



if (wayPoints.length > 0) {
    oBounds.destination = wayPoints.splice(wayPoints.length - 1, 1);
}

Now, the origin, destination, and waypoints are set. Now it is time to render using google.maps.DirectionsServices,render method, which a google.maps.DirectionsRequest is passed as a parameter. Below is the DirectionsRequest.



var directionsRequest = {
    travelMode: google.maps.DirectionsTravelMode.BICYCLING
}
directionsRequest.origin = new google.maps.LatLng(oBounds.origin[0].latLng.lat, oBounds.origin[0].latLng.lng);
 
/* 
Destination is of type waypoint. A waypoint consists of the following fields: •location (required) specifies the address of the waypoint. • stopover (optional) indicates whether this waypoint is a actual stop on the route (true) or instead only a preference to route through the indicated location (false). Stopovers are true by default.
------------------------------------ */
lat = oBounds.destination[0].location.lat();
lng = oBounds.destination[0].location.lng();
 
directionsRequest.destination = new google.maps.LatLng(lat, lng);
oBounds.origin = [{ latLng: { lat: lat, lng: lng}}];
 
directionsRequest.waypoints = wayPoints;

Now passing in the DirectionsRequest to DirectionsService.render method:


gMap.directionsService.route(directionsRequest, function (result, status) {
     if (status == google.maps.DirectionsStatus.OK) {
         gMap.renderPoints(result);
     }
 });



function renderPoints(arryResult) {
    var directionsRendererOpt = { map: map, suppressMarkers: true };
    var obj = document.getElementById("googleWritten");
    obj.innerHTML = "";
 
        var directionsRenderer = new google.maps.DirectionsRenderer(directionsRendererOpt);
        directionsRenderer.setPanel(obj);
        //Hold on to created overlay, so it could be removed from the map later.
        directionsRenders.push(directionsRenderer);
 
        directionsRenderer.setDirections(arryResult);
}

View a working example at deDogs

Tuesday, October 18, 2011

T-SQL Look-up Table of States and Abbreviations

Creates a table named States and inserts all U.S. states and abbreviations.

 
USE [adminDB]
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[States](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [state] [nchar](250) NOT NULL,
    [state_abbr] [nchar](5) NOT NULL,
 CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Alabama', N'AL')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Alaska', N'AK')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Arizona', N'AZ')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Arkansas', N'AR')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'California', N'CA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Colorado', N'CO')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Connecticut', N'CT')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Delaware', N'DE')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'District of Columbia', N'DC')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Florida', N'FL')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Georgia', N'GA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Hawaii', N'HI')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Idaho', N'ID')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Illinois', N'IL')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Indiana', N'IN')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Iowa', N'IA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Kansas', N'KS')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Kentucky', N'KY')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Louisiana', N'LA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Maine', N'ME')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Maryland', N'MD')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Massachusetts', N'MA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Michigan', N'MI')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Minnesota', N'MN')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Mississippi', N'MS')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Missouri', N'MO')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Montana', N'MT')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Nebraska', N'NE')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Nevada', N'NV')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'New Hampshire', N'NH')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'New Jersey', N'NJ')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'New Mexico', N'NM')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'New York', N'NY')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'North Carolina', N'NC')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'North Dakota', N'ND')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Ohio', N'OH')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Oklahoma', N'OK')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Oregon', N'OR')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Pennsylvania', N'PA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Rhode Island', N'RI')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'South Carolina', N'SC')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'South Dakota', N'SD')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Tennessee', N'TN')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Texas', N'TX')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Utah', N'UT')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Vermont', N'VT')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Virginia', N'VA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Washington', N'WA')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'West Virginia', N'WV')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Wisconsin', N'WI')
INSERT INTO [adminDB].[dbo].[States]([state],[state_abbr]) VALUES (N'Wyoming', N'WY')
GO

My First T-SQL Stored Procedure

I have not got around with creating T-SQL stored procedures till today. Currently, I had developed a website utilizing the Entity Framework Methods. This is a very simplistic stored procedure as it is my first.

 

   1: -- ================================================
   2: -- Template generated from Template Explorer using:
   3: -- Create Procedure (New Menu).SQL
   4: --
   5: -- Use the Specify Values for Template Parameters 
   6: -- command (Ctrl-Shift-M) to fill in the parameter 
   7: -- values below.
   8: --
   9: -- This block of comments will not be included in
  10: -- the definition of the procedure.
  11: -- ================================================
  12: SET ANSI_NULLS ON
  13: GO
  14: SET QUOTED_IDENTIFIER ON
  15: GO
  16: -- =============================================
  17: -- Author:        Kirk deDoes
  18: -- Create date: October 18, 2011
  19: -- Description:    Inserts a new record into the TourLeg table.
  20: -- =============================================
  21: CREATE PROCEDURE [dbo].[InsertLeg]
  22:            @leg nchar(150),
  23:            @description text,
  24:            @sort int,
  25:            @state nchar(5)
  26: AS
  27: BEGIN
  28:     -- SET NOCOUNT ON added to prevent extra result sets from
  29:     -- interfering with SELECT statements.
  30: SET NOCOUNT ON;
  31: INSERT INTO [adminDB].[dbo].[TourLeg]
  32:            ([leg]
  33:            ,[description]
  34:            ,[sort]
  35:            ,[state])
  36:      VALUES
  37:            (
  38:            @leg,
  39:            @description,
  40:            @sort,
  41:            @state
  42:            )
  43: END
  44:  
  45: -- Returns Identity value of the newly inseerted record.
  46: SELECT SCOPE_IDENTITY() AS [ID];
  47:  
  48: GO
  49:  

Nice! Open-mouthed smile