Rounding Function

Feb 1, 2009 at 5:11 AM
If anyone is interested, here is a rounding function. It will take all of the points of a SqlGeometry and round them using Math.Round() to the specified # of decimal places. At first you might think this is pointless, as sql server and .net store a full decimal regardless of the data. However, when this data is serialized to a string and sent to a client like Virtual Earth, only the needed data is sent over (so the trailing 0s arent sent). If you have a lot of data this can make a reasonable performance difference.

Thanks
Craig Tadlock
www.tadlockenterprises.com

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlGeometry GeometryRound(SqlGeometry sqlGeometry, SqlInt32 decimals)
{
if (!sqlGeometry.IsNull)
{
SqlGeometryBuilder sqlGeometryBuilder = new SqlGeometryBuilder();
sqlGeometryBuilder.SetSrid(sqlGeometry.STSrid.Value);

BuildRoundedGeometry(sqlGeometryBuilder, sqlGeometry, decimals);

return sqlGeometryBuilder.ConstructedGeometry;
}
else
{
return SqlGeometry.Null;
}
}

private static void BuildRoundedGeometry(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlGeometry, SqlInt32 decimals)
{
string geometryType = sqlGeometry.STGeometryType().Value;

if (geometryType.Equals(OpenGisGeometryType.Polygon.ToString()))
{
BuildRoundedPolygon(sqlGeometryBuilder, sqlGeometry, decimals);
}
else if (geometryType.Equals(OpenGisGeometryType.MultiPolygon.ToString()))
{
BuildRoundedMultiPolygon(sqlGeometryBuilder, sqlGeometry, decimals);
}
else if (geometryType.Equals(OpenGisGeometryType.LineString.ToString()))
{
BuildRoundedLineString(sqlGeometryBuilder, sqlGeometry, decimals);
}
else if (geometryType.Equals(OpenGisGeometryType.GeometryCollection.ToString()))
{
BuildRoundedGeometryCollection(sqlGeometryBuilder, sqlGeometry, decimals);
}
else if (geometryType.Equals(OpenGisGeometryType.Point.ToString()))
{
BuildRoundedPoint(sqlGeometryBuilder, sqlGeometry, decimals);
}
else if (geometryType.Equals(OpenGisGeometryType.MultiPoint.ToString()))
{
BuildRoundedMultiPoint(sqlGeometryBuilder, sqlGeometry, decimals);
}
else if (geometryType.Equals(OpenGisGeometryType.MultiLineString.ToString()))
{
BuildRoundedMultiLineString(sqlGeometryBuilder, sqlGeometry, decimals);
}
else
{
throw new ApplicationException(string.Format("Dont know how to handle the geometry type '{0}'.", geometryType));
}
}

private static void BuildRoundedGeometryCollection(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlGeometryCollection, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginGeometry(OpenGisGeometryType.GeometryCollection);

for (int i = 1; i <= sqlGeometryCollection.STNumGeometries(); i++)
{
SqlGeometry sqlGeometry = sqlGeometryCollection.STGeometryN(i);

BuildRoundedGeometry(sqlGeometryBuilder, sqlGeometry, decimals);
}

sqlGeometryBuilder.EndGeometry();
}

private static void BuildRoundedMultiPolygon(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlMultiPolygon, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginGeometry(OpenGisGeometryType.MultiPolygon);

for (int i = 1; i <= sqlMultiPolygon.STNumGeometries(); i++)
{
SqlGeometry sqlPolygon = sqlMultiPolygon.STGeometryN(i);

BuildRoundedPolygon(sqlGeometryBuilder, sqlPolygon, decimals);
}

sqlGeometryBuilder.EndGeometry();
}

private static void BuildRoundedMultiPoint(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlMultiPoint, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginGeometry(OpenGisGeometryType.MultiPoint);

for (int i = 1; i <= sqlMultiPoint.STNumPoints(); i++)
{
SqlGeometry sqlPoint = sqlMultiPoint.STPointN(i);

BuildRoundedPoint(sqlGeometryBuilder, sqlPoint, decimals);
}

sqlGeometryBuilder.EndGeometry();
}

private static void BuildRoundedMultiLineString(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlMultiLineString, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginGeometry(OpenGisGeometryType.MultiLineString);

for (int i = 1; i <= sqlMultiLineString.STNumGeometries(); i++)
{
SqlGeometry sqlLineString = sqlMultiLineString.STGeometryN(i);

BuildRoundedLineString(sqlGeometryBuilder, sqlLineString, decimals);
}

sqlGeometryBuilder.EndGeometry();
}

private static void BuildRoundedPolygon(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlPolygon, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginGeometry(OpenGisGeometryType.Polygon);

SqlGeometry sqlExteriorRing = sqlPolygon.STExteriorRing();
AddRingRounded(sqlGeometryBuilder, sqlExteriorRing, decimals);

for (int i = 1; i <= sqlPolygon.STNumInteriorRing(); i++)
{
SqlGeometry sqlInteriorRing = sqlPolygon.STInteriorRingN(i);

AddRingRounded(sqlGeometryBuilder, sqlInteriorRing, decimals);
}

sqlGeometryBuilder.EndGeometry();
}

private static void BuildRoundedLineString(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlLineString, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginGeometry(OpenGisGeometryType.LineString);

for (int i = 1; i <= sqlLineString.STNumPoints(); i++)
{
SqlGeometry sqlPoint = sqlLineString.STPointN(i);

if (i == 1)
BeginFigureRounded(sqlGeometryBuilder, sqlPoint, decimals);
else
AddLineRounded(sqlGeometryBuilder, sqlPoint, decimals);
}

sqlGeometryBuilder.EndFigure();

sqlGeometryBuilder.EndGeometry();
}

private static void BuildRoundedPoint(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlPoint, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginGeometry(OpenGisGeometryType.Point);

BeginFigureRounded(sqlGeometryBuilder, sqlPoint, decimals);
sqlGeometryBuilder.EndFigure();

sqlGeometryBuilder.EndGeometry();
}

private static void BeginFigureRounded(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlPoint, SqlInt32 decimals)
{
sqlGeometryBuilder.BeginFigure(Math.Round(sqlPoint.STX.Value, decimals.Value), Math.Round(sqlPoint.STY.Value, decimals.Value));
}

private static void AddRingRounded(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlRing, SqlInt32 decimals)
{
List<point> points = new List<point>();

for (int i = 1; i <= sqlRing.STNumPoints(); i++)
{
SqlGeometry sqlPoint = sqlRing.STPointN(i);

Point p = new Point(Math.Round(sqlPoint.STX.Value, decimals.Value), Math.Round(sqlPoint.STY.Value, decimals.Value));

if (!points.Contains(p))
points.Add(p);
}

// its possible that due to rounding the points in a ring become equal which create an invalid ring, so if there are less than 3 unique points in the rounded ring then skip the ring
if (points.Count >= 3)
{
sqlGeometryBuilder.BeginFigure(points[0].X, points[0].Y);

for (int i = 1; i < points.Count; i++)
{
sqlGeometryBuilder.AddLine(points[i].X, points[i].Y);
}

sqlGeometryBuilder.AddLine(points[0].X, points[0].Y);

sqlGeometryBuilder.EndFigure();
}
}

private static void AddLineRounded(SqlGeometryBuilder sqlGeometryBuilder, SqlGeometry sqlPoint, SqlInt32 decimals)
{
sqlGeometryBuilder.AddLine(Math.Round(sqlPoint.STX.Value, decimals.Value), Math.Round(sqlPoint.STY.Value, decimals.Value));
}
}

internal class Point
{
public Point(double x, double y)
{
this.X = x;
this.Y = y;
}

public double X;
public double Y;
}</point></point>
Feb 1, 2009 at 5:15 AM
My last post got cutoff and missed some very important code...

Thanks
Craig Tadlock
www.tadlockenterprises.com

    internal class Point
{
public Point(double x, double y)
{
this.X = x;
this.Y = y;
}

public double X;
public double Y;

public override bool Equals(object obj)
{
Point p = obj as Point;

if (p != null)
return this.X.Equals(p.X) && this.Y.Equals(p.Y);
else
return base.Equals(obj);
}

public override int GetHashCode()
{
return this.X.GetHashCode();
}
}
Feb 2, 2009 at 10:02 PM
This might work better (and be a lot simpler) if implemented as IGeometrySink.
Also perhaps give some thought as to how robustness issues are handled (ie lines being collapsed).
Jan 28, 2010 at 1:18 AM

Thank you so much for sharing your code. It saved lots of my time.