0

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?

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
|improve this answer|||||

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.