using SqlGeography types in C# client-side

Question

In C# client-side, I am trying to use the SQLServerTypes (SqlServerSpatial140.dll) assembly (edit: doing so directly, SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory)) to measure the distance between points on the planet, between one street address and another street address which are usually within 50 miles of each other (but sometimes a lot farther). Each location is expressed as a latitude/longitude pair.

This is a Framework application and uses the NuGet package.

Is this code correct? I don't think it can be, since the values I'm getting for distance are much too small, e.g. 24.35946... when the two points are hundreds of miles away from each other, such as two towns, one of them in North Carolina and the other in Puerto Rico. Isn't meters the standard unit?

        foreach (Origin o in Origins)
        {
            o.loc = SqlGeometry.Point(o.lat, o.lon, 4326);
            foreach (Destination d in Destinations)
            {
                SqlDouble distance = o.loc.STDistance(SqlGeometry.Point(d.lat, d.lon, 4326));
                <snip>                   
            }
        }

Is 4326 the correct SRID? I get the same results if zero is the SRID. Also, the order in which the parameters to Point are supplied (lat,lon) or (lon,lat) doesn't make the distance numbers much larger.

P.S. I was able to get it working with Brian's help. Here's how I'm instantiating the Point:

 public Microsoft.SqlServer.Types.SqlGeography CreateGeographyPoint(double longitude, double latitude)
        {
            var text = string.Format("POINT({0} {1})", longitude, latitude);
            var ch = new System.Data.SqlTypes.SqlChars(text);
            return Microsoft.SqlServer.Types.SqlGeography.STPointFromText( ch, 4326);
        }

If you are using the NuGet package and not the assembly directly, please update your question to indicate as much. Also, is this .NET Core or Framework? — Ian Kemp, Sep 21 '20 at 20:26
@Ian Kemp. I do not understand the difference between "using the NuGet package" and using the assembly "directly". I obtained the package from NuGet, but the assembly must be explicitly loaded at run-time, so I figured I was using it directly. Also the DLL from the folder created by the NuGet installation must be copied over to the `bin` folder. — Tim, Sep 22 '20 at 12:04

score 2 · Accepted Answer · answered Sep 21 '20 at 21:03

2

You should use the Sqlgeography class and the Srid of 4326 will give you the result in meters.

SqlGeometry is for Cartesian coordinates (x, y), and SqlGeography is for Geospatial coordinates Long, Lat in that order.

Replace SqlGeometry with SqlGeography.

answered Sep 21 '20 at 21:03

Brian W.

118
6

Loading the DLL at runtime is new to me. Is there a way to declare objects, using types from the assembly, at design-time? I am unable to add a reference to `SqlServerSpatial140.dll` to my project. – Tim Sep 21 '20 at 22:29
1

What worked for me was to go to this file location below and copy the SqlServerSpatial240.dll to my project bin folder. C:\Users\\.nuget\packages\microsoft.sqlserver.types\14.0.1016.290\nativeBinaries\x64 – Brian W. Sep 21 '20 at 22:48
Thanks very much for your help in this. – Tim Sep 21 '20 at 22:49

score 0 · Answer 2 · answered Sep 21 '20 at 20:50

This is really more of a GIS question than a programming question.

SRID is Spatial Reference ID; the spatial reference that the data in that table is recorded in, or the spatial reference that you want to use when working with or displaying the data. See:

https://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/what-is-an-srid.htm

and

https://learn.microsoft.com/en-us/sql/relational-databases/spatial/spatial-reference-identifiers-srids

You can query data in your table to see what spatial reference it is recorded in. See:

https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/stsrid-geometry-data-type?view=sql-server-ver15

As to whether or not that SRID is correct, that's up to you. What SRID was used when the data was entered? If you want it out with the same spatial reference, use the same SRID. If you want the results out in a different spatial reference, use a different SRID. 4326 is WGS84:

https://en.wikipedia.org/wiki/World_Geodetic_System#WGS84

using SqlGeography types in C# client-side

2 Answers2