[postgis-users] Confused about st_snaptogrid and precision.

[Paul moen]

I create a table as

drop table the_control;
CREATE TABLE the_control
(
   gid integer
)
WITH (
   OIDS=FALSE
);
ALTER TABLE the_control OWNER TO postgres;
SELECT addGeometryColumn('','the_control','the_geom',-1,'POINT',2);
--
-- Data for Name: the_control; Type: TABLE DATA; Schema: public;  
Owner: postgres
--

INSERT INTO the_control VALUES (1,  
'0101000000E266E916EDB53C414A0CEC1CF1792341');
INSERT INTO the_control VALUES (2,  
'0101000000A7098E8438C03C4102C52BDECC792341');
INSERT INTO the_control VALUES (3,  
'01010000002FB1D6F681CA3C413A57A0B0AD792341');
INSERT INTO the_control VALUES (4,  
'0101000000CC3A53AFA6CF3C41C7F208619E792341');
INSERT INTO the_control VALUES (5,  
'01010000009F73C169CBD43C4178C4E23E8F792341');
INSERT INTO the_control VALUES (6,  
'010100000060279054F0D93C41775A479B81792341');
INSERT INTO the_control VALUES (7,  
'01010000003CAD833F15DF3C413F1F542574792341');
INSERT INTO the_control VALUES (8,  
'0101000000C108622C3AE43C41FD6035DD66792341');
INSERT INTO the_control VALUES (9,  
'0101000000BD73A0175FE93C41E36F9DC759792341');
INSERT INTO the_control VALUES (10,  
'0101000000E3DE13BFD5F83C41428CD5CA32792341');
INSERT INTO the_control VALUES (11,  
'010100000069DF144DFDFD3C41A951632926792341');
INSERT INTO the_control VALUES (12,  
'0101000000E3C6DA2B24033D41D3F8BC501B792341');
INSERT INTO the_control VALUES (13,  
'0101000000A9447FEB710D3D4157C0CD2806792341');
INSERT INTO the_control VALUES (14,  
'010100000033105DCC98123D41290485D9FB782341');
INSERT INTO the_control VALUES (15,  
'0101000000B4333505743D3F41999534C3AA772341');
INSERT INTO the_control VALUES (16,  
'01010000005816A018A9663F41A51D97D600782341');
INSERT INTO the_control VALUES (17,  
'0101000000C437881183613F41406780E5F1772341');
INSERT INTO the_control VALUES (18,  
'0101000000FC863181C2473F41633DCD1DB8772341');
INSERT INTO the_control VALUES (19,  
'010100000021B5AF68CE6B3F412580DDBA09782341');
INSERT INTO the_control VALUES (20,  
'010100000096D7450A5D5C3F419DDD8F27E3772341');

SELECT gid,st_asewkt(the_geom) FROM the_control WHERE gid in  
(1,2,3,4,5);

1;"POINT(1881581.08949893 638200.556488403)"
2;"POINT(1884216.51779232 638182.433927685)"
3;"POINT(1886849.96421344 638166.844973303)"
4;"POINT(1888166.68486373 638159.189521395)"
5;"POINT(1889483.41310809 638151.622823848)"

I then try

SELECT * FROM the_control WHERE  
st_equals(st_geomfromewkt('POINT(1881581.08949893  
638200.556488403)'),the_geom);

This returns 0 rows, which is what I would expect since the  
documentation for st_asewkt says 'WKT format does not maintain  
precision so to prevent floating truncation, use ST_AsBinary or  
ST_AsEWKB format for transport.'

If I try the following sql statement, it returns a row which leads me  
to believe that there is more precision in the binary geometry and  
truncation is occuring.
SELECT * FROM the_control WHERE  
st_equals(st_geomfromewkt('POINT(1881581.08949893  
638200.556488403)'),st_geomfromewkt(st_asewkt(the_geom)));

I wanted to reduce the precision of the geometry in the table  
the_control so the st_astext would match.  I tried using st_snaptogrid.

UPDATE the_control SET the_geom=st_snaptogrid(the_geom,.0000001);

I then get the following for
SELECT gid,st_asewkt(the_geom) FROM the_control WHERE gid in  
(1,2,3,4,5);

1;"POINT(1881581.0894989 638200.5564884)"
2;"POINT(1884216.5177923 638182.4339277)"
3;"POINT(1886849.9642134 638166.8449733)"
4;"POINT(1888166.6848637 638159.1895214)"
5;"POINT(1889483.4131081 638151.6228238)"

But I still can't get a row FROM the st_equals.
SELECT * FROM the_control WHERE  
st_equals(st_geomfromewkt('POINT(1881581.0894989  
638200.5564884)'),the_geom);

Shouldn't they be the same, now?

The only way I have found to make the geom the same is to UPDATE the  
table using the st_asewkt.

UPDATE the_control SET  
the_geom=st_geomfromewkt(st_asewkt(st_snaptogrid(the_geom,.0000001)));

Then it returns the row
SELECT * FROM the_control WHERE  
st_equals(st_geomfromewkt('POINT(1881581.0894989  
638200.5564884)'),the_geom);
1;"01010000006166E916EDB53C412D0CEC1CF1792341"

When I use ST_X and ST_Y, I get the x and y that I expect.

SELECT gid, st_X(the_geom), st_Y(the_geom) FROM the_control WHERE gid  
in (1,2,3,4,5);
1;1881581.0894989;638200.5564884
2;1884216.5177923;638182.4339277
3;1886849.9642134;638166.8449733
4;1888166.6848637;638159.1895214
5;1889483.4131081;638151.6228238


Can someone help explain this to me?  How do I see what coordinates  
are actually stored in the geometry?  Is there a way to force the  
precision of the geometry using a contstraint?

SELECT postgis_full_version();
"POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23  
September 2009" LIBXML="2.7.3" USE_STATS"

Thanks,

Paul