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 ?

Hugolpz
  • 2,653
  • 3
  • 26
  • 51
  • This one helps a bit – Hugolpz Nov 16 '14 at 23:40
  • 1
    This link http://www.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 '14 at 01:02
  • Limit in SQLite can take two parameters http://www.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 '14 at 05:33

2 Answers2

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.

Mike T
  • 42,095
  • 10
  • 126
  • 187
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
Benno
  • 736
  • 7
  • 16