2

I use ogr2org to filter naturalearth places.shp :

ogr2ogr -f GeoJSON -where "ADM0NAME = 'India' AND POP_MAX > 500000" places.tmp.geo.json ne_10m_populated_places.shp

I would prefer to select the 30 biggest places (via ORDER BY POP_MAX DESC LIMIT 30). In the ORG SQL doc recommands something such :

SELECT * FROM property WHERE class_code = 7 ORDER BY prop_value DESC

I tried both

ogr2ogr -f GeoJSON -where "ADM0NAME = 'India' ORDER BY POP_MAX LIMIT 1,30" places.tmp.geo.json ne_10m_populated_places.shp
ogr2ogr -f GeoJSON -sql "SELECT * FROM ne_10m_populated_places WHERE ADM0NAME = 'India' ORDER BY POP_MAX LIMIT 30" places.tmp.geo.json ne_10m_populated_places.shp

But it doesn't work. What does I do wrong ? How to query right ?

Improve this question
3
  • This one helps a bit
    – Hugolpz
    Nov 16, 2014 at 23:40
  • 1
    This link w3schools.com/sql/sql_top.asp suggests the syntax for LIMIT has only one parameter (LIMIT 30 not LIMIT 1,30), also it mentions the TOP operator which may help (SELECT TOP 30 POP_MAX * FROM ne_10m_populated_places). It may be though that your input SQL does't support all SQL operators.
    – Michael Stimson
    Nov 17, 2014 at 1:02
  • Limit in SQLite can take two parameters sqlite.org/lang_select.html. Then the first parameter means the same than using a separate OFFSET. Thus this query is selecting 30 cities beginning from the second biggests which was, according to the question, not the meaning.
    – user30184
    Nov 17, 2014 at 5:33

2 Answers 2

Reset to default
5

If you have GDAL/OGR 1.10 or later, use -dialect SQLITE for the SQLite SQL dialect, which supports ORDER BY POP_MAX DESC LIMIT 30 in SQL statements.

Improve this answer
4

'Limit' is not implemented with OGR2OGR . However, you can add and calculate a field with an integer on your 30 biggest places and filter on your new field.

this works :

ogr2ogr -f GeoJSON -sql "SELECT * FROM ne_10m_populated_places WHERE ADM0NAME = 'India' ORDER BY POP_MAX" places.tmp.geo.json ne_10m_populated_places.shp

with your new field :

ogr2ogr -f GeoJSON -sql "SELECT * FROM ne_10m_populated_places WHERE ADM0NAME = 'India' and my_new_integer_field < 31 ORDER BY POP_MAX" places.tmp.geo.json ne_10m_populated_places.shp
Improve this answer
3
  • I'am very new to SQL, how could I add this new_integer_field ?
    – Hugolpz
    Nov 17, 2014 at 1:47
  • 1
    Wrong answer if compile with Sqlite support. By adding -dialect SQLITE the LIMIT keyword works...
    – ThomasG77
    Nov 17, 2014 at 3:24
  • This is a great workaround for versions before GDAL/OGR 1.10
    – Mike T
    Nov 17, 2014 at 3:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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