sql server 2008 query

Posted on 2013-09-24
Medium Priority
Last Modified: 2013-09-24
I'm using SQL server 2008 R2

I have a table called EmployeesTable , it looks like this:

This is the script to create this table:

CREATE TABLE [dbo].[EmployeesTable](
	[EmployeeID] [int] NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[Address] [varchar](100) NULL,
	[City] [varchar](50) NULL,
	[State] [varchar](50) NULL,
	[ZipCode] [varchar](50) NULL
INSERT [dbo].[EmployeesTable] ([EmployeeID], [LastName], [FirstName], [Address], [City], [State], [ZipCode]) VALUES (1, N'Bugs', N'Bunny', N'1 Legends Way', N'Arlington', N'TX', N'76011')
INSERT [dbo].[EmployeesTable] ([EmployeeID], [LastName], [FirstName], [Address], [City], [State], [ZipCode]) VALUES (2, N'Daffy', N'Duck', N'490 Jamestown Avenue', N'San Francisco', N'CA', N'94124')
INSERT [dbo].[EmployeesTable] ([EmployeeID], [LastName], [FirstName], [Address], [City], [State], [ZipCode]) VALUES (3, N'Marvin', N'Martian', N'1000 Elysian Park Avenue', N'Los Angeles', N'CA', N'90012')

Open in new window

Is there a way to write a query where two additional columns are created where the addresses for each employee is geocoded and the latitude for that address is placed in a column called Lat and the longitude for that address is placed in a column called Long ?

So my query result should look something like this except it would have the lat, long values in these columns.

my desired result set
See what I plan to do is to generate xml from my query result set and then feed that xml to google maps javascript api to plot these addresses on a google map.

If there is a better technique to use , please let me know.
I already know how to create a map with xml data, i'm just having trouble geocoding a table of addresses.
Question by:maqskywalker
1 Comment
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 39518190
I would go with a geography datatype.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Over time, the online landscape has altered considerably, but that’s nothing compared to the up-and-coming trends that will shape the web design industry in the coming year. Keep reading to find out which trends will shape B2B web design in 2018.
How do you create a user-centered user experience on your website? And what are some things you should consider in the process?
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question