Geometry and Geography Difference SQL Server 2008

GEOMETRY and GEOGRAPHY difference SQL Server 2008

Read the wonderful manual at:

http://msdn.microsoft.com/en-us/library/bb933790.aspx

The geometry Data Type

The geometry data type (planar)
supported by SQL Server conforms to
the Open Geospatial Consortium (OGC)
Simple Features for SQL Specification
version 1.1.0.

For more information on OGC
specifications, see the following:

* OGC Specifications, Simple Feature Access Part 1 - Common

Architecture
* OGC Specifications, Simple Feature Access Part 2 – SQL Options

The geography Data Type

The geography data type (geodetic)
stores ellipsoidal (round-earth) data,
such as GPS latitude and longitude
coordinates.

It comes down to what model of the earth you're using - a planar or geodetic one.

difference between Geology and Geography Data Type MSSQL

There's no Geology data type. Where did you get this idea from? :)

New data types introduced since 2008 are called spatial.
In general, spatial data types are geometry which can be used to store "flat" shapes definitions, and geography for geographic coordinates.

You can read more about it here:
http://go.microsoft.com/fwlink/?LinkId=226407

Differences when convert between Geometry & Geography instances

MakeValid(), by necessity, changes the underlying data of a geometry instance. (If it made no changes, then the resulting geometry would still be just as invalid as the original).

In many cases, the only change is to the type of the resulting geometry (i.e. an invalid, self-intersecting linestring may become a valid multilinestring), but it is also possible for coordinate values to change. However, these only shift by absolutely fractional amounts necessary to validate the geometry, and will not make a significant difference to the length.

Differences when convert between Geometry & Geography instances

MakeValid(), by necessity, changes the underlying data of a geometry instance. (If it made no changes, then the resulting geometry would still be just as invalid as the original).

In many cases, the only change is to the type of the resulting geometry (i.e. an invalid, self-intersecting linestring may become a valid multilinestring), but it is also possible for coordinate values to change. However, these only shift by absolutely fractional amounts necessary to validate the geometry, and will not make a significant difference to the length.

Bing Maps API - SQL - geometry vs geography type

You've provided quite a good summary of the differences between the two types, and you've correctly identified the two sensible alternatives to be either geography(4326) or geometry(3857), so I'm not quite sure what more information anyone can provide - you just need to make the decision yourself based on the information available to you.

I would say that, although the geometry datatype is likely to be slightly quicker than the geography datatype (since it relies on simpler planar calculations, and can benefit from a tight bounding box over the area in question), this increase in performance will be more than offset by the fact that you'll then have to unproject back to WGS84 lat/long in order to pass back to Bing Maps - reprojection is an expensive process.

You could of course store WGS84 angular coordinates using the geometry datatype, but this is really a hack and not recommended - you are almost certain to run into difficulties further down the line.

So, I'd recommend using the geography datatype and WGS84. With careful index tuning, you should still be able to get sub-second response time for most queries of even large datasets. Incidentally, the "within a hemisphere" rule is lifted for the geography datatype in SQL Denali, so that limitation goes away if you were to upgrade.

Geometry and Geography datatypes in SQL server

geometry and geography are datatypes used for storing spatial information - describing the shape and position of objects in space (usually on the surface of the earth).

Why do you need a dedicated datatype for this sort of information? Think about how you would ORDER BY spatial information - it doesn't have a natural collation such as alphabetical for varchar, or chronological for date/time. And how would you write a query to identify features close to a given location - you can't SELECT * WHERE location BETWEEN 'Bristol' AND 'London'. So, ever since SQL Server 2008, there's been a whole range of methods (and indexes) specifically designed for doing these sorts of queries.

If you want practical examples of when they are used: an insurance company might use a geography field in a Customer table to record the location of every policyholder, and determine how many of them were likely to be affected by rising water levels by joining to a Rivers table which modelled river flood plains also in a geography field.
Or, you might want to determine the optimal location for a new store by analysing the geographic coverage of your (and your competitors) existing outlets.
Or, plan the optimal logistics route for a delivery vehicle, etc. etc.

How are Geography / Geometry Shape Area & Shape Length useful?

It takes time to compute these values - especially area which would require a complex integration (possibly on a sphere, depending on the coordinate system). So to store them as precomputed values saves a lot of computation time.

SQL Server 2008 Geography .STBuffer() distance measurement units

STBuffer is in meters. More info here.

To convert, miles to meters, divide the number of miles by 0.0006213712

(i.e. 5 miles / 0.0006213712 = 8,046.72 meters)

Convert Geography /Geometry to deg/min/sec - same calculation?

The short answer: yes. Both are just different representations of the same thing. One represents the mantissa in decimal and the other in sexagesimal.

---

Related Topics