Appending Geojson with json field using JQ

Question

I have a project I'm working on that creates a choropleth map with all US county borders loaded from file1.json and filled with a color gradient based on values in file2.json. In previous iterations, I just enter values manually into file1.json, but now I want to expand my map and make it more user-friendly.

file1.json is structured like this:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "GEO_ID": "0500000US06001",
        "STATE": "06",
        "COUNTY": "001",
        "NAME": "Alameda",
        "LSAD": "County",
        "CENSUSAREA": 739.017
      },
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              -122.30936,
              37.77615
            ],
            [
              -122.317215,
              37.778527
            ]
          ]
        ]
      }
    },
    ...
  ]
}

file2.json is structued like this:

[
  {
    "County": "Alameda",
    "Count": 25
  },
  {
    "County": "Amador",
    "Count": 1
  },
  {
    "County": "Butte",
    "Count": 2
  },
  ...
]

I want to create a new file that includes everything from file1.json, but append it to include the relevent Count field based on the County field.

The result would look like this:

[
{
  "type": "Feature",
  "properties": {
    "GEO_ID": "0500000US06001",
    "STATE": "06",
    "COUNTY": "001",
    "NAME": "Alameda",
    "Count": "25",
    "LSAD": "County",
    "CENSUSAREA": 739.017
  },
  "geometry": {
    "type": "Polygon",
    "coordinates": [
      [
        [
          -122.30936,
          37.77615
        ],
        [
          -122.317215,
          37.778527
        ]
      ]
    ]
  }
},
...
]

I'm new to using jq, but I've played around with it enough to get it running in PowerShell.

user_8379597 · Answer

Here is a test.jq file which may help

# utility to create lookup table from array of objects
# k is the name to use as the key
# f is a function to compute the value
#
def obj(k;f): reduce .[] as $o ({}; .[$o[k]] = ($o | f));

# create map from county to count
  ( $file2 | obj("County";.Count) ) as $count

# add .properties.Count to each feature
| .features |= map( .properties.Count = $count[.properties.NAME] )

Example use assuming suitable file1.json and file2.json:

$ jq -M --argfile file2 file2.json -f test.jq file1.json
{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "GEO_ID": "0500000US06001",
        "STATE": "06",
        "COUNTY": "001",
        "NAME": "Alameda",
        "LSAD": "County",
        "CENSUSAREA": 739.017,
        "Count": 25
      },
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              -122.30936,
              37.77615
            ],
            [
              -122.317215,
              37.778527
            ]
          ]
        ]
      }
    }
  ]
}

Try it online!

I notice that "Count" is a string in your example output but it's a number in the sample file2. If you need to convert that to a string you can include a call to tostring. e.g.

.features |= map( .properties.Count = ( $count[.properties.NAME] | tostring ) )

or you could perform the conversion when the lookup table is created, e.g.

  ( $file2 | obj("County"; .Count | tostring ) ) as $count

Appending Geojson with json field using JQ

1 Answers