How to transfer simple GeoTiff data into a database

Question

I'm trying to turn a relatively simple GeoTIFF file into a dataset readable by an API.

End goal:

Build a GET method for an API like /search_by_coordinates?lat=-73.1221&lng=44.4924 and get zone_index=19 in return.

Worth knowing:

• I know how to build and host an API with PostgreSQL/PostGIS as a DB with most back-end languages (Python, Node, Rails, etc).
• I know nothing about GeoTIFF files or spatial datasets like this one.
• The data range from 0 to 100 but I can aggregate it into 20 ranges of 5 [0-5][5-10] if it makes the process easier.
• My first intuition was to convert the raster into 20 polygons then store it into a PostgreSQL DB

What do you think is the best approach to transfer the data from the GeoTIFF into a readable database?

-- More info below --

GeoTIFF file information extracted from QGIS:

When adding the raster, I selected NAD83 to WGS 84 and it worked fine

Raster information

Extent  -141.0000000000000000,41.0000000000000000 : -52.0000035599999961,82.9999983200000031
Width   5340
Height  2520
Data type   Float32 - Thirty two bit floating point
GDAL Driver Description GTiff
GDAL Driver Metadata    GeoTIFF
Dataset Description zoning.tif
Compression LZW
Band 1  
STATISTICS_MAXIMUM=89
STATISTICS_MEAN=15.741086032058
STATISTICS_MINIMUM=4
STATISTICS_STDDEV=16.131822939431
More information    
AREA_OR_POINT=Area
Dimensions  X: 5340 Y: 2520 Bands: 1
Origin  -141,83
Pixel Size  0.01666666600000000012,-0.01666666600000000012


Coordinate Reference System (CRS)
Name    EPSG:4269 - NAD83
Units   Geographic (uses latitude and longitude for coordinates)
Method  Lat/long (Geodetic alias)
Celestial body  Earth
Reference   Static (relies on a datum which is plate-fixed)

Pixel information The zone_index value the API should return is the value 19 below.

GeoTIFF file information from ImageMagick:

Filename: zoning.tif

  Format: TIFF (Tagged Image File Format)
  Mime type: image/tiff
  Class: DirectClass
  Geometry: 5340x2520+0+0
  Units: PixelsPerInch
  Colorspace: Gray
  Type: Grayscale
  Endianness: LSB
  Depth: 32/16-bit
  Channel depth:
    Gray: 16-bit
  Channel statistics:
    Pixels: 13456800
    Gray:
      min: -6.54695e+07  (-999)
      max: 6.16029e+06 (94)
      mean: -3.49942e+07 (-533.977)
      median: -6.54695e+07 (-999)
      standard deviation: 3.31437e+07 (505.741)
      kurtosis: -1.96945
      skewness: 0.16904
      entropy: 0.994964
  Colors: 20
  Rendering intent: Undefined
  Gamma: 0.454545
  Matte color: grey74
  Background color: white
  Border color: srgb(223,223,223)
  Transparent color: none
  Interlace: None
  Intensity: Undefined
  Compose: Over
  Page geometry: 5340x2520+0+0
  Dispose: Undefined
  Iterations: 0
  Compression: LZW
  Orientation: TopLeft
  Properties:
    date:create: 2022-02-24T16:18:22+00:00
    date:modify: 2021-03-25T14:43:14+00:00
    quantum:format: floating-point
    signature: ff68aa8ec5dd2c71fed0767e5db4d58fa360ddd699b11837513baaa9d42751be
    tiff:alpha: unspecified
    tiff:endian: lsb
    tiff:photometric: min-is-black
    tiff:rows-per-strip: 1
  Artifacts:
    verbose: true
  Tainted: False
  Filesize: 2.59357MiB
  Number pixels: 13456800

Answer 1

I know spatial data well, but I don't know much about back-end. So I don't know how you would build the db, but a raster is just an array after all. If you are using the TM coordinate system, a change of one row will move a distance exactly the size of a pixel. (I'm not sure if you use longitude and latitude coordinates. You can consider the coordinates for each data by reflecting the affine transformation.) In the case of python, there is a package that returns the pixel value of a specific coordinate in a package such as rasterio. It already exists.

If you ask me to implement the process of finding a value with the desired coordinates, I think the following process will be followed.

1. Find which raster file contains the entered lat & long.

• All you need is the bbox(minx, miny, maxx, maxy) of each raster.

2. Return the value of the coordinates within the raster

• It will return by using the rasterio function or as an array that reflects the coordinates (converting the coordinate values ​​to np.array index).