Pre-built Databases

Shiny.Spatial ships with four pre-built SQLite databases containing geographic data for the United States and Canada. These databases are ready to use out of the box for prototyping, testing, or production use.

Available Databases

Database	Table Name	Geometry Type	Records	Properties
us-states.db	states	Polygon	51	name, abbreviation, population
us-cities.db	cities	Point	100	name, state, population
ca-provinces.db	provinces	Polygon	13	name, abbreviation, population
ca-cities.db	cities	Point	50	name, province, population

All databases use CoordinateSystem.Wgs84 (longitude/latitude).

Usage

Find which US state a point is in

using var db = new SpatialDatabase("databases/us-states.db");
var states = db.GetTable("states");

var denver = new Point(-104.99, 39.74);
var results = states.FindIntersecting(denver);

var stateName = results[0].Properties["name"];        // "Colorado"
var abbreviation = results[0].Properties["abbreviation"]; // "CO"
var population = results[0].Properties["population"];  // state population

Find nearby cities

using var db = new SpatialDatabase("databases/us-cities.db");
var cities = db.GetTable("cities");

var center = new Coordinate(-104.99, 39.74); // Denver

var nearbyCities = cities.Query()
    .WithinDistance(center, 200_000) // 200km radius
    .OrderByDistance(center)
    .ToList();

foreach (var city in nearbyCities)
{
    Console.WriteLine($"{city.Properties["name"]}, {city.Properties["state"]}");
}

Find cities in a state polygon

using var statesDb = new SpatialDatabase("databases/us-states.db");
using var citiesDb = new SpatialDatabase("databases/us-cities.db");

var states = statesDb.GetTable("states");
var cities = citiesDb.GetTable("cities");

// Get Colorado's polygon
var colorado = states.Query()
    .WhereProperty("abbreviation", "=", "CO")
    .FirstOrDefault();

// Find cities within Colorado
var coloradoCities = cities.FindContainedBy(colorado!.Geometry);

Query Canadian provinces

using var db = new SpatialDatabase("databases/ca-provinces.db");
var provinces = db.GetTable("provinces");

var toronto = new Point(-79.38, 43.65);
var result = provinces.FindIntersecting(toronto);
// result[0].Properties["name"] == "Ontario"

Regenerating Databases

The databases can be regenerated using the included seeder tool:

dotnet run --project tools/Shiny.Spatial.DatabaseSeeder -- ./databases

This regenerates all four databases from the seeder’s embedded data.