Using PostGIS with Azure Database for PostgreSQL

With libelo we are building a platform for discovering parks and nature highlights. Almost all entities in our platform have a location attached: parks have boundaries, highlights like waterfalls or viewpoints have a precise coordinate, and users browse the app with their current position in hand. That means almost every interesting query the backend runs is spatial in some way. Which parks are nearby? Which park contains this highlight? What is the closest trail to where I am standing right now?

You could answer those questions by pulling coordinates out of the database and doing the math in application code. For a small dataset that works. The moment your dataset grows (people are adding new highlights every daily) and you want to filter and sort spatially at query time, you need the database to do that work. PostGIS is the extension that gives PostgreSQL exactly that capability, and it is available out of the box on Azure Database for PostgreSQL Flexible Server.

This post walks through how to enable it, how to wire it into a .NET application using Entity Framework Core and NetTopologySuite, and I'll use the auto-linking of highlights to parks as a concrete example.

What PostGIS gives you

PostGIS adds native geometry and geography column types to PostgreSQL, along with several hundred spatial functions that operate on them. The ones that matter most for an application like Libelo are:

ST_DWithin

Returns true if two geometries are within a given distance of each other. This is the workhorse for "find everything within X kilometers of this point."

SELECT name FROM parks
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(4.9041, 52.3676), 4326), 10000);
-- returns all parks within 10 km of Amsterdam city centre

ST_Distance

Calculates the exact distance between two geometries. You use this to order results by proximity.

SELECT name, ST_Distance(location, ST_SetSRID(ST_MakePoint(4.9041, 52.3676), 4326)) AS distance_m
FROM parks
ORDER BY distance_m;
-- returns all parks sorted by distance from a point, closest first

ST_Contains

Returns true if one geometry completely contains another. This is how you answer "is this highlight inside the boundaries of a given park?"

SELECT name FROM parks
WHERE ST_Contains(location::geometry, ST_SetSRID(ST_MakePoint(4.9180, 52.3542), 4326)::geometry);
-- returns the park whose boundary polygon contains the given coordinate

GiST indexes

A special index type that understands spatial data. Without a GiST index, every spatial query is a full table scan. Using such an index will make proximity queries on a table with millions of rows complete in milliseconds.

PostGIS also distinguishes between geometry and geography column types. Geometry works in a flat 2D plane and uses whatever units your coordinate system defines. Geography works on the surface of the Earth and always uses meters for distance calculations. For a platform such as libelo, where users are scattered across the globe, geography is the right choice because it stays accurate at large distances without requiring a projected coordinate system.

Every geometry or geography value in PostGIS is tagged with an SRID: a Spatial Reference ID that identifies which coordinate system the coordinates belong to. SRID 4326 refers to WGS 84, the coordinate system that GPS uses. It represents positions on the Earth's surface as latitude and longitude in decimal degrees, with the origin at the Greenwich meridian and the equator. When you store a point as (4.9041, 52.3676) with SRID 4326, PostGIS knows those numbers mean longitude 4.9041 and latitude 52.3676, and it can do correct Earth-surface calculations with them. Without the SRID tag, coordinates are just numbers and spatial functions have no frame of reference to work with. You will see 4326 appear in column type declarations, geometry constructors, and index definitions throughout this post. It is always doing the same job: telling PostGIS that the data is in the global GPS coordinate system.

Enabling PostGIS on Azure Database for PostgreSQL

On Azure Database for PostgreSQL Flexible Server, PostGIS is a pre-installed extension that you activate rather than install. Before you can use it, you need to allowlist it in the server configuration.

In the Azure Portal, navigate to your PostgreSQL Flexible Server, open the Server parameters blade, and search for azure.extensions. Add POSTGIS to the allowed list and save. This tells Azure that the extension is permitted to be loaded.

Once it is allowlisted, you enable it in your database with a single SQL statement:

CREATE EXTENSION IF NOT EXISTS postgis;

If you are using Flyway for migrations (as we do on Libelo), put this in your first migration file and it will run once on initial database setup:

-- V001__Enable_extensions.sql
CREATE EXTENSION IF NOT EXISTS postgis;

In case of using Entity Framework (which we do for now), you also declare the extension in your EF Core model so that tooling is aware of it:

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

Setting up the .NET packages

NetTopologySuite is the .NET library that represents spatial types like Point, Polygon, and MultiPolygon. The Npgsql EF Core provider has a plugin that bridges between NetTopologySuite and PostGIS, translating LINQ expressions like IsWithinDistance into the correct PostGIS SQL functions.

Add the following packages to your infrastructure project:

<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.0" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite" Version="10.0.0" />

Then enable the plugin when configuring the database connection:

options.UseNpgsql(connectionString, npgsql =>
    npgsql.UseNetTopologySuite());

That one call is what makes the translation layer work. Without it, EF Core has no idea that Point and MultiPolygon should map to PostGIS geography columns, and spatial LINQ methods will not translate to SQL.

Defining geometry columns

On Libelo, parks have boundaries and highlights have positions. These map to different geometry types.

A highlight is a single point: a specific waterfall, a peak, a cave entrance. The data entity stores it as a Point:

[Column("location", TypeName = "geography(Point, 4326)")]
public Point Location { get; set; } = null!;

A park is a boundary, and parks are not always simple single polygons. A national park can have non-contiguous sections separated by private land, or a coastal reserve can include several islands. See the screenshot of our app.
Using MultiPolygon handles that without needing special cases:

public MultiPolygon Location { get; set; } =
    new MultiPolygon(Array.Empty<Polygon>()) { SRID = 4326 };

The entity configurations register the column types and create GiST indexes:

// Park configuration
builder.Property(p => p.Location)
    .HasColumnType("geography(MultiPolygon, 4326)");

builder.HasIndex(p => p.Location)
    .HasMethod("gist");

// Highlight configuration
builder.Property(e => e.Location)
    .HasColumnType("geography(Point, 4326)")
    .IsRequired();

builder.HasIndex(e => e.Location)
    .HasMethod("gist");

The GiST index is not optional. PostGIS spatial queries can only use index-accelerated lookup when a GiST index exists on the column.

Auto-linking highlights to parks

When a user submits a new highlight through the app, they provide a coordinate. They do not define if the highlight is linked with a park. (we don't want users to do our data management, right?) The backend automatically determines which park that coordinate falls inside, and links the highlight to it. This is one of the places where PostGIS earns its place.

The containment check runs a PostGIS query that asks: does any park's boundary polygon contain this point?

public async Task<Park?> GetContainingPointAsync(
    Point location,
    CancellationToken cancellationToken = default)
{
    var entity = await context.Parks
        .AsNoTracking()
        .Where(p => p.DeletedAt == null)
        .Where(p => p.Location.IsWithinDistance(location, 0))
        .FirstOrDefaultAsync(cancellationToken);

    return entity == null ? null : MapToDomain(entity);
}

The IsWithinDistance(location, 0) call translates to ST_DWithin(location, @point, 0) in SQL. Using a distance of zero is the geography-safe way to test containment. PostGIS has a ST_Contains function, but it only works on the geometry type, not geography. Because our columns are geography for accurate Earth-surface distance calculations, ST_DWithin(..., 0) is the correct idiom: a point is "within zero meters" of a polygon only when it lies inside it.

In the service layer, this containment check plugs into the highlight creation flow. When a highlight is created without an explicit park ID, the service resolves the park automatically:

private async Task<Guid?> ResolveParkIdAsync(
    Guid? parkId,
    Location location,
    CancellationToken cancellationToken)
{
    if (parkId.HasValue)
        return parkId;

    var containingPark = await parkService.FindParkContainingLocationAsync(
        location.Latitude, location.Longitude, cancellationToken);

    if (containingPark == null)
        return null;

    logger.LogInformation(
        "Auto-linked highlight at ({Lat}, {Lon}) to park {ParkId} ({ParkName})",
        location.Latitude, location.Longitude,
        containingPark.Id, containingPark.Name);

    return containingPark.Id;
}

The result is that users never have to think about park assignment. They drop a pin on a waterfall inside a park, and the link is created in the database automatically. If the coordinate falls outside any park boundary (a highlight on a public trail that is not inside a mapped park, for example), the highlight is simply created without a park association.

Proximity queries

The other core use case is finding things near a location. When the app loads, it sends the user's current coordinates to the backend, and the API returns parks and highlights within a configurable radius, ordered by distance.

The query uses IsWithinDistance for the filter and Distance for the ordering:

var query = context.Parks
    .AsNoTracking()
    .Where(p => p.DeletedAt == null)
    .Where(p => p.Status == published)
    .Where(p => p.Location.IsWithinDistance(queryPoint, radiusMeters))
    .OrderBy(p => p.Location.Distance(queryPoint));

IsWithinDistance translates to ST_DWithin, which uses the GiST index and returns only rows within the specified radius before ordering. Without that filter, ORDER BY ST_Distance would scan the entire table to compute distances. The two-step pattern, filter with ST_DWithin then sort with ST_Distance, is the standard way to write efficient proximity queries in PostGIS.

The query point is constructed from the user's latitude and longitude with an explicit SRID:

var geometryFactory = new GeometryFactory(new PrecisionModel(), 4326);
var queryPoint = geometryFactory.CreatePoint(new Coordinate(longitude, latitude));

Note that NetTopologySuite follows the GIS convention of (x, y) which means (longitude, latitude), not (latitude, longitude). This is a common source of bugs when first working with the library.

What the database sees

If you connect to the database directly and inspect what is happening, the EF Core queries translate cleanly into PostGIS SQL. A nearby parks query looks like this:

SELECT p.*
FROM parks p
WHERE p.deleted_at IS NULL
  AND p.status = 'Published'
  AND ST_DWithin(p.location, ST_SetSRID(ST_MakePoint($1, $2), 4326), $3)
ORDER BY ST_Distance(p.location, ST_SetSRID(ST_MakePoint($1, $2), 4326));

And the containment check for auto-linking becomes:

SELECT p.*
FROM parks p
WHERE p.deleted_at IS NULL
  AND ST_DWithin(p.location, ST_SetSRID(ST_MakePoint($1, $2), 4326), 0)
LIMIT 1;

These are exactly the queries you would write by hand. The NetTopologySuite translation layer does not add overhead or generate surprising SQL.

Keeping it simple

The setup has a few moving pieces: enabling the extension on Azure, adding two NuGet packages, one line in the DbContext configuration, and geography column types with GiST indexes in the entity configurations. Beyond that, spatial queries in EF Core look like any other LINQ query. You do not need to drop down to raw SQL or manage spatial types manually.

The main thing to be aware of is the geography versus geometry distinction. Geography columns use spherical calculations and always measure distance in meters, which is what you want for a mapping application. If you use geometry columns instead, you lose that accuracy and distance calculations become coordinate-system-dependent. Stick with geography and SRID 4326 and the math stays correct regardless of where in the world your data is.