[raster] Issues with Mapserver and GDAL

[robe]

Bborie,

I think you are right that gdal is not aware of the new column naming. Though I'm not sure if that is coded in GDAL or mapserver.

I'm seeing two issues.

In my PostgreSQL logs for mapserver 6.0.1 and 6.1 svn I see this error:

— this is the query its executing —-

select o_table_name, overview_factor, o_column, o_table_schema from raster_overviews where r_table_schema = 'aerials' and r_table_name = 'o_4_boston' and r_column = 'rast';

—and error:

column "o_column" does not exist

}}}

The more disturbing issue I am seeing is that with my mapserver 6.0.1 my aerials load all be-it kind of slow and it seems to transform too.

Doing queries something of the form:

ST_SetSRID(ST_MakeBox2d(ST_MakePoint(samepoint), ST_MakePoint(samepoint)),26986)

My 6.1 is not putting in a spatial filter at all — its trying to do a query like this:

select (foo.md).* from (select distinct st_bandmetadata( rast, 3) as md from aerials. o_4_boston) as foo;

-- and--
select st_astext(st_setsrid(st_extent(rast::geometry),26986)) from aerials.o_4_boston;

Which is repeated for each band. Given I have 9396 records in o_4_boston table (and it does a separate for each band 9396*3), its no wonder the thing never loads.

My mapserver layer looks like this:

LAYER
	NAME boston_aerials
	TYPE raster
	STATUS ON
	MINSCALE 1
    MAXSCALE 1000
	DATA "PG:host=localhost port=5433 dbname='dnddts' user='mapuser' schema='aerials' table='o_4_boston' column='rast'"	
	#PROCESSING "NODATA=0"
END

I'll have to swap my binaries again to log the 6.01 but it was definitely (smarter and correctly transforming and rendering the aerial in the right spot) or the syntax has changed between 6.01 and 6.1.

My OpenLayers query looks something like:

mapserv?LAYERS=boston_aerials&STYLES=&TRANSPARENT=true&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&SRS=EPSG%3A900913&BBOX=-7916494.5962898,5208175.3278665,-7916353.5162278,5208332.0834909&WIDTH=945&HEIGHT=1050

[robe]

Just checked my old Mapserv 6.01 and it writes a query like:

select rid, rast from aerials.o_4_boston where rast ~ st_setsrid(st_makebox2d(st_point(231686.436174, 895871.024778), st_point(231866.436174, 896051.024778)),26986)

Why it creates a single pointed bounding box is beyond me. That gets repeated a lot of times. I may have had mode=2 left out on my 6.1 so will have to check that taht was not the culprit of the missing WHERE clauses.

[dustymugs]

I have no ideas about whether or not it is Mapserver or GDAL generating those queries. I do know that GDAL in its current state (1.9 rc1) is unable to handle the current/permanent naming convention of raster_columns and raster_overviews.

I have made Jorge (the GDAL PostGIS Raster driver maintainer) aware of the changes so hopefully he can make the changes. I've done the same for the QGIS PostGIS Raster plugin maintainer. I've done what poking I need to do to both GDAL's driver and QGIS plugin to function for my needs but I think more would be needed to properly make use of raster_columns, raster_overviews and their related functions.

I don't know what we can do about the performance issues in Mapserver though assuming that it is generating those queries. Once PostGIS 2.0 is out, I could then see someone improving Mapserver's querying abilities…

[rouault]

I don't think MapServer is involved at all in that issue. It only relies on the GDAL PostgisRaster driver, where actually you can find the culprit request :

        osCommand.Printf("select o_table_name, overview_factor, o_column, "
                "o_table_schema from raster_overviews where r_table_schema = "
                "'%s' and r_table_name = '%s' and r_column = '%s'",
                poDS->pszSchema, poDS->pszTable, poDS->pszColumn);

The same for the other request :

        osCommand.Printf("select rid, %s from %s.%s where %s ~ "
                "st_setsrid(st_makebox2d(st_point(%f, %f), st_point(%f,"
                "%f)),%d)", pszColumn, pszSchema, pszTable, pszColumn, 
                dfProjLowerLeftX, dfProjLowerLeftY, dfProjUpperRightX, 
                dfProjUpperRightY, poPostGISRasterDS->nSrid);

Once the PostgisRaster driver is optimized, MapServer performance should improve.

Looking a bit at the driver, I also see things in the PostGISRasterDataset::IRasterIO() implementation, in particular the bBlocksCached concept, that lead me to think that only the first call to IRasterIO() will be properly optimized (and potentially too much agressively optimized if the query window is larger than the GDAL block cache size!), but if further calls are made with a different window, they will fallback to the non-optimized IReadBlock().

[robe]

Even,

Thanks very much for the input. I guess it's too late to make corrections for GDAL 1.9.0 since its already at RC1.

At the very least we'll need to correct the column namings or provide a legacy view version that people stuck using the old driver can use that includes both old and new names. Now that our tables are all views, it's a bit easier to do as a patch.

I'll put in a ticket on GDAL to fix the column names in queries. As far as the window case not sure what to do about that. We should be suing ST_MakeEnvelope instead of ST_SetSRID(ST_MakeBox2D .. since we are trying to phase out box2d and its also shorter to write. ST_MakeEnvelope was introduced in 1.5, but since raster is new itself I think its safe enough to replace on the raster driver.

I unfortunately have not been successful building postgis raster support into my mingw compiled gdal because it can't find pgqconnectdb or something.

[robe]

okay I've submitted a GDAL ticket for this at ​http://trac.osgeo.org/gdal/ticket/4416

[rouault]

Regina,

I've added Jorge in the CC list of the GDAL ticket.

In case you are not aware of it, I should mention ​http://vbkto.dyndns.org/sdk/ that offer automated daily builds of GDAL and MapServer trunk (compiled with MSVC). AFAICS, it has PostgreSQL support, so it should be ready to use.

As mentionned by Frank, fixes can be pushed in the 1.9 branch. IMHO, I don't think you should not care too much of providing compatibility view in PostGIS to make it work with the current GDAL driver. I think the driver is still a bit young and needs a bit more love, especially since it is targetting a still moving target.

[robe]

Even,

Yap that is the build I've using for my mapserver testing and postgis raster exports and it does have PostGIS raster.

Thanks, Regina

[dustymugs]

Added legacy.sql script to support non-updated 3rd party software in r8643.

[robe]

Bborie,

You probably want to give this file a different name. postgis folder also generates a legacy.sql and since both get installed in the same location, I presume one will overwrite the other.

[dustymugs]

Thanks Regina. Fixed in r8647.

[robe]

Bborie,

I don't think you need to drop the raster_overviews / raster_columns for the legacy case since you are just adding extra columns at the end. You would need to if you are changing data types or dropping columns. With using extensions, I couldn't apply the script because it won't let me drop the views since they are required by postgis extension, however it will happily let me do a CREATE OR REPLACE to add new columns at the end. I know adding columns to views was allowed at least since 8.4 (I think it might have been introduced in 8.3) so we are in safe territory.

[dustymugs]

Regina, you are correct. Fixed in r8653.

[jorgearevalo]

Sorry for my long abscence. I've made some improvements to GDAL driver. It's faster now. So, I'll make it a try.

[robe]

I don't think this is an issue anymore but too lazy to check