Return all results from join of non-spatial CSV to spatial data using ogr2ogr and SQLite dialect

Question

I am attempting to carry out a simple join of a non-spatial CSV to a GeoJSON data source using ogr2ogr an its built-in SQL capabilities. I would like to use the SQLite dialect to carry out the join. When I try to use the SQLite dialect (-dialect sqlite) I expect all joined rows to be returned, however the actual result is only the first joined row. I demonstrate this and some variants below.

Example

Data

Spatial Data Source: map.geojson

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {"id": 1},
      "geometry": {
        "type": "Point",
        "coordinates": [-78.653, 35.7874]
      }
    },
    {
      "type": "Feature",
      "properties": {"id": 2},
      "geometry": {
        "type": "Point",
        "coordinates": [-78.6298, 35.7873]
      }
    },
    {
      "type": "Feature",
      "properties": {"id": 3},
      "geometry": {
        "type": "Point",
        "coordinates": [-78.6408, 35.7795]
      }
    }
  ]
}

Non-Spatial CSV Data Source: tbl.csv

id,atrr
1,ipsum
2,lorem
3,ipsum

1. Join using -dialect sqlite

Command

ogr2ogr -f geojson \
  -dialect sqlite \
  -sql "select map.* from map join 'tbl.csv'.tbl AS tbl on map.id = tbl.id" \
  /vsistdout/ map.geojson

Result

{
"type": "FeatureCollection",
"name": "SELECT",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "id": 1, "attr": "ipsum" }, "geometry": { "type": "Point", "coordinates": [ -78.653, 35.7874 ] } }
]
}

Only the first row of the join is returned when I expected 3 rows.

2. Join using OGR SQL

However, if I remove the -dialect sqlite parameter and the default OGR SQL dialect, the result is different:

Command

ogr2ogr -f geojson \
  -sql "select map.*, tbl.* from map join 'tbl.csv'.tbl AS tbl on map.id = tbl.id" \
  /vsistdout/ map.geojson

Result

{
"type": "FeatureCollection",
"name": "map",
"features": [
{ "type": "Feature", "properties": { "map.id": 1, "tbl.id": "1", "tbl.attr": "ipsum" }, "geometry": { "type": "Point", "coordinates": [ -78.653, 35.7874 ] } },
{ "type": "Feature", "properties": { "map.id": 2, "tbl.id": "2", "tbl.attr": "lorem" }, "geometry": { "type": "Point", "coordinates": [ -78.6298, 35.7873 ] } },
{ "type": "Feature", "properties": { "map.id": 3, "tbl.id": "3", "tbl.attr": "ipsum" }, "geometry": { "type": "Point", "coordinates": [ -78.6408, 35.7795 ] } }
]
}

3. Join using SQLite dialect and non-spatial table in SQLite database

I also reproduced the CSV as a table (tbl) in a SQLite database (tbl.db) and attempted to carry out the join using the SQLite dialect:

Command

ogr2ogr -f geojson \
  -dialect sqlite \
  -sql "select map.*, tbl.* from map join 'tbl.db'.tbl AS tbl on map.id = tbl.id" \
  /vsistdout/ map.geojson

Result

{
"type": "FeatureCollection",
"name": "SELECT",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "id": 1, "attr": "ipsum" }, "geometry": { "type": "Point", "coordinates": [ -78.653, 35.7874 ] } },
{ "type": "Feature", "properties": { "id": 2, "attr": "lorem" }, "geometry": { "type": "Point", "coordinates": [ -78.6298, 35.7873 ] } },
{ "type": "Feature", "properties": { "id": 3, "attr": "ipsum" }, "geometry": { "type": "Point", "coordinates": [ -78.6408, 35.7795 ] } }
]
}

This result is what I expected when I ran the join on the CSV using the SQLite dialect (#1).

While I have demonstrated two successful methods (#2 & #3) for joining non-spatial data to a spatial source here, I specifically would like to be able to carry out a join of a non-spatial CSV to a spatial data source using the SQLite dialect in ogr2ogr (#1).

How should I proceed?

Answer 1

Try using

ogr2ogr -f geojson \
  -dialect sqlite \
  -sql "select map.*,tbl.atrr from map left join 'tbl.csv'.tbl AS tbl on cast(map.id as text) = tbl.id" \
  /vsistdout/ map.geojson

The casting is nececessary, but it has no sense to apply left join. It's an old bug http://osgeo-org.1560.x6.nabble.com/gdal-dev-ogr2ogr-strange-results-joininig-non-spatial-CSV-to-spatial-data-using-SQLite-dialect-tp5423722p5423725.html

EDIT: I have compiled and build the 2.4 branch and now it works using

ogr2ogr -f geojson \
  -dialect sqlite \
  -sql "select map.*,tbl.atrr from map join 'tbl.csv'.tbl AS tbl on map.id = tbl.id" \
  /vsistdout/ map.geojson