Tip #4: What Coordinate System is that?

SQL Server’s spatial reference system is based on EPSG.

When using Oracle Spatial I have constant recourse to MDSYS.CS_SRS – the master table that holds all the spatial reference systems Oracle supports – to discover the right SRID to use when constructing Sdo_Geometry objects.

For example, if I want to find the SRID for the ESPG defined geographic srid based on GDA94 I do this:

gis@xe> select srid, cs_name
  2 from mdsys.cs_srs
  3 where cs_name like '%GDA%'
  4* and auth_name like '%EPSG%'
gis@XE> /
      SRID CS_NAME
---------- -------------------------
      4283 GDA94
  62836405 GDA94 (deg)
  62836413 GDA94 (3D deg)

Now, in SQL Server 2008 “Katmai” I can find the same information by querying the sys.spatial_reference_systems system table as follows:

select spatial_reference_id, well_known_text
  from sys.spatial_reference_systems
 where well_known_text like '%GDA%';

spatial_reference_id  well_known_text
4283                            GEOGCS[“GDA94”, DATUM[“Geocentric Datum of Australia 1994”, ELLIPSOID[“GRS 1980”, 6378137, 298.257222101]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]]
4939                            GEOGCS[“GDA94”, DATUM[“Geocentric Datum of Australia 1994”, ELLIPSOID[“GRS 1980”, 6378137, 298.257222101]], PRIMEM[“Greenwich”, 0], UNIT[“Degree”, 0.0174532925199433]]