Geospatial Support

Velocity provides comprehensive geospatial capabilities for working with spatial data across multiple database platforms. Built on top of NetTopologySuite, it offers a consistent, cross-platform API for spatial operations while intelligently leveraging each database's native geospatial features.

Overview

Velocity's geospatial support includes:

Two spatial data types: Geometry (from NetTopologySuite) and Geography
Transparent SRID management: Configure default SRID once; it's automatically applied to all Geography operations
Comprehensive spatial functions: Over 50 spatial operations including measurements, relationships, and transformations
Units of measure: Support for metric and imperial units in distance calculations
Cross-platform consistency: Unified API across all supported databases

Geometry vs Geography

Understanding the difference between Geometry (from NetTopologySuite) and Geography is crucial for choosing the right spatial data type for your application.

Geometry Type

Geometry uses a flat, two-dimensional Cartesian coordinate system. It treats the Earth as a flat plane, which is ideal for:

Small-scale mapping (city blocks, building layouts, local areas)
Engineering and CAD applications
Abstract spatial data not representing Earth's surface
When precision requirements don't account for Earth's curvature

using YndigoBlue.Velocity.Utils;

// Create a point using Geometry
var point = GeospatialUtils.GeometryFromText("POINT(-100 40)");

// Create a polygon using Geometry
var polygon = GeospatialUtils.GeometryFromText(
    "POLYGON((-100 40, -100 44, -96 44, -96 40, -100 40))"
);

Characteristics:

Uses planar (flat) mathematics
Faster calculations
Typically uses SRID 0 (no specific coordinate system)
Measurements are in coordinate units (not real-world distances)
Best for abstract or small-scale spatial data

Geography Type

Geography uses an ellipsoidal (spherical) coordinate system that accounts for Earth's curvature. It's ideal for:

Large-scale mapping (countries, continents, global data)
GPS coordinates and navigation
Real-world distance and area calculations
Any application requiring accurate Earth-surface measurements

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Utils;

// Create a point using Geography (WGS 84 coordinates)
var point = GeospatialUtils.GeographyFromText("POINT(-118.2437 34.0522)");

// Create a polygon representing a real area
var polygon = GeospatialUtils.GeographyFromText(
    "POLYGON((-118.5 33.7, -118.5 34.3, -117.9 34.3, -117.9 33.7, -118.5 33.7))"
);

Characteristics:

Uses ellipsoidal/spherical mathematics
More computationally expensive
Typically uses SRID 4326 (WGS 84 - GPS coordinates)
Measurements return real-world distances in meters
Best for Earth-surface spatial data

Decision Matrix

Factor	Geometry	Geography
Scale	Local/small areas	Regional/global
Accuracy	Approximation	Earth-curvature aware
Performance	Faster	Slower
Use Case	CAD, floor plans, abstract	GPS, navigation, real-world
Default SRID	0 (none)	4326 (WGS 84)
Distance Units	Coordinate units	Real-world (meters, km)

SRID (Spatial Reference System Identifier)

SRID defines the coordinate system used for spatial data. Velocity provides transparent SRID management - you configure it once in your connection, and it's automatically applied to all Geography operations without any manual intervention.

Configuring Default SRID

Set the default SRID for all Geography operations in your connection context:

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "user",
    Password = "password",
    Database = "spatialdb",
    DefaultSrid = 4326
};

Common SRIDs

SRID	Name	Description	Use With
0	None	Planar/Cartesian coordinates	Geometry
4326	WGS 84	World Geodetic System 1984 (GPS)	Geography
3857	Web Mercator	Used by Google Maps, OpenStreetMap	Web mapping
1619	Teradata Specific	Teradata's geography SRID	Geography (Teradata)

How SRID is Applied

Geography objects are created without specifying SRID. Velocity automatically applies the DefaultSrid from your connection when Geography data is written to the database or used in queries:

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Utils;

// Create Geography without specifying SRID
var location = GeospatialUtils.GeographyFromText("POINT(-118.2437 34.0522)");

// Or from an existing Geometry
var geometry = GeospatialUtils.GeometryFromText("POINT(-118.2437 34.0522)");
var geography = new Geography(geometry);

// DefaultSrid (4326) is automatically applied when:
// - Inserting records into the database
// - Creating query parameters
// - Performing spatial operations
record.SetFieldGeography("Location", location);
manager.AddRecord(record);  // SRID automatically applied here

Note: You cannot manually set SRID when creating Geography objects. This ensures consistent SRID usage throughout your application based on your connection configuration.

Retrieving SRID from Spatial Data

You can query the SRID of stored spatial data:

var query = new Query()
    .Select(new Expression("SRID", new Function(FunctionType.SpatialSrid, spatialColumn)))
    .From(table);

Units of Measure

Velocity supports multiple units of measure for geographic calculations, allowing you to work in your preferred unit system using the UnitOfMeasure enumeration.

Supported Units

Unit	Abbreviation	System	Common Use
Millimeter	mm	Metric	Precision engineering
Centimeter	cm	Metric	Small measurements
Meter	m	Metric	Standard metric (default)
Kilometer	km	Metric	Large distances
Inch	in	Imperial	Engineering (US)
Foot	ft	Imperial	Construction (US)
Yard	yd	Imperial	Land measurement
Mile	mi	Imperial	Road distances

Using Units of Measure

Units of measure apply to Geography operations only. Geometry operations return values in coordinate units.

Distance Calculation

using YndigoBlue.Velocity.Enums;
using YndigoBlue.Velocity.Functions;

// Calculate distance in meters (default)
var query = new Query()
    .Select(new Expression("DistanceMeters",
        new SpatialDistance(locationColumn, targetPoint, UnitOfMeasure.Meter)))
    .From(table);

// Calculate distance in kilometers
var queryKm = new Query()
    .Select(new Expression("DistanceKm",
        new SpatialDistance(locationColumn, targetPoint, UnitOfMeasure.Kilometer)))
    .From(table);

// Calculate distance in miles
var queryMiles = new Query()
    .Select(new Expression("DistanceMiles",
        new SpatialDistance(locationColumn, targetPoint, UnitOfMeasure.Mile)))
    .From(table);

Area Calculation

// Calculate area in square meters
var query = new Query()
    .Select(new Expression("AreaSqMeters",
        new SpatialArea(polygonColumn, UnitOfMeasure.Meter)))
    .From(table);

// Calculate area in square kilometers
var queryKm = new Query()
    .Select(new Expression("AreaSqKm",
        new SpatialArea(polygonColumn, UnitOfMeasure.Kilometer)))
    .From(table);

Length Calculation

// Calculate length in feet
var query = new Query()
    .Select(new Expression("LengthFeet",
        new SpatialLength(linestringColumn, UnitOfMeasure.Foot)))
    .From(table);

// Calculate length in yards
var queryYards = new Query()
    .Select(new Expression("LengthYards",
        new SpatialLength(linestringColumn, UnitOfMeasure.Yard)))
    .From(table);

Buffer Operations

// Create a 500-meter buffer
var query = new Query()
    .Select(new Expression("Buffer500m",
        new SpatialBuffer(locationColumn, 500F, UnitOfMeasure.Meter)))
    .From(table);

// Create a 1-mile buffer
var queryMile = new Query()
    .Select(new Expression("Buffer1Mile",
        new SpatialBuffer(locationColumn, 1F, UnitOfMeasure.Mile)))
    .From(table);

Unit Conversion Example

// Calculate the same distance in multiple units
var query = new Query()
    .Select([
        nameColumn,
        new Expression("Meters",
            new SpatialDistance(locationColumn, targetPoint, UnitOfMeasure.Meter)),
        new Expression("Kilometers",
            new SpatialDistance(locationColumn, targetPoint, UnitOfMeasure.Kilometer)),
        new Expression("Feet",
            new SpatialDistance(locationColumn, targetPoint, UnitOfMeasure.Foot)),
        new Expression("Miles",
            new SpatialDistance(locationColumn, targetPoint, UnitOfMeasure.Mile))
    ])
    .From(citiesTable);

var results = manager.Retrieve(query);
foreach (var record in results)
{
    Console.WriteLine($"{record.GetFieldString("Name")}:");
    Console.WriteLine($"  {record.GetFieldDouble("Meters"):N2} meters");
    Console.WriteLine($"  {record.GetFieldDouble("Kilometers"):N2} kilometers");
    Console.WriteLine($"  {record.GetFieldDouble("Feet"):N2} feet");
    Console.WriteLine($"  {record.GetFieldDouble("Miles"):N2} miles");
}

Database Support Matrix

While Velocity aims to provide cross-platform geospatial support, each database vendor implements spatial functions differently. The following tables show which geospatial functions are fully supported on each platform.

Legend

✅ Fully Supported: Function works as expected
❌ Not Supported: Function not available on this database
⚠️ Partial Support: Function works with limitations

Geometry Functions

Function	DB2	MySQL	Oracle	PostgreSQL	SQL Server	SQLite	Teradata
Area	✅	✅	✅	✅	✅	✅	✅
Boundary	✅	❌	✅	✅	✅	✅	✅
Buffer	✅	✅	✅	✅	✅	✅	✅
Centroid	✅	✅	✅	✅	✅	✅	✅
ConcaveHull	❌	❌	✅	✅	❌	✅	❌
Contains	✅	✅	✅	✅	✅	✅	✅
ConvexHull	✅	✅	✅	✅	✅	✅	✅
Covers	❌	❌	❌	✅	❌	✅	❌
Crosses	✅	✅	✅	✅	✅	✅	✅
Difference	✅	✅	✅	✅	✅	✅	✅
Dimension	✅	✅	✅	✅	✅	✅	✅
Disjoint	✅	✅	✅	✅	✅	✅	✅
Distance	✅	✅	✅	✅	✅	✅	✅
EndPoint	✅	✅	✅	✅	✅	✅	✅
Envelope	✅	✅	✅	✅	✅	✅	✅
Equals	✅	✅	✅	✅	✅	✅	✅
ExteriorRing	✅	✅	✅	✅	✅	✅	✅
GeometryType	✅	✅	✅	✅	✅	✅	✅
InteriorRingN	❌	✅	✅	✅	✅	✅	✅
Intersection	✅	✅	✅	✅	✅	✅	✅
Intersects	✅	✅	✅	✅	✅	✅	✅
IsClosed	✅	✅	✅	✅	✅	✅	✅
IsRing	✅	❌	✅	✅	✅	✅	✅
IsSimple	✅	✅	✅	✅	✅	✅	✅
IsValid	❌	✅	✅	✅	✅	✅	✅
Length	✅	✅	✅	✅	✅	✅	✅
NumGeometries	✅	✅	✅	✅	✅	✅	✅
NumInteriorRing	✅	✅	✅	✅	✅	✅	✅
NumPoints	✅	✅	✅	✅	✅	✅	✅
Overlaps	✅	✅	✅	✅	✅	✅	✅
PointN	✅	✅	✅	✅	✅	✅	✅
PointOnSurface	✅	❌	✅	✅	✅	✅	✅
Relate	✅	❌	✅	✅	✅	✅	✅
SRID	✅	✅	✅	✅	✅	✅	✅
StartPoint	✅	✅	✅	✅	✅	✅	✅
SymDifference	✅	✅	✅	✅	✅	✅	✅
Touches	✅	✅	✅	✅	✅	✅	✅
Union	✅	✅	✅	✅	✅	✅	✅
Within	✅	✅	✅	✅	✅	✅	✅
X	✅	✅	✅	✅	✅	✅	✅
Y	✅	✅	✅	✅	✅	✅	✅

Geography Functions

Function	DB2	MySQL	Oracle	PostgreSQL	SQL Server	SQLite	Teradata
Area	✅	✅	✅	✅	✅	✅	✅
Boundary	✅	❌	✅	❌	❌	✅	✅
Buffer	✅	✅	✅	✅	✅	✅	✅
Centroid	✅	❌	✅	✅	❌	✅	✅
ConcaveHull	❌	❌	✅	❌	❌	✅	❌
Contains	✅	✅	✅	❌	✅	✅	✅
ConvexHull	✅	❌	✅	❌	✅	✅	✅
Covers	❌	❌	❌	✅	❌	✅	❌
Crosses	✅	✅	✅	❌	❌	✅	✅
Difference	✅	✅	✅	❌	✅	✅	✅
Dimension	✅	✅	✅	❌	✅	✅	✅
Disjoint	✅	✅	✅	❌	✅	✅	✅
Distance	✅	✅	✅	✅	✅	✅	✅
EndPoint	✅	✅	✅	❌	✅	✅	✅
Envelope	✅	❌	✅	❌	❌	✅	✅
Equals	✅	✅	✅	❌	✅	✅	✅
ExteriorRing	✅	✅	✅	❌	❌	✅	✅
GeographyType	✅	✅	✅	❌	✅	✅	✅
InteriorRingN	❌	✅	✅	❌	❌	✅	✅
Intersection	✅	✅	✅	✅	✅	✅	✅
Intersects	✅	✅	✅	✅	✅	✅	✅
IsClosed	✅	❌	✅	❌	✅	✅	✅
IsRing	✅	❌	✅	❌	❌	✅	✅
IsSimple	✅	✅	✅	❌	❌	✅	✅
IsValid	❌	✅	✅	❌	✅	✅	✅
Length	✅	✅	✅	✅	✅	✅	✅
NumGeometries	✅	✅	✅	❌	✅	✅	✅
NumInteriorRing	✅	✅	✅	❌	❌	✅	✅
NumPoints	✅	✅	✅	❌	✅	✅	✅
Overlaps	✅	✅	✅	❌	✅	✅	✅
PointN	✅	✅	✅	❌	✅	✅	✅
PointOnSurface	✅	❌	✅	❌	❌	✅	✅
Relate	✅	❌	✅	❌	❌	✅	✅
SRID	✅	✅	✅	✅	✅	✅	✅
StartPoint	✅	✅	✅	❌	✅	✅	✅
SymDifference	✅	✅	✅	❌	✅	✅	✅
Touches	✅	✅	✅	❌	❌	✅	✅
Union	✅	✅	✅	❌	✅	✅	✅
Within	✅	✅	✅	❌	✅	✅	✅
X	✅	✅	✅	❌	❌	✅	✅
Y	✅	✅	✅	❌	❌	✅	✅

Key Observations

PostgreSQL Geography: PostgreSQL uses the PostGIS extension which has a different implementation model for Geography types. While PostgreSQL excels at Geometry operations through PostGIS, the Geography type in Velocity is not fully supported on this platform. Use Geometry types with appropriate SRID for PostgreSQL spatial operations.

SQLite Excellence: SQLite through SpatiaLite provides the most comprehensive support for both Geometry and Geography functions, making it an excellent choice for embedded spatial applications.

MySQL Limitations: MySQL has notable gaps in Geography support, particularly for advanced topological operations. Consider using Geometry types or alternative databases for complex geographic applications.

Vendor-Specific Calculation Differences

Important: While Velocity provides a unified API, each database vendor implements spatial calculations using their own algorithms and mathematical models. This can lead to slight variations in results, particularly for complex operations.

Distance and Area Calculations

Different databases use different ellipsoid models and calculation methods:

// Example: Distance calculation may vary slightly between databases
var point1 = GeospatialUtils.GeographyFromText("POINT(-118.2437 34.0522)");  // Los Angeles
var point2 = GeospatialUtils.GeographyFromText("POINT(-73.9352 40.7306)");    // New York

var query = new Query()
    .Select(new Expression("DistanceKm",
        new SpatialDistance(point1Column, point2, UnitOfMeasure.Kilometer)))
    .From(table);

// Results from different databases (approximate):
// SQL Server:  3,944.42 km
// PostgreSQL:  3,944.41 km
// Oracle:      3,944.43 km
// Teradata:    3,944.40 km

Expected Variance

Operation	Typical Variance	Reason
Short distances (< 100 km)	< 0.01%	Minimal impact from ellipsoid choice
Medium distances (100-1000 km)	< 0.1%	Slight ellipsoid differences
Long distances (> 1000 km)	< 0.5%	Different geodesic algorithms
Area calculations	< 1%	Projection and ellipsoid variations
Buffer operations	Visual differences	Different algorithms, especially at poles

Best Practices

Consistency: Use the same database for all spatial calculations in a given application
Tolerance: Build tolerance into distance/area comparisons (e.g., ±0.1%)
Testing: Test spatial operations on your target database platform
Documentation: Document which database platform was used for critical calculations

Example with Tolerance

// Comparing distances with tolerance
double distance1 = result1.GetFieldDouble("Distance");
double distance2 = result2.GetFieldDouble("Distance");
double tolerance = 0.001; // 0.1% tolerance

bool areDistancesEqual = Math.Abs(distance1 - distance2) / distance1 < tolerance;

Complete Examples

Finding Nearby Locations

using YndigoBlue.Velocity;
using YndigoBlue.Velocity.Enums;
using YndigoBlue.Velocity.Functions;
using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Utils;

// Find all cities within 50km of a point
var targetLocation = GeospatialUtils.GeographyFromText("POINT(-118.2437 34.0522)");

var query = new Query()
    .Select(cityNameColumn)
    .Select(new Expression("DistanceKm",
        new SpatialDistance(locationColumn, targetLocation, UnitOfMeasure.Kilometer)))
    .From(citiesTable)
    .Where(new Criterion<bool>(
        new Expression(new SpatialDistance(locationColumn, targetLocation, UnitOfMeasure.Kilometer)),
        ConditionalType.LessThan,
        50.0))
    .OrderBy(new Expression(new SpatialDistance(locationColumn, targetLocation, UnitOfMeasure.Kilometer)));

using var manager = new Manager(connection);
var results = manager.Retrieve(query);

Spatial Containment

// Find all points within a polygon
var searchArea = GeospatialUtils.GeographyFromText(
    "POLYGON((-118.5 33.7, -118.5 34.3, -117.9 34.3, -117.9 33.7, -118.5 33.7))"
);

var query = new Query()
    .Select(nameColumn, locationColumn)
    .From(pointsTable)
    .Where(new Criterion<bool>(
        new Expression(new SpatialContains(searchArea, locationColumn)),
        true));

var results = manager.Retrieve(query);

Buffer Analysis

// Create 1km buffer zones around locations
var query = new Query()
    .Select(nameColumn)
    .Select(new Expression("BufferZone",
        new SpatialBuffer(locationColumn, 1000F, UnitOfMeasure.Meter)))
    .From(facilitiesTable);

var results = manager.Retrieve(query);

// Store buffered geometries
foreach (var record in results)
{
    var bufferGeometry = record.GetFieldGeography("BufferZone");
    // Use bufferGeometry for further analysis
}

Intersection Analysis

// Find overlapping regions
var referencePolygon = GeospatialUtils.GeometryFromText(
    "POLYGON((-100 40, -100 44, -96 44, -96 40, -100 40))"
);

var query = new Query()
    .Select(nameColumn, polygonColumn)
    .Select(new Expression("OverlapArea",
        new SpatialIntersection(polygonColumn, new Literal<Geometry>(referencePolygon))))
    .From(regionsTable)
    .Where(new Criterion<bool>(
        new Expression(new SpatialIntersects(new Literal<Geometry>(referencePolygon), polygonColumn)),
        true));

var results = manager.Retrieve(query);

Geospatial Joins

Geospatial joins allow you to combine data from multiple tables based on spatial relationships rather than simple equality. Unlike traditional attribute-based joins, spatial joins determine relationships through geometric predicates like intersects, contains, or distance thresholds. This is essential for analyzing relationships between different spatial datasets, such as finding which cities fall within specific counties, which delivery routes intersect service areas, or which properties are within a certain distance of parks.

Velocity implements spatial joins using the Join class in combination with the From() method and strongly-typed spatial function classes that support column-to-column comparisons.

Basic Spatial Join Pattern

All spatial joins in Velocity follow this pattern:

using YndigoBlue.Velocity.Functions;
using YndigoBlue.Velocity.Model;

// Create a Join object with two tables and a spatial predicate
var join = new Join(
    leftTable,
    rightTable,
    new SpatialFunction(leftTableSpatialColumn, rightTableSpatialColumn));

// Use the join in a query
var query = new Query()
    .Select(columns...)
    .From(join)
    .OrderBy(...);

using var manager = new Manager(connection);
var results = manager.Retrieve(query);

The Join constructor takes three parameters:

Left table: The first table in the join
Right table: The second table in the join
Spatial predicate: A strongly-typed spatial function (e.g., SpatialIntersects, SpatialContains) that accepts two columns

SpatialContains Join

Tests whether geometries from the left table completely contain geometries from the right table. The contained geometry must be entirely within the containing geometry (boundaries may touch).

SpatialCovers Join

Tests whether geometries from the left table completely cover geometries from the right table. Similar to SpatialContains, but the covered geometry's boundary can extend beyond the covering geometry's boundary.

SpatialCrosses Join

Tests whether geometries from two tables cross each other. Typically used for line/polygon or line/line intersections where the geometries share some but not all interior points.

SpatialDisjoint Join

Tests whether geometries from two tables do not share any common space. Useful for finding non-overlapping or completely separate features.

SpatialEquals Join

Tests whether geometries from two tables are spatially equal (same shape and location, regardless of vertex order or representation).

SpatialIntersects Join

The most common spatial join tests whether geometries from two tables intersect (share any common space). This is useful for finding overlaps, points within polygons, or lines crossing areas.

SpatialOverlaps Join

Tests whether geometries from two tables overlap. The geometries must have the same dimension, share some but not all interior points, and neither can completely contain the other.

SpatialTouches Join

Tests whether geometries from two tables touch at their boundaries but do not overlap interiors. The geometries must share at least one boundary point but no interior points.

SpatialWithin Join

The inverse of SpatialContains. Tests whether geometries from the left table are completely within geometries from the right table.

Distance-Based Spatial Queries

While not a traditional join predicate, you can combine joins with distance calculations using SpatialDistance:

// Find all stores within 5km of distribution centers
var storesTable = schema.GetTable("stores");
var centersTable = schema.GetTable("distribution_centers");

var storeLocationCol = new Column(storesTable, "location");
var centerLocationCol = new Column(centersTable, "location");

// Use a cross join and filter by distance in the query
var query = new Query()
    .Select(
        new Column(storesTable, "store_name"),
        new Column(centersTable, "center_name"))
    .Select(new Expression("DistanceKm",
        new SpatialDistance(storeLocationCol, centerLocationCol, UnitOfMeasure.Kilometer)))
    .From(storesTable)
    .From(centersTable)
    .Where(new Criterion<bool>(
        new Expression(new SpatialDistance(storeLocationCol, centerLocationCol, UnitOfMeasure.Kilometer)),
        ConditionalType.LessThanOrEqualTo,
        5.0))
    .OrderBy(new Expression(new SpatialDistance(storeLocationCol, centerLocationCol, UnitOfMeasure.Kilometer)));

using var manager = new Manager(connection);
var results = manager.Retrieve(query);

Multi-Table Spatial Joins

The Join class supports joining multiple tables with different spatial predicates:

// Find regions that intersect with both locations and routes
var regionsTable = schema.GetTable("regions");
var locationsTable = schema.GetTable("locations");
var routesTable = schema.GetTable("routes");

var regionDataCol = new Column(regionsTable, "geometry");
var locationDataCol = new Column(locationsTable, "geometry");
var routeDataCol = new Column(routesTable, "geometry");

var query = new Query()
    .Select(
        new Column(regionsTable, "region_name"),
        new Column(locationsTable, "location_name"),
        new Column(routesTable, "route_name"))
    .From(new Join([
        (regionsTable, locationsTable, new SpatialIntersects(regionDataCol, locationDataCol)),
        (regionsTable, routesTable, new SpatialIntersects(regionDataCol, routeDataCol))
    ]))
    .OrderBy(new Column(regionsTable, "region_name"));

var results = manager.Retrieve(query);

Combining Spatial Functions in Joins

Spatial functions can be chained together to create complex join conditions:

// Find parcels that intersect with the intersection of two zones
var parcelsTable = schema.GetTable("parcels");
var zone1Table = schema.GetTable("zone1");
var zone2Table = schema.GetTable("zone2");

var parcelGeometryCol = new Column(parcelsTable, "geometry");
var zone1GeometryCol = new Column(zone1Table, "geometry");
var zone2GeometryCol = new Column(zone2Table, "geometry");

// First compute the intersection of the two zones
var zoneIntersection = new SpatialIntersection(zone1GeometryCol, zone2GeometryCol);

// Then create a join using the intersection
var query = new Query()
    .Select(new Column(parcelsTable, "parcel_id"))
    .From(parcelsTable)
    .From(zone1Table)
    .From(zone2Table)
    .Where(new Criterion<bool>(
        new Expression(new SpatialIntersects(parcelGeometryCol, zoneIntersection)),
        true));

var results = manager.Retrieve(query);

Performance Optimization for Spatial Joins

Spatial joins can be computationally expensive. Follow these best practices for optimal performance:

Always Create Spatial Indexes: Ensure all spatial columns involved in joins have spatial indexes defined:

table.CreateSpatialIndex(
    "idx_geometry",
    "geometry_column",
    xmin: -180.0, ymin: -90.0,
    xmax: 180.0, ymax: 90.0,
    tolerance: 0.1,
    gridSize1: 1.0,
    gridSize2: 5.0);

Filter Early with Non-Spatial Criteria: Apply attribute filters before spatial predicates:

var query = new Query()
    .Select(countyNameCol, cityNameCol)
    .From(join)
    .Where(new Criterion<string>(new Column(citiesTable, "type"), ConditionalType.EqualTo, "urban"))
    .Where(new Criterion<int>(new Column(countiesTable, "population"), ConditionalType.GreaterThan, 100000));

Use Bounding Box Pre-Filters: For complex geometries, filter by envelope first:

// Pre-filter with envelope intersection before detailed geometry check
var query = new Query()
    .Select(parcelIdCol, zoneNameCol)
    .From(parcelsTable)
    .From(zonesTable)
    .Where(new Criterion<bool>(
        new Expression(new SpatialIntersects(
            new SpatialEnvelope(parcelGeometryCol),
            new SpatialEnvelope(zoneGeometryCol))),
        true))
    .Where(new Criterion<bool>(
        new Expression(new SpatialIntersects(parcelGeometryCol, zoneGeometryCol)),
        true));

Consider Table Order: Place the smaller table first in the join when possible.
Limit Result Sets: Use appropriate WHERE clauses to avoid massive cross products.

Geospatial Join Database Support

The following tables show which geospatial joins are supported on each database platform. Support is determined by the absence of ignored tests in the Velocity test suite.

Geometry Join Support

Join Type	DB2	MySQL	Oracle	PostgreSQL	SQLite	SQL Server	Teradata
SpatialContains	✅	✅	✅	✅	✅	✅	✅
SpatialCovers	❌	❌	❌	✅	✅	❌	❌
SpatialCrosses	✅	✅	✅	✅	✅	✅	✅
SpatialDisjoint	✅	✅	✅	✅	✅	✅	✅
SpatialEquals	✅	✅	✅	✅	✅	✅	✅
SpatialIntersects	✅	✅	✅	✅	✅	✅	✅
SpatialOverlaps	✅	✅	✅	✅	✅	✅	✅
SpatialTouches	✅	✅	✅	✅	✅	✅	✅
SpatialWithin	✅	✅	✅	✅	✅	✅	✅

Geography Join Support

Join Type	DB2	MySQL	Oracle	PostgreSQL	SQLite	SQL Server	Teradata
SpatialContains	✅	✅	✅	❌	✅	✅	✅
SpatialCovers	❌	❌	❌	✅	✅	❌	❌
SpatialCrosses	✅	✅	✅	❌	✅	❌	✅
SpatialDisjoint	✅	✅	✅	❌	✅	✅	✅
SpatialEquals	✅	✅	✅	❌	✅	✅	✅
SpatialIntersects	✅	✅	✅	✅	✅	✅	✅
SpatialOverlaps	✅	✅	✅	❌	✅	✅	✅
SpatialTouches	✅	✅	✅	❌	✅	❌	✅
SpatialWithin	✅	✅	✅	❌	✅	✅	✅

Legend:

✅ Fully Supported: Join operation works correctly on this database
❌ Not Supported: Join operation not available or not tested on this database

Key Observations:

SQLite provides the most comprehensive support, with all join types supported for both Geometry and Geography
PostgreSQL has excellent Geometry support but limited Geography join support (only SpatialIntersects and SpatialCovers work)
SQL Server does not support SpatialTouches or SpatialCrosses joins for Geography types
SpatialCovers join is only supported on PostgreSQL and SQLite for both data types
All databases fully support SpatialIntersects joins for both Geometry and Geography, making it the most portable join type

Performance Considerations

Spatial Indexes

Always create spatial indexes on geometry/geography columns for optimal performance:

// Create a spatial index when defining a table
table.CreateSpatialIndex(
    "idx_location",          // Index name
    "location_column",       // Column name
    xmin: -180.0,           // Minimum X coordinate
    ymin: -90.0,            // Minimum Y coordinate
    xmax: 180.0,            // Maximum X coordinate
    ymax: 90.0,             // Maximum Y coordinate
    tolerance: 0.1,         // Tolerance
    gridSize1: 1.0,         // Grid size level 1
    gridSize2: 5.0          // Grid size level 2
);

Query Optimization

Use bounding box filters before expensive spatial operations
Limit result sets with distance thresholds
Choose appropriate data types (Geometry for local, Geography for global)
Cache frequently used geometries

Conclusion

Velocity's geospatial support provides a powerful, cross-platform solution for spatial data management. By understanding the differences between Geometry (from NetTopologySuite) and Geography, configuring DefaultSrid in your connection, and choosing appropriate units of measure, you can build robust spatial applications that work consistently across all supported database platforms.

Key Takeaways:

SRID is transparent: Configure once in your connection context; Velocity handles the rest
No manual SRID management: Geography objects are created without SRID parameters, ensuring consistency
Database-agnostic API: Write code once, run on any supported database
Vendor differences exist: Test on your target platform and use appropriate tolerances

Remember that while Velocity provides a unified API, vendor-specific implementation differences mean you should always test spatial operations on your target database platform and build appropriate tolerance into distance and area comparisons.

For more information on specific spatial functions and operations, refer to the API Reference.

Table of Contents