[raster] Operator does not exist: raster ~ geometry

[nicklas]

qgis can't show raster layer with latest trunk.

r9240 works

The issue isn't in the C part since I can change to latest binaries so something must have happened with the ~definition on the sql-side

the error is: operator does not exist: raster ~ geometry

[pracine]

Must be since casts became explicit...

Is raster::geometry ~ geometry working?

[nicklas]

yes :-)

It is

[pracine]

I guess it would not be wise to reverse the decision to make the cast explicit. So I guess the QGIS plugin will have to adapt... Bborie, Regina?

[robe]

or you could explicitly create an operator for this. That might be wiser since I think this might be a common one and will affect mapserver as well or we could add to legacy install script file.

[dustymugs]

I concur with robe. Creating the operators is the best solution and possibly the easiest for the long term.

[pracine]

All right. Who takes it?

[dustymugs]

Now that I've looked at the operator code in rtpostgis.sql.in.c, I take back my prior comments. If we're going to add two more operators for ~ (raster ~ geometry and geometry ~ raster), do we need to add operators for all the other raster/geometry operators?

Maybe it is best to have the applications catch up and provide the appropriate IMPLICIT CASTs in legacy.sql

[strk]

I think there's no problem with QGIS catching up, is just a matter of notifying the thing to the appropriate tracker. I never used postgis rasters with qgis so I'm not the best person to look at it.

[strk]

I'd take the chance to notice that if all bounding box operators were defined on an Envelope type all of this would be a non-issue in that all types would implicitly cast to Envelope...

[dustymugs]

Is there an envelope type?

I forget the name of the QGIS PostGIS Raster plugin developer. Pierre, if you remember, can you contact them?

[strk]

Replying to dustymugs:

Is there an envelope type?

No, but we've given that a lot of thoughts. Stuff for 2.1 (maybe)

[pracine]

Replying to dustymugs:

I forget the name of the QGIS PostGIS Raster plugin developer. Pierre, if you remember, can you contact them?

Done. I set it to the new 'third party' component.

[mcayland]

Bear in mind that changing SQL function/operator definitions in a patch release violates our release policy. If you do need these extra operators, then they need go into either 2.0 or 2.1, not 2.0.1.

[pracine]

So Borie shall we make those operators in 2.0 or never? I vote never and add to legacy.

[strk]

Question: is "the bounding box of a raster" open to different interpretations ? I mean, why is it that we don't want to provide an implicit path from RASTER to "bounding box" ? Because that's what we are after when dealing with all operators.

[pracine]

All we need is a set of

CREATE OPERATOR ~ (
    LEFTARG = raster, RIGHTARG = geometry, PROCEDURE = raster_contain,
    COMMUTATOR = '@',
    RESTRICT = contsel, JOIN = contjoinsel
    );

and

CREATE OR REPLACE FUNCTION raster_contain(raster, geometry)
    RETURNS bool
    AS 'select $1::geometry ~ $2'
    LANGUAGE 'SQL' IMMUTABLE STRICT;

right?

[strk]

yeah, it sounds about right. Lots of noise, isnt' it ? If you go there I recommend putting in a separate, but included, SQL file. I really hope we'll be able to replace all this noise with a single set of operators defined for a new Envelope type to which all our spatial types implicitly cast.

[pracine]

Bborie, Regina, if your ok with that I can do it.

So we need the two permutations of (geometry, raster) for all the 12 gist operators, right? 24 new operators and 24 new functions. Good calculation?

[dustymugs]

Correct. I do like strk's idea of putting those operators in a separate file to keep the noise down.

[robe]

Pierre,

I wouldn't bother for all. Really I think ~ and && are the ones most people use and that will fix the third party issues we have. Lets not over clutter because it is much harder to take things out than put them in.

[pracine]

So then just 2 operators time 2 permutations = 4 operators and 4 functions. I'll keep everything in the same file.

[dustymugs]

Sounds good to me!

[strk]

Fine with me. Targetting 2.0 then ?

[pracine]

Yes. Do I have to make regress for them? They are just SQL variants of existing functions and operators after all...

[dustymugs]

You probably should create regression tests for them. I think we have them for all the other operators.

[pracine]

Fixed at r9372. Nicklas, can you try QGIS to see if that fix the problem?

[strk]

Don't forget to update utils/postgis_restore.pl to have the new signatures in the final list! (granted, not an easy task).

Easy way: create and spatially-enable database temp; pg_dump -Fc temp > temp.dump; postgis_restore.pl -v temp.dump 2> log > /dev/null; grep ^{KEEP log | grep raster}

Then copy & paste the output by the end of postgis_restore.pl dropping the "KEEP: " prefix, and sort.

[nicklas]

Now it works like a charm :-)

Thanks!

see screenshoot

I leave it open because of strks last comment.

[nicklas]

Nice picture of postgis raster in qgis

[pracine]

Thanks nick. Could give a try to #1636 as well?

[pracine]

Replying to strk:

Don't forget to update utils/postgis_restore.pl to have the new signatures in the final list! (granted, not an easy task). Easy way: create and spatially-enable database temp; pg_dump -Fc temp > temp.dump; postgis_restore.pl -v temp.dump 2> log > /dev/null; grep ^{KEEP log | grep raster Then copy & paste the output by the end of postgis_restore.pl dropping the "KEEP: " prefix, and sort.}

ok. I did the whole process. I get:

$ grep ^KEEP log | grep raster
KEEP: FUNCTION geometry_raster_contain(geometry, raster)
KEEP: FUNCTION geometry_raster_overlap(geometry, raster)
KEEP: FUNCTION postgis_raster_scripts_installed()
KEEP: FUNCTION raster_geometry_contain(raster, geometry)
KEEP: FUNCTION raster_geometry_overlap(raster, geometry)
KEEP: FUNCTION st_clip(raster, geometry, double precision[], boolean)
KEEP: FUNCTION st_clip(raster, integer, geometry, double precision[], boolean)
KEEP: FUNCTION st_geotransform(raster)
KEEP: FUNCTION st_intersection(raster, raster, double precision[])
KEEP: FUNCTION st_intersection(raster, raster, double precision)
KEEP: FUNCTION st_intersection(raster, raster, text, double precision[])
KEEP: FUNCTION st_intersection(raster, raster, text, double precision)
KEEP: FUNCTION st_intersection(raster, integer, raster, integer, double precision[])
KEEP: FUNCTION st_intersection(raster, integer, raster, integer, double precision)
KEEP: FUNCTION st_intersection(raster, integer, raster, integer, text, double precision[])
KEEP: FUNCTION st_intersection(raster, integer, raster, integer, text, double precision)
KEEP: FUNCTION st_setgeotransform(raster, double precision, double precision, double precision, double precision, double precision, double precision)

If I understand well I should have seen only me new functions. so there seem to be missing more entries in postgis_restore.pl. Right?

Also I saw many lines starting with FUNCION instead of FUNCTION. Looks like a typo.

I add just the one I added...

[pracine]

Fixed at r9389.