geometry STAsText: Z and M Values Need a Different Export

geometry STAsText returns spatial text without the Z and M values carried by an instance. I compare it with ToString using two points. Identical XY text can hide a difference in the original payload.

The first point includes two optional values in addition to its horizontal coordinates. The second point contains only those horizontal coordinates. Their STAsText results should look the same despite that difference.

Inspect text and properties together

Both points use horizontal coordinates two and three. The first additionally supplies Z as seven and M as nine. The second leaves those optional properties unspecified.

The result includes both text methods alongside all four point properties. This makes the omitted values visible independently of text formatting. No geometry is overwritten during the comparison.

The query uses fixed input strings and a common reference identifier zero. It creates no persistent objects and changes no session settings. The final ORDER BY preserves the supplied case order.

WITH Inputs AS
(
    SELECT CaseId, Wkt
    FROM (VALUES
        (1, 'POINT(2 3 7 9)'),
        (2, 'POINT(2 3)')
    ) AS v(CaseId, Wkt)
), Shapes AS
(
    SELECT CaseId, geometry::STGeomFromText(Wkt, 0) AS Shape
    FROM Inputs
)
SELECT CaseId, Shape.STAsText() AS XYText, Shape.ToString() AS TextWithZM,
       Shape.STX AS X, Shape.STY AS Y, Shape.Z AS Z, Shape.M AS M
FROM Shapes
ORDER BY CaseId;

Read the expected payload difference

Both XYText cells should describe a point with horizontal coordinates two and three. TextWithZM should include seven and nine for the first row. The second row has no extra values to include.

The X and Y property columns should match for both rows. The first row should report Z seven and M nine. The second should report NULL for those optional properties.

Those property columns are the essential diagnostic witnesses. Text spacing is a representation detail that the actual result must still verify. A consumer should inspect the payload rather than infer it from an abbreviated string.

STAsText does not remove the properties from the original instance. It chooses an output format that omits them. Losing those values during later reconstruction is a consequence of exporting only that limited representation.

Choose an appropriate interchange contract

ToString returns augmented text that includes the carried Z and M values. That makes it a useful alternative for this payload. The receiving system must still support and interpret the augmented format.

The meanings and units of Z and M are supplied by the application. They are not established by the numbers seven and nine. An interface should document those meanings beside its coordinate representation.

I don’t assume that optional values participate in ordinary planar spatial calculations. Z and M are not used by the library’s calculations. Keeping them in an export is a payload requirement rather than a different distance calculation.

This example uses Point values because their properties provide direct scalar witnesses. A collection or line needs an appropriate inspection strategy for its coordinate payload. Do not treat a NULL point property on another type as proof of absent payload.

Verify the reconstructed value

An export test should compare the properties that the application requires after importing the representation again. Matching a displayed XY position alone is insufficient. The optional fields can be lost while the point appears unchanged.

A missing geometry is also different from a present point with unspecified Z and M. Keep those cases separate in an interface. Text representations can have their own conventions for missing instances.

The example establishes no storage savings or performance claim. It demonstrates a specific export limitation using fixed values. Choose the representation deliberately before relying on it as a complete copy of the source geometry.

Pick the text form first, then test what survives the trip back.

STAsText is not a full export, it is an XY-only text form.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.