geometry STAsBinary: Carry the SRID Beside the WKB

geometry STAsBinary exports a geometry as WKB, while reconstruction takes a separate spatial reference identifier. I carry that identifier beside the bytes. Supplying a different identifier creates a differently tagged value from the same exported coordinates.

A binary payload alone is not a complete reference-system contract for this reconstruction method. The receiving side needs the intended identifier. Keeping it separately makes that requirement explicit.

Restore one point in two ways

The source point has coordinates two and three and reference identifier 2272. The export expression returns its WKB bytes and its STSrid property. The query keeps the original available for comparison.

The first reconstruction receives the source identifier. The second deliberately receives zero instead. Both calls use the same WKB expression, so the difference is the supplied reference identifier.

The output includes byte length, all relevant identifiers, two equality results and the correctly restored coordinates. These witnesses avoid relying on a long hexadecimal display. No persistent objects or coordinate transformation is performed.

WITH Input AS
(
    SELECT geometry::STGeomFromText('POINT(2 3)', 2272) AS Original
), Exported AS
(
    SELECT Original, Original.STAsBinary() AS Wkb, Original.STSrid AS SourceSrid
    FROM Input
), Restored AS
(
    SELECT Original, Wkb, SourceSrid,
           geometry::STGeomFromWKB(Wkb, SourceSrid) AS CorrectCopy,
           geometry::STGeomFromWKB(Wkb, 0) AS OtherReferenceCopy
    FROM Exported
)
SELECT DATALENGTH(Wkb) AS WkbBytes, SourceSrid,
       CorrectCopy.STSrid AS CorrectSrid, OtherReferenceCopy.STSrid AS OtherSrid,
       Original.STEquals(CorrectCopy) AS CorrectShapeEqual,
       Original.STEquals(OtherReferenceCopy) AS OtherReferenceEqual,
       CorrectCopy.STX AS RestoredX, CorrectCopy.STY AS RestoredY
FROM Restored;

Read coordinates and identifiers separately

The point’s expected WKB length is 21 bytes. The correct copy should retain identifier 2272 and coordinates two and three. Its equality comparison with the original should return one.

The other copy should carry identifier zero. Comparing it with the original using STEquals should return NULL because the identifiers differ. That result is not a definite statement that the coordinate point sets are unequal.

The byte payload is identical for both reconstruction calls. It does not make the supplied reference identifiers interchangeable. Keep the identifier column when storing or transporting this form of interchange data.

The example does not claim that assigning zero transforms the source coordinates. It changes the identifier attached during reconstruction. A true coordinate transformation is a separate operation with its own requirements.

Define the export format deliberately

STAsBinary returns the OGC WKB representation as varbinary(max). STGeomFromWKB accepts that representation and an explicit integer identifier. Those two arguments explain why the receiver must know the reference identifier independently.

I don’t substitute SQL Server’s native serialization format for WKB without changing the interface contract. Different formats have different consumers and reconstruction methods. Naming the chosen format prevents accidental mixing.

An identifier also does not prove that the coordinates actually belong to the stated reference system. This example uses a small fixed point only. Source-system validation must establish whether that tag is correct for real data.

Optional coordinate payloads and other spatial types require additional export tests. This example covers one ordinary XY point. Do not infer that every extended property survives from a single successful point comparison.

Check the complete reconstruction

A useful round-trip check includes the restored identifier and the required spatial content. Comparing only byte length cannot establish correctness. Different data can have the same number of bytes.

A NULL equality result should remain visible as a separate outcome. Replacing it with zero or one would hide an unavailable comparison. Decide how such mismatches should be rejected or reported by the interface.

The query makes no compression, storage efficiency or performance claim. It demonstrates the role of one reconstruction argument. Preserve that argument alongside the payload whenever the receiving method needs it.

Send the identifier along with the bytes, and check both when you restore.

WKB is not a complete geometry, it is bytes that need a reference identifier beside them.

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