PostGIS filesystem

[knutole]

Steps for setting up PostGIS filesystem

• [x] 1. Docker PostGIS db
• [x] 2. More robust uploader
• [x] 3. Create API endpoints and processing functions
• [x] 4. New filesystem for data based on PostGIS

  1. Docker PostGIS database

• [x] Create docker container with latest Postgres and PostGIS (from Dockerfile), this is a good Dockerfile.
• [x] Create a separate data container for PostGIS, so all data is stored in a pluggable data container.
• [x] Enable PostGIS extensions, create user, table on PostGIS database (if not exists) on docker build
• [x] Add PostGIS container to docker-compose.yml docker-systemapic composition.
• [x] Tune and enable all kinds of spatial functions

  2. Robust Uploader

• [ ] Separate API endpoint for data uploads and image/doc/pdf uploads, and separate flows in client.
• [ ] Support direct connection with PostGIS for importing tables.
• [x] Create separate PostGIS database for each user, and table for each data file.
• [x] Support zip, tar archives
• [x] Oauth2 access_token based authenication
• [x] To make life easier, enough to support one file upload at a time (ie. one shapefile per zip, etc.)
• [ ] Comprehensive error checks, catch invalid files.
• [x] Register files/dataset as MongoDB file model objects.
• [x] Return full information about upload back to client on successful upload.
• [ ] Documentation of endpoints /api/data/upload and /api/data/upload/chunked.
• [ ] Clean-up and delete old routes.

  Resumable (later)

• [x] Support chunked uploading with Resumable.js
• [ ] Check headers of data-files (first chunk) for metadata: type of file, other metadata. Enable first/last chunk upload in Resumable.js. Check beginning of file in normal upload. Send back with sockets to systemapic.js client, notify in other way with other clients (perhaps do a GET request to an endpoint). Can also check .zip files for content by reading last chunk.
• [x] Resumable chunks that are already uploaded sometimes don't start processing due to wrong redis-count, fix.
• [x] Delete chunks after succesful upload.

  3. Error checks

• [x] Feedback for not accepted files
• [x] Feedback for error in files

  4. PostGIS API Endpoints

See #153 and #151

Features

All requests to /api/db/* are proxied to pile. All export of postgis data (ie. tiles, geojson, files, tables) go from pile, incl. manipulating of data, etc.

• [ ] Create endpoints in PostGIS Tile Server API for:
  • [ ] getting geojson, shapefile from PostGIS data
  • [x] getting vector tile from PostGIS data
  • [x] getting raster tiles from PostGIS data + CartoCSS
  • [ ] doing processing on vectors
  • [ ] doing processing on rasters
  • [x] doing advanced SQL queries on data
  • [ ] exporting data to external PostGIS database
  • [ ] updating data
  • [ ] deleting data
  • [ ] creating new tables (datasets) from SQL queries
  • ... etc.
• [ ] Create db maintenance scripts for PostGIS (to be used by admins)
• [ ] Write tests for all endpoints.

  Endpoints, pile:

All requests must contain access_token, either as body in POST requests, or query param in GET requests.

• [ ] /api/db/getData: get postgis data represented as a file or table.
  Method: GET
  Options: layerUuid, desired format (eg. Shapefile, GeoJSON, table)
  Returns: A file.
• [x] /api/db/vector: get a vector tile from postgis
  Method: GET
  Options: layerUuid, z, x, y.
  Returns: A vector tile (.pbf)
• [x] /api/db/raster: get a raster tile from postgis
  Method: GET
  Options: layerUuid, z, x, y.
  Returns: A raster tile (.png)
• [x] /api/db/createLayer: create a layer from postgis data
  Method: POST
  Options: layerJSON see #151
  Returns: A layerUuid
• [x] /api/db/getLayer: get stored layer from layerUuid
  Method: GET
  Options: layerUuid
  Returns: A layer-object

  Endpoints, wu:

• [ ] /api/file/get: get a file object from a fileUuid

  4. File system

For normal files (images, docs, etc.), we'll use simple files. For data based files (geodata), we'll import to PostGIS.

• [x] The dataset (table) in PostGIS is represented in the MongoDB file model.
• [ ] The layers created from the dataset are represented in the MongoDB layer model.
• [x] Create a database in PostGIS for all new user's.

  References:

• https://blog.openshift.com/get-more-spatial-in-your-database-with-postgis-21-on-openshift/

[knutole]

Upload flow:

• Upload to server
• Check files etc.
• Import to PostGIS with fileUuid
• Register files
• Notify client of changes

Request tile flow:

• Create vector tile from PostGIS fileUuid
• Create raster from vector tile
• Return tile to client

Quick'n dirty benchmarks:

1GB Shapefile import: 210 seconds = 3,5 mins.

Import with GiST indexing by shp2psql: 1GB, 234.791ms Import w/o GiST indexing by shp2psql: 1GB, 213.432ms (21s less) Creating GiST manually with CREATE INDEX turkey_data_3_geom_gist ON turkey_data_3 USING GIST (geom); 30.247.354 ms. (Not much to gain by creating index later.)

[knutole]

Potentially supported formats:

Rasters:

gdalinfo --formats

  VRT (rw+v): Virtual Raster
  GTiff (rw+vs): GeoTIFF
  NITF (rw+vs): National Imagery Transmission Format
  RPFTOC (rovs): Raster Product Format TOC format
  ECRGTOC (rovs): ECRG TOC format
  HFA (rw+v): Erdas Imagine Images (.img)
  SAR_CEOS (rov): CEOS SAR Image
  CEOS (rov): CEOS Image
  JAXAPALSAR (rov): JAXA PALSAR Product Reader (Level 1.1/1.5)
  GFF (rov): Ground-based SAR Applications Testbed File Format (.gff)
  ELAS (rw+v): ELAS
  AIG (rov): Arc/Info Binary Grid
  AAIGrid (rwv): Arc/Info ASCII Grid
  GRASSASCIIGrid (rov): GRASS ASCII Grid
  SDTS (rov): SDTS Raster
  DTED (rwv): DTED Elevation Raster
  PNG (rwv): Portable Network Graphics
  GTA (rwv): Generic Tagged Arrays (.gta)
  JPEG (rwv): JPEG JFIF
  MEM (rw+): In Memory Raster
  JDEM (rov): Japanese DEM (.mem)
  GIF (rwv): Graphics Interchange Format (.gif)
  BIGGIF (rov): Graphics Interchange Format (.gif)
  ESAT (rov): Envisat Image Format
  BSB (rov): Maptech BSB Nautical Charts
  XPM (rwv): X11 PixMap Format
  BMP (rw+v): MS Windows Device Independent Bitmap
  DIMAP (rov): SPOT DIMAP
  AirSAR (ro): AirSAR Polarimetric Image
  RS2 (ros): RadarSat 2 XML Product
  PCIDSK (rw+v): PCIDSK Database File
  PCRaster (rw): PCRaster Raster File
  ILWIS (rw+v): ILWIS Raster Map
  SGI (rw+): SGI Image File Format 1.0
  SRTMHGT (rwv): SRTMHGT File Format
  Leveller (rw+): Leveller heightfield
  Terragen (rw+): Terragen heightfield
  GMT (rw): GMT NetCDF Grid Format
  netCDF (rw+s): Network Common Data Format
  HDF4 (ros): Hierarchical Data Format Release 4
  HDF4Image (rw+): HDF4 Dataset
  ISIS3 (rov): USGS Astrogeology ISIS cube (Version 3)
  ISIS2 (rw+v): USGS Astrogeology ISIS cube (Version 2)
  PDS (rov): NASA Planetary Data System
  TIL (rov): EarthWatch .TIL
  ERS (rw+v): ERMapper .ers Labelled
  ECW (rw): ERDAS Compressed Wavelets (SDK 3.x)
  JP2ECW (rw+v): ERDAS JPEG2000 (SDK 3.x)
  JP2OpenJPEG (rwv): JPEG-2000 driver based on OpenJPEG library
  L1B (rovs): NOAA Polar Orbiter Level 1b Data Set
  FIT (rwv): FIT Image
  GRIB (rov): GRIdded Binary (.grb)
  MrSID (rov): Multi-resolution Seamless Image Database (MrSID)
  JP2MrSID (rov): MrSID JPEG2000
  JPEG2000 (rwv): JPEG-2000 part 1 (ISO/IEC 15444-1)
  MG4Lidar (ro): MrSID Generation 4 / Lidar (.sid)
  RMF (rw+v): Raster Matrix Format
  WCS (rovs): OGC Web Coverage Service
  WMS (rwvs): OGC Web Map Service
  MSGN (ro): EUMETSAT Archive native (.nat)
  RST (rw+v): Idrisi Raster A.1
  INGR (rw+v): Intergraph Raster
  GSAG (rwv): Golden Software ASCII Grid (.grd)
  GSBG (rw+v): Golden Software Binary Grid (.grd)
  GS7BG (rw+v): Golden Software 7 Binary Grid (.grd)
  COSAR (ro): COSAR Annotated Binary Matrix (TerraSAR-X)
  TSX (rov): TerraSAR-X Product
  COASP (ro): DRDC COASP SAR Processor Raster
  R (rwv): R Object Data Store
  MAP (rov): OziExplorer .MAP
  PNM (rw+v): Portable Pixmap Format (netpbm)
  DOQ1 (rov): USGS DOQ (Old Style)
  DOQ2 (rov): USGS DOQ (New Style)
  ENVI (rw+v): ENVI .hdr Labelled
  EHdr (rw+v): ESRI .hdr Labelled
  GenBin (rov): Generic Binary (.hdr Labelled)
  PAux (rw+): PCI .aux Labelled
  MFF (rw+): Vexcel MFF Raster
  MFF2 (rw+): Vexcel MFF2 (HKV) Raster
  FujiBAS (ro): Fuji BAS Scanner Image
  GSC (rov): GSC Geogrid
  FAST (rov): EOSAT FAST Format
  BT (rw+v): VTP .bt (Binary Terrain) 1.3 Format
  LAN (rw+v): Erdas .LAN/.GIS
  CPG (ro): Convair PolGASP
  IDA (rw+): Image Data and Analysis
  NDF (rov): NLAPS Data Format
  EIR (rov): Erdas Imagine Raw
  DIPEx (rov): DIPEx
  LCP (rwv): FARSITE v.4 Landscape File (.lcp)
  GTX (rw+v): NOAA Vertical Datum .GTX
  LOSLAS (rov): NADCON .los/.las Datum Grid Shift
  NTv2 (rw+vs): NTv2 Datum Grid Shift
  CTable2 (rw+v): CTable2 Datum Grid Shift
  ACE2 (rov): ACE2
  SNODAS (rov): Snow Data Assimilation System
  KRO (rw+v): KOLOR Raw
  ARG (rwv): Azavea Raster Grid format
  RIK (ro): Swedish Grid RIK (.rik)
  USGSDEM (rwv): USGS Optional ASCII DEM (and CDED)
  GXF (ro): GeoSoft Grid Exchange Format
  HTTP (ro): HTTP Fetching Wrapper
  BAG (ro): Bathymetry Attributed Grid
  HDF5 (ros): Hierarchical Data Format Release 5
  HDF5Image (ro): HDF5 Dataset
  NWT_GRD (rov): Northwood Numeric Grid Format .grd/.tab
  NWT_GRC (rov): Northwood Classified Grid Format .grc/.tab
  ADRG (rw+vs): ARC Digitized Raster Graphics
  SRP (rovs): Standard Raster Product (ASRP/USRP)
  BLX (rw): Magellan topo (.blx)
  Rasterlite (rws): Rasterlite
  EPSILON (rwv): Epsilon wavelets
  PostGISRaster (rws): PostGIS Raster driver
  SAGA (rw+v): SAGA GIS Binary Grid (.sdat)
  KMLSUPEROVERLAY (rwv): Kml Super Overlay
  XYZ (rwv): ASCII Gridded XYZ
  HF2 (rwv): HF2/HFZ heightfield raster
  PDF (rwvs): Geospatial PDF
  OZI (rov): OziExplorer Image File
  CTG (rov): USGS LULC Composite Theme Grid
  E00GRID (rov): Arc/Info Export E00 GRID
  WEBP (rwv): WEBP
  ZMap (rwv): ZMap Plus Grid
  NGSGEOID (rov): NOAA NGS Geoid Height Grids
  MBTiles (rov): MBTiles
  IRIS (rov): IRIS data (.PPI, .CAPPi etc)

Vector: ogr2ogr --formats

  -> "ESRI Shapefile" (read/write)
  -> "MapInfo File" (read/write)
  -> "UK .NTF" (readonly)
  -> "SDTS" (readonly)
  -> "TIGER" (read/write)
  -> "S57" (read/write)
  -> "DGN" (read/write)
  -> "VRT" (readonly)
  -> "REC" (readonly)
  -> "Memory" (read/write)
  -> "BNA" (read/write)
  -> "CSV" (read/write)
  -> "NAS" (readonly)
  -> "GML" (read/write)
  -> "GPX" (read/write)
  -> "LIBKML" (read/write)
  -> "KML" (read/write)
  -> "GeoJSON" (read/write)
  -> "Interlis 1" (read/write)
  -> "Interlis 2" (read/write)
  -> "GMT" (read/write)
  -> "GPKG" (read/write)
  -> "SQLite" (read/write)
  -> "ODBC" (read/write)
  -> "WAsP" (read/write)
  -> "MDB" (readonly)
  -> "PGeo" (readonly)
  -> "MSSQLSpatial" (read/write)
  -> "PostgreSQL" (read/write)
  -> "MySQL" (read/write)
  -> "PCIDSK" (read/write)
  -> "OpenFileGDB" (readonly)
  -> "FileGDB" (read/write)
  -> "XPlane" (readonly)
  -> "AVCBin" (readonly)
  -> "AVCE00" (readonly)
  -> "DXF" (read/write)
  -> "Geoconcept" (read/write)
  -> "GeoRSS" (read/write)
  -> "GPSTrackMaker" (read/write)
  -> "VFK" (readonly)
  -> "PGDump" (read/write)
  -> "OSM" (readonly)
  -> "GPSBabel" (read/write)
  -> "SUA" (readonly)
  -> "OpenAir" (readonly)
  -> "PDS" (readonly)
  -> "WFS" (readonly)
  -> "HTF" (readonly)
  -> "AeronavFAA" (readonly)
  -> "Geomedia" (readonly)
  -> "EDIGEO" (readonly)
  -> "GFT" (read/write)
  -> "GME" (read/write)
  -> "SVG" (readonly)
  -> "CouchDB" (read/write)
  -> "Idrisi" (readonly)
  -> "ARCGEN" (readonly)
  -> "SEGUKOOA" (readonly)
  -> "SEGY" (readonly)
  -> "XLS" (readonly)
  -> "ODS" (read/write)
  -> "XLSX" (read/write)
  -> "ElasticSearch" (read/write)
  -> "PDF" (read/write)
  -> "Walk" (readonly)
  -> "CartoDB" (readonly)
  -> "SXF" (readonly)

[knutole]

Bugs

• [x] Must allow dots . in names of uploaded files
• [x] Must allow spaces in names of uploaded files