STContains/STWithin returning all results even if isn't inside

[lzinga]

Hey I am not sure if I am doing something wrong or not. I am using GeoJson for states and am looking through my own data to add my data to it.

var data = await File.ReadAllTextAsync(Path.Combine(executingDirectory, StatesJson));
var collection = JsonConvert.DeserializeObject<FeatureCollection>(data);
foreach (var state in collection.Features)
{
    var geo = state.ToSqlGeography(options.Value.Srid).MakeValid();
    var records = myData.Where(i => SqlGeography.Point(i.Location.Y, i.Location.X, options.Value.Srid).MakeValid().STWithin(geo).Value);

    if(records != null && records.Count() > 0)
    {
        state.Properties.Add("stateCount", records.GetMyData());
    }
}

return collection;

The reason the Y goes in the latitude and X goes in the longitude is because of this. It works for 2 of the states (Washingotn and Utah which is where the data is expected) however it ends up filling up other states as well.

When I place a break point in the if statement it starts off with Washington with the expected results, and then Utah, but then it breaks on Arkansas.

The Washington geo data for the boundry of the state comes out to be

  | Name | Value | Type
-- | -- | -- | --
▶ | geo | {POLYGON ((-116.9980726794949 46.330169786151487, -117.02664974655227 47.72292715106596, -117.03142981653929 48.999309190458973, -120.00269628968556 49.000885321643864, -122.84 49.000000000000114, -122.49983068910967 48.180160223984387, -122.34002132224703 47.359951890647778, -122.58713802146673 47.095885321636388, -123.12 48.04, -124.56610107421875 48.379714965820426, -124.68721008300781 48.184432983398381, -124.39567 47.72017000000011, -124.079635 46.86475, -123.99867632678428 46.2830694871044, -123.77670365460955 46.282033189471633, -123.47137345441303 46.277253119484669, -123.22849422264113 46.186250922218562, -122.9379693202963 46.12909678810388, -122.75224422263926 45.93807485613678, -122.72578588930577 45.770333156917559, -122.65170772361546 45.63008331967734, -122.42045568910925 45.591997789078221, -122.17491512107449 45.595150051447945, -121.78809118715355 45.700983384781807, -121.56107662009549 45.732764390641307, -121.203889120094 45.689898790055224, -121.0467410894945 45.637498887711104, -120.83719315655094 45.672948920263423, -120.60963598695361 45.753900051448625, -120.15826818910023 45.740670884781935, -119.78730891989301 45.850767523779837, -119.59204952047821 45.932235419287906, -119.33699968145743 45.888336290055861, -118.97823605027111 45.992567653988658, -116.9150028145857 45.999983222022593, -116.90652787968992 46.177775987322832, -116.9980726794949 46.330169786151487))} | Microsoft.SqlServer.Types.SqlGeography

and here is the Arkansas

  | Name | Value | Type
-- | -- | -- | --
▶ | geo | {POLYGON ((-89.662919481143419 36.023072821591967, -89.673513149763252 35.94000295668279, -89.775109015649065 35.799236355119717, -89.950266282902376 35.701877956681827, -89.988894416040552 35.536229152970236, -90.147101813502132 35.404996853165017, -90.13493201369738 35.113955186497193, -90.249240281927 35.020834255507239, -90.268283047226561 34.941459255506913, -90.446618415065814 34.866838487277448, -90.4503132799747 34.7218602564826, -90.584206916042945 34.454098822562258, -90.6995487129184 34.397461452770358, -90.876307949572748 34.261474921194306, -90.9821412829065 34.0551050888367, -91.20068091506883 33.706392523731139, -91.223444383493415 33.469326890592171, -91.1080767484018 33.2068364527656, -91.156239183297828 33.010000922165858, -92.001303880566837 33.043874823533173, -93.094027879594648 33.010517686488768, -94.059757046265162 33.012119655889819, -94.002086147827441 33.57991445569678, -94.233338182333569 33.583609320605646, -94.427538214886425 33.570380153938927, -94.479912279014258 33.635983384733464, -94.451361050172991 34.510710354138, -94.430173712933311 35.483312486303348, -94.628611212934118 36.540586452778939, -93.412587246457889 36.52629791925024, -92.30717668330243 36.523662421203355, -91.251478848011743 36.52314565688043, -90.112194383488969 36.461754055317684, -90.029098680363632 36.337937323546356, -90.141804979192216 36.230502020811556, -90.253994513697862 36.122549953753833, -90.315386115260608 36.023072821591967, -89.662919481143419 36.023072821591967))} | Microsoft.SqlServer.Types.SqlGeography

The records variable inside the if statement for these states is filled with every result that is with in myData a total of 21 records. Like I mentioned both Utah and Washington (which actually has data) turn out fine but all the others East of Utah are for some reason getting all records even if their latitude/longitude don't fall with in the polygons.

I have tried both SqlGeography.Point( ... ).STWithin(geo) and geo.STContains(SqlGeography.Point( ... )) both return the same results.

Lastly here is one of the points that is showing up in a state that shouldn't have any data {POINT (47.2219 -122.4479)} yet some how the methods above are showing true even though the point shouldn't exist within that location.

Let me know if you need any more information, as I am really wanting to try to figure this out.

[lzinga]

After a bit of manual playing around I was able to accomplish the task using the following.

            var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: options.Value.Srid);
            foreach (var state in collection.Features)
            {
                IGeometry stateGeo = null;
                switch (state.Geometry.Type)
                {
                    case GeoJSON.Net.GeoJSONObjectType.Polygon:
                        var poly = state.Geometry as Polygon;
                        var coords = poly.Coordinates.SelectMany(i => i.Coordinates.Select(x => new GeoAPI.Geometries.Coordinate(x.Longitude, x.Latitude)));
                        stateGeo = geometryFactory.CreatePolygon(coords.ToArray());
                        break;
                    case GeoJSON.Net.GeoJSONObjectType.MultiPolygon:
                        // ...
                        break;
                }

                var records = myData
                    .Where(i => i.Location.Within(stateGeo)); 

                if(records != null && records.Count() > 0)
                {
                    state.Properties.Add("myGeoJsonData", records.Count());
                }
            }

I should of also added in the first post that I was using the new .net core/EF spatial data which seems to be different from the SqlGeography/SqlGeometry. Though I figured what I originally had should of still worked. For now I have found a work around. I am still needing to figure out MultiPolygon but shouldn't be too hard since I have a regular one done.

[xfischer]

Hi @lzinga !
Thanks for reporting. Which Nuget were you using ?
Did you try with SqlGeometry rather than SqlGeography ?

[lzinga]

Nuget Packages Used were the following with both being the latest version.
https://www.nuget.org/packages/GeoJSON.Net.Contrib.MsSqlSpatial/
https://www.nuget.org/packages/GeoJSON.Net/

I did indeed try with SqlGeometry instead of SqlGeography but then no data would should up on the map, I did play with the way the coordinates were entered too but didn't seem to change anything.

[xfischer]

Lastly here is one of the points that is showing up in a state that shouldn't have any data {POINT (47.2219 -122.4479)} yet some how the methods above are showing true even though the point shouldn't exist within that location.

This point is perfectly inside Washington. (see https://www.google.com/maps/place/47%C2%B013'18.8%22N+122%C2%B026'52.4%22W/@47.3602567,-124.4208462,6z/data=!4m5!3m4!1s0x0:0x0!8m2!3d47.2219!4d-122.4479)

The code I used was :

var geo = state.ToSqlGeography(4326);
if (geo.STIsValid().IsFalse) geo = geo.MakeValid();

bool within = SqlGeography.Point(47.2219, -122.4479, 4326).STWithin(geo).Value;
bool intersects = SqlGeography.Point(47.2219, -122.4479, 4326).STIntersects(geo).Value;

// both are true for Washington

I am missing something here ?

[lzinga]

So the point should exist within Washington state but it also shows for all the states highlighted east of Utah as shown on the map when it is trying to select records with this code:

var records = myData.Where(i => SqlGeography.Point(i.Location.Y, i.Location.X, options.Value.Srid).MakeValid().STWithin(geo).Value);

and the geo variable is the state boundry so I am unsure why it is selecting records that their location are not within the state boundary geojson. Sorry if I am not explaining this well.

[xfischer]

@lzinga I found what's the problem. It's a classic one with geography types : the ring orientation of the geojson states does not follow the "left-hand-rule".

More on that here on this excellent blog by @alastaira (I had to mention you!), the king-of-sql-spatial-who-then-switched-to-independant-gaming.

So if you add the @alastaira's workaround :

var geo = state.ToSqlGeography(4326);
if (geo.STIsValid().IsFalse) geo = geo.MakeValid(); // Make valid if not (warning this can alter geometry)
if (geo.EnvelopeAngle() >= 90) geo = geo.ReorientObject(); // reorient if needed

This should work!
Let me know...

[lzinga]

Awesome, thanks! Not exactly sure what changed from my code before when the code was working but now I get a dll error. I didn't change framework or anything

Unable to load DLL 'SqlServerSpatial140.dll' or one of its dependencies: The specified module could not be found.

I am sure the code above will fix the original issue though, just need to solve this problem now that appeared out of no where lol.

[xfischer]

Cool !
You have to add this bootstrap code at the very beginning of your program :
SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

Please mark as resolved if it is ok for you.
Cheers

[lzinga]

This is a .net core 2.2 application, I did the equivalent of the above and it alerts about msvcr120.dll not sure what is going on and why this could of possibly of worked before since it doesn't look like Microsoft.SqlServer.Types is .net core compatible...
Well either way I guess I can use the code I came up with to run it on .net core until I can figure out if it is something on my end.

Thanks!