The Beauty of Extensibility

After restoring a backup of an electric utility dataset that I had worked on sometime in the past, I got a strange error when editing in QGIS:

Layer infrastructure : PostGIS error while changing geometry values: ERROR: function st_pointiswhichn(geometry, geometry) does not exist
     LINE 1: vert_num = st_pointiswhichn(line_info.g, OLD.g)
      ^
     HINT: No function matches the given name and argument types. You might need to add explicit type casts.
     QUERY: vert_num = st_pointiswhichn(line_info.g, OLD.g)
     CONTEXT: PL/pgSQL function kingston21.move_associated_vertices() line 11 at assignment

Not ideal after doing a restore, but good error messages are helpful. Especially if you are at a loss as to what the problem could be, as I was, since I had not used this particular dataset for a couple of months. Looking at the error message, the issue is line #11 in the kingston21.move_associated_vertices function. Giving functions clear names can make life very easy.

A little Background

I had mentioned that this is an electric utility dataset which involves electric distribution lines, electric utility poles and other electrical entities. In real life, lines are suspended on poles (unless they run underground). So, in the kingston21 dataset, the same is true. Poles can also be a home for other types of electric distribution equipment such as transformers, capacitors and switches. If a distribution engineer is making changes or planning out a network, the editing workflow may involve moving a pole and then having to switch layers and move any associated objects attached to the pole. If this pole happens to have a secondary line and a primary line and a transformer, this would mean making four different edits. The kingston21.move_associated_vertices function eases that burden by identifying objects that share a vertex with the pole and moving them at the same time.

Why PostgreSQL Shines

I can do this because PostgreSQL was built from the start with extensibility in mind. In the abstract for their “Design of POSTGRES” paper, the Turing award winning Stonebreaker and his colleague Rowe had goals #2 & #3 as:

• “provide user extendibility for data types, operators and access methods”
• “provide facilities for active databases (i.e., alerters and triggers) and inferencing including forward- and backward-chaining”.

And this is done in the database by the database. One could certainly write some python code in QGIS to achieve the same end result of moving associated vertices across different layers. But, imagine if you decided to give distribution engineers the ability to edit on the web? You would potentially have to now write a JS function to end up with the same result. Having the function attached to your database means that whatever client you are using will benefit from that same function. If you have spoken to me for any length of time, you will know that I am a proponent of “let the database do the work”.

A little detective work

So, going back to troubleshooting the trigger function, I see that the problematic line is line 11 and there is a PostGIS function, st_pointiswhichn, that is missing. Now, for those of you not aware, PostGIS is a PostgreSQL extension (again, more extensibility) that adds spatial functionality to a database. It essentially turns your database into a GIS. PostGIS comes with hundreds of spatial functions. And somehow, this one function was missing. My initial thoughts were that I didn’t reinstall PostGIS before restoring the data, but that wouldn’t make sense because the tables would need the spatial data types and if that were the case the restore would have complained about these types not being found, this did not happen.

Dropping into psql and doing a \df st_* confirmed that PostGIS and its plethora of functions were there. So…at this point I am flummoxed. I venture over to the PostGIS website to see if the st_pointiswhichn function has been deprecated (unlikely, but…). While it is loading, I drop into the project folder with the postgresql dump file and notice a README.md. I started doing good documentation late in life. I open the file and that’s when I realize that if you write documentation, you should also read it. So my very detailed README.md lists the various functions that I created to help make editing this particular dataset easy and among the list of functions is st_pointiswhichn. Odd, why would I put a PostGIS function in there. And then it hit me: The st_pointiswhichn function is not a PostGIS function, it is a function that I created, much like the trigger function. Despite the myriad of functions that come with PostGIS, there is no function that returns a vertex of a line given a point. So, I wrote it myself. The issue here was that whereas all my other functions lived in the kingston21 schema, this was in the public schema and my backup script only backed up the kingston21 schema. I suspect I also named it st_pointiswhichn because I realized it could be valuable outside the context of the electric utility dataset.

The realization

Then all the memories around this time come flooding back: me scanning the PostGIS docs for a similar function, being perplexed that it does not exist, because in my mind, this must be something that someone in the two decades of PostGIS’ existence must have wanted. But, evidently, that was not the case. Either, I was the first human being using PostGIS to need to get the vertex number of a line given a point, or, (more likely) anyone who needed this, just wrote their own user defined function. Giving the function the st_ prefix was what most likely led to me not picking up that it wasn’t a PostGIS builtin function.

The function is quite simple, here it is:

CREATE OR REPLACE FUNCTION public.st_pointiswhichn(linestring geometry, point geometry)
 RETURNS bigint LANGUAGE sql RETURN (
  WITH _ AS (
    SELECT (st_dumppoints(linestring)).* 
    )
  SELECT _.path[1] AS path FROM _ WHERE st_equals(_.geom, point)
  )

The st_dumppoints in the CTE deconstucts the linestring into a set of points, it returns a path array and a geometry. Then st_equals checks each dumped point to see if it is the same as the supplied point and returns the vertex from the path where this is true.

After recreating the function, editing the dataset went as expected.

The takeaway

So, the moral of this story is that if your backups are not tested, then you don’t have backups. If this was a production dataset I would definitely be in some hot water. I immediately amended the backup script to dump this additional function. On the list of things to do after returning to a project after a brief hiatus, I’ll also include reading the README.md file.

I’m serious, but I kid. Yes backups are important, but this post is just showing that because of the foresight of Stonebreaker and Rowe, when it comes to PostgreSQL, you don’t have to wait for new functionality, you can build it yourself.