4

I have .shp layer with field "FIELD_1" in the attribute table (5000 records). I have also Excel file with field "FIELD_2" in the attribute table (1600 records).

I want to select in .shp all 1600 records from Excel file ("FIELD_1" and "FIELD_2" have corresponding attributes). What tool should I use?

1
  • Excel is a spreadsheet application, not a database. If the data is table-like enough to read as a table, then you can do so. Otherwise you would have to reformat it, then use the result. GIS SE is problem-solving site, so you need to tell us what tool you used, and what resulted before we could recommend a fix.
    – Vince
    Feb 9 at 12:44

2 Answers 2

5

You can use an expression.

I have a point layer called random with the field FIELD_1.

I have dragged and dropped an excel table called abcd with a field named Field1.

I want to select all points whose FIELD_1 value is in the Excel table Field1 column.

array_agg is creating an array of all values in the Field1 field of the layer abcd, and array_contains is selecting all rows in the point layer whose FIELD_1 value is in the aggregated array:

array_contains( array:=
aggregate( layer:='abcd', aggregate:='array_agg', expression:="Field1"), value:="FIELD_1")

enter image description here

1
  • 1
    Great. Many thanks it works perfectly !
    – com
    Feb 12 at 6:36
1

It is a one line command with ogrinfo or ogr2ogr.

Take a shapefile that has an id field "shp_id"

enter image description here

Then take an Excel table that has on id field "excel_id"

enter image description here

Excel_ids 10, 11, and 12 appear also in the shp_id of the shapefile.

Then have a look at the JOIN in the GDAL SQLite SQL dialect documentation https://gdal.org/user/sql_sqlite_dialect.html.

Before writing the command we must know what table names GDAL finds from the shapefile and the Excel spreadsheet file. Use ogrinfo for the check.

ogrinfo jointest.shp
INFO: Open of `jointest.shp'
      using driver `ESRI Shapefile' successful.
1: jointest (Point)

ogrinfo jointest.xlsx
INFO: Open of `jointest.xlsx'
      using driver `XLSX' successful.
1: table1 (None)

The table names are "jointest" and "table1".

ogrinfo jointest.shp -dialect SQLite -sql "select shape.*,excel.* from
 jointest shape LEFT JOIN 'jointest.xlsx'.table1 excel
 ON shape.shp_id=excel.excel_id where excel.excel_id is not null"
    INFO: Open of `jointest.shp'
          using driver `ESRI Shapefile' successful.
    
    Layer name: SELECT
    Geometry: Point
    Feature Count: 3
    Extent: (21.701551, 64.282740) - (21.934438, 64.479450)
    Layer SRS WKT:
    (unknown)
    Geometry Column = GEOMETRY
    shp_id: Integer (0.0)
    excel_id: Integer (0.0)
    attr: String (0.0)
    OGRFeature(SELECT):0
      shp_id (Integer) = 10
      excel_id (Integer) = 10
      attr (String) = foo
      POINT (21.7015510367855 64.4794502413075)
    
    OGRFeature(SELECT):1
      shp_id (Integer) = 11
      excel_id (Integer) = 11
      attr (String) = bar
      POINT (21.8485186954945 64.2827396827278)
    
    OGRFeature(SELECT):2
      shp_id (Integer) = 12
      excel_id (Integer) = 12
      attr (String) = baz
      POINT (21.9344382498167 64.4410125459529)

Only the three features with the matching ids were selected.

Notice: I split the ogrinfo command on three lines for making it easier to read. If you try that at home keep the command on a single line.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.