How can I use the GeomFromEWKT function in Linq to calculate a more accurate distance

[Loongle]

The effect of using Linq is different from that of using SQL

.Where(c => c.Point.Distance(x) > y)

select * ,ST_Distance(GeomFromEWKT('SRID=4326;POINT(116.510431 40.040612)'), location) km from public.poi_test where ST_Distance(GeomFromEWKT('SRID=4326;POINT(116.510431 40.040612)'), location)<0.005 order by km asc;

How do I generate the above SQL statement

[roji]

Can you provide more detail, what exactly is different? What issue are you seeing?

In general, EF Core translates LINQ queries very differently depending on whether the value is an external variable or an inline constant. In your query, y is an external variable, so it is sent to the database as a parameter (and you should see a placeholder in SQL, something like @y). If you include the expression inside the query (e.g. new Point(1, 1)) you will get an embedded textual representation instead, more similar to what you're asking for.

[Loongle]

Now. I need to generate one of these SQL queries.

select * ,ST_Distance(GeomFromEWKT('SRID=4326;POINT(116.510431 40.040612)'), location) km from public.poi_test where ST_Distance(GeomFromEWKT('SRID=4326;POINT(116.510431 40.040612)'), location)<0.005 order by km asc;

but. i use linq generate, it look like this SELECT * FROM public.poi_test WHERE ST_Distance(POINT(116.510431 40.040612)), location)<0.005

Linq: context.poi_test.Where(c => c.Location.Distance(new Point(116.510431 40.040612)) < 0.005)

I wish I could make myself clear

[roji]

@Loongle what you should be seeing is the following (see program below):

SELECT b."Id", b."Name", b."Point"
FROM "Blogs" AS b
WHERE ST_Distance(b."Point", GEOMETRY 'POINT (1 2)') < 5.0

Note that the literal translation is GEOMETRY 'POINT (1 2)', which is different from what you've posted above. Can you submit a runnable code sample that generates your SQL? Any reason to prefer GeomFromEWKT over the what is generated here?

Code:

class Program
{
    static void Main(string[] args)
    {
        using var ctx = new BlogContext();
        ctx.Database.EnsureDeleted();
        ctx.Database.EnsureCreated();

        var blogs = ctx.Blogs.Where(b => b.Point.Distance(new Point(1, 2)) < 5).ToList();
    }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("..", o => o.UseNetTopologySuite());

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasPostgresExtension("postgis");
    }
}

public class Blog
{
    public int Id { get; set; }
    public Point Point { get; set; }
}

[Loongle]

@roji This is my program

 class Program
    {
        static void Main(string[] args)
        {
            using var ctx = new CityContext();
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            if (!ctx.Citys.Any())
            {
                ctx.Citys.Add(new City("A", 113.75631220439912, 23.021193961066416));
                ctx.Citys.Add(new City("B", 113.74671190778257, 23.023717153780666));
                ctx.Citys.Add(new City("C", 113.74081607709171, 23.021696937088816));
                ctx.Citys.Add(new City("D", 113.739291911819, 23.016301109919954));
                ctx.Citys.Add(new City("E", 113.75245552311183, 23.01096061456806));
                ctx.SaveChanges();
            }

            var citys = ctx.Citys.ToList();
            foreach (var item in citys)
            {
                Console.WriteLine($"Name:{item.Name} Distance:{item.Location.Distance(new Point(116.510431, 40.040612))}");

            }
        }
    }

    public class CityContext : DbContext
    {
        public DbSet<City> Citys { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseNpgsql("...;", o => o.UseNetTopologySuite());

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasPostgresExtension("postgis");
            modelBuilder.Entity<City>().Property(b => b.Location).HasColumnType("geography (point)");
        }
    }

    public class City
    {
        public City()
        {

        }
        public City(string name, double longitude, double latitude)
        {
            Name = name;
            Location = new Point(longitude, latitude);
        }
        public int Id { get; set; }
        public string Name { get; set; }
        [Column(TypeName = "geography")]
        public Point Location { get; set; }
    }

Console result:

but i use sql select. Unexpected results：

SQL:

select * ,ST_Distance(GeomFromEWKT('SRID=4326;POINT(116.510431 40.040612)'), "Location") km from public."Citys"  

SQL Result:

What's going on here?

[roji]

Sorry for not responding sooner on this.

Your database Location is defined as geography (SRID 4326). However, in your sample above, you're using NetTopologySuite to calculate the distance client-side (without the database), and you're not specifying any SRID. As a result, the calculations don't use SRID 4326 (geography), and you get completely different results.

You can use the following to calculate the distance in the database instead of client-side:

var dbCities = ctx.Citys.Select(c => new
{
    City = c,
    Distance = c.Location.Distance(new Point(116.510431, 40.040612))
}).ToList();

This gives the expected results:

Name:A Distance:1904910.36403259
Name:B Distance:1904756.56818803
Name:C Distance:1905054.529108
Name:D Distance:1905667.00260493
Name:E Distance:1906084.24143259

Complete code sample

```c# class Program { static void Main(string[] args) { using var ctx = new CityContext(); ctx.Database.EnsureDeleted(); ctx.Database.EnsureCreated(); if (!ctx.Citys.Any()) { ctx.Citys.Add(new City("A", 113.75631220439912, 23.021193961066416)); ctx.Citys.Add(new City("B", 113.74671190778257, 23.023717153780666)); ctx.Citys.Add(new City("C", 113.74081607709171, 23.021696937088816)); ctx.Citys.Add(new City("D", 113.739291911819, 23.016301109919954)); ctx.Citys.Add(new City("E", 113.75245552311183, 23.01096061456806)); ctx.SaveChanges(); } var citys = ctx.Citys.ToList(); foreach (var item in citys) { Console.WriteLine($"Name:{item.Name} Distance:{item.Location.Distance(new Point(116.510431, 40.040612))}"); } var dbCities = ctx.Citys.Select(c => new { City = c, Distance = c.Location.Distance(new Point(116.510431, 40.040612)) }).ToList(); Console.WriteLine("From the database:"); foreach (var item in dbCities) { Console.WriteLine($"Name:{item.City.Name} Distance:{item.Distance}"); } } } public class CityContext : DbContext { public DbSet Citys { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseNpgsql(@"Host=localhost;Username=test;Password=test", o => o.UseNetTopologySuite()) .EnableSensitiveDataLogging() .UseLoggerFactory(ContextLoggerFactory); private static ILoggerFactory ContextLoggerFactory => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information)); protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.HasPostgresExtension("postgis"); modelBuilder.Entity().Property(b => b.Location).HasColumnType("geography (point)"); } } public class City { public City() {} public City(string name, double longitude, double latitude) { Name = name; Location = new Point(longitude, latitude); } public int Id { get; set; } public string Name { get; set; } [Column(TypeName = "geography")] public Point Location { get; set; } } ``` Am going to go ahead and close this is a usage issue, but if something is still unclear please don't hesitate to post back here.