Answered by:
how to insert geography data with other projection(not 4326 srid)?
Question

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

declare @g1 geography;
set @g1= geography:TGeomFromText('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:TGeomFromText('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 decimaldegree 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 coordinate 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 coordinates of longitude/latitude, from one of the supported spatial reference systems in the sys.spatial_reference_systems table.Friday, October 24, 2008 9:54 AMAnswerer 
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 lefthand side of the 0yard 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 planethere 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,
IsaacMonday, October 27, 2008 5:39 AM
All replies

EPSG 2163 is a projected coordinate 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 coordinates of longitude/latitude, from one of the supported spatial reference systems in the sys.spatial_reference_systems table.Friday, October 24, 2008 9:54 AMAnswerer 
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.
Friday, October 24, 2008 10:04 AM 
You can find the geography datatype method reference here:
http://msdn.microsoft.com/enus/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 AMAnswerer 
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.
Cheers,
IsaacFriday, 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,
IsaacSunday, October 26, 2008 9:56 PM 
Monday, October 27, 2008 1:03 AM

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 lefthand side of the 0yard 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 planethere 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,
IsaacMonday, 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... ;) )
JasonMonday, December 22, 2008 2:00 PM 
thanx JasonFollas
u r right . i m struggling with that order of coordinates 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 coordinates) 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 PMAnswerer