Converting Non-Spatial Data to Spatial Data

Question

Converting Non-Spatial Data to Spatial Data

thomashohner

SSCrazy Eights

Points: 9856
More actions
August 13, 2013 at 4:19 pm

#277024

I have been looking in my SSRS book and online and can not find a clear answer to this question. I have a list of 30 dummy CPT codes in our EHR that our MD's use while on Outreaches to different shelters or Motels each dummy code represents the location they are doing their Dr. Visit at. For instance code (OR001)CPT Code = Label (Albuquerque Rescue Mission). I would like to be able to convert each code to a spatial Long and Lat that i can then use in Report Builders mapping tool. I do not mind looking up each of the 30 codes actual Long/Lat using bing.
If I could get the spacial conversion, we could create a Heat Map over our city where we have the most outreach encounters.
What is the best process or where can I find good information on accomplishing such a feat.
Any help is appreciated
Thanks Thomas
***SQL born on date Spring 2013:-)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mickyT SSChampion Points: 10360 More actions · Answer 1

I suppose if you don't mind manually doing this, you could locate each of your CPTs in Google maps. For each one right click in the map and select 'What's here'. That should put the Lat/Long in the search field.

Another way is to click the link icon which will pop up a dialog with an URL like the following:

https://maps.google.com/?ll=35.284589,-95.583394&spn=0.000835,0.001414&t=h&z=20

The lat/long should be the center of the map

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 2

But how would i do that in SQl? I figured I would need to have some sort of statment saying that when code OR001 is returned it equals a specific Latitude and Longitude... Of course hand entered via looking up each location as the way you described. This is a hard one because evry thing I find on spatial data assumes it is already part of the result set. I want to add the spatial data to my result set by hand since its only going to be 30 seperate values.

If I said that I wanted each zipcode from our registration to equal a specific lat/longitude that I picked by hand would that make more sence as a way to describe my issue?

***SQL born on date Spring 2013:-)

Ville-Pekka Vahteala SSCrazy Points: 2458 More actions · Answer 3

I think you just need to create table with columns code and location.

create table codeToLocation (code varchar(20), location geography);

After setting up data you just join this table to your query.

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 4

Yes, I think this is what I am concluding could I still do this with just a temp table? Sorry if the question sounds dumb still very new.

***SQL born on date Spring 2013:-)

mickyT SSChampion Points: 10360 More actions · Answer 5

Sorry for the delay getting back.

There would be no problem using a Geography in a temporary table

CREATE TABLE #TempLocation

(Code Varchar(5) PRIMARY KEY

,Location Geography

)

INSERT INTO #TempLocation

VALUES ('OR001',Geography::Point(-95.583394,35.284589, 4326))

,('OR002',Geography::Point(-95.583494,35.284579, 4326))

SELECT * from #TempLocation

Please note the coordinates I have used are probably no where near where you want them:-)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 6

Thanks Mickey this is the second time you have helped me and I very much appreciate it. I still very new with SQL and trying my best to learn it as fast as possible. This is going to help us out immensley !

Currently finishing SQl quires 2008 Joes 2 pros volume 2.

Thanks Again.

***SQL born on date Spring 2013:-)