Finding markets near me: geospatial search with SQL Server, EF Core and NetTopologySuite

On Où est le marché ?, my directory of open-air markets in France, the most common question from visitors fits in a few words: "which markets are near me?". With over 5,000 markets, scrolling through a list is not an option. You need real geospatial search: from a position, find the closest markets, sorted by distance.

Good news: SQL Server handles this very well, and so does EF Core, thanks to NetTopologySuite. Here's how I set it up, along with the few traps I fell into.

TL;DR

• Store your coordinates in a SQL Server geography column, mapped to a NetTopologySuite Point.
• Watch the order: a Point takes (longitude, latitude), not the other way around.
• Distances are in meters.
• Without a spatial index, every search scans the whole table.
• A single invalid coordinate can make the whole query fail: check them first.

Setup

The NuGet package to add to the project that holds your DbContext:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite

Then enable it in your EF Core configuration:

services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString, sql => sql.UseNetTopologySuite()));

In the entity, the position is just a Point:

using NetTopologySuite.Geometries;

public class Market
{
    public long Id { get; set; }
    public string Name { get; set; } = string.Empty;

    // SQL Server column of type geography
    public Point GpsCoordinates { get; set; } = null!;
}

EF Core generates a geography column: the type that computes distances on the surface of the Earth, in meters. (The other spatial type, geometry, works on a flat plane: perfect for a factory floor plan, not for a country.)

Creating a point: the order trap

The classic trap that everyone falls into once:

// ❌ Wrong: latitude first
var point = new Point(48.11, -1.68);

// ✅ Right: X = longitude, Y = latitude
var point = new Point(-1.68, 48.11) { SRID = 4326 };

A Point is a mathematical point: X first, then Y. And on a map, X is the longitude. Swap them and Rennes ends up somewhere in the Indian Ocean... with no error to warn you.

SRID 4326 is the GPS coordinate system (WGS 84). Without it, SQL Server doesn't know how to interpret your coordinates.

The query: the closest markets

Here's a simplified version of my method:

public async Task<List<ClosestMarketDto>> GetClosestMarkets(Point point, double maxDistanceKm, int nbResults = 30)
{
    point.SRID = 4326;
    var maxDistanceMeters = maxDistanceKm * 1000; // geography works in meters

    return await _dbContext.Markets
        .Where(m => m.Status == MarketStatus.Published)
        // The distance is computed once, then reused to filter and sort
        .Select(m => new { Market = m, Distance = m.GpsCoordinates.Distance(point) })
        .Where(x => x.Distance < maxDistanceMeters)
        .OrderBy(x => x.Distance)
        .Take(nbResults)
        .Select(x => new ClosestMarketDto
        {
            Id = x.Market.Id,
            Name = x.Market.Name,
            DistanceKm = x.Distance / 1000
        })
        .ToListAsync();
}

EF Core translates Distance() into STDistance() on the SQL Server side. Everything happens in the database: only the 30 relevant markets come back, already sorted.

A small detail that matters: the distance is computed once in a Select, then reused by Where and OrderBy. My first version called Distance() three times.

The spatial index: from "it works" to "it works fast"

Without an index, SQL Server computes the distance between your point and every market in the table, on every search. With 5,000 rows it's still fine. With more, or on shared hosting, you feel it.

EF Core has no API to create a spatial index, so it goes into a migration, as raw SQL:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        CREATE SPATIAL INDEX [SIDX_Markets_GpsCoordinates]
        ON [dbo].[Markets] ([GpsCoordinates])
        USING GEOGRAPHY_AUTO_GRID;
    ");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("DROP INDEX IF EXISTS [SIDX_Markets_GpsCoordinates] ON [dbo].[Markets];");
}

GEOGRAPHY_AUTO_GRID lets SQL Server tune the index on its own: no need to fiddle with grid levels.

The invalid coordinates trap

This one caused me a few errors in production. Some towns, especially in the French overseas territories, had coordinates corrupted during an import: latitude and longitude swapped, or values out of range. As a result, SQL Server refuses the computation with an "invalid geography instance" kind of error... and the whole query fails, not just the faulty row.

The fix: check the point before querying the database.

// A latitude is between -90 and 90, a longitude between -180 and 180
if (double.IsNaN(point.Y) || double.IsNaN(point.X) ||
    point.Y < -90 || point.Y > 90 || point.X < -180 || point.X > 180)
{
    return [];
}

And of course, fix the data at the source. But this safeguard keeps a single damaged row from crashing a page.

Bonus: caching the result

On a town page, the list of "nearby markets" hardly ever changes, yet it was recomputed on every visit, including for search engine bots. So I keep it in memory for a few hours, in a small, dedicated, size-limited cache (my shared hosting counts every megabyte). The spatial query now only runs once in a while per town.

To sum up

• geography + NetTopologySuite + UseNetTopologySuite(): three lines to do geography with EF Core.
• new Point(longitude, latitude) { SRID = 4326 }: in that order, always.
• Distances are in meters.
• A spatial index, added as SQL in a migration.
• Coordinates checked before the query.

Want to see the result? Look for markets near you on ouestlemarche.fr (in French) 😉

A question about the setup? Feel free to get in touch or leave a comment!