Key Error in Livebook

Elixir Framework Forums Livebook Forum
1

Hello folks.

I am playing around with using a PostGIS enabled database in Livebook (with Maplibre).

I figured out how to add Postgrex types to handle the Geometry type used by PostGIS. However, when trying to reference the results of a query in LiveBook through the following query:

stations = Postgrex.query!(conn, "select * from bc_stns limit 100", [])

then trying to reference a field in the query results:

geo_pts = stations.geom

I get the following error. I am missing something basic, but cannot figure it out.

** (KeyError) key :geom not found in: %Postgrex.Result{
  command: :select,
  columns: ["id", "station_number", "station_name", "prov", "hyd_status",
   "drainage_area_gross", "drainage_area_effect", "geom", "latitude",
   "longitude", "from_year", "to_year", "record_length", "regulated",
   "reg_from", "reg_to", "station_id"],
  rows: [
    [
      4858,
      "07EA001",
      "FINLAY RIVER AT WARE",
      "BC",
      "D",
      11100.0,
      nil,
      %Geo.Point{
        coordinates: {-125.62639, 57.42083},
        srid: 4269,
        properties: %{}
      },
      57.42083,
      -125.62639,
      1960,
      1983,
      23,
      false,
      nil,
      nil,
      4858
    ],

All help is appreciated.

Thanks.
DJ

2

Postgrex.query! returns a single Postgrex.Result value, you’ll need to extract the data from the result’s rows and columns.

1 Like
3

Thanks for the reply.

Yes. I can call result.rows and get returned a map of maps with all the values. I can call result.columns and get a map of column (field) names as strings.

What I want to do is pass a map of the values of a column into another function (the “id” column, or the “geom” column. How do I access that value?

I think I am missing something basic in elixir… but if I try
result.rows.id I get a key error.

Thinking the issue was the keys are strings, not atoms, I tried result.rows["id"]and received the following error:

** (ArgumentError) the Access module supports only keyword lists (with atom keys), got "id"

What am I missing?

Thanks again for the help.
Regards
DJ

4

Okay, so I solve my particular issue. I merged teh column and row values into a map and then worked with that.

Here is the function that did that if anyone else is interested:

stn_map = Enum.map(result.rows, fn row ->
  result.columns
  |> Enum.zip(row)
  # |> Map.new()
  |> Map.new(fn {k, v} -> {String.to_atom(k), v} end)
end)

Thanks again for all help.
DJ

5

Neither of those things are maps.

  • rows is a list of lists of mixed types
  • columns is a list of binaries

A simple way to get something closer to what you want might be:

Enum.map(result.rows, fn row ->
  result.columns
  |> Enum.zip(result.columns, row)
  |> Map.new()
end)

This will give you a list of maps with string keys. HOWEVER, the results may not be what you expect if result.columns has duplicate names (since a Map can only have one value for a key).

Alternatively, consider a library like Ecto that has already taken care of details llke this.

6

cross reply … I was working on that this morning.

Thanks again, al2o3cr

DJ

7

You can also use Table.to_rows(stations) to convert the result to list of maps : )