What SRID and Spatial Type Should I Use for this Prj file

Question

Hi, I am trying to import Zillow neighborhood boundry shape files (http://www.zillow.com/howto/api/neighborhood-boundaries.htm) in my DB. I want to ask what data type should I use (geometry or geography) and what SRID should I Use for this

here is the prj file data

GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

thanx

Answer 1

Hi there,

That's a geographic coordinate system based on the NAD83 datum, so use the geography datatype with SRID 4269.

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

tanoshimi u rock. What is ur source of information by the way . I want to learn more about spatial data

Answer 3

The .prj file starts with the word GEOGCS. That stands for GEOGraphic Coordinate System, so you need the geography datatype.

So, now you look at the other parameters in the file - it uses the North American Datum of 1983, which is based on the GRS1980 ellipsoid with a semi-major axis of 6,378,137 metres, with an inverse-flattening of 298.257222101. You can look in SQL Server's sys.spatial_reference_systems table for a SRID that matches those parameters with the following query:

SELECT * FROM sys.spatial_reference_systems WHERE well_known_text LIKE '%North%American%1983%6378137%298.257222101%';

which gives one result:

spatial_reference_id: 4269
well_known_text: GEOGCS["NAD83", DATUM["North American Datum 1983", ELLIPSOID["GRS 1980", 6378137, 298.257222101]], PRIMEM["Greenwich", 0], UNIT["Degree", 0.0174532925199433]]

You can check the rest of the parameters and see that they match.

<plug>To learn more about using spatial data in SQL Server, you might want to check out Pro Spatial with SQL Server (full disclosure - I'm the author)</plug>

---

twitter: @alastaira blog: http://alastaira.wordpress.com/