ogrinfo sql example

Question

I'm having trouble learning how to query my shapefiles for certain features. I have a shapefile that looks something like this when I summarize it using the following command:

ogrinfo -al -geom=OFF tl_2013_us_uac10.shp

OGRFeature(tl_2013_us_uac10):3599
  UACE10 (String) = 31450
  GEOID10 (String) = 31450
  NAME10 (String) = Franklin--Highland Lake, NJ
  NAMELSAD10 (String) = Franklin--Highland Lake, NJ Urban Cluster
  LSAD10 (String) = 76
  MTFCC10 (String) = G3500
  UATYP10 (String) = C
  FUNCSTAT10 (String) = S
  ALAND10 (Real) = 84345960
  AWATER10 (Real) = 3320730
  INTPTLAT10 (String) = +41.1696387
  INTPTLON10 (String) = -074.5456645

OGRFeature(tl_2013_us_uac10):3600
  UACE10 (String) = 16171
  GEOID10 (String) = 16171
  NAME10 (String) = Chester, NY
  NAMELSAD10 (String) = Chester, NY Urban Cluster
  LSAD10 (String) = 76
  MTFCC10 (String) = G3500
  UATYP10 (String) = C
  FUNCSTAT10 (String) = S
  ALAND10 (Real) = 9360429
  AWATER10 (Real) = 3420
  INTPTLAT10 (String) = +41.3627073
  INTPTLON10 (String) = -074.2780785

How can I query for features that have NJ in their Name10 field, for example? I tried something like this:

ogrinfo -al -sql "SELECT * FROM 'tl_2013_us_uac10' WHERE UACE10 = 16171" tl_2013_us_uac10.shp

but I get this error:

INFO: Open of `tl_2013_us_uac10.shp'
      using driver `ESRI Shapefile' successful.
ERROR 1: Type mismatch or improper type of arguments to = operator.

Can anyone help me out or point me to a good resource?

Answer 1

These are the features that contain the NJ string in their Name10 field:

ogrinfo -al -where "NAME10 LIKE '%NY'" tl_2013_us_uac10.shp

where the percent symbol (%) matches any number of characters.

Instead, your example query should be:

ogrinfo -al -sql "SELECT * FROM tl_2013_us_uac10 WHERE UACE10 = '16171'" tl_2013_us_uac10.shp

or, more simply:

ogrinfo -al -where "UACE10 = '16171'" tl_2013_us_uac10.shp

The better resource I know about OGR SQL is its official documentation page. Another useful resource for more advanced purpose is the SQLite SQL dialect that extends the OGR SQL including the Spatialite SQL functions among other things.