COPY statement REAL vs VARCHAR precision issue

[Samuel Gilbert <samuel.gilbert@xxxxxxxx>]

Hello all,

  I'm using a COPY statement to load data into a PostGIS.  The issue I am 
facing is that if I define fields with the REAL type, the COPY will only 
preserve 4 decimals which is not sufficient for my application.

Here is the commands I'm running and a sample of the results :

CREATE TABLE sites (
   id VARCHAR(9) PRIMARY KEY,
   name VARCHAR(128),
   type VARCHAR(1),
   agency VARCHAR(128),
   status INTEGER,
   napsDesignated BOOLEAN,
   address VARCHAR(128),
   city VARCHAR(128),
   network VARCHAR(128),
   timeZone REAL,
   lat REAL,
   lon REAL,
   elevation REAL
);

COPY sites (
   id,
   name,
   type,
   agency,
   status,
   napsDesignated,
   address,
   city,
   network,
   timeZone,
   lat,
   lon,
   elevation
) FROM
   '/data/sites.csv'
WITH DELIMITER '|' CSV HEADER;

-- Then I use some PostGIS functions to convert the lat/lon into a geometry
-- The issue is independent of the GIS processing.

SELECT lat, lon FROM sites LIMIT 4;
--    lat   |   lon    
-- ---------+----------
--  47.5681 | -52.7022
--    47.56 | -52.7114
--  49.3208 | -57.3972
--  48.9495 | -57.9454
-- (4 rows)

-- If I define the fields as being VARCHAR instead of REAL, I get all
-- the decimals :

CREATE TABLE sites (
   id VARCHAR(9) PRIMARY KEY,
   name VARCHAR(128),
   type VARCHAR(1),
   agency VARCHAR(128),
   status INTEGER,
   napsDesignated BOOLEAN,
   address VARCHAR(128),
   city VARCHAR(128),
   network VARCHAR(128),
   timeZone REAL,
   lat VARCHAR(32),
   lon VARCHAR(32),
   elevation REAL
);

COPY sites (
   id,
   name,
   type,
   agency,
   status,
   napsDesignated,
   address,
   city,
   network,
   timeZone,
   lat,
   lon,
   elevation
) FROM
   '/data/sites.csv'
WITH DELIMITER '|' CSV HEADER;

SELECT lat, lon FROM sites LIMIT 4;
       lat        |        lon        
------------------+-------------------
 47.5680555555556 | -52.7022222222222
 47.56            | -52.7113888888889
 49.3208333333333 | -57.3972222222222
 48.949479        | -57.945387
(4 rows)


-- When I convert the lat/lon to GEOMETRY, the function takes a string as 
input.  Therefore, the precision depends on how the REAL are converted to 
strings. :

UPDATE sites SET
   position = GeomFromEWKT('SRID=4326;POINT(' || lon || ' ' || lat ||' ' || 
elevation || ')');

-- Are the values stored with all the possible precision of a REAL or are they 
stored as displayed?

-- Is it because of the behaviour of the COPY statement?


Thank you for your help!


Samuel

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general