Count Distinct with Where clause in OGR

Question

I am looking to run a query in OGR to write back all instances where there is only one record of an attribute. I was thinking that I would use the following:

ogrinfo -dialect sqlite -sql "SELECT COUNT(*) FROM roads AS roadcnt WHERE roadcnt = 1 GROUP BY road_name" roads.shp >report.txt

This query returns an error: near "road_name": syntax error

Answer 1

This is mostly a SQL question that fits better to stackoverfow.

First error in your SQL is that you use "GROUP BY road_name" but you have not selected "road_name".

Another error is that aggregate functions like count() can't generally be used in WHERE but it must be placed into HAVING or into a subquery. See https://stackoverflow.com/questions/6319183/aggregate-function-in-sql-where-clause and http://www.sqlite.org/lang_select.html

Your aim is actually a "find duplicates" query but reversed. Therefore answers in https://stackoverflow.com/questions/6779607/sql-query-to-find-the-duplicate-records suit for you too. I made a test by using a modified version of the third answer. Don't pay attention to count(*)=4, that's only because from my data using count = 1 does not find anything.

ogrinfo -dialect sqlite -sql "select sub_region, count(*) as count
 from states group by sub_region having count(*)=4" states.shp
INFO: Open of `states.shp'
      using driver `ESRI Shapefile' successful.

Layer name: SELECT
Geometry: None
Feature Count: 2
Layer SRS WKT:
(unknown)
SUB_REGION: String (0.0)
count: Integer (0.0)
OGRFeature(SELECT):0
  SUB_REGION (String) = E S Cen
  count (Integer) = 4

OGRFeature(SELECT):1
  SUB_REGION (String) = W S Cen
  count (Integer) = 4

This ogrinfo command should work for you:

ogrinfo -dialect sqlite -sql "SELECT road_name, COUNT(*) FROM roads AS roadcnt GROUP BY road_name HAVING roadcnt=1" roads.shp >report.txt

Answer 2

Bingo. It involved a little witchcraft piping two ogr2ogr instructions together via STDIN/OUT, and you'll need to adapt it to your purposes, but something like this should work:

ogr2ogr -f "GeoJSON" /vsistdout/ -dialect sqlite -sql " SELECT fullname, COUNT(*) as ct FROM ROADS_3857 GROUP BY fullname " "C:\xGIS\Vector\ROADS_3857.shp" | ogr2ogr -f "CSV" -where "ct=1" /vsistdout/ /vsistdin/ >"C:\xGIS\Vector\ROADS_3857_shpreport.txt"

This pumps out a file called "ROADS_3857_shpreport.txt" in the same directory as the initial .shp source. The data in the file looks like this:

FULLNAME,ct .. truncated .. Casterton Dr E,1 Casterton Dr W,1 Castine Dr,1 Castle Hall Ln,1 Castleburg Ln,1 Castledale Rd,1 .. truncated ..

Here's the basic thinking.. the first ogr2ogr call does the work of an inner select, using OGR SQL to create the count value you need (named ct in my example) and impose the GROUP BY, which it pushes to STDOUT (as GeoJSON, more on that in a bit) per the /vsistdout/ flag where the data output path would normally go. Next, the pipe | signals a second ogr2ogr call, which receives the initial result set via STDIN (note /vsistdin/ flag in the data input path) and applies a simple -where argument to return only the records with a count value of "1", once again pushing the final result to STDOUT by setting the output path to /vsistdout/ in order to write it into a .txt file like you implied in your question.

I should probably explain why I used -f "GeoJSON" and -f "CSV" as the two output formats. First, apparently ogr2ogr's willingness to push various data formats over /vsistdout/ is "hard to know", and I didn't find anything solid Googling the question. Additionally, ogr2ogr's willingness to receive various formats over /vsistdin/ is equally mysterious. So refusing to believe this was impossible, I just experimented pushing one format through /vsistdout/ and receiving it over /vsistin/ until I found a combination that worked---that's how I arrived at GeoJSON and CSV, respectively. There wasn't any compelling logic there. :) The ogr data model deep in the guts of all this keeps things intact, so it doesn't really matter which driver formats we use to shuttle the data around in the middle, as long as they play well together over SDTIN/STDOUT.

It's equally important to mention that, if you wanted, you could replace the /vsistout/ in the second ogr2ogr instruction with any export output format ogr2ogr supports, such as shapefile, MySQL, PostGRESql, etc. But to stay true to your initial question, I used /vsistdout/ again in order to push it to the logfile.

That pretty much explains things--it just required approaching it a little differently. And again, I'm sure this will need to be tweaked to fit your exact case as I worked with a similar dataset that had different field names, etc. But I think this represents a good starting point to get where you want to go.