CODEWITHSUNDEEP
c#postgresqllinqasp.net-coreentity-framework-core
Gicu Mironica
Gicu Mironica

Reputation: 635

Geospacial search - Entity Framework Core / PostgreSQL

I have a table storing some events locations and a class mapped to it with ef core:

public class Location
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        
        [Column(TypeName = "geography (point)")]
        public Point Location { get; set; }

        public string? Address { get; set; }

        public string? City { get; set; }

        public string? Region { get; set; }

        public string? Country { get; set; }

        public Post Post { get; set; }

        public int PostId { get; set; }
    }

The Problem
The Location class has a 1 - 1 relationship with the Post. Now, given an input of latitude and longitude and radius of X km, I want to be able to get all posts from the database within that radius. i.e I want to perform a geospacial query.

I found a solution but it involves performing some plain SQL queries which I try to avoid at all costs. How would I perform it with LINQ?

Upvotes: 2

Views: 932

Answers (1)

Gicu Mironica
Gicu Mironica

Reputation: 635

The solution comes from Postgis. It adds support for geographic objects allowing location queries to be run in SQL. It has to be installed on the DB side. For the server-side, the following library has to be installed.

The relevant method that can perform a geospatial query is called ".IsWithinDistance". The latitude, longitude have to be passed as a Point, and the radius. That will return all records within the specified range.

Example:

posts = await Context.Posts
        .Include(p => p.Location)                
        .Where(p => p.Location.Location.IsWithinDistance(location, radius * 1000) 
        .AsNoTracking().ToListAsync();

Upvotes: 2

Related Questions