Determine if point is inside shape SQL Server 2014

Question

I'm developing a GPS project I allow user to draw (using google) a shape(rectangle, polygon, circle, line) and save it in database

SQL Fiddle

Now my requirement is to determine if a given point within anyone in the table and return the id else return 0.

the trick is I save multiple shapes (Type column determine the shape type 2 is a rectangle- 4 is polygon)

What I have tried

Example

Note: I will be using this from C# and pass point as parameter

Answer 1

First of all I would suggest you save the Polygons as Spatial Data Types

In JavaScript you can easily convert the drawn polygons to a List/Array of Lat/Lngs

for a circle you need the radius and the center lat/lng of the circle

something like this:

var circleCenter = null;
var circlePolygon = null;

// set draw options on google map
mapObject.setOptions({ draggableCursor: 'crosshair' });

var drawGeofenceEvent = google.maps.event.addListener(mapObject, 'click', event => {

    var myLatLng = event.latLng;

    // create a marker for center
    circleCenter = new google.maps.Marker({
        map: mapObject,
        position: myLatLng,
        draggable: true
    });

    // create a temp circle polygon to plot on map
    circlePolygon = new google.maps.Circle({
        radius: 150,
        editable: true,
        draggable: true
    });

    circlePolygon.setMap(mapObject);

    circlePolygon.bindTo('center', circleCenter, 'position');

    google.maps.event.removeListener(drawGeofenceEvent);
});


// after drawing the geofence you want to get the lat/longs along the circumference
var circleCoordinates = this.googleCircleToPolylineArray(this.circleCenter.position, this.circlePolygon.getRadius() * 0.000621371, 1);


// copied from Stackoverflow - will post the link when I find it
googleCircleToPolylineArray(point, radius, dir) {
    var lat = point.lat();
    var lng = point.lng();
    var d2r = Math.PI / 180; // degrees to radians
    var r2d = 180 / Math.PI; // radians to degrees
    var earthsradius = 3963; // 3963 is the radius of the earth in miles
    // ReSharper disable once AssignedValueIsNeverUsed
    var points = 32;
    //var radius = 1;             // radius in miles
    // find the raidus in lat/lon
    var rlat = (radius / earthsradius) * r2d;
    var rlng = rlat / Math.cos(lat * d2r);
    var extp = new Array();
    for (var i = 0; i < points + 1; i++) {
        var theta = Math.PI * (i / (points / 2));
        var ex = lng + (rlng * Math.cos(theta)); // center a + radius x * cos(theta)
        var ey = lat + (rlat * Math.sin(theta)); // center b + radius y * sin(theta)
        extp.push({ Latitude: ey, Longitude: ex }); //   new google.maps.LatLng(ey, ex)
    }
    return extp;
};


// then you would POST the circleCoordinates to the Server to save as a spatial data type

Then on the server you would convert the co-ordinates to a Well-Known-Text (WKT) Wikipedia - for Polygon use the following, when I say Polygon I mean circle, rectangle, triangle, hexagon etc. Anything which has the same start and end point (is closed).

for a line you would use the LINESTRING WKT

public static DbGeography CreatePolygon(Coordinate[] latLongs)
{
    //POLYGON ((73.232821 34.191819,73.233755 34.191942,73.233653 34.192358,73.232843 34.192246,73.23269 34.191969,73.232821 34.191819))
    var polyString = "";
    foreach (var point in latLongs)
    {
        polyString += point.Longitude + " " + point.Latitude + ",";
    }
    polyString = polyString.TrimEnd(',');
    polyString = string.Format("POLYGON(({0}))", polyString);
    var polygonFromText = DbGeography.PolygonFromText(polyString, DbGeography.DefaultCoordinateSystemId);
    return polygonFromText;
}

Reverse From DbGeography to Coordinates

public static List<Coordinate> PolygonToGeoPoints(DbGeography sptGeofenceArea)
 {
     var points = new List<Coordinate>();
     string polygonText = sptGeofenceArea.ProviderValue.ToString();
     polygonText = polygonText.Replace("POLYGON", "");
     polygonText = polygonText.Replace("(", "").Replace(")", "").Trim();
     var polPoints = polygonText.Split(',');
     foreach (var point in polPoints)
     {
         var latlong = point.Trim().Split(' ');
         points.Add(new Coordinate { Latitude = double.Parse(latlong[1]), Longitude = double.Parse(latlong[0]) });
     }
     return points;
 }

I use Entity Framework and DbGeography types, I save the polygon as a spatial data type in the database.

You can edit the above code to return the Well-Known-Text (WKT) instead of the DbGeography data type.

Then once the spatial data type is stored in the database all you have to do is convert the point you want to check to a spatial data type OR WKT

WKT - SQL VERSION

DECLARE @point GEOGRAPHY;
SET @point = geography::Point(47.653, -122.358, 4326)


Select
    *
From Polygons 

where POLYGON.STIntersects(@point) = 1

SPATIAL TYPE - ENTITY FRAMEWORK

DbGeography point;

dbCOntext.Polygons.Where(s => point.Intersects(s.Polygon)).ToList();

EDIT

There is a common problem when creating the Polygon types - the points have to be in a certain order otherwise you would have a Polygon covering the entire earth except the required Polygon - to overcome that you can use the following -

LINK

    #region
    //https://www.exceptionnotfound.net/fixing-sql-server-spatial-not-a-valid-instance-of-geography-errors-in-c-sharp/
    private static DbGeography CreatePolygon(string wellKnownText)
    {
        //First, get the area defined by the well-known text using left-hand rule
        var sqlGeography = SqlGeography.STGeomFromText(new SqlChars(wellKnownText), DbGeography.DefaultCoordinateSystemId);

        if(!sqlGeography.STIsValid())
            throw new Exception("Invalid polygon, please draw the polygon again.");

        sqlGeography = sqlGeography.MakeValid();

        //Now get the inversion of the above area
        var invertedSqlGeography = sqlGeography.ReorientObject();

        //Whichever of these is smaller is the enclosed polygon, so we use that one.
        if (sqlGeography.STArea() > invertedSqlGeography.STArea())
        {
            sqlGeography = invertedSqlGeography;
        }
        return DbSpatialServices.Default.GeographyFromProviderValue(sqlGeography);
    }
    #endregion

    public static DbGeography CreatePolygon(Coordinate[] latLongs)
    {
        //POLYGON ((73.232821 34.191819,73.233755 34.191942,73.233653 34.192358,73.232843 34.192246,73.23269 34.191969,73.232821 34.191819))
        var polyString = "";
        foreach (var point in latLongs)
        {
            polyString += point.Longitude + " " + point.Latitude + ",";
        }
        polyString = polyString.TrimEnd(',');
        polyString = string.Format("POLYGON(({0}))", polyString);

        var dbGeographyPolygon = CreatePolygon(polyString);

        return dbGeographyPolygon;


        //var polygonFromText = DbGeography.PolygonFromText(polyString, DbGeography.DefaultCoordinateSystemId);
        //return polygonFromText;

    }