How to speed up conversion of lat-long to a SqlGeometry?

Question

I have this code, it takes the geometry of countries and also a set of points, and then it only returns the points within those countries :

public static IEnumerable<Point> RemovePointsOutsideBorders(IEnumerable<Point> points, IEnumerable<Country> countries)
{
    var cc = new List<Point>();

    var SPAT_REF_ID = 4326;
    foreach (var p in points)
    {
            var validText = new SqlChars(new SqlString(string.Format("POINT({0} {1})", p.Longitude, p.Latitude)));
            var geoPoint = SqlGeometry.STPointFromText(validText, SPAT_REF_ID);

        foreach (var c in countries)
        {
            if(c.Polygon.STIntersects(geoPoint))
            {
                cc.Add(p);
                break;
            }
        }
    }

    return cc;
}

Current its quite slow, there are about 4000 points, with double lat/long values, the conversion from that into a SqlGeometry is slow (takes about 25 seconds -- I need this to be perhaps down to a second or two):

var s = new SqlChars(new SqlString(string.Format("POINT({0} {1})", p.Longitude, p.Latitude)));
var pGeo = SqlGeometry.STPointFromText(s, SPAT_REF_ID);

This is only done because the SqlGeometry.Point takes x,y instead of lat,long ... any tips on how can I speed this up?

I already know the SqlGeometry (c.Polygon) could be reduced to speed up things, however I am unable to control that. What I am after is a way to speed up the conversion from lat/long to SqlGeometry point.

Are you sure you want to be dealing with `SqlGeometry` and not `SqlGeorgaphy`? Those two are dramatically different in some crucial aspects, especially when dealing with points that are far away (like two countries might be). — Matthew Haugen, Nov 28 '14 at 09:42
@MatthewHaugen I am limited to Geometry since that's what we have in the database, any operation to turn that into SqlGeography in C# would be a further speed loss IMO. — sprocket12, Nov 28 '14 at 09:58
@Muhammad convert the DB to SqlGeography. Any other solution will only postpone the inevitable, the DB is wrong and needs to be changed. — Pedro.The.Kid, Nov 28 '14 at 10:10
From whence are the points coming? If they're coming from a database table, have the conversion happen there. Even if they're not already there, consider throwing them all in a temp table and doing the conversion en masse. Also, once they're there, do the STIntersects() part as a set as well. SQL is good at set-based solutions and right now you're doing a RBAR ("row by agonizing row") solution. — Ben Thul, Nov 28 '14 at 11:16
@BenThul The points are generated, in code. Evenly spaced between a bounding box, part of which is not inside a country (ie, in the sea) thus the check — sprocket12, Nov 28 '14 at 11:35
Sure. But you're iterating over a list within this method which takes a *set of points* and a *set of regions*. Put both sets into a table-valued parameter (one for each set) and pass the TVPs to a stored procedure that will do this check set-wise rather than point-wise. Right now, if you have m points and n regions, you're making n*m database calls. By going with my method, you have an opportunity to make 1 database call. — Ben Thul, Nov 28 '14 at 13:22

score 2 · Answer 1 · answered Nov 28 '14 at 15:59

This is the solution I came up with in the end, it does the whole thing in half a second :

public static IEnumerable<Point> RemovePointsOutsideBorders(IEnumerable<Point> points, IEnumerable<Country> countries)
{
    // join all the country polygons into one (make a stamp)
    var allCountryPolygon = countries.Select(x => x.Polygon).UnionAll();

    // make a single geometry shape from our evenly spaced extent points (cookie dough)
    var pointsGeo = PointsToGeometry(points);

    // do an intersect (stamp country shape over extent based geometry)
    var cookieOfPoints = pointsGeo.STIntersection(allCountryPolygon);

    // how many points left inside? pick them all back out
    for (int n = 1; n <= cookieOfPoints.STNumPoints(); n++)
    {
        var insidePoint = cookieOfPoints.STPointN(n);
        yield return new Point
        {
            Longitude = insidePoint.STX.Value,
            Latitude = insidePoint.STY.Value
        };
    }
}

public static SqlGeometry PointsToGeometry(IEnumerable<Point> points)
{
    var bld = new SqlGeometryBuilder();
    bld.SetSrid(4326);
    bld.BeginGeometry(OpenGisGeometryType.MultiPoint);

    foreach (var p in points)
    {
        bld.BeginGeometry(OpenGisGeometryType.Point);
        bld.BeginFigure(p.Longitude, p.Latitude);
        bld.EndFigure();
        bld.EndGeometry();
    }

    bld.EndGeometry();
    return bld.ConstructedGeometry;
}

public static class ExtensionMethods
{
    /// <summary>
    /// Joins many geometries into one
    /// </summary>
    /// <param name="geometries">geometries to join</param>
    /// <returns>composite geometry</returns>
    public static SqlGeometry UnionAll(this IEnumerable<SqlGeometry> geometries)
    {
        var compositeGeometry = geometries.First();
        foreach (var g in geometries.Skip(1))
        {
            compositeGeometry = compositeGeometry.STUnion(g);
        }
        return compositeGeometry;
    }
}

How to speed up conversion of lat-long to a SqlGeometry?

1 Answers1