how to cast integer to float in an ogrinfo sql statement?

Question

I have a shapefile for which I would like to add the values in a column. As the sum exceed the capacity of an integer, I need to cast values to a float. I tried the following:

ogrinfo -sql "SELECT sum(cast(POP as float(10,0))) FROM Settlement_points" Settlement_points.shp

ogrinfo returns error messages like:

ERROR 1: Invalid index : -1
More than 1000 errors or warnings have been reported. No more will be reported from now.
OGRFeature(Settlement_points):0
  SUM_FIELD_1 (Real) = 0

1./ How can I correct my sql statement? 2./ What is actually processing the sql statment in ogrinfo; are there some limits?

Answer 1

Bruno,

I don't know what your workflow is, but you can do it with the ogr tools in two steps:

ogr2ogr -sql "SELECT cast(POP as float(10,0)) FROM Settlement_points" sp_float.shp Settlement_points.shp

ogrinfo -sql "SELECT sum(POP) FROM sp_float" sp_float.shp

You could also use ogr2ogr in your second command to write the values to the shapefile.

Answer 2

I found this on ogr sql page

While the CAST operator can be applied anywhere in an expression, including in a WHERE clause, the detailed control of output field format is only supported if the CAST operator is the "outer most" operators on a field in the field definition list. In other contexts it is still useful to convert between numeric, string and date data types.

So it looks you can't nest it inside the SUM function. OGR SQL also doesn't support using a SELECT inside the FROM which would have been my next pick of style.

You are going to have to write out a new file with the POP column as float or upload you data into something like spatialite/sqlite to give you more power.