SQL 2008 geography & geometry - which to use?

Question

I'm creating a Google map mashup and am using SQL 2008.

I will have a large number of points on the earth and will want to perform various calculations on them in SQL - such as selecting all points contained within a particular polygone, or select all points within 10km of XY.

I have never used and SQL spatial features before. Should I use the geography or the geometry datatype for this?

Answer 1

Most likely you want the geography type since it accounts for the curvature of the earth. Geometry is more for a "flat" view of things. Check out this article for more info http://www.mssqltips.com/tip.asp?tip=1847

Answer 2

You can follow the answer given in PostGIS FAQ

I'm all confused. Which data store should I use geometry or geography?

Short Answer: geography is a new data type that supports long range distances measurements. If you use geography -- you don't need to learn much about planar coordinate systems. Geography is generally best if all you care about is measuring distances and lengths and you have data from all over the world. Geometry datatype is an older data type that has many functions supporting it and enjoys great support from third party tools. Its best if you are pretty comfortable with spatial reference systems or you are dealing with localized data where all your data fits in a single spatial reference system (SRID), or you need to do a lot of spatial processing. Refer to Section 8.8, “PostGIS Function Support Matrix” to see what is currently supported and what is not.

The geometry and geography types in both databases, PostGIS and SQL Server, follow the same concept, so the answer given in the PostGIS FAQ is applicable to your problem.

Answer 3

Geography is the type that is intended for plotting points on the earth.

If you have a table that stores Google Maps points like this:

CREATE TABLE geo_locations (
    location_id       uniqueidentifier  NOT NULL,
    position_point    geography         NOT NULL
);

then you could fill points in it with this stored procedure:

CREATE PROCEDURE proc_AddPoint
    @latitude     decimal(9,6),
    @longitude    decimal(9,6),
    @altitude     smallInt
AS

DECLARE @point     geography = NULL;

BEGIN

    SET NOCOUNT ON;

    SET @point = geography::STPointFromText('POINT(' + CONVERT(varchar(15), @longitude) + ' ' + 
                                                       CONVERT(varchar(15), @latitude) + ' ' + 
                                                       CONVERT(varchar(10), @altitude) + ')', 4326)

    INSERT INTO geo_locations
    (
        location_id, 
        position_point
    )
    VALUES 
    (
        NEWID(),
        @point
    );

END

Then if you want to query for the latitude, longitude and altitude, simply use the following query format:

SELECT
    geo_locations.position_point.Lat  AS latitude,
    geo_locations.position_point.Long AS longitude,
    geo_locations.position_point.Z    AS altitude
FROM
    geo_locations;