Working with Geospatial data in Elixir-Postgrex, Mimic Node-PgPromise behaviour.
Tags: elixir, postgres
Published: 2022-08-30 19:22:12

A client required me to rewrite a Geospatial REST API from Node-Express to Elixir-Cowboy.The Front-end was wrote in React, it interchange json with the API. The geospatial information is stored in Postgis, it is a spatial database extension for Postgres. 

The communication between the front-end and the back-end is using json, so does not need to load or unload any binary object from the database. Just only need the string representation of geometries objects, the classis WKT [Well Know Text Representation](https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry)


The data manager of Elixir is Ecto, but working with it would imply create many views and some time, work with binaries.The app have 5 modules where every one have more or less 10 sql queries, so rewrite almost 50 well writed and optimized queries, wasn't on my mind.

Searching the web I found an interesting article, that was my main source of inspiration : [Elixir-Without-Ecto](https://www.openmymind.net/Elixir-Without-Ecto/)


## Table of Contents
* [1-Introduction](#intro)
* [2-Working with geospatial data](#geospatial)
* [3-Configure and Start](#configure)
* [4-Postgrex Query Helpers](#query)
* [5-Named Parameters](#named)


## 1) Introduction<a name="intro"></a> 

### Query Helpers

The Back-end use PgPromise, so the option was create some function helpers to mimic his behaviour: 

```elixir

# For queries that must to return only 1 row,
# raise if are 0 or more than 1
row = DB.one!(sql, data_map)

# For queries that must to return only 1 row,
# return error if are 0 or more than 1
{status, row} = DB.one(sql, data_map)

# For queries that return 0 or multiple rows
rows = DB.any!(sql, data_map)

# Get affected rows for update, insert, delete, 
affected = DB.affected!(sql, values)

# For update, insert, delete, store procedures, functions
# return a map with operation status "fail" or "succes"
status =  DB.status(sql, values)

```

### Working with named parameters

Another Pg-Promise very useful thing is work with named parameters stored in json.
With it we avoid to use list values with reference numbers: $1, $2.
In elixir Json is stored in maps, named parameters let us write things like the next:

```elixir
#iex>
sql= "INSERT into books(name,author,pages)
            VALUES($(name),$(author),$(pages));"
      
values= %{ "name"=>"Lord of the Rings", "author"=>"John Ronald Reuel Tolkien", "pages"=>500}

DB.status(sql, values)

%{status: "success"}


```

### Obtain columns as maps

Postgrex return every row as a list with values, if we run the next query:

```elixir
# iex> 
sql = "Select 1 as id, "Jhon" as name"

Postgrex.query!(sql,[])

```
Will be get:

```elixir
%Postgrex.Result{
  columns: ["id", "name"],
  command: :select,
  connection_id: 5849,
  messages: [],
  num_rows: 1,
  rows: [[1, "Jhon"]]
}
```

But sometimes is more usefull to have a list of maps, where every column have an access key:

```elixir
# iex> 
sql = "Select 1 as id, 'Jhon' as name;"

DB.one!(sql,%{})

```
Will give me:

```elixir
# iex> 
{:ok, %{"id" => 1, "name" => "Jhon"}}

```

## 2) Working with geospatial data<a name="geospatial"></a>

Some examples with geometries:

**Select:**

```elixir

 def selectField(id) do

    sql ="select  json_build_object(
                'id', id::text,
                'name', name,
                'centroid', st_AsGeoJSON(st_centroid(boundary))::json ,
                'area', to_char(ST_Area(boundary::geography)/10000, 'FM999999999.00') || 'ha',
                'boundary', st_AsGeoJSON(boundary)::json ) as field FROM fields WHERE id=$(id);"

    DB.one!(sql, %{"id"=>id})

  end         

```  
The result will be a list of rows where every row is a map ready to encode and send:

```elixir
[%{"field"=>
            %{
              "id" => "14",
              "name" => "Field name here",
              "area" => "28.18ha",
              "boundary" => %{
                "coordinates" => [
                  [
                    [-63.69112612, -31.394299467],
                    [-63.70054825, -31.393974797],         
                    [-63.69087755, -31.39156248],
                    [-63.69094669, -31.39416452],
                    [-63.69112612, -31.394299467]
                  ]
                ],
                "type" => "Polygon"
              },
              "centroid" => %{
                "coordinates" => [-63.695616434, -31.392755962],
                "type" => "Point"
              }    
            }
  }          
]

```  

**Insert a Polygon:**

Load geometries to the database:


```elixir
 def insertField() do

    json_polygon= %{
                    "coordinates" => [
                      [
                        [-63.694052, -31.389509],
                        [-63.694352, -31.395883],
                        [-63.691005, -31.396286],
                        [-63.690061, -31.389839],
                        [-63.694052, -31.389509]
                      ]
                    ],
                    "type" => "Polygon"
                  }

    values= %{"name"=>"Soy Beans", "polygon"=>json_polygon}

    sql = "INSERT into fields(name,boundary)
            VALUES($(name),ST_GeomFromGeoJSON($(polygon)));"

    DB.status(sql, values)
  end
```  
Will return :

```elixir

%{status: "success"}


```  


**Update some points:**

```elixir
def updateLocations() do

 values = %{ "id"=>[10,11,12], "long"=> -63.694052, "lat"=> -31.389509, "srid"=>4326}
 sql= "UPDATE locations SET point=ST_SetSRID( ST_Point($(lon),$(lat)),$(srid)) WHERE id=ANY($(id));"
 IO.inspect(DB.affected(sql, values), label: "Rows affected")
end

```  

After running this code the output will return : 

```elixir

Rows affected : 3

```  



## 3) Configure and Start<a name="configure"></a>


The first thing to do is configure the access to Postgres and then start the process.

Create file `config/config.exs` where the 
the name key is really important, it will be used has reference to the running process.


**Config file**
```elixir
#file: config/config.exs
import Config
config  :postgrex, config: [
                            name: :mydb, 
                            hostname: "localhost", 
                            username: "user",
                            password: "pasw", 
                            database: "gis",
                            port: 5432
                            ]

```


**Start the process**
```elixir
defmodule AppGis.Application do
  use Application

  @impl true
  def start(_type, _args) do
    dbconfig = Application.fetch_env!(:postgrex, :config)

    children = [
      {Postgrex, dbconfig},
      Another process...
    ]

    opts = [strategy: :one_for_one, name: Appgis.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

```
## 4) Postgrex Query Helpers <a name="query"></a>

```elixir
defmodule DB do
 
  defmodule DBError do
   
    defexception message: "", plug_status: 422
  end

  # For arguments values inside map
  def query!(sql, values_map) when is_map(values_map) do
    {sql, values} = replace(sql, values_map)
    Postgrex.query!(:mydb, sql, values)
  end

  # For arguments values in a list
  def query!(sql, values) do
    Postgrex.query!(:mydb, sql, values)
  end

  # For queries that return 0 or multiple rows
  def any!(sql, values) do
    case query!(sql, values) do
      %{rows: []} ->
        nil

      %{rows: rows, columns: columns} ->
        Enum.map(rows, fn row -> mapify(columns, row) end)

      _ ->
        raise DBError, message: "returned multiple rows"
    end
  end

  # For queries that must to return only 1 row,
  # raise if are 0 or more than 1
  def one!(sql, values) do
    case query!(sql, values) do
      %{rows: []} ->
        raise(DBError, message: "returned 0 rows")

      %{rows: [row], columns: columns} ->
        mapify(columns, row)

      _ ->
        raise DBError, message: "returned multiple rows"
       
    end
  end

  # For queries that must to return only 1 row,
  # return error if are 0 or more than 1
  def one(sql, values) do
    case query!(sql, values) do
      %{rows: []} -> {:error, nil}
      %{rows: [row], columns: columns} -> {:ok, mapify(columns, row)}
      _ -> {:error, nil}
    end
  end

  # For update, insert, delete
  def affected!(sql, values), do: query!(sql, values).num_rows

  # For update, insert, delete
  # with operation status
  def status(sql, values) do
    case query!(sql, values).num_rows do
      0 -> %{status: "fail"}
      _ -> %{status: "success"}
    end
  end

  # Tranform from [[cols...]] and [[values...]] to [%{"col"=>value}]
  defp mapify(columns, row) do
    columns
    |> Enum.zip(row)
    |> Map.new()
  end


end


```


## 5) Named Parameters <a name="named"></a>


In order to work with named parameters, a function is writted.

Basically receive:

```elixir
values = %{"firstName"=>"Jhon", "phone"=>"(555) 456 789 522"}
sql= "INSERT into clients(name,phone) VALUES($(name),($(phone));"

```

And transform to:

```elixir
values = ["Jhon","(555) 456 789 522"]
sql= "INSERT into clients(name,phone) VALUES($1,$2);"


```


A thing to have in count is that Postgrex need to be feeded with te right amount of arguments, not one more, not one less.
If a value is a map, it is encoded to json.


```elixir
def replace(sql, mapvalues) do
  # Pattern to find keys $(keyname)
  pattern = ~r/\$\(([^)]+)\)/
  # Get unique keys  
  keys = Enum.uniq(Enum.map(Regex.scan(pattern, sql), fn [_ | [tail]] -> tail end))
  # Build values list
  values = Enum.reverse(Enum.reduce(keys, [], fn k, lk -> [encode(Map.get(mapvalues, k)) | lk] end))

  # Replace key names with their associate index
  sql =
    String.replace(sql, pattern, fn match ->
      key = String.replace(match, ["$", "(", ")", " "], "")
      idx = Enum.find_index(keys, fn x -> x == key end) + 1
      # IO.puts("$#{idx}")
      "$#{idx}"
    end)  
  {sql, values}
end


def encode(v) when is_map(v) do
  {:ok, str} = Jason.encode(v)
  str
end

def encode(v), do: v
end

```