OGR and OSM: Get lat, lon of a point as field for SQL-Query

Question

I am trying to process a .osm file in python with ogr, but I have problems accessing the lat and lon attributes of the point layer - they are always null.

I want to do spatial filtering to find the closest neighbor in a certain distance, e.g. like:

sql_lyr = osmData.ExecuteSQL("SELECT * FROM points WHERE lat BETWEEN ... AND ... AND long BETWEEN ... AND ...") 

(Inspired by the answer to this question: Python: how to speed up spatial search (nearest point)? )

When I call DumpReadable on a feature in the point layer, I get the following:

OGRFeature(points):392633
  osm_id (String) = 392633
  name (String) = (null)
  ...
  lon (String) = (null)
  lat (String) = (null)
  all_tags (String) = (null)
  POINT (16.3540923 48.2041024)

So I see that the lat and lon are read in for the POINT Geometry, but I have no idea how to use this information in a SQL query.

Is there any way I can access the lat and lon via the POINT Geometry in a query, or can I do any further configuration to the OSMdriver so that lat and lon are read in as fields?

I modified osmconf.ini to enable all_tags, to report all nodes, I tried enabling all common attributes and I also tried adding lat and long to the list of keys like this (even though I think this is pointless, because lat long are attributes of the node tag itself and not really keys...):

# keys to report as OGR fields attributes=name,barrier,highway,ref,address,is_in,place,man_made,lon,lat

Answer 1

If you want to do it with SQL you can use the SQLite dialect http://www.gdal.org/ogr_sql_sqlite.html and if your GDAL is compiled with the most recent SpatiaLite version then you can use all these functions: https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

Here is a test query that works for me with ogrinfo

ogrinfo -dialect sqlite -sql "select * from points where (ST_X(geometry) between 10.1 and 10.2) AND (ST_Y(geometry) between 53.4 and 53.5)" hamburg-latest.osm.pbf

However, GDAL/OGR has a native method for spatial filters and I believe that layer.SetSpatialFilter would work for you as in the example in http://pcjericks.github.io/py-gdalogr-cookbook/vector_layers.html#spatial-filter