Error creating New Space Materialised View for sinusoidal(WKT) products in v1.8.x

mpaget commented 4 months ago

Description

The CSIRO EASI database includes MODIS Land sinusoidal products with projection information stored as WKT. Running datacube-ows-update for v1.8.39 resulted in the following error. Perhaps the New Space Materialised View is expecting an EPSG integer somewhere but not also a WKT string?

ows@easi-ows-datacube:~$ datacube-ows-update --schema --role <ows_db_username>
The 'month' time resolution type is deprecated.  Please use 'summary'.
The 'month' time resolution type is deprecated.  Please use 'summary'.
Checking schema....
Creating or replacing WMS database schema...
 Creating/replacing wms schema

 Creating/replacing product ranges table

 Creating/replacing sub-product ranges table

 Creating/replacing multi-product ranges table

 Granting usage on schema

Creating or replacing materialised views...
 Installing Postgis extensions on public schema

 Setting default timezone to UTC

 Creating NEW TIME Materialised View (start of hard work)

 Creating NEW SPACE Materialised View (Slowest step!)

Traceback (most recent call last):
  File "/usr/local/bin/datacube-ows-update", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/datacube_ows/update_ranges_impl.py", line 85, in main
    create_views(dc)
  File "/usr/local/lib/python3.10/dist-packages/datacube_ows/update_ranges_impl.py", line 119, in create_views
    run_sql(dc, "extent_views/create", database=dbname)
  File "/usr/local/lib/python3.10/dist-packages/datacube_ows/update_ranges_impl.py", line 176, in run_sql
    psycopg2connection.execute(q)
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "S["unnamed",GEOGCS["Unknown datum based upon the custom spheroid",DATUM["Not_specified_based_on_custom_spheroid",SPHEROID["Custom spheroid",6371007.181,0]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]]],PROJECTION["Sinusoidal"],PARAMETER["longitude_of_center",0],PARAMETER["false_easting",0],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH]]"

Context (Environment)

`datacube-ows` version (datacube-ows --version):

datacube-ows v1.8.39, https://ows.csiro.easi-eo.solutions/

`ows_config.py` file (link, sample code)

datacube product metadata (datacube product show product_name)

One of these MODIS Land products, I assume: https://explorer.csiro.easi-eo.solutions/products#modis-land-group

SpacemanPaul commented 4 months ago

This is a limitation of Postgis. Postgis requires a numeric SRID which is more or less equivalent to an EPSG code, and does not understand WKT. The materialised view reprojects the extent from the native CRS to EPSG:4326 for the search index within the database, using Postgis SQL functions, so there's really no way around this with the current implementation. You could create a new SRID for the CRS in Postgis (which would involve translating from WKT to proj4text) but you would also need to set the CRS to "EPSG:" which would break everything outside of Postgis, so that's not very helpful.

Note that this is not a new regression in 1.8.39 - this issue will affect all OWS versions since the materialised view approach was first adopted.

The new postgis index driver in datacube-core 1.9.x should facilitate a solution (as the projection from the native CRS to the search index CRS(s) is done in Python at indexing time), but OWS support for this new driver is still a few months off.

mpaget commented 4 months ago

OK. Thank you, @SpacemanPaul. We'll look into some work-arounds.

Could we turn off materialised views for selected products? For our case the MODIS Land sinusoidal products are not in the ows-config; they're just having their materialised views created as part of the whole datacube, I assume.

SpacemanPaul commented 4 months ago

That sounds doable. I'm planning to start work on some major cleanups of and updates to OWS shortly, so I should be able to get a workaround into the next release.

SpacemanPaul commented 3 weeks ago

Fixed in both 1.8 and 1.9 branches.

opendatacube / datacube-ows