CODEWITHSUNDEEP
c#sqlgeographysql-types
Tim
Tim

Reputation: 8921

using SqlGeography types in C# client-side

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

Upvotes: 0

Views: 1371

Answers (2)

Brian W.
Brian W.

Reputation: 118

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.

Upvotes: 2

Jonathan
Jonathan

Reputation: 5018

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

Upvotes: 0

Related Questions