Opened 12 years ago

Closed 12 years ago

#1373 closed defect (fixed)

Can't apply Scale Y constraint -- raster2pgsql

Reported by: robe Owned by: pracine
Priority: medium Milestone: PostGIS 2.0.0
Component: raster Version: master
Keywords: Cc:

Description

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.

Change History (9)

comment:1 by Bborie Park, 12 years ago

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);

comment:2 by robe, 12 years ago

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.

comment:3 by robe, 12 years ago

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

comment:4 by Bborie Park, 12 years ago

Can you run

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

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

comment:5 by robe, 12 years ago

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

comment:6 by Bborie Park, 12 years ago

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

comment:7 by robe, 12 years ago

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.

comment:8 by Bborie Park, 12 years ago

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

comment:9 by Bborie Park, 12 years ago

Resolution: fixed
Status: newclosed

Fixed in r8396.

Note: See TracTickets for help on using tickets.