The issue is with your use of STDistance rather than the index. This function calculates the distance between two points so you need to replace HomeGeog with a feature rather than the name of the table.
E.g. @g.STDistance(@g) should work (but always return 0)
What are you trying to return from your query?
See http://msdn.microsoft.com/en-us/library/bb933952.aspx for more details on how to use the STDistance function.
Further to Russell's Answer
It's unfortunate that you have you locations stored in the wrong data type. The easiest way to get an accurate distance between your is to cast the geometries as geographies.
You will need to confirm the the Lat is stored in the Y and the Long is stored in the X of the geometry, otherwise you will most likely end up with inaccurate distances.
If you are doing a Nearest Neighbour query you will need to take into account that 1º North/South is different than 1º East/West and the distance returned from a geometry containing Lat/Long will not reflect this. Generally this is unlike to cause an issue, but you may end up with nearest neighbours in the wrong order.
To demonstrate this
CREATE TABLE #TestGeomVsGeog (
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Geom Geometry,
Geog Geography
);
INSERT INTO #TestGeomVsGeog (Geom, Geog)
VALUES
(Geometry::Point(-57,-25,4326), Geography::Point(-25,-57,4326)), -- AnchorPoint
(Geometry::Point(-58,-25,4326), Geography::Point(-25,-58,4326)), -- 1 degree East
(Geometry::Point(-57,-26,4326), Geography::Point(-26,-57,4326)), -- 1 degree South
(Geometry::Point(-57,-25.9999,4326), Geography::Point(-25.9999,-57,4326)) -- Closest Point
;
SELECT a.ID FromID, b.ID ToID,
a.geom.STDistance(b.geom) GeomDegreeDistance,
a.geog.STDistance(b.geog) GeogMetreDistance,
ROW_NUMBER() OVER (ORDER BY a.geom.STDistance(b.geom)) GeometryOrder,
ROW_NUMBER() OVER (ORDER BY a.geog.STDistance(b.geog)) GeographyOrder
FROM (SELECT * FROM #TestGeomVsGeog WHERE ID = 1) a,
(SELECT * FROM #TestGeomVsGeog WHERE ID != 1) b
DROP TABLE #TestGeomVsGeog
This returns the following
FromID ToID GeomDegreeDistance GeogMetreDistance GeometryOrder GeographyOrder
----------- ----------- ---------------------- ------------------ ---------------- ----------------
1 2 1 100949.861402787 2 1
1 4 0.9999 110769.324596193 1 2
1 3 1 110780.403395342 3 3
As you can see the Geometry distance will end up with the wrong point as the nearest neighbour.
Depending on the amount of data you are dealing with I would suggest creating copy of the original into temporary table (using geography) to do the processing on, or lobby to have the original table datatype changed to a geography.
Best Answer
I'm not sure this is the most efficient way of doing it, but it certainly works:
where 4326 is the spatial reference ID (SRID)