Point System ideas

[Corfman Clan]

This is more a Nuts-N-Bolts question, and this topic is off-topic of this thread, but anywho.... What equation(s) are you using to compute distance (great circle, etc.)? If you have a link, I'd like see the formulation.

We're using the SQL Server database engine, so it's just a sql query. I don't know what it uses. Basically, each cache has a Location column of type Geography. Then we use the spatial distance function. A basic query looks something like:

Code: Select all

DECLARE @A Geography
DECLARE @B Geography
SET @A = Geography::Point(33.00000, -112.00000, 4326)
SET @B = Geography::Point(34.00000, -113.00000, 4326)
-- 4326 is the Spatial Reference ID (SRID) for the WGS 84 Datum
SELECT @A.STDistance(@B) AS MetersFromAtoB
-- MetersFromAtoB = 144692.110342176

So the query I ran to get my most wanted is:

Code: Select all

declare @Me bigint
declare @Home Geography
set @Me=93710 -- My geocaching.com ID
set @Home = Geography::Point(33.333333, -112.112112, 4326) -- bogus coordinates

;WITH MyUnfound
AS
(
	Select
		*
		,(Points / (NumFinders + 1)) AS NextFindPts
		,Location.STDistance(@Home) / 1000 * 0.621371192 AS Miles
		,(CASE
		  WHEN (Location.STDistance(@Home) / 1000 * 0.621371192) <= 1
		  THEN Points / (NumFinders + 1)
		  ELSE (Points / (NumFinders + 1)) / (Location.STDistance(@Home) / 1000 * 0.621371192)
		  END) AS PtsPerMile
	FROM Geocache
	WHERE ID NOT IN(
		SELECT ID FROM Geocache WHERE OwnerID = @Me
		UNION
		SELECT CacheID FROM Log WHERE CacherID =@Me
	)
)
SELECT TOP 20 Code, Name, FinderPoints, NextFindPts, Miles, PtsPerMile 
FROM MyUnfound
WHERE Miles <= 100 AND 10 <= NextFindPts
ORDER BY PtsPerMile DESC

[Corfman Clan]

Cheaters! Er... I mean, well, that's a ... convenient ... piece of functionality to have available.

Hah, yes it is convenient. I crossposted but I believe my last post answers most of your questions.

[rocketsciguy]

Cheaters! Er... I mean, well, that's a ... convenient ... piece of functionality to have available.

Hah, yes it is convenient. I crossposted but I believe my last post answers most of your questions.

Yes, indirectly you answered most. The SRID concept was vague (at least to me) on the MSDN site, but knowing '4326' ties it to WGS-84 helps, as does knowing the distance calculated is in meters. The sample calculation you showed matches FizzyCalc's High Accuracy calculation to 0.2 millimeters, so I think you can consider it validated.

And if you want an exact conversion (cleaner looking code too), there are exactly 1609.344 meters in a statute mile (=5280 ft/mi * 0.3048 m/ft, by definition). For example:

Code: Select all

Location.STDistance(@Home) / 1609.344 AS Miles

[Corfman Clan]

And if you want an exact conversion (cleaner looking code too), there are exactly 1609.344 meters in a statute mile (=5280 ft/mi * 0.3048 m/ft, by definition). For example:

Code: Select all

Location.STDistance(@Home) / 1609.344 AS Miles

True that would be a bit more exact. For a query I ran with a distance just under 100 miles, the difference was only a few 100 millionths of a mile.
So in reality, for our purposes, it doesn't make a difference.