Answered by:
How do I decide on what SRID to use when creating SqlGeometry type
Question

I am reading ESRI shape files and creating a SqlGeometry data type so that I can do intersections of boundaries. I am not doing any distance calculation or area calculations. How do I decide if I use 0 or 4326 for SRID? I understand that all geometries have to have the same SRID but which one? Does it matter for using intersection and contains functionality? I am also extracting the X and Y points for each polygon.Thursday, January 27, 2011 7:07 PM
Answers

"Shapefile documentation refers to the points being in X, Y coordinate system. I was under the impression that if the points are in X, Y coordinate system, then geometry data type is appropriate. Is that not correct?"
Generally speaking, you'd be absolutely correct. The geometry datatype is for X,Y data, whereas the geography datatype is for lat, long data.
The problem is that, since the coordinates in an ESRI shapefile can either be projected or geographic (according to whether the accompanying PRJ file begins with PROJCS or GEOGCS), they typically use X and Y to refer to any two coordinates in a coordinate tuple, whether they are planar or not.
In your case, the PRJ file shows that this shapefile uses coordinates from a geographic coordinate system, so X and Y really refers to Longitude and Latitude, in which case the geography datatype is appropriate.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by kcjailbirds Thursday, January 27, 2011 9:38 PM
Thursday, January 27, 2011 8:58 PMAnswerer
All replies

Look in the .PRJ file that came with your shapefile data. It's a small textfile that details the parameters of the spatial reference system in which the coordinates are defined  whether it's projected or geographic coordinates, the datum and type of projection used, the unit of measurement etc.
You should create your geometry data using the SRID code that corresponds to that spatial reference system. You can look this up online at www.epsgregistry.com.
If you can't find it, post the contents of your PRJ file here and I'll tell you the corresponding SRID.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290Thursday, January 27, 2011 7:41 PMAnswerer 
Thank you. Here is the .prj file.
GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]
Not sure what it all means.
Thursday, January 27, 2011 8:01 PM 
GEOGS => Geographic coordinate system
SPHEROID["WGS_1984", 6378137, 298.257223563] => The ellipsoid on which this coordinate system is based has a semimajor axis (at the equator) of 6,378,137 metres, and it has an inverseflattening ratio of 298.257223563 (a measure of how "squashed" it is).
PRIMEM["Greenwich",0] => The prime meridian, i.e the line at which longitude = 0, is the Greenwich prime meridian.
UNIT["Degree",0.017453292519943295] => Coordinates are measured in degrees
These parameters correspond to the EPSG:4326 spatial reference system, so you should use SRID 4326. Note that this is a geographic coordinate system, so you should really use the geography datatype. (But, if you really must use the geometry datatype, you still should use 4326).
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290Thursday, January 27, 2011 8:07 PMAnswerer 
Thank you very much.
I was trying to use the geography data type but kept getting error with invalid data type. When I posted a question about that, you mentioned that to create a geography polygon using SqlGeographyBuilder, the polygon points have to be in counter clockwise direction. However, the points stored in the shapefile are in clockwise direction for polygon. Shapefile documentation refers to the points being in X, Y coordinate system. I was under the impression that if the points are in X, Y coordinate system, then geometry data type is appropriate. Is that not correct?
Thursday, January 27, 2011 8:40 PM 
"Shapefile documentation refers to the points being in X, Y coordinate system. I was under the impression that if the points are in X, Y coordinate system, then geometry data type is appropriate. Is that not correct?"
Generally speaking, you'd be absolutely correct. The geometry datatype is for X,Y data, whereas the geography datatype is for lat, long data.
The problem is that, since the coordinates in an ESRI shapefile can either be projected or geographic (according to whether the accompanying PRJ file begins with PROJCS or GEOGCS), they typically use X and Y to refer to any two coordinates in a coordinate tuple, whether they are planar or not.
In your case, the PRJ file shows that this shapefile uses coordinates from a geographic coordinate system, so X and Y really refers to Longitude and Latitude, in which case the geography datatype is appropriate.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by kcjailbirds Thursday, January 27, 2011 9:38 PM
Thursday, January 27, 2011 8:58 PMAnswerer 
Thank you again! I will go back and try to figure out why SqlGeographybuilder fails on me. I am assuming the points are in the wrong orientation. I thank you once again.Thursday, January 27, 2011 9:38 PM