DEV Community

Lionel Marco
Lionel Marco

Posted on

Working with Geospatial data in Elixir-Postgrex, Mimic Node-PgPromise behaviour.

#elixir #postgres

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

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

Table of Contents

1) Introduction

Query Helpers

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

 # 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)
Enter fullscreen mode Exit fullscreen mode

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:

#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"}
Enter fullscreen mode Exit fullscreen mode

Obtain columns as maps

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

# iex>  sql = "Select 1 as id, "Jhon" as name" Postgrex.query!(sql,[])
Enter fullscreen mode Exit fullscreen mode

Will be get:

%Postgrex.Result{ columns: ["id", "name"], command: :select, connection_id: 5849, messages: [], num_rows: 1, rows: [[1, "Jhon"]] }
Enter fullscreen mode Exit fullscreen mode

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

# iex>  sql = "Select 1 as id, 'Jhon' as name;" DB.one!(sql,%{})
Enter fullscreen mode Exit fullscreen mode

Will give me:

# iex>  {:ok, %{"id" => 1, "name" => "Jhon"}}
Enter fullscreen mode Exit fullscreen mode

2) Working with geospatial data

Some examples with geometries:

Select:

 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
Enter fullscreen mode Exit fullscreen mode

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

[%{"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" } } } ]
Enter fullscreen mode Exit fullscreen mode

Insert a Polygon:

Load geometries to the database:

 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
Enter fullscreen mode Exit fullscreen mode

Will return :

 %{status: "success"}
Enter fullscreen mode Exit fullscreen mode

Update some points:

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
Enter fullscreen mode Exit fullscreen mode

After running this code the output will return :

 Rows affected : 3
Enter fullscreen mode Exit fullscreen mode

3) Configure and Start

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

#file: config/config.exs import Config config :postgrex, config: [ name: :mydb, hostname: "localhost", username: "user", password: "pasw", database: "gis", port: 5432 ]
Enter fullscreen mode Exit fullscreen mode

Start the process

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
Enter fullscreen mode Exit fullscreen mode

4) Postgrex Query Helpers 

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
Enter fullscreen mode Exit fullscreen mode

5) Named Parameters

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

Basically receive:

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

And transform to:

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

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.

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
Enter fullscreen mode Exit fullscreen mode

Top comments (0)