2

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

  • This is not really any GIS question. What you use in GROUP BY must also be selected select road_name, count(*) as roadcnt from roads group by road_name – user30184 Nov 4 '15 at 18:40
  • ^ I disagree @user30184. The context added by ogrinfo and OGR SQL makes this community particularly suitable for providing assistance. – elrobis Nov 4 '15 at 19:03
  • thank you @elrobis, I would agree. This query works perfectly in SQLite and Oracle, but not when I run it in OGR. – Ryan Garnett Nov 4 '15 at 19:25
  • Ryan, I've tried to get this concept working without luck. I'm concerned it may be impossible in ogr sql. As stated in this answer respecting GROUP BY clauses, ..if OGR needs to fall back to OGR SQL (like when using shapefiles) then no. That said, I'm also not finding a way to perform an inner SELECT with ogr sql, so my next best idea would be to try to pipe the results from one ogrinfo/ogr_sql output into a subsequent ogrinfo/ogr_sql instruction. – elrobis Nov 4 '15 at 20:08
  • Query select sub_region, count(*) as count from states where count=1 group by sub_region gives me error "misuse of aggregate count()`. Do you mean that it works for you? – user30184 Nov 4 '15 at 20:43
2

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
  • Yes that works for me, but that is not what I am looking for. I would like to have, as stated in the question, "where there is only one record of an attribute". So for example, John Cres = 1. I was assuming my query was the best way to complete this, but I am unsure based on the syntax error. – Ryan Garnett Nov 4 '15 at 20:55
  • See my edited answer and test the edited ogr2ogr command. – user30184 Nov 4 '15 at 22:56
  • Very cool. Note to self: HAVING :D – elrobis Nov 5 '15 at 14:11
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.

  • So if I am understanding correctly, it is a two staged process. Thank you for the help, I will give this a try. Thanks! – Ryan Garnett Nov 4 '15 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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