Having a bad SQL day
-
I am sure I am overlooking something very simple but I can't see it. I have a database of locations with latitudes and longitudes. I need to be able to query for locations that fall within a given radius from a lat/lon point The code I am intending to use is: (Showing fixed centre 38,-118 in this version)
SELECT quakeid, (6371 * acos(cos(radians(38)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-118)) + sin(radians(38)) * sin(radians(latitude)))) AS [dkm]
FROM tblUSGSData(Derived from Creating a store locator[^]) This works fine and returns just over half a million entries. My problem is that as soon as I add
HAVING [dkm] <= 50
or
WHERE [dkm] <= 50
I get the message Msg 207, Level 16, State 1, Line 3 Invalid column name 'dkm'. I obviously have not got my SQL glasses on today as I can't seem to resolve this. Can any one make (polite) suggestions?
-
I am sure I am overlooking something very simple but I can't see it. I have a database of locations with latitudes and longitudes. I need to be able to query for locations that fall within a given radius from a lat/lon point The code I am intending to use is: (Showing fixed centre 38,-118 in this version)
SELECT quakeid, (6371 * acos(cos(radians(38)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-118)) + sin(radians(38)) * sin(radians(latitude)))) AS [dkm]
FROM tblUSGSData(Derived from Creating a store locator[^]) This works fine and returns just over half a million entries. My problem is that as soon as I add
HAVING [dkm] <= 50
or
WHERE [dkm] <= 50
I get the message Msg 207, Level 16, State 1, Line 3 Invalid column name 'dkm'. I obviously have not got my SQL glasses on today as I can't seem to resolve this. Can any one make (polite) suggestions?
Well, [dkm] is an Alias, not a columnname. You would need to have the same expression in the having or where clause as you have in the select clause.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Well, [dkm] is an Alias, not a columnname. You would need to have the same expression in the having or where clause as you have in the select clause.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Ah yes. My bad
SELECT quakeid, latitude, longitude, (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude))))
FROM tblUSGSData
GROUP BY quakeid, latitude, longitude
HAVING (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude)))) <= 50works - at least does not produce an error. Not sure about the working bit but that is another story. Many thanks.
-
Ah yes. My bad
SELECT quakeid, latitude, longitude, (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude))))
FROM tblUSGSData
GROUP BY quakeid, latitude, longitude
HAVING (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude)))) <= 50works - at least does not produce an error. Not sure about the working bit but that is another story. Many thanks.
Are you sure you need to use Haversines formula? If you Radiuses are small enough the Cartesian distance formula would do, depending on what coordinate system you're using of course. Ignore that question, I just remembered what USGS is, and realized it's a different sort of Quake you're working with. :doh:
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
I am sure I am overlooking something very simple but I can't see it. I have a database of locations with latitudes and longitudes. I need to be able to query for locations that fall within a given radius from a lat/lon point The code I am intending to use is: (Showing fixed centre 38,-118 in this version)
SELECT quakeid, (6371 * acos(cos(radians(38)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-118)) + sin(radians(38)) * sin(radians(latitude)))) AS [dkm]
FROM tblUSGSData(Derived from Creating a store locator[^]) This works fine and returns just over half a million entries. My problem is that as soon as I add
HAVING [dkm] <= 50
or
WHERE [dkm] <= 50
I get the message Msg 207, Level 16, State 1, Line 3 Invalid column name 'dkm'. I obviously have not got my SQL glasses on today as I can't seem to resolve this. Can any one make (polite) suggestions?
To add to Jorgen's answer, I've always wondered why SQL Server doesn't allow us to use alias in WHERE and HAVING clauses. The answer to that lies in the logical order in which the query is processed. The WHERE and HAVING clauses are processed before the SELECT clause and the alias do not exist at that stage. However, technically it should be possible to introduce another stage earlier in the query processing pipeline where a mapping between expressions and their alias is made and WHERE and HAVING clauses can look up to these mappings and substitute the actual expression in place of the alias.
-
To add to Jorgen's answer, I've always wondered why SQL Server doesn't allow us to use alias in WHERE and HAVING clauses. The answer to that lies in the logical order in which the query is processed. The WHERE and HAVING clauses are processed before the SELECT clause and the alias do not exist at that stage. However, technically it should be possible to introduce another stage earlier in the query processing pipeline where a mapping between expressions and their alias is made and WHERE and HAVING clauses can look up to these mappings and substitute the actual expression in place of the alias.
You are looking for CTE.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
-
Ah yes. My bad
SELECT quakeid, latitude, longitude, (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude))))
FROM tblUSGSData
GROUP BY quakeid, latitude, longitude
HAVING (6371 * acos(cos(radians(37)) * cos(radians(latitude)) * cos(radians(longitude) - radians(-122)) + sin(radians(37)) * sin(radians(latitude)))) <= 50works - at least does not produce an error. Not sure about the working bit but that is another story. Many thanks.
By the way, 50 km is about half a degree. Depending on the amount of rows in your table, it might be wise to filter for longitude between -122.5 and -121.5 and latitude between 36.5 and 37.5 first. Also a simple transformation with 110 km/degree for latitude, and 110 * cos(latitude) for longitude, and then using simple pythagoras might speed up the query.
-
By the way, 50 km is about half a degree. Depending on the amount of rows in your table, it might be wise to filter for longitude between -122.5 and -121.5 and latitude between 36.5 and 37.5 first. Also a simple transformation with 110 km/degree for latitude, and 110 * cos(latitude) for longitude, and then using simple pythagoras might speed up the query.
Thanks for your input. Yes I am adding some filtering now. I just needed to get the thing working first.
-
You are looking for CTE.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull
Yes, or a simple
SELECT ... FROM
(
SELECT ... FROM table WHERE ...
) T
... -
Yes, or a simple
SELECT ... FROM
(
SELECT ... FROM table WHERE ...
) T
...Yes, thats equivalent. I just prefer the readability of the CTE (which is merely an opinion) plus that you can refer to a CTE in more than one place.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull