ZipCodeGeoJSON Model

ZipCodeGeoJSON.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;
using System.Data.SqlClient;
using System.Web.Helpers;

namespace Models
{
    public static class ZipCodeGeoJSON
    {
        /// <summary>
        /// Returns all geo json data within radius meters of the point
        /// </summary>
        public static List<zipCombined> GetZipsInCircle(decimal latitude, decimal longitude, decimal radius)
        {
            var result = new List<zipCombined>();
            using (var con = new SqlConnection(connectString))
            {
                var quarryText = @"
                SELECT
                gj.zip,
                gj.latitude,
                gj.longitude,
                gj.geoJSON,
                d.population,
                d.age_median as medianAge,
                d.income_median as medianIncome,
                d.home_pct_own,
                d.home_pct_rent,
                d.age_pct_0_19
                age_pct_0_19,
                age_pct_20_39,
                age_pct_40_59,
                age_pct_60_79,
                age_pct_80_over
                FROM (
                    SELECT zip, latitude, longitude, geoJSON,
                    point.STDistance(geography::Point(@center_latitude, @center_longitude, 4326)) AS distance
                    from zipcode_geojson
                ) gj
                INNER JOIN zipcode_demographics d on gj.zip = d.zip
                WHERE gj.distance < @radius
                ";
    
                var quarry = con.Query<zipCombined>(quarryText, 
                    new {center_latitude = latitude, center_longitude = longitude, radius = radius }
                );
    
                result.AddRange(quarry);
            }
    
            return result;
        }
    
        /// <summary>
        /// Returns all geo json data within bounds
        /// </summary>
        public static List<zipCombined> GetZipsInRectangle(
            decimal NELat,
            decimal NELng,
            decimal SWLat,
            decimal SWLng
        )
        {
            var result = new List<zipCombined>();
            using (var con = new SqlConnection(dbConnectString))
            {
                var quarryText = @"
                    DECLARE @area geography = GEOGRAPHY :: STGeomFromText('polygon(({3} {0}, {3} {2}, {1} {2}, {1} {0}, {3} {0}))', 4326)
    
                    SELECT 
                        gj.zip,
                        gj.latitude,
                        gj.longitude,
                        gj.geoJSON,
                        d.population,
                        d.age_median as medianAge,
                        d.income_median as medianIncome,
                        d.home_pct_own,
                        d.home_pct_rent,
                        d.age_pct_0_19
                        age_pct_0_19,
                        age_pct_20_39,
                        age_pct_40_59,
                        age_pct_60_79,
                        age_pct_80_over
                    FROM zipcode_geojson gj
                    INNER JOIN zipcode_demographics d on gj.zip = d.zip 
                    WHERE @area.STIntersects(point) = 1
                ";
    
                quarryText = string.Format(quarryText, NELat, NELng, SWLat, SWLng);
    
                var quarry = con.Query<zipCombined>(quarryText, 
                    new {NELat = NELat, NELng = NELng, SWLat = SWLat, SWLng = SWLng}
                );
    
                result.AddRange(quarry);
            }
    
            return result;
        }
    
        public static geoFeature selectGeoJSON(zipCombined zip)
        {
            var json = zip.geoJSON;
            var jsonObj = Json.Decode(json);
    
            var newGeo = new geoFeature()
            {
                type = "Feature",
                properties = new geoProperties(){
                    zip = jsonObj.properties.ZCTA5CE10,
                    name = jsonObj.properties.ZCTA5CE10,
                    lat = Convert.ToDecimal(jsonObj.properties.INTPTLAT10),
                    lon = Convert.ToDecimal(jsonObj.properties.INTPTLON10),
                    //lArea = Convert.ToDecimal(jsonObj.properties.ALAND10),
                    //wArea = Convert.ToDecimal(jsonObj.properties.AWATER10),
                    pop = zip.population,
                    mAge = zip.medianAge,
                    mInc = zip.medianIncome,
                    home_pct_own = zip.home_pct_own,
                    home_pct_rent = zip.home_pct_rent,
                    age_pct_0_19 = zip.age_pct_0_19,
                    age_pct_20_39 = zip.age_pct_20_39,
                    age_pct_40_59 = zip.age_pct_40_59,
                    age_pct_60_79 = zip.age_pct_60_79,
                    age_pct_80_over = zip.age_pct_80_over
                },
                geometry = new geoGeometry()
                {
                    type = jsonObj.geometry.type,
                    coordinates = jsonObj.geometry.coordinates
                }
            };
    
            return newGeo;
        }
    }
    
    public class zipCombined
    {
        public string zip { get; set; }
        public decimal latitude { get; set; }
        public decimal longitude { get; set; }
        public string geoJSON { get; set; }
        public int population { get; set; }
        public decimal medianAge { get; set; }
        public decimal medianIncome { get; set; }
    
        public decimal home_pct_own { get; set; }
        public decimal home_pct_rent { get; set; }
    
        //age percentages
        public decimal age_pct_0_19 { get; set; }
        public decimal age_pct_20_39 { get; set; }
        public decimal age_pct_40_59 { get; set; }
        public decimal age_pct_60_79 { get; set; }
        public decimal age_pct_80_over { get; set; }
    }
    
    public class geoJSON
    {
        public string type { get; set; }
        public List<geoFeature> features { get; set; }
    }
    
    public class geoFeature
    {
        public string type { get; set; }
        public geoProperties properties { get; set; }
        public geoGeometry geometry { get; set; }
    }
    
    public class geoProperties
    {
        public string name { get; set; }
        public string zip { get; set; }
        //public decimal lArea { get; set; }
        //public decimal wArea { get; set; }
        public decimal rev { get; set; } //revenue
        public decimal qty { get; set; } //quantity
        public decimal lat { get; set; }
        public decimal lon { get; set; }
        public int pop { get; set; } //population
        public decimal mAge { get; set; } //median age
        public decimal mInc { get; set; } //median income
    
        public decimal home_pct_own { get; set; }
        public decimal home_pct_rent { get; set; }
    
        //age percentages
        public decimal age_pct_0_19 { get; set; }
        public decimal age_pct_20_39 { get; set; }
        public decimal age_pct_40_59 { get; set; }
        public decimal age_pct_60_79 { get; set; }
        public decimal age_pct_80_over { get; set; }
    }
    
    public class geoGeometry
    {
        public string type { get; set; }
        public dynamic coordinates { get; set; }
    }
}