I use ogr2ogr to import an .sqlite file into my PostgreSQL database. The new database table I get has a column of type "character varying". However I want it be of type integer as all the values in the particular column are integer numbers with the exception of some empty fields. How do I do this?
2 Answers
If you want to do it in the ogr2ogr -sql parameter then you want to use the cast(<expr> AS <type-name>) syntax - outlined in the 'CAST expressions' section here.
eg:
SELECT cast(a.my_text_field AS int), a.geometry FROM mytable a etc
If you want to do it in Postgres then you can use the same syntax or the :: syntax.
If this isn't working post an example of your data and the code you've been trying.
-
just to add, the
::cast shorthand syntax isn't recognised by theogr2ogrinbuilt SQL interpreter. Nor does it recognise any functions from extensions. For example,ST_GeomFromTextPostGIS function will result in aERROR 1: Undefined function 'ST_GeomFromText' used.error.– Theo FCommented Apr 17 at 16:01 -
I often need to add -dialect sqlite. E.g.: ogrinfo -sql "select sum(bijeen) totaalbijeen from vbo_nl_pc_5_10" vbo_nl_pc_5_10.dbf does NOT work, gives: ERROR 1: Column Summary Function 'SUM' found in an inappropriate context. ogrinfo -dialect sqlite -sql "select cast(sum(bijeen) as integer) totaalbijeen from vbo_nl_pc_5 _10" vbo_nl_pc_5_10.dbf works fine.– JanCommented May 16 at 9:24
I couldn't get this casting to work (I'm using the OCI driver to pull from an Oracle data source and write to a .csv), but used a workaround: a CASE statement
The problem is that the data I'm pulling from contain many nulls in these fields, and in some cases, one of the fields is 100% nulls.
Ex.1 Tried to cast, but it didn't work when writing to CSV. I think this is the limitation of a CSV file itself (I don't think it can store field types at all).
select CAST(s.slope AS int) as IAPP_slope, CAST(s.aspect AS int) as IAPP_aspect, CAST(s.elevation AS int) as IAPP_elev, from invasive_plant_site s
Ex. 2 So instead, I use case statements to just write dummy int values as outputs when I encounter a null value, to ensure I have a clear-cut integer value in that field. I use 9999 as it's not an actual value I would encounter for any of these fields:
select case when s.slope is null then 9999 else s.slope end as IAPP_slope, -- force int field types via dummy value 9999 case when s.aspect is null then 9999 else s.aspect end as IAPP_aspect, case when s.elevation is null then 9999 else s.slope end as IAPP_elev, from invasive_plant_site s
ogrinfo -al -so my_db.sqlite?String (0.0)to be more precise.gdal-config --versiongives2.2.2.