So what's the area of the earth? Well…depends on who's measurement you want to use.
There an interesting SQL Server Denali spatial feature known as fullglobe. Actually there's two features with that name, more later; I was using the FULLGLOBE geometric type (geometric type is the term used by OGC, this one only works with SQL Server's GEOGRAPHY data type). You create one like this:
DECLARE @g geography = ('FULLGLOBE', 4326) — where 4326 is your SRID
It's a special extension to the OGC (that doesn't have a specific geography type as such either) that specifies the entire earth. So you can get the area of the earth by using @g.STArea(). After doing the "figure out the water area by subtracting the union of the countries' area" thing with it, I thought this might be a cool way to demonstrate one facet of SRIDs (that is spatial reference system identifiers).
There's a table called sys.spatial_reference_systems that lists all of the SRIDs supported by SQL Server's geography type (they use these in the computations). The also includes the OCG well-known text that describes them. One parameter describes the ellipsoid used by this SRID. Ellipsoids' measurements differ for two main reasons; different surveys and the fact that the earth changes shape slightly over time.
So you could probably guess (and you'd be right) that different SRIDs would produce different areas for the earth. Here's the code. Note that as a special bonus for using it, I found two new SRIDs in Denali, Unit Sphere (SRID 101) and Spherical Earth (SRID 126). Nice.
SELECT spatial_reference_id AS SRID,
dbo.measure_globe(spatial_reference_id) AS Area,
unit_of_measure,
well_known_text,
dbo.get_ellipsoid(well_known_text) AS Ellipsoid
FROM sys.spatial_reference_systems
ORDER BY area, ellipsoid, spatial_reference_id – whatever you want
CREATE FUNCTION dbo.measure_globe(@srid INT)
RETURNS FLOAT
AS
BEGIN
DECLARE @g geography = geography::STGeomFromText('FULLGLOBE',@srid);
RETURN @g.STArea()
END
CREATE FUNCTION dbo.get_ellipsoid(@wkt NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
— grungy string-handling alert
DECLARE @begin INT = PATINDEX('%ELLIPSOID%',@wkt) + 11
DECLARE @rest NVARCHAR(4000) = SUBSTRING(@wkt, @begin, 4000)
DECLARE @end INT = CHARINDEX('"', @rest)
RETURN SUBSTRING(@rest, 1, @end-1)
END
GO
@bobbeauch