Convert Geometry to Geography against PostGIS with Npgsql and EntityFramework Core

Question

In PostgreSQL PostGIS I can get the distance in meters by just casting the geometry column to geography using this syntax inside the sql query "geometry_column::geography". like so :

select * from "Tags" where ST_Distance("Location",'POINT(31.233334 30.033333)'::geography) < 1000 ;

Now I want to convert tag.Location and point to Geography inside the where function of Linq to get the same result.

var tags = db.Tags.Where((tag) => tag.Location.Distance(point) < RangeInMeters )
                .Select(tag => new {
                    Id = tag.Id,
                    Title = tag.Title,
                    Coordinates = new double[]{tag.Location.X,tag.Location.Y},
                });

here's tag.Location definition

[Column(TypeName="geometry (point)")]
public Point Location{get;set;}

and here's "point" variable definition

var point = new Point(new Coordinate(Longitude,Latitude));
point.SRID = 4326;

There's no just in that cast. geography contains coordinates on a sphere while geometry on a plane. You need to project from the sphere to a plane and vice versa. NTS alone doesn't include projections and makes this quite explicit in the docs page. That cast doesn't calculate distances though. What's the actual PostgreSQL query you used? To calculate distances you need two points — Panagiotis Kanavos
inside the where function of Linq to get the same result. the same as what query? LINQ queries aren't executed directly, they'r converted to SQL queries. What query are you trying to generate? — Panagiotis Kanavos
@PanagiotisKanavos I use something like this query to get the points that are within a range of meters ... select * from "Tags" where ST_Distance("Location",'POINT(31.233334 30.033333)'::geography) < 1000 ; ..... the challenge is I want to tell linq to do something like "::geography" inside the sql query. — Mawardy
Change the column attribute to [Column(TypeName="geography")] — Panagiotis Kanavos

Panagiotis Kanavos Panagiotis Kanavos · Accepted Answer · 2020-12-15T16:46:16

Change the column attribute to [Column(TypeName="geography")]. You don't need a cast if the point is already a geography. Check the examples in Spatial Mapping with NetTopologySuite.

From the doc example :

public class City
{
    public int Id { get; set; }
    public string Name { get; set; }

    [Column(TypeName="geography")]
    public Point Location { get; set; }
}

var nearbyCities = context.Cities.Where(c => c.Location.Distance(somePoint) < 100);

You can create a geography point directly with ST_GeographyFromText. From the PostGIS geography docs, you can calculate the distance between two points in meters with :

SELECT ST_Distance(
  ST_GeographyFromText('POINT(-118.4079 33.9434)'), -- Los Angeles (LAX)
  ST_GeographyFromText('POINT(2.5559 49.0083)')     -- Paris (CDG)
  );

This returns :

9124665.26917268

Or 9124Km

Convert Geometry to Geography against PostGIS with Npgsql and EntityFramework Core

1 Answers