Brainstorming

Input coordinate system

EPSG:4326 (unprojected lat/long)

Supported output coordinate systems

In the order of priority (table names from proj.db):

1. Projected coordinate systems (projected_crs)
2. Geodetic coordinate systems (geodetic_crs)
3. Vertical coordinate systems (vertical_crs)
4. Compound coordinate systems (compound_crs)

Listing all projections

https://gis.stackexchange.com/a/330283

sqlite3 /usr/share/proj/proj.db "SELECT name,auth_name,code FROM projected_crs WHERE auth_name = 'EPSG';"

Notice that names are not unique. For example,

sqlite3 /usr/share/proj/proj.db "select name,auth_name,code from projected_crs where name='WGS84 UTM Sud Fuseau 1'"
WGS84 UTM Sud Fuseau 1|IGNF|WGS84UTM1S
WGS84 UTM Sud Fuseau 1|IGNF|UTM01SW84

auth_name || code is unique.

sqlite3 /usr/share/proj/proj.db "select auth_name||code from projected_crs" | wc
   7401    7401   73596
sqlite3 /usr/share/proj/proj.db "select distinct (auth_name||code) from projected_crs" | wc
   7401    7401   73596

Retrieving projection information

sqlite3 /usr/share/proj/proj.db "select auth_name||':'||code from projected_crs"
projinfo -o projjson EPSG:9674

outputs

PROJJSON:
{
  "$schema": "https://proj.org/schemas/v0.2/projjson.schema.json",
  "type": "ProjectedCRS",
  "name": "NAD83 / USFS R6 Albers",
  "base_crs": {
    "name": "NAD83",
    "datum": {
      "type": "GeodeticReferenceFrame",
      "name": "North American Datum 1983",
      "ellipsoid": {
        "name": "GRS 1980",
        "semi_major_axis": 6378137,
        "inverse_flattening": 298.257222101
      }
    },
    "coordinate_system": {
      "subtype": "ellipsoidal",
      "axis": [
        {
          "name": "Geodetic latitude",
          "abbreviation": "Lat",
          "direction": "north",
          "unit": "degree"
        },
        {
          "name": "Geodetic longitude",
          "abbreviation": "Lon",
          "direction": "east",
          "unit": "degree"
        }
      ]
    },
    "id": {
      "authority": "EPSG",
      "code": 4269
    }
  },
  "conversion": {
    "name": "US Forest Service region 6 Albers",
    "method": {
      "name": "Albers Equal Area",
      "id": {
        "authority": "EPSG",
        "code": 9822
      }
    },
    "parameters": [
      {
        "name": "Latitude of false origin",
        "value": 34,
        "unit": "degree",
        "id": {
          "authority": "EPSG",
          "code": 8821
        }
      },
      {
        "name": "Longitude of false origin",
        "value": -120,
        "unit": "degree",
        "id": {
          "authority": "EPSG",
          "code": 8822
        }
      },
      {
        "name": "Latitude of 1st standard parallel",
        "value": 43,
        "unit": "degree",
        "id": {
          "authority": "EPSG",
          "code": 8823
        }
      },
      {
        "name": "Latitude of 2nd standard parallel",
        "value": 48,
        "unit": "degree",
        "id": {
          "authority": "EPSG",
          "code": 8824
        }
      },
      {
        "name": "Easting at false origin",
        "value": 600000,
        "unit": "metre",
        "id": {
          "authority": "EPSG",
          "code": 8826
        }
      },
      {
        "name": "Northing at false origin",
        "value": 0,
        "unit": "metre",
        "id": {
          "authority": "EPSG",
          "code": 8827
        }
      }
    ]
  },
  "coordinate_system": {
    "subtype": "Cartesian",
    "axis": [
      {
        "name": "Easting",
        "abbreviation": "E",
        "direction": "east",
        "unit": "metre"
      },
      {
        "name": "Northing",
        "abbreviation": "N",
        "direction": "north",
        "unit": "metre"
      }
    ]
  },
  "scope": "Forestry.",
  "area": "United States (USA) - Oregon and Washington.",
  "bbox": {
    "south_latitude": 41.98,
    "west_longitude": -124.79,
    "north_latitude": 49.05,
    "east_longitude": -116.47
  },
  "id": {
    "authority": "EPSG",
    "code": 9674
  }
}

Input/output format

We will use JSON and GeoJSON for non-geospatial and geospatial data input and output, respectively.

API

Read latitude and longitude, and return a tuple of

{
  'epsg': epsg_code,
  'proj': proj_code,
  'extent': [[ulx, uly], [urx, ury], [lrx, lry], [llx, lly]],
  'products': [
    {
      'name': 'UNG Gainesville LiDAR',
      'auth': 'IESA'
    },
    {
      'name': 'Hall County Remote Sensing Data',
      'auth': 'Hall County'
    },
  ]
}

Just four corners may not be enough to correctly represent the boundary of a certain projection on an EPSG:4326 map. Extent will be in coordinates in EPSG:4326.

Spatial indexing

Split the world into a 1-degree grid, and assign indices to rectangles in the grid. Establish a relational table between the grid and projection bounding boxes.

Database schemas

create table projbbox (
  auth_code varchar(100) primary key,
  name varchar(100) not null,
  south_latitude real not null,
  west_longitude real not null,
  north_latitude real not null,
  east_longitude real not null,
)

create table projbbox_to_products (
  id int primary key,
  auth_code varchar(100) not null,
  product varchar(100),
  agency varchar(100)
)

create table grid (
  index int primary key,
  row int not null,
  column int not null
)

create table grid_to_projbbox (
  id int primary key,
  index int not null,
  auth_code varchar(100) not null
)