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 ?

  • This one helps a bit – Hugolpz Nov 16 '14 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 '14 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 '14 at 5:33
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.

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
  • I'am very new to SQL, how could I add this new_integer_field ? – Hugolpz Nov 17 '14 at 1:47
  • 1
    Wrong answer if compile with Sqlite support. By adding -dialect SQLITE the LIMIT keyword works... – ThomasG77 Nov 17 '14 at 3:24
  • This is a great workaround for versions before GDAL/OGR 1.10 – Mike T Nov 17 '14 at 3:41

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.