How to Use Google Map API From Stored Procedure

Hi Guys,

Are there any tutorials on how to use google map API and connect it with the stored procedure to make the marker points?

regards
Ali ShahSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ali ShahSQL DeveloperAuthor Commented:
Hi Terry,

Sorry i should have been more specific. So the requirement is that if a user types an address and the radius. The google maps should create the markers on the suppliers within that radius. Now the address of the suppliers are saved within our SQL Server database with Lat ad Long.

regards
0
ZberteocCommented:
You can't use Google APIs from SQL server and they were never intended for that. SO your problem, if I understand correctly is this:

From an application you get a location in the form of lat-long coordinates, a radius and then you have to find all the suppliers within that radius by querying the supplier table in the database.

This can be done in 2 ways:

1. Using the lat long given by the user and select all the rows from the table where the distance between supplier lat long and the user lat long <= radius

2. You can use the spatial datatypes to store the location of the suppliers and then buffer the user location by the radius and select from the table all the suppliers where location intersects the user buffered location.


I would recommend the first approach because is simpler and doesn't need spatial datatype which are not familiar for everyone. For this you need a distance function to be created in teh same database where the suppliers table is:
CREATE FUNCTION [dbo].[fnDistance] 
( 
	@Lat1 decimal(11, 8), 
	@Lng1 decimal(11, 8),
	@Lat2 decimal(11, 8),
	@Lng2 decimal(11, 8)
)
RETURNS decimal(10, 6) -- max 10km
AS
BEGIN

if abs(@Lat2-@Lat1) > 1 or abs(@Lng2-@Lng1) > 1
	return 10
declare	@distance decimal(30, 8)

declare @R as int
select @R = 6371000

declare @dLat as decimal(21, 17)
declare @dLng as decimal(21, 17)
select @dLat = (@Lat2-@Lat1) * pi() / 180
select @dLng = (@Lng2-@Lng1) * pi() / 180

declare @a as decimal(18, 18)
declare @c as decimal(18, 18)
select @a = Sin(@dLat / 2) * Sin(@dLat / 2) +
	Cos(@Lat1 * pi() / 180) * Cos(@Lat2 * pi() / 180) * Sin(@dLng /2) * Sin(@dLng / 2);
select @c = 2 * Atn2(Sqrt(@a), Sqrt(1-@a))

select @distance = (@R * @c )/1000-- % 10000

return @distance 


END

Open in new window

Use this function to find the suppliers like this:
declare
	@UserLat decimal(11,8)=42.998282,
	@UserLong decimal (11,8)=-81.243822,
	@radius int =1500 -- radius should be in meters
	
select 
	* 
from 
	Suppliers 
where
	[dbo].[fnDistance](SupplierLat, SupplierLong, @UserLat, @UserLong)<@radius

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ali ShahSQL DeveloperAuthor Commented:
Thanks a lot it did help.
Kindest regards
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.