MapServer Raster WMTS From PostGIS Column

Question

I'm going to guess this isn't possible, since I haven't seen documentation supporting this in either MapServer, PostGIS, or GDAL.

I have a PostGIS table with two columns -- timestamp and rast -- timestamp is of type timestamp without time zone and rast is of type raster. I'm trying to set up a WMTS layer in MapServer that uses timestamp as the wms_timeitem and the associated rast as the DATA.

I've tried a variety of approaches -- CONNECTIONTYPE and CONNECTION of course although the MapServer documentation says that isn't supported for raster layers. I've also used a GDAL PG connection in DATA instead (e.g. PG:host=...) but I see no way to select a specific column let alone tie that to the time request. I obviously get errors with either of those approaches and no raster is produced.

I have no control over the structure of these tables so if this isn't possible with MapServer I will have to try something else (maybe just serving raw output via GDAL) and I am open to FOSS4G suggestions in that regard.

Answer 1

Using @user30184's comment I was able to create a solution. Though not a WMTS, I was able to create a WMS layer that has a modifiable timestamp variable -- good enough!

Before worrying about the PostGISRaster connection, set up your mapfile to serve a WMS and create a new raster layer where you will add the connection string.

First, let's make sure we have a GDAL PostGISRaster connection string working via gdalinfo.

gdalinfo "PG:host=... port=5432 user='...' password='...' dbname='the_db' schema='the_schema' table='the_table' column='rast' mode=1"

Supposing everything is correct and the database accepts connections from this IP and port, gdalinfo should return information for all the rows of rasters in the table. What happens when this connection is used in a mapfile?

MAP
 ...
 LAYER
  ...
  DATA "PG:host=... port=5432 user='...' password='...' dbname='the_db' schema='the_schema' table='the_table' column='rast' mode=1"
 END
END

Maybe you get lucky, maybe you don't. MapServer might tell you that it is unable to connect, and even with the maximum debugging level set and CPL_DEBUG turned on, you might not be able to get any useful information about the issue.

NOTE: If you're looking at the error returned from MapServer, you might notice something spooky -- the entire connection string is returned, including the password. Please don't include your password in the mapfile, use a PGPASS environment variable or file instead.

If you are otherwise able to connect from that server, the solution is likely simple, though not obvious: try removing the single quotes from the connection string.

MAP
 ...
 LAYER
  ...
  DATA "PG:host=... port=5432 user=... password=... dbname=the_db schema=the_schema table=the_table column=rast mode=1"
 END
END

Hopefully MapServer is no longer producing errors, and instead is returning rasters from the table.

A few more steps are required to return a specific raster based on a user-supplied timestep variable in the GET parameters of the request.

Create a working WHERE clause in your connection string first, such as the following:

gdalinfo "PG:... where='timestamp=\'2019-03-06 00:00\''"

This might work in your gdalinfo tests, but MapServer will likely produce a connection error when it is used in the DATA connection string:

  DATA "PG:... where='timestamp=\'2019-03-06 00:00\''"

Finding the magic combination of escaped string literals is not worth the effort when there is a simpler, cleaner solution -- Postgres's dollar-quoted string constants. Try this instead:

  DATA "PG:... where='timestamp=$$2019-03-06 00:00$$'"

Now MapServer should be returning a single raster, pertinent to that timestamp. Next, use run-time substitution to set that query to the timestamp supplied in the GET request.

MAP
 ...
 LAYER
  ...
  VALIDATION
   "timestamp" "[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1]) (2[0-3]|[01][0-9]):[0-5][0-9]"
  END
  DATA "PG:host=... port=5432 user=... password=... dbname=the_db schema=the_schema table=the_table column=rast mode=1 where='timestamp=$$%timestamp%$$'"
 END
END

Now you can append, for example, &timestamp=2019-03-06 00:00 to your WMS URL and receive the raster for that specified timestamp.