July 14, 2008 at 11:25 am
I am working on doing a proximity search and the concept is simple to an extent. right now, i have what i need for the proximity search. What i Dont' have is a table that has updated Latitudes and Longitudes. Here is what i have for so far. This is going to be a stored procedure.
DECLARE @loc AS VARCHAR(50)
DECLARE @long AS FLOAT
DECLARE @lats AS FLOAT
Select @loc = zip FROM Locations
UPDATE Locations
SET Latitude = g.Latitude,
Longitude = g.Longitude
FROM dbo.fncGeocodeByLocation( @loc, 'appidhere') g
WHERE @loc = Locations.zip;
SELECT @lats = Latitude FROM Locations WHERE zip = @loc
SELECT @long = Longitude FROM Locations WHERE zip = @loc
UPDATE Locations
SETXAxis = dbo.XAxis(@lats,@long)
FROM Locations
WHERE Locations.zip = @loc;
UPDATE Locations
SETYAxis = dbo.YAxis(@lats,@long)
FROM Locations
WHERE Locations.zip = @loc;
UPDATE Locations
SETZAxis = dbo.ZAxis(@lats)
FROM Locations
WHERE Locations.zip = @loc;
--This is only to show me the results of the Above Actions, not going to be part of the stored procedure.
GO
SELECT * FROM Locations
Now, when i execute this, i only get an update on ONE row, and only the last row. I'm not sure what my select statement is missing or if it is an update statment error, but i cant get it to do any other Zip code unless I change the:
SELECT @loc = Zip from Locations
to
SET @loc = '50401'
Thanks.
txtSignature_CommentEmoticon(':)');
🙂
July 14, 2008 at 11:37 am
This statement:
Select @loc = zip FROM Locations
Will only return one record -- in reality indeterminate but usually the last one.
It looks like if you remove the restriction @loc = Locations.zip from each of your queries and use the table values instead of variables for the lat and long values you will get all the records updated. Also, you seem to only need two updates, the first to set the lat and long, the second to set the axis values.
If you want to do a specific zipcode at a time, then pass the zipcode in as a parameter to your stored procedure.
July 14, 2008 at 11:58 am
Thanks that helped to shorten my proc, but that still didn't fix the problem, or i don't understand. here is the New proc:
DECLARE @loc AS VARCHAR(50)
--Select @loc = zip_code FROM Locations
--SET @loc = ''
UPDATE Locations
SET Latitude = g.Latitude,
Longitude = g.Longitude,
XAxis = dbo.XAxis(g.Latitude,g.Longitude),
YAxis = dbo.YAxis(g.Latitude,g.Longitude),
ZAxis = dbo.ZAxis(g.Latitude)
FROM dbo.fncGeocodeByLocation( @loc, 'appidhere') g
WHERE @loc = Locations.Zip
GO
SELECT * FROM Locations
Now i still have it updating only One Row. I have to have an input (@loc) for the fncGeocodeByAddress TVF. The Locations Table Currently has inserted the Zip codes that i am using for the location, but i want the row to be returned updated and then to do the next one and so on. I know there is more to this than that, but i am not sure what to do next.
txtSignature_CommentEmoticon(':)');
🙂
July 14, 2008 at 12:22 pm
This, "Select @loc = zip FROM Locations", will always select the last value in the table, based on the clustered index. Then each of your following updates uses that as the Where clause. That's why it's only updating the last row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 12:25 pm
Try this:
UPDATE Locations
SET Latitude = g.Latitude,
Longitude = g.Longitude
FROM
Locations
cross apply dbo.fncGeocodeByLocation(zip, 'appidhere') g
That should get you all the rows for the first update. The rest should come out of that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 2:16 pm
Thanks So Much! That got if figured out. I'm sure i'll still be confused somewhere, but it's help like this that is always appreciated.
txtSignature_CommentEmoticon(':)');
🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply