Regarding geometry datatype RRS feed

  • Question


    Does geometry and geography both are one and the same?

    When does geometry datatype would be available to the customers ?



    SQL Trigger

    Saturday, October 27, 2007 5:33 PM


All replies

  • Geometry and geography are not same. They are different.


    Geometry is used to represent 2-dimesnional quantities and geography is used to represent geodetic entities.


    Both are available in the next CTP.


    Check this link :



    Saturday, October 27, 2007 5:40 PM
  • To be a little more explicit:

    - Geography is meant to handle round-earth coordinates using angular measures like latitude and longitude.  While that may sound fancy, it's really what most people expect when they work with geospatial data.  If you're working with lat/long data, this is the type for you.

    - Geometry works on a flat, Euclidean plane.  For most people, this would make sense if you're not actually working with geospatial data, but rather working with local, flat coordinate spaces, such as in building layouts, etc.  To use this type with geospatial data really requires that you be working in a projected coordinate space---something GIS experts are very familiar with, but would be pretty foreign to a non-geographer.

    Tuesday, November 13, 2007 8:55 PM
  • Isaac,

    I 100% agree with you regarding the technical difference between GEOGRAPHY and GEOMETRY types, but I would respectfully disagree that GEOGRAPHY is always right for most people doing lat/long and GEOMETRY something only for GIS experts.  Perhaps I misunderstood the guidance intended, but just in case it might be useful to point out some misconceptions about lat/long that often cause trouble for beginners. 

    A spatial DBMS is useless without the data you put into it and virtually all data that people use within a spatial DBMS is pre-existing GIS data, or data that is created using GIS processes.  In effect, it is all GEOMETRY data, even that data which is often inaccurately thought of by beginners as being "unprojected" Latitude / Longitude data.

    One of the most common errors is to think that lat/long values are somehow absolute, so the beginner  thinks "Oh, it's in lat/lon so I don't have to think about this projections stuff" - the beginner then takes two different maps in lat/long and overlays them and they don't line up and he or she wonders why.  How can that be?

    What's going on is that nothing on Earth is measured within a single, abstract lat/long coordinate system - instead, real-life lat/long values are measured within what is, in effect, some particular *projection* that uses radial lat/long measures and is based on a particular set of underlying coordinate system parameters, most notably the particular datum (ellipsoid, offset, etc.).  Even in the case of lat/long measured using a spherical Earth there are underlying parameters in use.  Choose a different set of underlying parameters used when you measure the lat/long of a particular spot and the map created from such spots will not align to a map created in lat/long using different underlying parameters.  The differences can be very large, on the order of hundreds of meters.  This leads to all sorts of errors such as the dots being shown on a web site for, say, the locations of ATMs appearing in the middle of a pond.

    However it is you get your latitude and longitude coordinates that you are using, whether it is by importing a map from some GIS data set (shapefiles or whatever), tracing over an aerial photo, using a geocoder to provide estimated lat/long coordinates for a street address or using a GPS device that you walk over to a particular asset to measure the GPS-estimated location of that asset, the latitude and longitude values you get are based upon an underlying set of coordinate system parameters.  Like it or not, you have to deal with it all as projected data or your data is not going to line up and your work will be full of errors.  If you don't know the underlying parameters behind the lat/long data you are using you have already made the first error from which many more will follow.

    It's no big deal to manage this and even to make it easy.  Any modern GIS will do that for you invisibly and automatically.  I also agree that using the Latitude / Longitude projection (and it *is* a projection) is easier for most beginners so long as they realize it is a projection of sorts.  If they realize they are working with projected data they can take that necessary extra step of standardizing on a particular datum, such as WGS84, and taking care to re-project Latitude / Longitude data that uses other datums or parameters into their target standard.  But dealing with lat / long data as if datums don't matter and the data is not projected is usually an error, a gross error even in applications where spatial accuracy is important.

    Can GEOGRAPHY be used by custom applications?  Sure, absolutely!  This is a great data type and Microsoft should be applauded for implementing it.  I'm just suggesting that beginners not succumb to the notion that if they use GEOGRAPHY then all lat/long values they get from wherever will be just fine and all will be well.  If you know what you are doing you could choose this type for an application, taking care to keep track of the particular lat/long coordinate system used and making sure that the data you put into it is transformed from any other different lat/long projections (for example, using different datums) into whatever particular lat/long form is being used.  There are plenty of tools that will help you do that.

    But there are also many tools, and probably more of them and richer tools as well, that make GEOMETRY effortless and transparent and easy to use.  As far as the existing data being used, the new data being created and the toolsets everyone uses are concerned it is a fundamentally GEOMETRY world, so there are plenty of options there.  Learn how to use the right tools and have faith in them and all will be well, and even easy within your SQL Server application.

    Wednesday, November 14, 2007 5:13 PM
  • OK, after my previous post I've received about a dozen private emails asking for some examples of how lat/lon is not always the same.   I'd appreciate it if folks interested in pursuing a thread in this forum would post in the forum so that everyone can benefit from their questions and comments.

    For a quick overview (with some nice visuals) of datums and ellipsoids, see


    Note from the bottom of that topic there are several ellipsoids in common use for US data, including WGS 72, WGS 84 and GRS 80, an ellipsoid very similar to, but not the same as, WGS 72.  All of these ellipsoids and many more are used for lat/lon data.

    The classic example of spatial data in lat/lon that varies based upon ellipsoid is data in shapefiles.  Shapefiles storing "lat/lon" data don't encode the datum used, yet there are many different datums in use for lat/lon data in shapefiles.  For an example of how this matters, see the "Importing a Shapefile" example in


    That example imports a shapefile showing 109th Congress congressional districts from the federal website for the National Atlas of the United States:

    (illustration at http://www.manifold.net/doc/images/eg_import_shapefile_01.gif)

    Because shapefiles don't encode the datum, we have to manually ascertain from the metadata posted elsewhere on that web site that the datum used for the above shapefile is NAD 83 using the GRS1980 ellipsoid:

    (illustration at http://www.manifold.net/doc/images/eg_import_shapefile_02.gif)

    If we weren't able to find the above note buried on the web site and correctly infer that it applied to the shapefile we downloaded, we'd never get it right because there is no way to tell from the lat/lon values themselves whether they were intended to be used with a GRS1980 ellipsoid or something different, like WGS 84.  That's obviously a risky situation and the source of no end of chaos with shapeifles.  It happens all the time in applications where developers import "Lat/lon" shapefiles willy-nilly and then discover the data doesn't line up.

    If you took that federal data, uploaded it unmodified into SQL Server 2008 GEOGRAPHY and then overlaid it in some cool Virtual Earth mashup with point data stored in GEOGRAPHY that was acquired from a GPS device (say, in a 911-addressing emergency response application), you would get a mismatch because the GPS lat/lon data would be based upon a different datum than the National Atlas lat/lon data.    In most parts of the US it wouldn't be wrong by much, and you might not even care if your mashup was some hobby thing like showing the locations of your favorite pubs. 

    But it would be wrong enough you could not use it for an application where accuracy matters, such as running real estate parcel and property tax maintenance and computations for a county [one of the most popular applications for Oracle Spatial and quite likely will be a big application for SQL Server 2008 as well] or maintaining flood zone maps for insurers where lawsuits get filed over location errors of 20 feet.

    There is a lesson for SQL Server 2008 users in the chaos caused by the failure of shapefiles to encode important projection info for lat/lon data:  if you are going to take latitude/longitude from any source and put it into SQL Server using either GEOGRAPHY or GEOMETRY, you have to be sure to keep track of what datums were used so you can use that data accurately.  Once the data is in SQL Server 2008, your toolset should take care of that for you, as SQL Server 2008 gives your toolset the freedom to do so.

    For example, Manifold uses EPSG codes (a worldwide, widely-used standard to unambiguously define projections) to associate coordinate systems with specific GEOMETRY data.  EPSG code 4326 is Latitude / Longitude:

    (illustration at http://www.manifold.net/eg/eg_reproj_sqls_02.png)

    Although this sort of thing is normally kept hidden from ordinary users, an expert can call up the projection dialog used by Manifold for GEOMETRY type in SQL Server 2008, to see in the lower pane the particular parameters used for "Latitude / Longitude" by EPSG 4326. Not being someone who likes the idea of keeping track of that stuff in the lower pane, I sure like it that the tool does it for me, so that any data exported into SQL Server 2008 or linked from SQL Server 2008 will be automatically reprojected on the fly if need be.   That lower pane is also a useful illustration of the sorts of things one must consider if you want to create your own application to manage such things, even in the case of "simple" Latitude / Longitude data.

    Thursday, November 15, 2007 6:37 PM
  • Hi Dimitry,


    I agree completely with your sentiment, although I don't quite follow how you connect this with me advocating the use of Geography for geodetic data.  That is, I agree that one needs to pay attention to the ellipsoid/datum of their geodetic data: it's not all the same, and I don't think I implied otherwise.


    That said---except for possible legacy concerns---I don't see why one would work with lat/long data in the Geometry type.  Lat/long data are angular measures, and should be dealt with in a system, like the Geography type, that understands them.


    I think for the "neogeographers" using the system, most of the data is in some geodetic coordinate system, and so I would advocate the Geography type.



    Tuesday, November 20, 2007 5:03 PM
  • Hi Isaac,

    I am in strong agreement with you in general and had intended nothing more than a cautionary note for people new to geospatial data and representations thereof within spatial DBMS.

    Being an expert, you know that awareness of matters like datum is part of the picture when you write "Geography is meant to handle round-earth coordinates using angular measures like latitude and longitude.  [...]  If you're working with lat/long data, this is the type for you." But long experience with very many GIS and spatial DBMS users shows that most people do not share your expert awareness and thus will get it wrong if they don't consider matters like datum.

    In fact, after problems involving use of projected data stored in shapefiles (the number one error), a lack of awarness that most lat/lon data should be dealt with as projected data is the second biggest source of problems for people who do not have a sophisticated understanding of coordinate systems.  It is a case where a bit of care can eliminate chaos later on, hence my cautionary note.

    You raise a very good point when you write: "
    I don't see why one would work with lat/long data in the Geometry type.  Lat/long data are angular measures, and should be dealt with in a system, like the Geography type, that understands them."

    In a more evolved world I agree 100% with you that would be the way to go. That is why I agree it is such a good idea for Microsoft to have introduced Geography as well as Geometry type.   I think the main reason people would use Geometry type now instead of Geography boils down to two factors:

    1. That is universal practice these days for dealing with lat/lon, and

    2. There is greater support for Geometry within accessory toolsets.

    For now,  even though
    lat/lon values from a mathematical perspective are angular measures (degrees, like radians, of course are angular measures), from a practical perspective for geospatial work on Earth, 99.999+% of the geospatial data sets and effectively all of the users, tools and applications out there treat latitude and longitude values like linear measures employed in a cylindrical projection.  It is true that such use is often wrong-headed and it also is true there are a variety of elementary user mistakes stemming from such usage (not the least of which is astonishment that a "degree" represents a different linear distance in longitude depending on  where you are in the coordinate system) but existing practice is what it is and it is darn near universal.

    And it is near universal for some very good reasons, so I don't want to criticize folks who use
    lat/lon as a planar system.

    The main reason is that idealized angular measures don't work on Earth for precision applications: the Earth is not a perfect sphere and there is not just one datum in use, so as a practical matter there is no unambiguous meaning to abstract angular measures if we are talking about locations on Earth.  As the Wikipedia article puts it "
    Latitude and longitude values can be based on several different geodetic systems or datums [...] In other words, the same point on the earth’s surface can be described by different latitude and longitude values depending on the reference datum." (From http://en.wikipedia.org/wiki/Geographic_coordinate_system )

    Another practical reason people treat latitudes and longitudes as a cylindrical, planar projection is because if you are going to go to the hassle of keeping track of coordinate system parameters, such as datum, you may as well do it utilizing the same apparatus used for all other coordinate systems.  Otherwise, you end up with two sets of code:  one set of GUI and object model interfaces defined for the zillions of different projections you use and then a special case just for lat/lon projections.

    Another reason is that most geospatial applications become visual applications at one point or another, since few people want to work exclusively in a text interface of SQL statements and lists of coordinates - instead, they like to see pretty maps.  The moment you visualize lat/lon data in a 2D setting like a monitor screen, a web application or printed material, you have entered the wonderful world of a) cylindrical projections or b) re-projection on the fly. In both cases you are visualizing lat/lon coordinates as linear measures, not as angular measures, so there is some practical utility to doing that in the first place using the same routines employed for other visualizations.

    Another reason is that almost all the geospatial data out there that is useful (that is, which exists in formats that correctly capture coordinate system information like datum) treats lat/lon as if it were projected planar data anyway. 

    A final reason is that virtually all useful tools out there treat lat/lon as planar data.  If you want to use those tools to make your life easier and to avoid re-inventing the wheel there is a great incentive to continue using the same conceptual framework.

    Given that effectively all of the geospatial data out there is Geometry type, and that in effect we have the entire world treating lat/lon data as if it were Geometry type both for programmatic work and visualization and accessory tools, well, I can understand why many folks would prefer to continue to use Geometry type for such data, at least for now. 

    For all that I can also see the benefits of Geography and 100% applaud Microsoft for providing the Geography type.  It is a very confident approach that is clearly looking to the future and that has a lot of appeal for potential simplification of more complex matters, to broaden the utilization and manipulation of geospatial data.  Anything that broadens awareness and utilization of geospatial data we're all in favor of! :-)   As the CTP progresses, as people get educated about Geography type, as the accessory toolset vendors support it in rigorous maturity (as inevitably will happen), well, clearly Geography type will be very popular.

    Tuesday, November 20, 2007 9:50 PM