geometry MakeValid: Repair Can Change the Geometry Type

geometry MakeValid returns a valid geometry from an invalid instance, but the result can change type. I keep the original beside the proposed repair. A validity flag alone does not describe everything that changed.

The input is a line with a retraced central segment. Its expected repair is a MultiLineString rather than one LineString. That change matters when a downstream consumer expects a single line.

Keep the original in the result

The VALUES-free input expression constructs one fixed geometry. A second common table expression obtains the proposed repair. Nothing overwrites the original or creates a permanent object.

The query returns the supplied WKT and original validity beside the proposed type and validity. It also counts the repaired members. Both reference identifiers remain available for inspection.

These diagnostics deliberately avoid relying on the text order of repaired coordinates. The important contract is validity and the resulting geometry structure. A different textual listing should not become a hidden acceptance rule.

WITH Input AS
(
    SELECT CAST('LINESTRING(0 4, 2 2, 2 0, 2 2, 4 4)' AS varchar(80)) AS InputWkt,
           geometry::STGeomFromText('LINESTRING(0 4, 2 2, 2 0, 2 2, 4 4)', 0) AS Original
), Repaired AS
(
    SELECT InputWkt, Original, Original.MakeValid() AS Proposed
    FROM Input
)
SELECT CAST(InputWkt AS varchar(80)) AS InputWkt,
       Original.STIsValid() AS OriginalValid,
       Proposed.STGeometryType() AS ProposedType,
       Proposed.STIsValid() AS ProposedValid,
       Proposed.STNumGeometries() AS ProposedMembers,
       Original.STSrid AS OriginalSrid, Proposed.STSrid AS ProposedSrid
FROM Repaired;

Inspect the proposed structure

InputWkt is the supplied text, not a measured type from the invalid instance. The original should return zero for validity. The proposed value should identify itself as MultiLineString and return one. Its expected member count is two.

The retraced excursion does not remain a simple path traversed twice. The repair represents the relevant point set as valid line components. An application that records traversal order needs a separate interpretation.

The reference identifiers should remain zero in this bounded example. That result does not validate the choice of reference system. The coordinates must still be meaningful for the source data.

I don’t select an exact repaired WKT string as the check. Component ordering and coordinate presentation are secondary to the structural witnesses here. Some type diagnostics refuse invalid instances, so this query avoids calling STGeometryType on the original. Consumers should inspect the proposed geometry rather than guessing from the input syntax.

Separate technical repair from intended meaning

Repairs can change the type and slightly shift points. That is a reason to review the returned instance. It is not a promise of a faithful correction for every business case.

For a route, two returned line components may need separate identifiers or connectivity rules. For a boundary, changed points may affect measurements. Those decisions belong to the data model and its requirements.

I would preserve the source geometry and the reason for its rejection during an import. That allows a reviewer to compare the proposal with the original. A silent overwrite removes useful evidence.

A valid result can still describe the wrong location or shape. Validity says that the representation satisfies its type’s geometric rules. It does not establish that the repaired value matches a real object.

Keep the example bounded

This query demonstrates one known self-overlapping line. It does not exercise every invalid polygon or collection. Additional input categories need their own examples and acceptance rules.

The example also does not promise a speed improvement or a spatial index benefit. Its output is a diagnostic comparison. Measure any production workload separately after choosing an acceptable representation.

When a repair is accepted, check the declared expectations of every consumer. A single-line assumption might no longer hold after a MultiLineString result. Preserving both values makes that review concrete.

Keep the original next to the repair and you can always go back.

MakeValid is not a correction, it is a proposal you still have to inspect.

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