locked
how to insert geography data with other projection(not 4326 srid)? RRS feed

  • Question

  •  

    I try to insert data by 2163 srid data, for example, look at sql statement

    --

    declare @g1 geography;
    set @g1= geography:Tongue TiedTGeomFromText('POLYGON((9014466.6352143 2571533.08540209,9725690.99523269 2571533.08540209,9725690.99523269 945315.907497828,9014466.6352143 945315.907497828,9014466.6352143 2571533.08540209))',2163)
    --

    it will throw exception:

    The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view.

     

    So i change the srid to 4326 or some valid one.

    --

    declare @g1 geography;
    set @g1= geography:Tongue TiedTGeomFromText('POLYGON((9014466.6352143 2571533.08540209,9725690.99523269 2571533.08540209,9725690.99523269 945315.907497828,9014466.6352143 945315.907497828,9014466.6352143 2571533.08540209))',4326)
    --

    also throw exception:

    Latitude values must be between -90 and 90 degrees

     

    I don't know how to insert this polygon. Is anybody can help?

    I check the sys.spatial_reference_systems table, I found all records are EPSG, and just 4000~4999, i mean not all srid can support, but I don't find any specfication can prove it.

    If Microsoft just can allow decimal-degree WGS84 data, I mean lat/long data, please tell me the manul/document/specification you get this information, or the web page from microsoft said that.

     

    Thanks

     

    James

    Friday, October 24, 2008 9:20 AM

Answers

  • EPSG 2163 is a projected co-ordinate reference system, so you should store it using the geometry datatype.

    set @g1= geometry:: STGeomFromText('POLYGON((9014466.6352143 2571533.08540209,9725690.99523269 2571533.08540209,9725690.99523269 945315.907497828,9014466.6352143 945315.907497828,9014466.6352143 2571533.08540209))',2163)

    The geography datatype can only be used for unprojected geographic co-ordinates of longitude/latitude, from one of the supported spatial reference systems in the sys.spatial_reference_systems table.
    Friday, October 24, 2008 9:54 AM
    Answerer
  • Smile Sorry.  Please excuse me if this is too pedantic:

    Basically, there are two (common) ways to represent coordinates.  The first is what we'd call planar or Cartesian coordinates.  These work on the plane, and use a linear measure (like feet or meters) to describe a point offset from a known location we call the origin.

    As an example, consider a football field.  We could take the left-hand side of the 0-yard line as the origin, and then measure the points on the field in yards across the field and yards downfield.  So, the center of the field would be (26.66, 50).

    The other common type of coordinate system is a geographic one.  These are measured on a sphere (or ellipsoid) not with linear measures, but with angles (almost always in degrees). 

    When we say that Madison, WI is at (approximately) 43N 89W, we mean that if you looked at a globe, the ray from the center of the globe to Madison would be elevated 43 degrees above the plane of the equator, and rotated 89 degrees west of some reference (usually near Greenwich, England).

    Since the Earth is quite obviously round, it's not immediately clear why or how we'd use a planar coordinate system to describe locations on it.  It's pretty common to project coordinates to the plane---there are many methods for doing this, but they all involve somehow flattening out the globe so that one can work with it on the plane.  There is a lot of such projected data out there; you seem to have some.

    This is a little hard to imagine without pictures.  You might find Wikipedia's pages on Cartesian and geographic coordinate systems, as well as their discussion of map projections helpful in understanding this.  I also wrote a white paper that discusses this whole mess, which you may find useful.

    At any rate, geography works with geographic data; geometry works with planar data, including projected data like yours.

    Hope this helps.

    Cheers,
    -Isaac
    Monday, October 27, 2008 5:39 AM

All replies

  • EPSG 2163 is a projected co-ordinate reference system, so you should store it using the geometry datatype.

    set @g1= geometry:: STGeomFromText('POLYGON((9014466.6352143 2571533.08540209,9725690.99523269 2571533.08540209,9725690.99523269 945315.907497828,9014466.6352143 945315.907497828,9014466.6352143 2571533.08540209))',2163)

    The geography datatype can only be used for unprojected geographic co-ordinates of longitude/latitude, from one of the supported spatial reference systems in the sys.spatial_reference_systems table.
    Friday, October 24, 2008 9:54 AM
    Answerer
  •  

    Tanoshimi,

     

    Thank you for your advice.

    But I must geography datatype, if I can not. please give the URL which have document or description from Microsoft.

    I need know the detail and official reason.

     

     

    James
    Friday, October 24, 2008 10:04 AM
  • You can find the geography datatype method reference here:
    http://msdn.microsoft.com/en-us/library/bb933802.aspx

    But I don't think you'll find what you're looking for - that's like asking for the official Microsoft description why you can't store the value "apple" in an int column... why must you use the geography datatype?
    Friday, October 24, 2008 10:52 AM
    Answerer
  • Hi James,

    Tanoshimi is absolutely correct: this is a projected coordinate system and should be used with the geometry type, not geography.  Geography only understands angular coordinates on an ellipsoidal surface, but these are planar coordinates perfect for use with geometry.

    You can consider this the official Microsoft answer.  Smile

    Cheers,
    -Isaac


    Friday, October 24, 2008 2:50 PM
  • James,

    For what it's worth, BOL does have a bit of information regarding the difference between the two types here.  Please don't hesitate to speak up if you have more questions.

    Cheers,
    -Isaac
    Sunday, October 26, 2008 9:56 PM
  •  

    Can someone give me an example to explan what Isaac said?
    Monday, October 27, 2008 1:03 AM
  • Smile Sorry.  Please excuse me if this is too pedantic:

    Basically, there are two (common) ways to represent coordinates.  The first is what we'd call planar or Cartesian coordinates.  These work on the plane, and use a linear measure (like feet or meters) to describe a point offset from a known location we call the origin.

    As an example, consider a football field.  We could take the left-hand side of the 0-yard line as the origin, and then measure the points on the field in yards across the field and yards downfield.  So, the center of the field would be (26.66, 50).

    The other common type of coordinate system is a geographic one.  These are measured on a sphere (or ellipsoid) not with linear measures, but with angles (almost always in degrees). 

    When we say that Madison, WI is at (approximately) 43N 89W, we mean that if you looked at a globe, the ray from the center of the globe to Madison would be elevated 43 degrees above the plane of the equator, and rotated 89 degrees west of some reference (usually near Greenwich, England).

    Since the Earth is quite obviously round, it's not immediately clear why or how we'd use a planar coordinate system to describe locations on it.  It's pretty common to project coordinates to the plane---there are many methods for doing this, but they all involve somehow flattening out the globe so that one can work with it on the plane.  There is a lot of such projected data out there; you seem to have some.

    This is a little hard to imagine without pictures.  You might find Wikipedia's pages on Cartesian and geographic coordinate systems, as well as their discussion of map projections helpful in understanding this.  I also wrote a white paper that discusses this whole mess, which you may find useful.

    At any rate, geography works with geographic data; geometry works with planar data, including projected data like yours.

    Hope this helps.

    Cheers,
    -Isaac
    Monday, October 27, 2008 5:39 AM
  • Issac,

     

    Thank you for your warm help.

     

    I am pretty sure what difference between gemetry and geography data type and how they work on MsSql2008 after read your post.

     

    And I review your blog, that's amazing, I can't belive that you did a great job!

     

    James

    Monday, October 27, 2008 8:35 AM
  • hi.

    i m trying to insert latitude,longitude beyond range -90 to +90
    so this times i need to decide which SRID to use
    for example 
    Latitude Longitude
    34.09601 -122.1324
    47.62795 -122.1324

    now how can i decide which SRID to use while inserting above details in my table . i tried with 4326 and 4301 but not getting exact position on the map for above mentioned data. i also have gone thru sys.spatial_reference_systems but not getting any clue.

    i have many data for which i m not able to decide which SRID to use in order to store it and get exact position on map.
    pls help me since i m beginner in this matter.

    i m using geography data type

    thanx...

    Monday, December 22, 2008 11:00 AM
  •  hi.

    i m trying to insert latitude,longitude beyond range -90 to +90
    so this times i need to decide which SRID to use
    for example 
    Latitude Longitude
    34.09601 -122.1324
    47.62795 -122.1324

    now how can i decide which SRID to use while inserting above details in my table . i tried with 4326 and 4301 but not getting exact position on the map for above mentioned data. i also have gone thru sys.spatial_reference_systems but not getting any clue.

    i have many data for which i m not able to decide which SRID to use in order to store it and get exact position on map.
    pls help me since i m beginner in this matter.

    i m using geography data type

    thanx...
    Monday, December 22, 2008 1:11 PM
  • Bhologic:

    4326 should work just fine with your data, assuming that the coordinates that you provided are in the Pacific Ocean, just west of Orange County, CA and also 156th and 20th in Bellevue, WA

    I would suspect that you're having an issue with the order that you provide the coordinates.  When populating a Geography type using WKT, you must use Longitude, Latitude ordering for the points.  Example:  POINT(-122.1324 34.09601).

    Note that (Long, Lat) ordering preserves the concept of (X, Y) ordering from the Cartesian coodinate system, if that helps you to remember which goes first.  Though, I do still get burned on occasion when (Lat, Long) ordering is expected and I don't pay attention (like AddPoint() in the Builder API...  ;-)  )

    -Jason

    Monday, December 22, 2008 2:00 PM
  • thanx JasonFollas

    u r right . i m struggling with that order of co-ordinates problem since i found that in many cases its difficult to decide whether to go with (Long, Lat) pattern or with (Lat,Long) pattern programmatically.

    this is my observation that even if i insert POINT(34.09601 -122.1324) it takes POINT(-122.1324 34.09601) in WKT and while displaying this on MAP i need to reverse it again..

    is there any chance to overcome this? i mean with 80% of data i have(mostly all positive co-ordinates) every thing works fine .. no ordering issue.. but with mostly all negative values its difficult to decide programmatically which ordering pattern to follow.
    Monday, December 22, 2008 2:35 PM
  • If you are only going to be storing Points (rather than LineStrings or Polygons, for example), you could use the Point() method instead, which accepts coordinate parameters in lat/long order:

    geography::Point(lat, long, srid)

    That might make it simpler for you. Note that it doesn't make any difference whether the coordinate values are positive or negative - the WKT methods (STPointFromText(), STAsText() etc.) will always use long/lat ordering, and some other methods (GeomFromGml(), Point()) will always use lat/long ordering. It is confusing, but that's the way it is.

    'Programmatically', I would recommend you use whichever order is most logical to you - if you find it difficult to remember then be sure to comment your code!

    Monday, December 22, 2008 4:10 PM
    Answerer