Search code examples
sql-serverspatialsqlgeography

Inaccurate figure with Sql geography

I am converting geoFences into Sql geography instance.The problem currently is for some instance.The geography figure is pretty weird and inaccurate.

The first one(green area) is a valid geofence and the second is which is created in sql.

This is the valid geofence

This is the Sql geography instance

The co-ordinates are:-

Long Lat

51.576004 24.125605, 51.580467 24.122041, 51.585875 24.119730, 51.591239 24.118751, 51.597633 24.120043, 51.603470 24.123843, 51.607161 24.126114, 51.609950 24.126976, 51.616087 24.127133, 51.625915 24.125997, 51.639776 24.119691, 51.576004 24.125605

With deviation i.e. Buffer of 100 meters.

The Sql code :-

geography::STGeomFromText('POLYGON((51.576004 24.125605, 51.580467  24.122041,51.585875 24.11973,51.591239 24.118751,51.597633 24.120043,51.60347 24.123843,51.607161 24.126114,51.60995 24.126976,51.616087 24.127133,51.625915 24.125997,51.639776 24.119691,51.576004 24.125605))', 4120).MakeValid().STBuffer(100)
Solution

  • A polygon is a closed loop. This means that your fence should be a ring around the road. In this case, you have a line following the road, and then repeat the first point, i.e.

    POLYGON((51.576004 24.125605, ... ,51.576004 24.125605))

    If you change this to a LINESTRING

    geography::STGeomFromText('LINESTRING(51.576004 24.125605, 51.580467  24.122041,51.585875 24.11973,51.591239 24.118751,51.597633 24.120043,51.60347 24.123843,51.607161 24.126114,51.60995 24.126976,51.616087 24.127133,51.625915 24.125997,51.639776 24.119691,51.576004 24.125605)', 4120)

    It looks like this

    Linestring

    Which should explain the shape you are getting in the end.

    To get the result you want, you can convert to a linestring, remove the last point, and then apply your buffer.

    geography::STGeomFromText('LINESTRING(51.576004 24.125605, 51.580467  24.122041,51.585875 24.11973,51.591239 24.118751,51.597633 24.120043,51.60347 24.123843,51.607161 24.126114,51.60995 24.126976,51.616087 24.127133,51.625915 24.125997,51.639776 24.119691)', 4120).STBuffer(100)

    Buffered Linestring