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?