Looking closely at your data it looks like in the distance calculations you have used different coordinates from those in the *activity_objects* table.
In example on the first query you wrote:
[...] ST_GeographyFromText('SRID=4326;POINT(-70.01 15.01)'));
where it should have been:
[...] ST_GeographyFromText('SRID=4326;POINT(-73.01 15.01)'));
Notice -70.01 being used in place of -73.01.
hence the discrepancies in all your subsequent calculations.
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
North, east, south and west are directions relative to “something”. The two things you are taking about are:
If you do not mix these two things, I can't see no problem.
The eastmost point in the blue polygon is marked with a red point. We are talking about a position relative to the polygon. We to not talk about a position relative to Greenwich. We even do not know whether the point is located east or west of Greenwich. And it is regardless of where prime meridian is defined. Even if the prime meridian is not defined in Greenwich but in Berlin, Moscow or Tokyo: The red point is still the eastmost point of our blue polygon.
Let us now replace the blue polygon by the area of USA. Maine is the easternmost point in USA:
If we use a GIS and want to ask “where is the easternmost point of USA” we must not ask: “Where is the maximum x coordinate (in eastern direction) measured in a geographic coordinate system where prime meridian is set to Greenwich?”. The answer of the second question will be “Aleutians” (180 degree) .