private static List <Section> FindContainedSections(DbConnection connection, SqlGeographyBuilder searchArea)
{
IEnumerable <Township> townships = FindContainedTownships(connection, searchArea);
List <Section> results = new List <Section>();
foreach (var township in townships)
{
var sectionsParameters = new BoundingBoxParam();
sectionsParameters.AddGeography("@geometry", searchArea.ConstructedGeography);
sectionsParameters.AddString("@township", township.TWP);
sectionsParameters.AddString("@range", township.RGE);
sectionsParameters.AddString("@meridian", township.MER);
var sections = connection.Query <Section>(@"select *,
geom as Coordinates from sections s
where ptwp=@township
and prge = @range
and pmer = @meridian
and @geometry.STIntersects(s.geom) > 0",
sectionsParameters);
results.AddRange(sections);
}
return(results);
}
private IEnumerable <dynamic> GetLSDs(DbConnection connection, SqlGeographyBuilder searchArea, int zoomLevel)
{
List <LSD> results = new List <LSD>();
foreach (var section in FindContainedSections(connection, searchArea))
{
var lsdsParameters = new BoundingBoxParam();
lsdsParameters.AddGeography("@geometry", searchArea.ConstructedGeography);
lsdsParameters.AddString("@township", section.PTWP);
lsdsParameters.AddString("@range", section.PRGE);
lsdsParameters.AddString("@meridian", section.PMER);
lsdsParameters.AddString("@section", section.SECT);
var lsds = connection.Query <LSD>(@"select *,
LSD as LSDName,
geom as Coordinates from lsds l
where ptwp=@township
and prge = @range
and pmer = @meridian
and psect = @section
and @geometry.STIntersects(l.geom) > 0",
lsdsParameters);
results.AddRange(lsds);
}
return(results.Select(x => new { Name = x.Name, Coordinates = GetCoordinates(x.Coordinates, zoomLevel), CenterCoordinates = GetLabelCoordinates(x.Coordinates) }));
}
private static IEnumerable <Township> FindContainedTownships(DbConnection connection, SqlGeographyBuilder searchArea)
{
var boundingBoxParameters = new BoundingBoxParam();
boundingBoxParameters.AddGeography("@geometry", searchArea.ConstructedGeography);
var townships = connection.Query <Township>(@"select *, geom as Coordinates from townships t where @geometry.STIntersects(t.geom) > 0", boundingBoxParameters);
return(townships);
}