In the last week or so, I’ve had a blast playing around with basic Postgres geometric types to do basic earth distance queries.
From my favorite blog, Datachomp shows how to use radius queries in postgres to find the closest place to get a burrito fix. Since I’ve been on an Elixir kick lately, I figured it was time to contribute back to the open source world by adding first class burrito, err, geometric type support.
I immediately made an Ecto model trying to use the point type in my model:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
Right away, when I ran the commands to retrieve this location from
iex, it gave me some errors:
1 2 3 4 5
Right away, I knew this mission was up to me to get point support into Postgrex.
In this post, I’ll outline how to add type support to Postgres via the Elixir package, postgrex. We will walk through adding the
Point data type in Postgres.
This post will cover:
- How to see how postgres stores its types (built in and composite)
- How postgrex does its type lookups
- Finding the source type – adding it to postgres senders
- Looking up postgres source code for data mapping
- Adding new type
- Adding built in
- Adding encode method
- Adding decode method
How Postgres stores its types
Postgres stores its types in a special system table called
pg_type (docs). It defines a few things about the type:
typelem– how the type is stored – array, or otherwise
typsend– Output conversion function (binary format), or 0 if none
typarray– an oid to another array type that has its send method
How Postgrex does type lookups
Postgrex at it’s core is a simple data adapter into PostgreSQL from Elixir. It’s an awesome library, and if you’re using Ecto, you’re already using it!
First, let’s look at how they are loading most types, by looking them up in the
pg_type table in postgres:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
You can see that under the hood, we’re querying Postgres and asking it for it’s types, so we can do OID lookups and call the appropriate encoder/decoder methods. From here, we can match up our newly added types encoding/decoding methods.
Finding the source type – adding it to postgres senders
Find information about the geometrics category:
SELECT * from pg_type where typcategory = 'G';
We will see the point type has an oid of 600, which is using a send specification of
point_send. Other notable send types for geometries:
point_send lseg_send path_send box_send poly_send line_send circle_send.
Thus, we’ll update the send types in postgrex, located in the
1 2 3 4 5 6
Boom, that gets us the oid to encode/decode off of!
Looking up postgres source code for data mapping
I hopped into the Postgres source code and looked up the struct type for point, found here.
1 2 3 4 5
Great, its just two floats, no big deal.
Adding the point struct
Let’s craft our Postgrex stuct type in
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Adding the encode method
Now since we are sending PostgreSQL binary data, we need to take our data and map it to it’s binary form, via an
However, postgrex is going to do a type look up, based on the types that we used in that query above.
We’ll add the methods to encode, that does some pattern matching to decipher we are getting the correct sender value.
As you can see, we are encoding, when a
TypeInfo tuple is passed with type
point_send! Great, we just pass that to this method to parse out the two floats passed in the binary object:
It just takes those two values, and serializes them down to their binary counterparts.
That now handles the test we’ve got to keep us honest:
1 2 3
This test as promised, takes a
Postgrex.Point type, and encodes it to the binary form, and sends it off to Postgres. How beautiful.
Adding the decode method
Now, when we get binary values from Postgres, we need to map that to our
Point type we’ve created.
Adding the functions to
1 2 3 4 5 6 7 8
The real meat and potatoes is, receiving our binary parameter, mapping its individual segmets as two floats, sized 8 bytes, and then with the pattern matching mapping those to our
Postgrex.Point struct. QED.
And the test:
1 2 3 4
Once I finally figured out what pieces were what, I was able to run and create the point type, its mappings, and its senders it required, easily mapping to the struct in Elixir.
I plan to keep working on postgrex, to add first class support for Postgres geometric types.