What does the srid code mean in an sde.st_geometry?

Question

In case of an sql insert query like :

INSERT INTO OUTBREAKS (OID, address, city_code, loc)
   VALUES (1, 
   '1420 kirchestrasse', 
   43, 
   sde.st_geometry (0.00003, 0.00051, null, null, 4326)
   );

Does the srid (4326) mean that the decimal coordinates used for x and y are of the spacial reference specified from the srid? Or it says that the geometry will keep its coordinates in that spatial reference? In other words can I store a geometry with decimal coordinates (in WGS_84) for latitude/longitude ( like 50.91212 , -0.1221) and use an srid of a projected system which uses meters? Will they be automatically convented from wgs_84 to my new spatial reference and be kept in that way in the database ?

Answer 1

The term "SRID" is overloaded in the GIS realm. In most contexts it refers to the coordinate reference system (CRS) of spatial data, but in Esri use, it really does refer to the spatial reference system, which is a combination of CRS with the X, Y, Z, and M offsets, X/Y, Z, and M scaling factors, the precision (HIGH or BASIC), the cluster tolerances (which are only really used by ArcGIS) and few other sundry properties.

If you don't understand how an Esri SRID works, you probably shouldn't be attempting to load data to SDE.ST_GEOMETRY via SQL. This is doubly true if you want to change coordinate references on the fly during storage or query. The Understanding Coordinate Management in the Geodatabase whitepaper explains all aspects of the Esri coordinate reference implementation. At that point you'd have to move on to the ST_GEOMETRY documentation to review how SRIDs interact with SQL (the link will bring you to the start of a dozen or two topics on the subject).

Suffice it to say that you can't mix and match SRIDs and expect the data be usable (or even load). The SQL Spatial Types and Functions standard has a very specific mechanism for changing coordinate references within SQL expressions (using ST_Transform). Once you're comfortable loading and querying data in the SRID associated with a table you'll be ready to start transforming during load or query.

Answer 2

The database will accept any value for the x,y, and srid. The srid is just like the .prj file and is just metadata about what coordinate system the data should be.

Esri stores this coordinate system metadata in the sde_spatial_references table.

You would be able to store GCS coordinates in a projected srid. Your data just wouldn't line up with properly defined data. Don't go the other way because if a value is too large ArcMap will complain about the data being outside the spatial extent.

I'm not sure you want to actually do any of this because it will probably only lead to troubles with ArcMap. Here's the docs on the gdb tables http://resources.arcgis.com/en/help/main/10.1/index.html#//006z0000000n000000