Spatial Querying example in CDSA

The CDSA architecture templates generate managers and a SQLDAL that is capable of performing any combination of the "normal" queries that you might perform in SQL (such as Equals, Like, In, Less, Greater etc..) However, the default template output does not include any code for performing spatial queries. This is where the extensible nature of CDSA helps us, as we can simply define a new abstraction in the DML for performing a spatial query against the entity that supports it, such as:

public abstract class ExampleGeographyManagerExtns
{
    public abstract Dictionary<Guid, double> GetDataIDsInProximityTo(Guid targetExampleId, double distance);

    public abstract Dictionary<Guid, double> GetDataIDsInProximity(double latitude, double longitude, double distance);

    public abstract List<Guid> GetDataIDsInWKT(string wkt);
}

You can then implement that extension in the SQLDAL as a set of SQL queries:

public class ExampleGeographyManagerExtns : DML.ManagementExtns.ExampleGeographyManagerExtns
{
    private Provider.SqlDataProvider provider;
    private Schemas.SqlExampleGeographySchema schema = Schemas.SqlExampleGeographySchema.GetInstance();

    internal ExampleGeographyManagerExtns(Provider.SqlDataProvider providerInstance)
    {
        this.provider = providerInstance;
    }

    public override Dictionary<Guid, double> GetDataIDsInProximityTo(Guid targetExampleId, double distance)
    {
        // use a sql query to get the averaged data
        string sqlQryText = string.Format(
            @"
SELECT ExampleGeography.ExampleId, ExampleGeography.PointGeography.STDistance(b.PointGeography) Distance
from ExampleGeography
inner join ExampleGeography b
on b.ExampleId=@ExampleId
and b.PointGeography.STBuffer({0}).STIntersects(ExampleGeography.PointGeography)=1
where ExampleGeography.ExampleId<>b.ExampleId
order by Distance asc", distance);

        // create the parameters to the query
        Dictionary<string, object> sqlQryParams = new Dictionary<string, object>();
        sqlQryParams.Add("ExampleId", targetExampleId);

        DataSet rawData = this.provider.SqlHelper.Execute(sqlQryText, CommandType.Text, sqlQryParams);

        Dictionary<Guid, double> results = new Dictionary<Guid, double>();

        if (rawData != null && rawData.Tables.Count > 0)
        {
            foreach (DataRow dr in rawData.Tables[0].Rows)
            {
                results.Add((Guid)dr[this.schema.UniqueId], (double)dr["Distance"]);
            }
        }

        return results;
    }

    public override Dictionary<Guid, double> GetDataIDsInProximity(double latitude, double Longitude, double distance)
    {
        // use a sql query to get the averaged data
        string sqlQryText = string.Format(
            @"
Declare @GeogPoint as geography
Declare @GeogShape as geography

SET @GeogPoint = geography::STGeomFromText('POINT (' + Cast(@longitude as varchar) + ' ' + Cast(@latitude as varchar) + ')', 4326)
SET @GeogShape = @GeogPoint.STBuffer({0})

SELECT ExampleGeography.ExampleId, ExampleGeography.PointGeography.STDistance(@GeogPoint) Distance
from ExampleGeography
where @GeogShape.STIntersects(ExampleGeography.PointGeography)=1
order by Distance asc", distance);

        // create the parameters to the query
        Dictionary<string, object> sqlQryParams = new Dictionary<string, object>();
        sqlQryParams.Add("Latitude", latitude);
        sqlQryParams.Add("Longitude", longitude);

        DataSet rawData = this.provider.SqlHelper.Execute(sqlQryText, CommandType.Text, sqlQryParams);

        Dictionary<Guid, double> results = new Dictionary<Guid, double>();

        if (rawData != null && rawData.Tables.Count > 0)
        {
            foreach (DataRow dr in rawData.Tables[0].Rows)
            {
                results.Add((Guid)dr[this.schema.UniqueId], (double)dr["Distance"]);
            }
        }

        return results;
    }

    public override List<Guid> GetDataIDsInWKT(string wkt)
    {
        string sqlQryText = @"
Declare @GeogPolygon as geography
Declare @GeomPolygon as geometry

Set @GeomPolygon = Geometry::STGeomFromText(@WKT, 4326)
Set @GeogPolygon = Geography::STGeomFromWKB(@GeomPolygon.MakeValid().STUnion(@GeomPolygon.STStartPoint()).STAsBinary(), 4326)

SELECT ExampleGeography.ExampleId
from ExampleGeography
WHERE PointGeography.STIntersects(@GeogPolygon)=1";

        // create the parameters to the query
        Dictionary<string, object> sqlQryParams = new Dictionary<string, object>();
        sqlQryParams.Add("WKT", wkt);

        DataSet rawData = this.provider.SqlHelper.Execute(sqlQryText, CommandType.Text, sqlQryParams);

        List<Guid> results = new List<Guid>();

        if (rawData != null && rawData.Tables.Count > 0)
        {
            foreach (DataRow dr in rawData.Tables[0].Rows)
            {
                results.Add((Guid)dr[this.schema.UniqueId]);
            }
        }

        return results;
    }
}

We can then expose the extension in the IDataProvider interface and SQL implemention:

public partial interface IDataProvider : ClauseWrappers.WhereClauseWrapper.IWhereClauseHandler, ClauseWrappers.OrderByClauseWrapper.IOrderByClauseHandler
{
    // expose specialised management functions/classes
    ManagementExtns.ExampleGeographyManagerExtns ExampleGeographyManagerExtns { get; }
}

public partial class SqlDataProvider : DML.Provider.IDataProvider
{
    private ManagementExtns.ExampleGeographyManagerExtns exampleGeographyManagerExtns;

    public DML.ManagementExtns.ExampleGeographyManagerExtns ExampleGeographyManagerExtns
    {
        get
        {
            if (this.exampleGeographyManagerExtns == null)
            {
                this.exampleGeographyManagerExtns = new ManagementExtns.ExampleGeographyManagerExtns(this);
            }

            return this.exampleGeographyManagerExtns;
        }
    }
}

Finally, we can now expose this functionality through our extended business layer manager class:

public partial class ExampleGeographyManager : BaseClasses.ExampleGeographyManagerBase
{
    public Dictionary<long, double> GetRecordIDsInProximityTo(long targetExampleId, double distance)
    {
        return DataProviders.Current.ExampleGeographyManagerExtns.GetDataIDsInProximityTo(targetExampleId, distance);
    }

    public List<long> GetRecordIDsInWKT(string wkt)
    {
        return DataProviders.Current.ExampleGeographyManagerExtns.GetDataIDsInWKT(wkt);
    }

    public Dictionary<long, double> GetRecordIDsInProximity(double latitude, double longitude, double radiusInMeters)
    {
        return DataProviders.Current.ExampleGeographyManagerExtns.GetDataIDsInProximity(latitude, longitude, radiusInMeters);
    }
}