Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for your 2 lines solutions! Nathan W (below) explains why it is not possible to do it in a single command. –  Bruno von Paris Dec 26 '12 at 20:35
add comment

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.

share|improve this answer
    
Nicely done Nathan! I just figured the same thing out. The cast stuff works nicely. His errors were 'Invalid indes : -1' –  DavidF Dec 20 '12 at 22:21
    
Yeah I get the same error if I nest the CAST in the SUM –  Nathan W Dec 20 '12 at 22:31
    
Many thanks for your detail answer! I should have search more on ogr pages... –  Bruno von Paris Dec 26 '12 at 20:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.