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); }