locked
Choose GEOGRAPHY or GEOMETRY? RRS feed

  • Question

  • Hello,

    as a beginner in using SQLServer Spatial it's not clear for me if i should GEOMETRY or GEOGRAPHY for Lat/Lon's. 

    (+) GEOGRAPHY methods work spherically, STDistance(), STArea() etc. return usable result (even if they don't know anything about different ellipsoids).

    (-) GEOGRAPHY isn't OGC conform and not supported by all GIS tools.

    What are your tips?

    Regards,

    Klaus

    Friday, February 4, 2011 1:42 PM

Answers

  • geography.

    What do you mean "they don't know anything about different ellipsoids"? The geography datatype supports data in 390 different geographic coordinate systems, as listed in the sys.spatial_reference_systems table, and many of these are based on different ellipsoids. To use coordinates from a different reference system, supply a different SRID when you create a geography instance.

    What GIS Tools are you using that support geometry but not geography?

    The only situation in which I use geometry to store lat/long data is if I want to use SQL Server for storage/retrieval only (i.e. not using any methods other than SELECT STAsText() / STAsBinary()) and I want to be able to add an index with a tight bounding box around a limited geographic area. Since geography indexes implicitly cover the whole earth, you can get better performing spatial indexes in the geometry datatype. That's the only reason.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by K.Kuehne Friday, February 4, 2011 4:14 PM
    Friday, February 4, 2011 2:34 PM
    Answerer

All replies

  • geography.

    What do you mean "they don't know anything about different ellipsoids"? The geography datatype supports data in 390 different geographic coordinate systems, as listed in the sys.spatial_reference_systems table, and many of these are based on different ellipsoids. To use coordinates from a different reference system, supply a different SRID when you create a geography instance.

    What GIS Tools are you using that support geometry but not geography?

    The only situation in which I use geometry to store lat/long data is if I want to use SQL Server for storage/retrieval only (i.e. not using any methods other than SELECT STAsText() / STAsBinary()) and I want to be able to add an index with a tight bounding box around a limited geographic area. Since geography indexes implicitly cover the whole earth, you can get better performing spatial indexes in the geometry datatype. That's the only reason.


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by K.Kuehne Friday, February 4, 2011 4:14 PM
    Friday, February 4, 2011 2:34 PM
    Answerer
  • Hello,

    (1) Thanks for this hint, it's very important for me. It seems I got an incorrect information ("SQLServer 2008 doesn't take care of different SRID's except that it refuses their combined processing") at a training seminar. In the meanwhile I could verify that GEOGRAPHY methods like STDistance() produce SRID-specific results. On the other hand, it's my impression that GEOMETRY methods treat coordinates as dumb numbers, absolutely independent of their stored SRID's, even if a projection isn't length-preserving. Am I right in this?

    (2) UMN mapserver (in combination with the SQLServerSpatial Plugin) makes trouble. After defining a SQLServer-GEOGRAPHY-layer, UMN tries to compare stored GEOGRAPHY objects with the current map window corners by using geometry::STIntersects(), which produces an SQL error of course. Any tip to overcome this would be welcomed! Moreover, if I don't anything wrong (documentation is still very poor), the GDAL-OGR2OGR converter (in it's current version 1.60 for Windows) doesn't accept SQLServer-GEOGRAPHY as target format. However, this isn't a severe problem because I can import geodata into a GEOMETRY column OLD, create a new table column NEW and update it with: UPDATE TAB SET NEW = geography::Parse(OLD.ToString());

    Klaus

    Monday, February 7, 2011 9:00 AM
  • "SQLServer 2008 doesn't take care of different SRID's except that it refuses their combined processing" at a training seminar.

    Ask for your money back from the training provider :)

    it's my impression that GEOMETRY methods treat coordinates as dumb numbers, absolutely independent of their stored SRID's, even if a projection isn't length-preserving

    That's basically correct - the assumption is that you chose the appropriate projection for your data in the first place, so SQL Server can then operate on those planar coordinates without worrying about which projection they're in (so long as all data is the same)

     

    And sorry - I've got no experience with UMN mapserver But, it's open source, so why not submit a patch?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, February 7, 2011 9:12 AM
    Answerer
  • Hello,

    if there are other UMN Mapserver users in this forum: UMN understands SQLServerSpatial layers of type GEOGRAPHY, too. The trick is to add the spatial datatype to the DATA statement within the map file, e. g.

    DATA "river_geometry(geography) from rivers USING UNIQUE ogr_fid USING SRID=4326".

    If omitted, UMN uses GEOMETRY as default.

    Klaus

    Friday, March 4, 2011 8:01 AM