Importing and Exporting GeoJSON

GeoJSON is the format most spatial data arrives in. Government open data portals publish it, GIS packages export it, and every mapping library that runs in a browser consumes it directly. A file might hold administrative boundaries, the depots belonging to a delivery fleet, or every public charging station in a country, and in each case the file itself is unremarkable: readable text, a documented structure, and a specification short enough to read in a single sitting.

The work appears when that file has to become rows in a relational database. The format and a table disagree about structure in several specific ways, and the specification does not resolve those disagreements because resolving them is not its purpose. Whoever writes the import decides where the feature identifier belongs, how nested properties become columns, what a missing value means, and how the process behaves when the file is larger than the memory available to it.

This article covers both directions, import and export, with working code for each.

What is GeoJSON, and why is storing it awkward?

The specification defines a small number of structures. Every Feature carries a geometry, an optional id, and a properties object, and a FeatureCollection is an array of features. Seven geometry types are permitted: Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon and GeometryCollection.

A short example, which the rest of this article refers back to:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "id": 4471,
      "geometry": { "type": "Point", "coordinates": [-118.3004, 34.1184] },
      "properties": {
        "location_name": "Griffith Observatory",
        "opened": "1935-05-14",
        "sensors": 12,
        "active": true,
        "contact_info": { "manager": "A. Chen", "phone": "+1-213-473-0800" }
      }
    }
  ]
}

Four structural differences separate that document from a table, and each requires a decision from whoever writes the import.

Features nest and rows do not. The contact_info value is an object, whereas relational columns hold scalars. Reaching manager therefore requires a way of expressing a path rather than a name. The id is positioned outside properties, which means it cannot be addressed by the same mechanism as everything else in the feature. Coordinates are ordered longitude first and latitude second, reversing the order in which people normally state them. And because JSON defines only one number type, the values 4471, 12 and 1935 are indistinguishable within the document, leaving the destination column to determine what each of them actually is.

None of these is individually difficult. Together they are the reason importing GeoJSON usually becomes a bespoke loop, written once per project and slightly different every time.

Why doesn't my ORM just import it?

Entity Framework Core stores spatial data through the NetTopologySuite plugin, and NetTopologySuite includes a GeoJSON reader of its own. Both are capable, and both are well maintained. They address different halves of the problem, however, and nothing in either of them connects the two, so the connecting code belongs to the application: open the file, iterate the features, construct entities, determine what each property becomes, attach them to the context and save.

That code runs to roughly forty lines the first time it is written, and to forty slightly different lines in the next project that needs it. It also carries two ceilings that are not obvious until the files grow. AddRange followed by SaveChanges issues INSERT statements rather than using the database's bulk loading path, which makes half a million features a matter of hours rather than minutes. The straightforward implementation also deserializes the entire document before writing any rows, so the file has to fit in memory alongside the object graph built from it.

The question worth asking is therefore not whether an ORM can read GeoJSON, since with a plugin it can. It is whether anything owns the complete path from a file on disk to indexed rows in a table.

How do you map a feature onto a table?

Three separate methods describe the mapping, one for each part of a feature, because each part is located differently within the document and cannot be addressed the same way.

The target is an ordinary table with a spatial column:

Table sites = schema.CreateTable("sites");

sites.CreateColumn("site_id", DataType.Long, notNull: true);
sites.CreateColumn("name", DataType.VarChar, size: 120);
sites.CreateColumn("opened", DataType.Date);
sites.CreateColumn("sensor_count", DataType.Short);
sites.CreateColumn("readings_total", DataType.Long);
sites.CreateColumn("depth_m", DataType.Double);
sites.CreateColumn("is_active", DataType.Boolean);
sites.CreateColumn("manager", DataType.VarChar, size: 80);
sites.CreateColumn("location", DataType.Geography, notNull: true);

sites.CreatePrimaryKey("pk_sites", "site_id");

The configuration states how a feature fills it, and one call to ImportGeoJSON performs the import:

var config = new GeoJSONImportConfig();

config.MapGeometry("location", DataType.Geography);
config.MapId("site_id", DataType.Long);
config.MapProperty("location_name", "name", DataType.VarChar);
config.MapProperty("opened", "opened", DataType.Date);
config.MapProperty("sensors", "sensor_count", DataType.Short);
config.MapProperty("readings", "readings_total", DataType.Long);
config.MapProperty("depth", "depth_m", DataType.Double);
config.MapProperty("active", "is_active", DataType.Boolean);
config.MapProperty("contact_info.manager", "manager", DataType.VarChar);

using var manager = new Manager(connection);

manager.ImportGeoJSON(sites, "sites.geojson", config);

MapProperty takes a path into the feature's properties collection. MapGeometry takes no path, since a feature has exactly one geometry and it is not stored among the properties. MapId takes no path either, for reasons described in the following section.

Arguments on MapProperty are ordered source first and destination second, so MapProperty("sensors", "sensor_count", DataType.Short) reads the sensors property into the sensor_count column. Where the two names already agree the value is repeated, which looks redundant but preserves a single rule rather than introducing an exception.

The default for MapGeometry is DataType.Geography, which suits GeoJSON because its coordinates are longitude and latitude by specification. Supplying anything other than Geography or Geometry raises an exception rather than attempting to interpret the intent.

Columns absent from the configuration are left to the database, taking their declared default or null exactly as they would during a CSV import. There is no requirement to account for every column, which matters when a table carries bookkeeping columns unrelated to the file being loaded.

What about the coordinate reference system?

Coordinates in a GeoJSON document are WGS 84 longitude and latitude, which is SRID 4326. That is also what GeographySrid applies to Geography columns by default. For files that follow the specification, which is to say almost all of them, no configuration is required at all. The setting is nonetheless worth knowing about:

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "localhost",
    Database = "gis",
    GeographySrid = 4326
};

Databases whose Geography columns were created against a different reference system need GeographySrid set to match them. That decision belongs at the beginning of a project rather than partway through, since data already written was stored under whatever the previous value was, and changing the setting reinterprets those existing values without moving them.

Requesting units explicitly on spatial functions follows from the same principle. Given a Geography column with a declared reference system, UnitOfMeasure.Mile returns miles on every supported engine, whereas omitting the unit returns whatever that particular engine treats as native, which varies.

Where does the feature's id go?

The id in GeoJSON is a member of the Feature object, positioned alongside geometry and properties rather than within them. That placement is deliberate: the identifier belongs to the feature as a whole and is not one of its attributes.

No property path can reach something sitting outside properties, so the id has a mapping method of its own:

config.MapId("site_id", DataType.Long);

The specification allows an id to be either a string or a number, so the target column is normally Long or VarChar. Features carrying no id import as null in that column, the member being optional.

Where the target column is auto-generated and the identifiers in the file should be preserved rather than replaced by the database's own sequence, KeepIdentity controls that:

var config = new GeoJSONImportConfig { KeepIdentity = true };

config.MapId("site_id", DataType.Long);

Omitting it causes the import to stop with a ValueSetForAutoGeneratedColumnException before any rows are written. Refusing is the correct behaviour here, because silently substituting generated keys for the ones in the source file produces a table that cannot afterwards be reconciled against the data it came from.

What about nested properties?

Producers of GeoJSON commonly group related values into objects, the format permitting arbitrary nesting beneath properties. Dotted paths address those values, with each segment stepping into the object named by the segment before it:

config.MapProperty("contact_info.manager", "manager", DataType.VarChar);
config.MapProperty("operating_hours.weekday.open", "opens_weekday", DataType.Time);
config.MapProperty("survey.readings.depth_m", "depth_m", DataType.Double);

Paths may nest to any depth. Where a path terminates on something other than a scalar value the column receives null, there being no meaningful way to write an object into a single field.

What happens when a property is missing?

Unresolved paths produce null in the target column and the import continues, which applies whether the property is absent, its parent object is absent, or the parent resolves to something that is not an object.

This behaviour is what makes the import usable against real data rather than only against data prepared for it. Properties in GeoJSON form a free-form collection, and optional attributes really are optional. Any file assembled from more than one source contains features omitting values that other features carry. Treating absence as failure would mean that a single feature missing a phone number aborts a load of eighty thousand.

The distinction the design rests on is between structure and content. Malformed documents raise an exception. Documents ending partway through a feature raise an exception. Mappings naming a column the table does not contain raise an exception at the outset, before any rows are written. Features missing an optional value do not.

What if the file isn't a FeatureCollection?

The specification permits more than one structure at the top level of a document, and files encountered in practice use all of them. Accommodating that matters, because the commonest cause of an import script failing on real data is a file shaped slightly differently from the one the script was written against.

FeatureCollections import one row per feature, as in the earlier example. Bare features import a single row and behave exactly as one element of a collection would:

{
  "type": "Feature",
  "id": 8802,
  "geometry": { "type": "Point", "coordinates": [-0.1276, 51.5072] },
  "properties": { "location_name": "Westminster" }
}

Bare geometries of any of the seven types also import a single row:

{
  "type": "Polygon",
  "coordinates": [[
    [-3.2000, 55.9400], [-3.1600, 55.9400],
    [-3.1600, 55.9600], [-3.2000, 55.9600],
    [-3.2000, 55.9400]
  ]]
}

Since a bare geometry carries neither an identifier nor properties, only the geometry mapping draws anything from it and the remaining mapped columns receive null. The practical benefit is that a fragment pasted out of a console imports through the same call as a hundred-megabyte export from a GIS, with no branching on what was supplied:

manager.ImportGeoJSON(sites, "one_polygon.geojson", config);

How do the types convert?

JSON defines a single number type, so a document draws no distinction between an integer, a long, a float and a decimal. Numeric values arrive as decimals and textual values as strings. The DataType given in the mapping then determines what each becomes.

The range covered is wider than the obvious cases suggest. A survey table might be mapped as follows:

config.MapGeometry("position", DataType.Geography);

config.MapProperty("station_ref", "station_ref", DataType.VarChar);
config.MapProperty("is_active", "is_active", DataType.Boolean);
config.MapProperty("sensor_count", "sensor_count", DataType.Short);
config.MapProperty("readings_total", "readings_total", DataType.Long);
config.MapProperty("depth_m", "depth_m", DataType.Double);
config.MapProperty("calibration", "calibration", DataType.Decimal);
config.MapProperty("grade", "grade", DataType.Char);
config.MapProperty("notes", "notes", DataType.Clob);
config.MapProperty("thumbnail", "thumbnail", DataType.Blob);
config.MapProperty("commissioned", "commissioned", DataType.Date);
config.MapProperty("last_reading", "last_reading", DataType.DateTime);
config.MapProperty("window_opens", "window_opens", DataType.Time);
config.MapProperty("uptime", "uptime", DataType.Interval);

Reading a document containing these properties:

"properties": {
  "station_ref":    "STN-4471",
  "is_active":      true,
  "sensor_count":   12,
  "readings_total": 9876543210,
  "depth_m":        2.25,
  "calibration":    1234.5678,
  "grade":          "A",
  "notes":          "Replaced housing after storm damage.",
  "thumbnail":      "SGVsbG8gV29ybGQ=",
  "commissioned":   "2021-03-04",
  "last_reading":   "2021-03-04 13:45:30",
  "window_opens":   "06:30:00",
  "uptime":         "128.04:12:00"
}

Every one of those converts. Base64 strings become Blob values and longer text becomes Clob values. The numeric types from Byte through Decimal each take the value and narrow it to the declared column. Dates, times, timestamps and intervals are parsed from their string representations.

Two conversions exist specifically because real files contain both cases. Numbers written as text convert to numbers, and booleans written as text convert to booleans. Files produced by hand, or exported from a spreadsheet, routinely quote values that required no quoting, and rejecting them would be pedantry rather than rigour.

Date formats are configurable, with a default matching what GeoJSON producers emit:

var config = new GeoJSONImportConfig(DateFormatType.Iso);

Files using local formats are handled through the same format strings the CSV importer uses, so DateFormat, DateTimeFormat, TimeFormat, TimestampFormat and IntervalFormat all apply.

What about a file too big to fit in memory?

Reading a GeoJSON document in the ordinary way holds the whole file in memory before the first row reaches the database. For files of a few megabytes this is of no consequence, and for a large regional or national export it either fails outright or spends most of its time in the garbage collector.

Streaming changes the strategy, and it is requested with a single argument:

manager.ImportGeoJSON(sites, "sites_1m_features.geojson", config, streamImport: true);

One feature is held at a time rather than the entire document, so memory consumption stays flat regardless of file size. Rows then reach the database in batches, sized on the connection:

connection.ImportBatchSize = 10_000;

Streaming is opt-in rather than automatic because the ordinary path is faster for files that fit comfortably in memory, and the batching only earns its cost once the file is large enough to need it.

Did the import do what you think it did?

Two failures are common with spatial imports, and neither produces an error. Both therefore need checking deliberately.

Coordinate order is the first. GeoJSON writes longitude before latitude, reversing both the order people state them in and the order most mapping interfaces display. The pair [-118.3004, 34.1184] is Los Angeles, and reversing it produces a point in the Indian Ocean off the coast of Somalia. Both values remain plausible numbers, nothing rejects them, and every row imports successfully. Files produced by an established GIS follow the specification; files assembled by hand, or by a script written under time pressure, are less reliable.

Verification is cheaper as a query than as a visual inspection:

var here = GeospatialUtils.GeographyFromText("POINT(-118.3004 34.1184)");

var nearby = new Query()
    .Select([ new Expression("in_range", new Count(sites["site_id"])) ])
    .From(sites)
    .Where(new Criterion<double>(
        new SpatialDistance(sites["location"], here, UnitOfMeasure.Mile),
        ConditionalType.LessThanOrEqualTo,
        50));

long count = manager.GetScalar<long>(nearby);

Knowing roughly where the data should be, a count of zero indicates reversed coordinates. The check takes a minute and catches a failure that otherwise reaches production.

Unexpected nulls are the second. Because an unresolved path represents a legitimate absent value rather than an error, a mapping containing a typo in its property path imports cleanly and populates nothing. Whenever a column that should be populated is entirely null, the path is the more likely cause than the file:

var unmapped = new Query()
    .Select([ new Expression("missing", new Count(sites["site_id"])) ])
    .From(sites)
    .Where(new Criterion<string>(sites["manager"], ConditionalType.IsNull));

When should you build the spatial index?

Spatial indexes are maintained on every insert, so an index created before a bulk load is updated once per row throughout that load. Building it afterwards avoids several hundred thousand index updates and is measurably faster on any file of consequence:

sites.CreateSpatialIndexLatLong("spi_sites_location", "location");

manager.CreateSpatialIndex(sites.SpatialIndex);

That overload applies whole-world latitude and longitude defaults, which suits GeoJSON given that the format is WGS 84 by definition.

The bounding box carries a performance consequence worth understanding. PostGIS determines the extent of the data for itself and requires no declared box, whereas SQL Server and Teradata expect an extent, and the grid sizes govern how much of a query the index eliminates before the engine begins comparing geometries. Where the data covers one country rather than the whole planet, supplying a tighter bounding box through the explicit overload will improve query performance more than any other adjustment available. Left at whole-world defaults on those two engines, the result is an index that exists and achieves very little, which is a failure that resembles success.

What about polygons rather than points?

A Geography column stores any of the seven geometry types, so one table accepts points, lines, polygons, or a mixture of them, and a boundary file imports through the same mechanism as a file of stations.

{
  "type": "Feature",
  "id": "S12000036",
  "geometry": {
    "type": "MultiPolygon",
    "coordinates": [[[[-3.2000, 55.9400], [-3.1600, 55.9400], [-3.1600, 55.9600], [-3.2000, 55.9400]]]]
  },
  "properties": { "council": "City of Edinburgh", "population": 526470 }
}

The table and the configuration retain the same shape:

Table boundaries = schema.CreateTable("boundaries");

boundaries.CreateColumn("boundary_code", DataType.VarChar, size: 20, notNull: true);
boundaries.CreateColumn("name", DataType.VarChar, size: 120);
boundaries.CreateColumn("population", DataType.Long);
boundaries.CreateColumn("shape", DataType.Geography, notNull: true);

boundaries.CreatePrimaryKey("pk_boundaries", "boundary_code");

var boundaryConfig = new GeoJSONImportConfig { KeepIdentity = true };

boundaryConfig.MapGeometry("shape", DataType.Geography);
boundaryConfig.MapId("boundary_code", DataType.VarChar);
boundaryConfig.MapProperty("council", "name", DataType.VarChar);
boundaryConfig.MapProperty("population", "population", DataType.Long);

manager.ImportGeoJSON(boundaries, "council_areas.geojson", boundaryConfig);

The identifier here is a string rather than a number, which the specification permits and which official boundary datasets almost invariably use. Since MapId accepts the column type as an argument, VarChar is the only difference from the previous example.

Having two related tables imported the same way is what makes the queries in the next section possible.

What if you import the same file twice?

Import appends features to the table without emptying it first. That is the appropriate behaviour when accumulating data from more than one file, and the wrong behaviour when refreshing a dataset from a newer export of the same source.

Refreshing means clearing the table with EmptyTable beforehand:

manager.EmptyTable(sites);

manager.ImportGeoJSON(sites, "sites.geojson", config, streamImport: true);

Where the file covers one region and the table covers several, DeleteRecords removes only the affected rows, which is more appropriate than emptying everything:

var stale = new Delete(sites, new Criterion<long>(sites["region_id"], 4));

manager.DeleteRecords(stale);
manager.ImportGeoJSON(sites, "region4_sites.geojson", config);

Neither operation is specific to importing. Once the file has become rows, it is managed through the same operations as any other data in the schema.

What can you do with it once it's in there?

Storing the geometry as a column rather than retaining the file is what allows spatial predicates to compose with ordinary criteria inside a single query, which is the practical reason for importing at all.

SpatialContains selects everything inside a boundary imported from a second file:

var withinCity = new Query()
    .SelectAll(sites)
    .Where([
        new Criterion<bool>(new SpatialContains(boundaries["shape"], sites["location"]), true),
        new BooleanItem(BooleanType.And),
        new Criterion<bool>(sites["is_active"], true)
    ]);

SpatialArea measures those polygons, in units chosen rather than inherited from the engine:

var areas = new Query()
    .Select([
        boundaries["name"],
        new Expression("area_sq_mi", new SpatialArea(boundaries["shape"], UnitOfMeasure.Mile))
    ])
    .From(boundaries)
    .OrderBy(boundaries["name"], OrderClauseType.Ascending);

Ordering by distance from a point:

var closest = new Query()
    .Select([
        sites["name"],
        new Expression("miles", new SpatialDistance(sites["location"], here, UnitOfMeasure.Mile))
    ])
    .From(sites)
    .OrderBy(new SpatialDistance(sites["location"], here, UnitOfMeasure.Mile), OrderClauseType.Ascending);

The unit argument is the part of these queries that survives a change of database. Engines asked for a distance or an area without a specified unit answer in whatever unit they prefer, which is why portable spatial code otherwise accumulates a table of conversion constants per vendor. Stating the unit within the query means twenty-five miles is twenty-five miles wherever it runs.

These three examples only begin the subject. Spatial indexing, the choice between Geography and Geometry, the full set of OGC relationship predicates, shape arithmetic such as buffers and intersections, and validity checking are all covered in Spatial Data Across Relational Databases, which treats the spatial API in its own right rather than as the destination for an import.

How do you get GeoJSON back out?

Export is generally needed before import is, since the usual reason for holding spatial data in a database is to display it, and every mapping library in the browser expects GeoJSON as its input.

For a result set already held in memory:

var query = new Query()
    .Select([ sites["site_id"], sites["name"], sites["location"] ])
    .From(sites);

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

string geoJson = results.GetGeoJSON("location");

File.WriteAllText("sites.geojson", geoJson);

Naming the spatial column in GetGeoJSON is sufficient. Every other column in the result becomes a property on its feature, and the output is a FeatureCollection suitable for Leaflet, Mapbox GL, OpenLayers or anything else that reads the format.

The call itself does not change with the engine underneath it, which is worth more than it appears. Support for producing GeoJSON varies considerably between databases, in whether it exists at all and in the form it takes where it does, so an application working directly against the database carries that variation in its own code and gains a second path to maintain for every engine that lacks it. What the database returns is also a geometry rather than a document, leaving the surrounding FeatureCollection, the properties attached to each feature, and the promotion of the identifier still to be assembled.

What is the id property, and why does it matter?

The previous example selects a site_id column, yet only name and any other columns become properties, which raises the question of what happened to the identifier.

By default a property named id is promoted out of properties onto the feature's top-level id member, following the GeoJSON convention for identifying a row. Client libraries depend on this. Leaflet and Mapbox GL both use a feature's id for state management, for feature lookup, and for the hover and selection behaviour that works without additional code when the member is present.

Any column can fill that role:

string geoJson = results.GetGeoJSON("location", "site_id");

Promotion can also be disabled, leaving the identifier as an ordinary property:

string geoJson = results.GetGeoJSON("location", null);

A single argument therefore determines whether the file behaves correctly in the client, and the consequence of getting it wrong remains invisible until a developer working on the map asks why selection does not function.

Can you export something other than a whole table?

Export accepts a query, so anything expressible as a query can be serialized. Restricting the selected columns restricts the properties written. That matters when the output travels over a network to a browser:

var query = new Query()
    .Select([ sites["site_id"], sites["name"], sites["location"] ])
    .From(sites)
    .Where(new Criterion<bool>(sites["is_active"], true));

Joins work equally well, allowing features to carry attributes the spatial table does not itself hold:

var join = new Join(sites, regions, sites["region_id"], regions["region_id"]);

var query = new Query()
    .Select([
        sites["site_id"],
        sites["name"],
        regions["region_name"],
        sites["location"]
    ])
    .From(join);

Every non-spatial column in that result becomes a property, region_name included, so the map receives a joined view of the data without issuing a second request or performing the join itself.

What do the properties look like coming out?

Column names become property names exactly as written. The query therefore determines the shape of the file, and a column alias determines the name of a property.

Database column names are rarely what a browser should see, and the Select overload taking an alias renames them without a second pass over the output:

var query = new Query()
    .Select(sites["site_id"])
    .Select(sites["name"], "title")
    .Select(sites["opened"], "openedOn")
    .Select(sites["location"])
    .From(sites);

Those features carry title and openedOn rather than name and opened.

Values are written as their natural JSON equivalents, so numbers remain numbers, booleans remain booleans, and dates become strings in ISO form. Null columns are written as null properties rather than omitted. Every feature in the collection therefore carries an identical set of keys, and client code iterating them need not test for their presence.

Computed values behave the same way, and are frequently the reason to export from a query rather than from a table:

var query = new Query()
    .Select([
        boundaries["boundary_code"],
        boundaries["name"],
        new Expression("area_sq_mi", new SpatialArea(boundaries["shape"], UnitOfMeasure.Mile)),
        boundaries["shape"]
    ])
    .From(boundaries);

The map then receives an area per feature that was never stored, calculated by the database at the moment of export.

How do you export something too big for a string?

ResultSet loads its rows into memory and GetGeoJSON returns a string, which is adequate for a few thousand features and is the simplest approach that works. Beyond that scale, the streaming GetGeoJSON overload on a reader writes to a stream instead:

var query = new Query()
    .Select([ parcels["parcel_id"], parcels["owner"], parcels["boundary"] ])
    .From(parcels);

using var manager = new Manager(connection);
using ResultReader reader = manager.Search(query);
using FileStream file = File.Create("parcels.geojson");

reader.GetGeoJSON(file, "boundary", "parcel_id");

Features are serialized individually as they arrive from the database, so neither the result set nor the output document is ever fully resident, and a million parcels becomes a constant-memory operation at both ends.

Asynchronous equivalents exist throughout, which matter when the export happens inside a web request:

await using ResultReader reader = await manager.SearchAsync(query, cancellationToken);
await using FileStream file = File.Create("parcels.geojson");

await reader.GetGeoJSONAsync(file, "boundary", "parcel_id", cancellationToken: cancellationToken);

The cancellation token reaches the whole way down, so an export whose user has navigated away stops rather than continuing to write into a stream nobody is reading.

What errors will you actually see?

Failures are raised where they should be and the messages carry enough detail to act on. Four are likely in practice.

Mappings naming a column the table does not contain raise InvalidImportMappingsException before any rows are written, identifying the table, the column and the file. This is the most frequently encountered of the four, usually caused by a typo, and reporting it at the outset rather than partway through a large load is the difference between repeating the import and cleaning up after it.

Values that cannot convert to their column's type raise the same exception and identify the column. Typically this is a property mapped as Integer holding a non-numeric value in one feature out of eighty thousand.

Supplying a value for an auto-generated column raises ValueSetForAutoGeneratedColumnException, indicating that an id was mapped onto an identity column without KeepIdentity being set.

Documents that are not well-formed GeoJSON, or that end partway through a feature, raise InvalidDataException. The second case deserves attention because it is what a truncated download produces, and being told is considerably preferable to importing sixty percent of a file and treating the job as complete.

No exception is raised for absent data, which is the distinction the design rests on: structure is enforced and content is tolerated.

Putting it together

The complete cycle, from file to table to map, consists of four steps.

Defining the target:

using var manager = new Manager(connection);

Schema schema = manager.LoadSchema("app");
Table sites = schema.CreateTable("sites");

sites.CreateColumn("site_id", DataType.Long, notNull: true);
sites.CreateColumn("name", DataType.VarChar, size: 120);
sites.CreateColumn("opened", DataType.Date);
sites.CreateColumn("sensor_count", DataType.Short);
sites.CreateColumn("is_active", DataType.Boolean);
sites.CreateColumn("manager", DataType.VarChar, size: 80);
sites.CreateColumn("location", DataType.Geography, notNull: true);
sites.CreatePrimaryKey("pk_sites", "site_id");

manager.BuildSchema(schema);

Importing the file, streaming on account of its size:

var config = new GeoJSONImportConfig { KeepIdentity = true };

config.MapGeometry("location", DataType.Geography);
config.MapId("site_id", DataType.Long);
config.MapProperty("location_name", "name", DataType.VarChar);
config.MapProperty("opened", "opened", DataType.Date);
config.MapProperty("sensors", "sensor_count", DataType.Short);
config.MapProperty("active", "is_active", DataType.Boolean);
config.MapProperty("contact_info.manager", "manager", DataType.VarChar);

manager.ImportGeoJSON(sites, "sites.geojson", config, streamImport: true);

Building the index with CreateSpatialIndex now the rows are present:

sites.CreateSpatialIndexLatLong("spi_sites_location", "location");
manager.CreateSpatialIndex(sites.SpatialIndex);

Querying it as spatial data, which is the purpose of having imported it:

var here = GeospatialUtils.GeographyFromText("POINT(-118.3004 34.1184)");

var query = new Query()
    .SelectAll(sites)
    .Where([
        new Criterion<double>(
            new SpatialDistance(sites["location"], here, UnitOfMeasure.Mile),
            ConditionalType.LessThanOrEqualTo,
            25),
        new BooleanItem(BooleanType.And),
        new Criterion<bool>(sites["is_active"], true)
    ]);

And returning the result to a map:

using ResultReader reader = manager.Search(query);
using FileStream file = File.Create("nearby.geojson");

reader.GetGeoJSON(file, "location", "site_id");

Every call above is identical on every supported engine, the only line aware of which one is in use being the connection object at the top.

Spatial data is ordinarily where cross-vendor abstractions give up, since the type systems differ, the function names differ, the indexing models differ, and GeoJSON support ranges from comprehensive to absent depending on the engine. Twenty-five miles nonetheless means twenty-five miles on all of them.

Where to start

The free Community Edition is published on nuget.org and covers MySQL and PostgreSQL for ordinary data access, which is sufficient for becoming familiar with the query and schema API:

dotnet add package YndigoBlue.Velocity.Community

Spatial functionality, including GeoJSON in both directions, forms part of the full edition alongside the other five engines.

For anyone with a .geojson file already on disk, the most informative first test is not a benchmark. Import the file with a mapping for the geometry and two properties, export the table straight back out, and compare the result against the original. Whatever the tooling handles differently from your expectations will appear in that comparison within a minute.