A Small GIS Lesson From a Database Migration

I was working on a backend feature involving a GeoJSON import containing geographic zones. The validation rule sounded simple: a newly uploaded zone could not share any area with another zone in the upload or with a zone already stored in the database.

The difficult part was not detecting an intersection. It was defining what counted as overlap.

The application used ASP.NET, Entity Framework Core, and Microsoft SQL Server. During development, the spatial work was mostly uneventful. EF Core let us express the checks close to the rest of the validation logic, while the database handled the actual spatial operation.

Intersecting is not the same as sharing area

Our first bug came from using ST_Intersects. The name looked right, but the predicate was stricter than our business rule: it returns true when two spatial values have any point in common. Two neighbouring polygons with only a shared boundary therefore intersect.

We changed the check to ST_Overlaps, which distinguishes a shared region from a shared edge. SQL Server provides that predicate for its geography type, and the change fixed the shared-border cases we were seeing.

It did not, however, encode the complete rule as written. In formal spatial terminology, ST_Overlaps also returns false when one polygon completely contains the other. The PostGIS definition requires both shapes to have some area outside the other. If containment is possible and the rule is "reject any shared area," then ST_Overlaps alone is not the right predicate.

A closer expression of the rule is:

area(intersection(zone_a, zone_b)) > tolerance

The tolerance needs a unit and a domain justification. It should not be an unexplained epsilon. The test cases should also include containment, equality, shared borders, tiny slivers, invalid polygons, and multipolygons. Spatial predicates are compact, but the semantics packed into them are not.

Then the database changed

Shortly before launch, the project moved from SQL Server to PostgreSQL with PostGIS. This exposed an assumption hidden behind the ORM: similarly named spatial features are not necessarily available for the same data types in different databases.

SQL Server and PostGIS both provide geometry and geography spatial types. At a high level, the distinction is similar in both systems:

• geometry treats coordinates in a Cartesian plane.
• geography models geodetic coordinates on the earth, making distance and area calculations over large extents more natural.

We had chosen geography in SQL Server because the zones represented real places expressed in earth coordinates, and we retained that choice during the migration. The problem was not that PostgreSQL lacked a comparable type. It was that the same type did not have the same function surface: SQL Server provides ST_Overlaps for geography, while PostGIS exposes ST_Overlaps(geometry, geometry) but no equivalent geography overload. ST_Overlaps is a strict dimensional relationship: it depends on the interiors and boundaries of both shapes, not merely whether they share a point. Evaluating those relationships/operations on a curved, geodetic earth model is substantially more complex than evaluating them in a Cartesian plane. This is part of a wider tradeoff: spherical and spheroidal calculations are more difficult and expensive, so PostGIS implements only a restricted subset of its spatial operations directly for geography.

The same EF Core expression that had looked pleasantly database-agnostic had reached the edge of that abstraction.

What Npgsql translates

When EF Core is configured with the Npgsql NetTopologySuite plugin, many NetTopologySuite methods in C# queries are translated into PostGIS functions and evaluated by PostgreSQL. For example:

var overlaps = await context.MyEntities
    .Where(e => e.Geometry.Overlaps(userGeometry))
    .ToListAsync()

The provider translates Overlaps to ST_Overlaps. It similarly translates Intersects to ST_Intersects. The first query is roughly equivalent to:

SELECT * FROM "MyEntities" WHERE ST_Overlaps("Geometry", @userGeometry)

No additional extension is needed beyond PostGIS itself. The convenience is real, but translation does not guarantee that the resulting function accepts the mapped database type. In PostGIS, ST_Intersects supports both geometry and geography, while ST_Overlaps supports geometry only.

The available compromises

One option was to store the zones as geometry. With a coordinate reference system appropriate to the region, this would provide the planar predicates we needed and allow a GiST spatial index to do useful work. This does not mean that geometry is inherently inaccurate. The choice of projection and the geographic extent decide whether its distortion is acceptable. Web Mercator is convenient for display, but it should not be the automatic choice for spatial analysis.

Another option was to retain geography and cast to geometry for the predicate. In simplified PostgreSQL, that looks like:

ST_Overlaps(geography_column::geometry, @user_area::geometry)

That restores the formal ST_Overlaps predicate, not the complete area-sharing rule: containment still returns false. It is useful only if formal overlap is actually the intended relationship.

It is also a cast, not a reprojection. The operation becomes planar in the coordinates already present, so a cast from WGS 84 geography does not create a projection suitable for area measurement. The cast changes the indexed column into a different expression as well. On a large table, that can prevent the normal geography index from serving as the main lookup path unless the query has a separate index-friendly prefilter or the database has a matching expression index.

The exact EF Core implementation is provider- and version-dependent. Npgsql documents translations for many NetTopologySuite operations, but not an EF.Functions.AsGeometry helper. A cast like this may therefore require mapped SQL, a custom database function, or a raw SQL query rather than a convenient built-in method.

A third option is to retain geography storage and express the business rule through ST_Intersection(geography, geography) and ST_Area(geography). A geography ST_Intersects prefilter can use the spatial index before the more expensive intersection-area calculation.

There is still a projection detail to understand. The geography overload of ST_Intersection selects a suitable planar coordinate system, computes the overlay there, and transforms the result back to WGS 84 geography. ST_Area then measures that geography result. This keeps the stored data and area measurement in the geography model, but the overlay itself is not a purely spheroidal operation. The chosen tolerance and adversarial tests still matter.

Which compromise is best depends on the data extent, query frequency, required accuracy, and the generated query plan. The ORM syntax is only the start of that decision.

Index the spatial column

For a non-trivial stored dataset, the spatial column will usually need a spatial index. With Npgsql, the index can be declared in the EF Core model so that the generated migration creates a PostgreSQL GiST index:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyEntity>()
        .HasIndex(e => e.Geometry)
        .HasMethod("gist")
}

The important detail is that the index must match the expression used by the query. A GiST index on Geometry can support the usual index-aware geometry predicates. If the query instead casts an indexed geography column to geometry, PostgreSQL may not use the ordinary index on the original column. In that case, an index-friendly geography prefilter or a matching expression index may be necessary. The generated SQL and EXPLAIN ANALYZE output are the useful checks. The presence of an index in the migration is not enough by itself.

What I learned

The main lesson was not that one database handled GIS better than another. It was that a spatial type is part of the domain model, not an implementation detail that can always be swapped beneath an ORM.

I would approach the same problem by writing the rule first in geometric terms: reject a pair when the area of their intersection exceeds an agreed tolerance in explicit units. Then I would build adversarial fixtures for touching, partial overlap, containment, equality, and narrow numerical slivers. Only after that would I select the predicate, storage type, projection, and index strategy.

I would also inspect the SQL and the query plan before treating an EF Core expression as finished. Casting can make a query compile while quietly changing both its model of the earth and its access path through the data.

The useful residue is modest: database portability ends where semantics and performance become database-specific. GIS reaches that boundary quickly.