Can't apply Scale Y constraint -- raster2pgsql

[robe]

Bborie,

This may be my misunderstanding, but assuming its a bug

I have this command

raster2pgsql -I -C -s 26986 -t 100x100 bostonaerials2008\*.jpg  -l 4 aerials.boston | psql -U postgres -d gisdb -h localhost -p 5432

data loads but when it gets to applying constraints -- it gives this error:

psql:bostonl4.sql:8028: NOTICE:  Unable to add constraint "enforce_scaley_rast"

I verified my scales are all the same with these statements

SELECT DISTINCT ST_ScaleY(rast) from aerials.boston;

I just get one record -0.3

SELECT DISTINCT ST_ScaleY(rast) from aerials.o_4_boston;

Gives me -1.2

So not sure why it wouldn't be able to apply the constraints.

[Bborie Park]

Your two ST_ScaleY statements are exactly the statements that are used for the scaley constraint.

Are you able to run...

ALTER TABLE aerials.boston ADD CONSTRAINT enforce_scaley_rast CHECK (ST_ScaleY(rast) = -0.3::double precision);

[robe]

Hmm it gives me ERROR: check constraint "enforce_scaley_rast" is violated by some row

I checked my query in psql instead of pgAdmin -- I didn't notice there were extra digits.

So the scaley reads:

 -0.300000000000023

I tried using that but also gives same error. Why distinct would return just 1 row and constraint would have issue is really puzzling. I tried removing the ::double precision to see if maybe it was some floating coversion thing, but gives the same error.

[robe]

hmm maybe there are even more digits that my display is not showing. That's the only thing I can think of.

[Bborie Park]

Can you run

SELECT ST_BandMetadata(rast).scaley FROM aerials.boston;

That is a different code path to getting the scaley values.

[robe]

Yap that must be it precision issue. If I do this it works:

ALTER TABLE aerials.boston ADD CONSTRAINT enforce_scaley_rast CHECK (ST_ScaleY(rast)::numeric(10,8) =   -0.30000000);

And the view seems to be okay with that since it registers the scaley as -0.3

[Bborie Park]

So the question is, should we be specifying a scale for floating point?

[robe]

I think so. I suspect a lot of spatial data is going to have scales that are way out there but are just a result of flaot error accumulation. Especially if they were transformed from another projection. My table does have all the same scale.

I think the best solution is just convert it to numeric in check constraint to a reasonable level of precision. Say numeric(16,10). Beyond that I would say any extra digits are junk.

[Bborie Park]

OK. I'll use numeric(16,10) for those fields that are double precision

[Bborie Park]

Fixed in r8396.