SQL中geography转WKT时STAsText()与ToString()有何区别？

Great question! Even though both methods returned identical WKT in your test with a simple LINESTRING, there are key differences in their purpose, standards compliance, and behavior with more complex spatial objects. Let’s break them down:

Core Purpose & Standards Alignment #

• STAsText(): This is an OGC (Open Geospatial Consortium) standard method. Its sole job is to return the Well-Known Text (WKT) representation of the spatial object, strictly adhering to OGC specifications. This makes it ideal for interoperability—you can take the output from STAsText() and use it with any other spatial database or tool that supports standard WKT.
• ToString(): This is a .NET-style method inherited from SQL Server’s SqlGeography class. It’s designed to provide a human-readable string representation of the full object state, which may match WKT for simple cases but isn’t bound by OGC standards. It prioritizes showing all internal details of the SQL Server spatial object over strict compliance.

Behavior with Complex Spatial Objects #

For basic geometries like your LINESTRING, the outputs are identical, but they diverge with more complex data:

• Z/M Coordinates: If your spatial object includes elevation (Z) or measure (M) values, STAsText() will omit them (since OGC standard WKT for geography doesn’t include these by default), while ToString() will include them to show the complete object data.
  Example:

  DECLARE @geoWithZ geography = geography::Parse('POINT(-122.34900 47.65100 150)');
  SELECT
      @geoWithZ.STAsText() AS STAsText_Output, -- Returns: POINT (-122.349 47.651)
      @geoWithZ.ToString() AS ToString_Output; -- Returns: POINT (-122.349 47.651 150)
  

• Internal Metadata: In rare cases, ToString() might include subtle formatting or metadata specific to SQL Server’s implementation that STAsText() excludes to stay standard-compliant.

Recommended Use Cases #

• Use STAsText() when you need a portable, standards-compliant WKT string (e.g., exporting spatial data to another system, using with OGC-compliant tools).
• Use ToString() for debugging, logging, or quick checks where you want to see the full state of the SQL Server spatial object, and strict standards compliance isn’t necessary.

内容的提问来源于stack exchange，提问作者Milan