Interactive Google Map Web Part for Sharepoint 2010 – Part 2

 Computer, Software  Comments Off on Interactive Google Map Web Part for Sharepoint 2010 – Part 2
Jan 072012
 

Feature-creep version! I have added an additional list configuration/lookup column called Color.

This allows you to specify an alternate icon/color for your push-pins by item.

The format of the Color column in your list should be Single Line of Text and in the form of a URL. In the example below, I used:

http://maps.google.com/intl/en_us/mapfiles/ms/micons/purple-dot.png
http://maps.google.com/intl/en_us/mapfiles/ms/micons/yellow-dot.png
http://maps.google.com/intl/en_us/mapfiles/ms/micons/green-dot.png
http://maps.google.com/intl/en_us/mapfiles/ms/micons/red-dot.png

Here’s the Source; .WSP is in the /Bin/Release Directory.

I don’t know C-Sharp from Sh!T; so your mileage may vary. May break if you specify your Color column and have a NULL in there.

SQL Function to convert Float to VarChar

 Computer, Software  Comments Off on SQL Function to convert Float to VarChar
Jan 032012
 

This is related to the Google Maps web part from an earlier post. I have latitude and longitude columns in a database table. Both are Float datatypes. I needed to build a URL string for the push-pin description box. That would seem straight-forward enough, however, I was receiving an error on converting a float value to a varchar. Below is my first take on the SQL to create my location list. This location list is connected to Sharepoint as an external content type and external BCS list.

INSERT INTO LocationsPins (Title, Description, Latitude, Longitude)
Select Address as Title, ('URL: <a href="/sandbox/lists/locationdata/Read%20List.aspx?Lat='
+ Latitude + '">Location Detail</a>') AS Description, Latitude, Longitude
From LocationData
Where Latitude IS NOT NULL
and Latitude > 0
AND Longitude IS NOT NULL
AND Address IS NOT NULL
AND City is NOT NULL
AND State is NOT NULL
AND Zip is NOT NULL
GROUP BY Address, Latitude, Longitude

That by itself threw the error regarding converting a float to varchar. Using Convert or Cast was not getting what I needed as Latitude can be 6 or more decimal places and those functions would add trailing Zeroes.

So, I had to create a Function that would take this into account.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FloatConvert]
(@ParmValue Float)
RETURNS VARCHAR(25)
AS
BEGIN
DECLARE @RetValue VARCHAR(25)
SET @RetValue = CAST(CAST(@ParmValue AS DECIMAL(15,7)) AS VARCHAR(20))
SET @RetValue = REVERSE(@RetValue)
WHILE SUBSTRING(@RetValue,1,1) = 0
BEGIN
SET @RetValue = SUBSTRING(@RetValue,2,25)
END
SET @RetValue = REVERSE(@RetValue)
RETURN @RetValue
END

The SQL query (description field) to build the list looks like this;

Select Address as Title, (‘URL: <a href=”/sandbox/lists/locationdata/Read%20List.aspx?Lat=’+ dbo.FloatConvert(Latitude) + ‘”>Location Detail</a>’) AS Description, Latitude, Longitude

Hope this helps someone as I’ve seen postings regarding this conversion during some Google searches.