1

In SQL Server geo-spatial I can't run a STBuffer for MULTIPOLYGONS.

DECLARE @g geography = geography::STMPolyFromText('MULTIPOLYGON(((1 1, 1 -1, -1 -1, -1 1, 1 1)),((1 1, 3 1, 3 3, 1 1)))', 4326);  

SELECT @g   -- no problem to run
SELECT @g.ToString()  -- no problem to run
SELECT @g.STBuffer(1).ToString();  -- error

The error returned is:

Msg 6522, Level 16, State 1, Line 64
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly. System.ArgumentException:

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zax
  • 33
  • 3

1 Answers1

0

I think this is happening because the polygons you are creating are not valid geography polygons. Try creating the Multi Polygons using a valid set of Latitude/Longitude values.

Executing the same code with geometry data type seems to work.

DECLARE @g geometry = geometry::STMPolyFromText('MULTIPOLYGON(((1 1, 1 -1, -1 -1, -1 1, 1 1)),((1 1, 3 1, 3 3, 1 1)))', 4326);  

select 
    geoMultiPolys = @g, 
    geoMultiPolysString = @g.ToString(),
    buffered = @g.STBuffer(1),
    bufferedString = @g.STBuffer(1).ToString(),
    IsValid = @g.STIsValid()

Results

Buffered Spatial Polygon

Similarly this one works for geography

DECLARE @g geography = geography::STMPolyFromText('MULTIPOLYGON (((40 40, 20 45, 45 30, 40 40)), ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35), (30 20, 20 15, 20 25, 30 20)))', 4326);  

select 
    geoMultiPolys = @g, 
    geoMultiPolysString = @g.ToString(),
    buffered = @g.STBuffer(1),
    bufferedString = @g.STBuffer(1).ToString(),
    IsValid = @g.STIsValid()

valid SQL geography

Dawood Awan
  • 7,051
  • 10
  • 56
  • 119
  • Thanks a lot. I'll figure out some of the issues I have migrating Geography data/scripts from Postgres to MS SQL. – Zax Jun 04 '19 at 08:58