updated google map sample part3
USE [MLS]
GO
/****** Object: UserDefinedFunction [dbo].[SplitWords] Script Date: 02/25/2008 15:55:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitWords](@text varchar(8000))
RETURNS @words TABLE (pos smallint primary key, value varchar(8000))
AS
BEGIN
DECLARE @pos smallint,
@i smallint,
@j smallint,
@s varchar(8000)
SET @pos = 1
WHILE @pos <= LEN(@text)
BEGIN
SET @i = CHARINDEX(' ', @text, @pos)
SET @j = CHARINDEX(',', @text, @pos)
IF @i > 0 OR @j > 0
BEGIN
IF @i = 0 OR (@j > 0 AND @j < @i)
SET @i = @j
IF @i > @pos
BEGIN
-- @i now holds the earliest delimiter in the string
SET @s = SUBSTRING(@text, @pos, @i - @pos)
INSERT INTO @words
VALUES (@pos, @s)
END
SET @pos = @i + 1
WHILE @pos < LEN(@text) AND SUBSTRING(@text, @pos, 1) IN (' ', ',')
SET @pos = @pos + 1
END
ELSE
BEGIN
INSERT INTO @words
VALUES (@pos, SUBSTRING(@text, @pos, LEN(@text) - @pos + 1))
SET @pos = LEN(@text) + 1
END
END
RETURN
END
GO
/****** Object: UserDefinedFunction [dbo].[SplitWords] Script Date: 02/25/2008 15:55:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitWords](@text varchar(8000))
RETURNS @words TABLE (pos smallint primary key, value varchar(8000))
AS
BEGIN
DECLARE @pos smallint,
@i smallint,
@j smallint,
@s varchar(8000)
SET @pos = 1
WHILE @pos <= LEN(@text)
BEGIN
SET @i = CHARINDEX(' ', @text, @pos)
SET @j = CHARINDEX(',', @text, @pos)
IF @i > 0 OR @j > 0
BEGIN
IF @i = 0 OR (@j > 0 AND @j < @i)
SET @i = @j
IF @i > @pos
BEGIN
-- @i now holds the earliest delimiter in the string
SET @s = SUBSTRING(@text, @pos, @i - @pos)
INSERT INTO @words
VALUES (@pos, @s)
END
SET @pos = @i + 1
WHILE @pos < LEN(@text) AND SUBSTRING(@text, @pos, 1) IN (' ', ',')
SET @pos = @pos + 1
END
ELSE
BEGIN
INSERT INTO @words
VALUES (@pos, SUBSTRING(@text, @pos, LEN(@text) - @pos + 1))
SET @pos = LEN(@text) + 1
END
END
RETURN
END
USE [MLS]
GO
/****** Object: StoredProcedure [dbo].[SaveLocationSearch] Script Date: 02/25/2008 15:56:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveLocationSearch]
@title nvarchar(50),
@city nvarchar(50),
@region nvarchar(50),
@country nvarchar(50),
@longitude nvarchar(50),
@latitude nvarchar(50),
@subcategoryID int,
@phone nvarchar(50)
AS
DECLARE @Counter int
BEGIN
DECLARE @ErrorCode int
SELECT @ErrorCode = 0
DECLARE @TranStarted bit
SELECT @TranStarted = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SELECT @TranStarted = 1
END
ELSE
SELECT @TranStarted = 0
UPDATE testcounter SET [count]=[count]+1
SELECT @Counter = COUNT(*)
FROM nearby
WHERE longitude = @longitude
AND latitude = @latitude
AND title = @title
AND city = @city
AND region = @region
AND country = @country
AND subcategoryID = @subcategoryID
AND phone = @phone
IF (@Counter=0)
BEGIN
INSERT INTO nearby
(
title,
city,
region,
country,
longitude,
latitude,
subcategoryid,
phone
)
VALUES
(
@title,
@city,
@region,
@country,
@longitude,
@latitude,
@subcategoryid,
@phone
)
END
IF(@@ERROR <> 0)
BEGIN
SELECT @ErrorCode = -1
GOTO Cleanup
END
IF(@TranStarted = 1)
BEGIN
SELECT @TranStarted = 0
COMMIT TRANSACTION
END
Cleanup:
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[SaveLocationSearch] Script Date: 02/25/2008 15:56:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SaveLocationSearch]
@title nvarchar(50),
@city nvarchar(50),
@region nvarchar(50),
@country nvarchar(50),
@longitude nvarchar(50),
@latitude nvarchar(50),
@subcategoryID int,
@phone nvarchar(50)
AS
DECLARE @Counter int
BEGIN
DECLARE @ErrorCode int
SELECT @ErrorCode = 0
DECLARE @TranStarted bit
SELECT @TranStarted = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SELECT @TranStarted = 1
END
ELSE
SELECT @TranStarted = 0
UPDATE testcounter SET [count]=[count]+1
SELECT @Counter = COUNT(*)
FROM nearby
WHERE longitude = @longitude
AND latitude = @latitude
AND title = @title
AND city = @city
AND region = @region
AND country = @country
AND subcategoryID = @subcategoryID
AND phone = @phone
IF (@Counter=0)
BEGIN
INSERT INTO nearby
(
title,
city,
region,
country,
longitude,
latitude,
subcategoryid,
phone
)
VALUES
(
@title,
@city,
@region,
@country,
@longitude,
@latitude,
@subcategoryid,
@phone
)
END
IF(@@ERROR <> 0)
BEGIN
SELECT @ErrorCode = -1
GOTO Cleanup
END
IF(@TranStarted = 1)
BEGIN
SELECT @TranStarted = 0
COMMIT TRANSACTION
END
Cleanup:
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
USE [MLS]
GO
/****** Object: StoredProcedure [dbo].[SearchNearBy] Script Date: 02/25/2008 15:56:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchNearBy]
@categories nvarchar(2000),
@longitudeDifference nvarchar(50),
@latitudeDifference nvarchar(50),
@longitude nvarchar(50),
@latitude nvarchar(50)
AS
DECLARE @words AS TABLE
(
pos smallint primary key,
[value] varchar(8000)
)
DECLARE @subcategories AS TABLE
(
subcategoryID int
)
BEGIN
DECLARE @ErrorCode int
SELECT @ErrorCode = 0
DECLARE @TranStarted bit
SELECT @TranStarted = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SELECT @TranStarted = 1
END
ELSE
SELECT @TranStarted = 0
INSERT INTO @words(pos,[value])
SELECT * FROM dbo.SplitWords(@categories)
DECLARE c CURSOR FOR
SELECT [value] FROM @words
DECLARE @category AS nvarchar(100)
OPEN c
LOOP:
FETCH NEXT FROM c INTO
@category
IF @@FETCH_STATUS = -1 GOTO END_LOOP /* EOF */
IF @@FETCH_STATUS = -2 GOTO LOOP /* DELETED ROW */
DECLARE @categoryID AS int
SELECT @categoryID = categoryid FROM category where Description like @category
INSERT INTO @subcategories(subcategoryID)
SELECT subcategoryID FROM subcategory WHERE categoryid = @categoryid
GOTO LOOP
END_LOOP:
CLOSE c
DEALLOCATE c
SELECT *
FROM nearby
WHERE (longitude<>'null') AND (latitude<>'null')
AND
(
CAST(longitude AS DECIMAL (18,8))
BETWEEN (CAST(@longitude AS DECIMAL (18,8))-CAST(@longitudeDifference AS DECIMAL (18,8)))
AND (CAST(@longitude AS DECIMAL (18,8))+CAST(@longitudeDifference AS DECIMAL (18,8)))
)
AND
(
CAST(latitude AS DECIMAL (18,8))
BETWEEN (CAST(@latitude AS DECIMAL (18,8))-CAST(@latitudeDifference AS DECIMAL (18,8)))
AND (CAST(@latitude AS DECIMAL (18,8))+CAST(@latitudeDifference AS DECIMAL (18,8)))
)
AND subcategoryID IN
(
SELECT subcategoryID FROM @subcategories
)
IF(@@ERROR <> 0)
BEGIN
SELECT @ErrorCode = -1
GOTO Cleanup
END
IF(@TranStarted = 1)
BEGIN
SELECT @TranStarted = 0
COMMIT TRANSACTION
END
Cleanup:
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[SearchNearBy] Script Date: 02/25/2008 15:56:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchNearBy]
@categories nvarchar(2000),
@longitudeDifference nvarchar(50),
@latitudeDifference nvarchar(50),
@longitude nvarchar(50),
@latitude nvarchar(50)
AS
DECLARE @words AS TABLE
(
pos smallint primary key,
[value] varchar(8000)
)
DECLARE @subcategories AS TABLE
(
subcategoryID int
)
BEGIN
DECLARE @ErrorCode int
SELECT @ErrorCode = 0
DECLARE @TranStarted bit
SELECT @TranStarted = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SELECT @TranStarted = 1
END
ELSE
SELECT @TranStarted = 0
INSERT INTO @words(pos,[value])
SELECT * FROM dbo.SplitWords(@categories)
DECLARE c CURSOR FOR
SELECT [value] FROM @words
DECLARE @category AS nvarchar(100)
OPEN c
LOOP:
FETCH NEXT FROM c INTO
@category
IF @@FETCH_STATUS = -1 GOTO END_LOOP /* EOF */
IF @@FETCH_STATUS = -2 GOTO LOOP /* DELETED ROW */
DECLARE @categoryID AS int
SELECT @categoryID = categoryid FROM category where Description like @category
INSERT INTO @subcategories(subcategoryID)
SELECT subcategoryID FROM subcategory WHERE categoryid = @categoryid
GOTO LOOP
END_LOOP:
CLOSE c
DEALLOCATE c
SELECT *
FROM nearby
WHERE (longitude<>'null') AND (latitude<>'null')
AND
(
CAST(longitude AS DECIMAL (18,8))
BETWEEN (CAST(@longitude AS DECIMAL (18,8))-CAST(@longitudeDifference AS DECIMAL (18,8)))
AND (CAST(@longitude AS DECIMAL (18,8))+CAST(@longitudeDifference AS DECIMAL (18,8)))
)
AND
(
CAST(latitude AS DECIMAL (18,8))
BETWEEN (CAST(@latitude AS DECIMAL (18,8))-CAST(@latitudeDifference AS DECIMAL (18,8)))
AND (CAST(@latitude AS DECIMAL (18,8))+CAST(@latitudeDifference AS DECIMAL (18,8)))
)
AND subcategoryID IN
(
SELECT subcategoryID FROM @subcategories
)
IF(@@ERROR <> 0)
BEGIN
SELECT @ErrorCode = -1
GOTO Cleanup
END
IF(@TranStarted = 1)
BEGIN
SELECT @TranStarted = 0
COMMIT TRANSACTION
END
Cleanup:
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
USE [MLS]
GO
/****** Object: StoredProcedure [dbo].[SearchSurrounding] Script Date: 02/25/2008 15:56:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchSurrounding]
@longitude nvarchar(50),
@latitude nvarchar(50),
@longitudeDifference nvarchar(50),
@latitudeDifference nvarchar(50),
@minPrice money,
@maxPrice money,
@beds tinyint,
@baths decimal(7,3)
AS
BEGIN
DECLARE @ErrorCode int
SELECT @ErrorCode = 0
DECLARE @TranStarted bit
SELECT @TranStarted = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SELECT @TranStarted = 1
END
ELSE
SELECT @TranStarted = 0
select distinct zipcode,
ltrim(rtrim(mlsnumber)),
SUBSTRING(mlsnumber,DATALENGTH(mlsnumber)-4,2),
lprice,
isnull(communityd,'unknown'),
beds,
baths,
type1,
isnull(Tarea,0),
picturecount,
(select longitude from mlstest where mls=mlsnumber),
(select latitude from mlstest where mls=mlsnumber),
address
from V_RES_IDX_TESTING
where mlsnumber IN
(
SELECT mls
FROM mlstest
WHERE (longitude<>'null')
AND
(
CAST(longitude AS DECIMAL (18,8))
BETWEEN (CAST(@longitude AS DECIMAL (18,8))-CAST(@longitudeDifference AS DECIMAL (18,8)))
AND (CAST(@longitude AS DECIMAL (18,8))+CAST(@longitudeDifference AS DECIMAL (18,8)))
)
AND
(
CAST(latitude AS DECIMAL (18,8))
BETWEEN (CAST(@latitude AS DECIMAL (18,8))-CAST(@latitudeDifference AS DECIMAL (18,8)))
AND (CAST(@latitude AS DECIMAL (18,8))+CAST(@latitudeDifference AS DECIMAL (18,8)))
)
)
AND
(
lprice>=@minPrice AND lprice<=@maxPrice
)
AND (beds>@beds)
AND (baths>@baths)
IF(@@ERROR <> 0)
BEGIN
SELECT @ErrorCode = -1
GOTO Cleanup
END
IF(@TranStarted = 1)
BEGIN
SELECT @TranStarted = 0
COMMIT TRANSACTION
END
Cleanup:
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
GO
/****** Object: StoredProcedure [dbo].[SearchSurrounding] Script Date: 02/25/2008 15:56:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SearchSurrounding]
@longitude nvarchar(50),
@latitude nvarchar(50),
@longitudeDifference nvarchar(50),
@latitudeDifference nvarchar(50),
@minPrice money,
@maxPrice money,
@beds tinyint,
@baths decimal(7,3)
AS
BEGIN
DECLARE @ErrorCode int
SELECT @ErrorCode = 0
DECLARE @TranStarted bit
SELECT @TranStarted = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SELECT @TranStarted = 1
END
ELSE
SELECT @TranStarted = 0
select distinct zipcode,
ltrim(rtrim(mlsnumber)),
SUBSTRING(mlsnumber,DATALENGTH(mlsnumber)-4,2),
lprice,
isnull(communityd,'unknown'),
beds,
baths,
type1,
isnull(Tarea,0),
picturecount,
(select longitude from mlstest where mls=mlsnumber),
(select latitude from mlstest where mls=mlsnumber),
address
from V_RES_IDX_TESTING
where mlsnumber IN
(
SELECT mls
FROM mlstest
WHERE (longitude<>'null')
AND
(
CAST(longitude AS DECIMAL (18,8))
BETWEEN (CAST(@longitude AS DECIMAL (18,8))-CAST(@longitudeDifference AS DECIMAL (18,8)))
AND (CAST(@longitude AS DECIMAL (18,8))+CAST(@longitudeDifference AS DECIMAL (18,8)))
)
AND
(
CAST(latitude AS DECIMAL (18,8))
BETWEEN (CAST(@latitude AS DECIMAL (18,8))-CAST(@latitudeDifference AS DECIMAL (18,8)))
AND (CAST(@latitude AS DECIMAL (18,8))+CAST(@latitudeDifference AS DECIMAL (18,8)))
)
)
AND
(
lprice>=@minPrice AND lprice<=@maxPrice
)
AND (beds>@beds)
AND (baths>@baths)
IF(@@ERROR <> 0)
BEGIN
SELECT @ErrorCode = -1
GOTO Cleanup
END
IF(@TranStarted = 1)
BEGIN
SELECT @TranStarted = 0
COMMIT TRANSACTION
END
Cleanup:
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END