[gdal-dev] VRT/CSV to PostgreSQL - Convert Missing/Blank Values to NULL?

[Even Rouault]

Le jeudi 24 janvier 2013 22:54:48, Jason Greenlaw - NOAA Affiliate a écrit :
> When loading into a PostgreSQL/PostGIS database, can the VRT/CSV driver
> convert missing values (for a "Real" field) to NULL instead of defaulting
> to 0.00?
> 
> Example:
> 
> I'm attempting to load a CSV file into PostgreSQL using the following
> simple VRT file:
> 
> --------------------------------------------------------
> <OGRVRTDataSource>
>     <OGRVRTLayer name="sfcdump">
>         <SrcDataSource relativeToVRT="1">sfcdump.csv</SrcDataSource>
>         <GeometryType>wkbPoint</GeometryType>
>         <LayerSRS>WGS84</LayerSRS>
>         <GeometryField encoding="PointFromColumns" x="longitude"
> y="latitude"/>
>         <Field name="stnid" src="stnid" type="String"/>
>         <Field name="latitude" src="latitude" type="Real"/>
>         <Field name="longitude" src="longitude" type="Real"/>
>         <Field name="td" src="td" type="Real"/>
>         <Field name="t" src="t" type="Real"/>
>     </OGRVRTLayer>
> </OGRVRTDataSource>
> --------------------------------------------------------
> 
> Some of the records do not have any value for the "td" column; for example,
> this single-record CSV:
> 
> --------------------------------------------------------
> stnid,lat,lon,td,t
> KOVE,39.490002,-121.620003,,283.0019
> --------------------------------------------------------
> 
> When records such as these are loaded into the database using ogr2ogr, the
> missing value becomes 0.00 rather than NULL.
> 
> Is this expected behavior?  Is there any way to tell the VRT/CSV driver how
> to handle missing/null values for numeric fields?
> 
> Additionally, I've noticed that blank/missing values for String columns get
> imported as empty strings rather than NULL (but perhaps that's the expected
> behavior in this case).

Try creating a sfcdump.csvt file with the following line :

String,Real,Real,Real,Real

This way the CSV driver will have the correct type right at the source and 
will report a null real value, instead of a blank string that the VRT driver 
currently converts into 0.

As far as blank values in CSV being imported as empty string, I'm not sure 
there's a perfect choice on that. You can always update your database table to 
replace empty string by null if that's what fits more your purpose.

> 
> Thanks,
> Jason