SpatialDB Advisor

Spatial Representation of a Communications Pit

This article is about applying spatial and host database processing to solving a specific, and real, customer requirement: to visualise a communications pit and its contents on a map as a concept called a “Butterfly”.
The solution uses logical connectivity within the data model to create spatial objects on the fly from minimal hard geometry objects.
The solution was first creating using Oracle; then migrated to PostgreSQL/PostGIS.
The migration was quite a painless activity.

Introduction: The “whole” is more powerful than the “part”

Databases are not just great for managing data models, they are also great for creating different representations of one or more piece of data in such a way as to be consumed by multiple clients.
We can create intelligence out of small parts of interrelated data.

Additionally, one of the main strengths of a database is its universality: its data and services are made available to all client applications as long as they can connect and have permissions to query/view data.

This is not to take anything away from the middle tier application where one can implement abstracted data manipulation and publication via web services; but this article is about the processing and representation of data within the database (data tier).

The Main Objects: Paths, Pits, Ports, Lids and Ducts.

This article is about how a pit and and its ports are spatially represented in a Duct and Fibre database that holds communications assets for a customer.

Our main exposure to telecommunications assets is simply the fact that we walk over them every day in the pavements of our cities.

We know that some sort of pipes, wires and other “secret business” resides below each lid.

If you look more closely inside the pit you will see that the cables enter and leave the pit via holes in the walls (or floor) called ports which connect the pit to ducts (eg PVC or galvanized pipes) that carry the cable.

Pits generally come in standard sizes; they don’t have to be square or rectangular, they can be round.

The answer before the description

For those of us too busy to read detailed descriptions, the following pictures shows how we represent a pit and its ports through a construct called a Butterfly.

The images have been captured using the new pgAdmin 4.x’s spatial viewer and also qGIS.
The images show a pit with its sides placed around it like the wings of a butterfly.
In those wings are two ports with a port in the floor of the pit.

pgAdmin4	qGIS

If you want to know how we create this object, keep reading.

Database Objects

The only linear object (LineString) in the database is a path which can be thought of as a trench into which all manner of assets are buried.
Paths have topological properties in that they (along with pits) define the connectivity that links pits to pits, or pits to ports, ports to ducts, cables to ducts, buildings to buildings etc.

Pits

A pit is an in-ground container for accessing fibre optic cables and other assets.
A pit is represented in the database by two things:

1. A (topological node) point on a path line (the gray line in the image above) defining the centre of the pit (the light blue point in the image above);
2. A polygon centred over the point of 1 and which represents the actual shape of the pit.
Most pits are manufactured and so are of a standard sizes.
When a pit is created its standard shape is oriented to the path line and stored as a polygon.
If it is a non-standard pit, its length, breadth, depth are recorded.

Lids

Lids are just that: the lids covering a pit which we walk on without thinking!
They are not spatial objects though they could be, or they could be generated from pit metadata.

Ducts

Ducts can be thought of as PVC pipes that cables run though when in the ground.
Ducts are connected to the ports of pits allowing cables entry and exit to the pit.

Ports

Ports are the entry point of ducts (and hence cables) into pits.
Ports are normally in the walls of a pit though they can be in the floor of the pit.

Ports are not hard spatial objects involved in a physical spatial topology with ducts and other ports.
Port to duct; port to port connectivity is present in the database but it is done via attributes rather than spatial objects.
However, the business has decided that when a pit is drawn on a map, its ports need to be shown within the pit’s walls.

This is done as follows.

The ports in the walls of the pits are defined via linear referencing principles:

1. One of the corners of the pit is selected as the origin point.
2. The rim of the pit (stored as a polygon) is used as reference line.
3. For each port a distance is measured (anti-clockwise) along the rim to it, and a depth is recorded to the centre of the port.
4. The port’s properties are then recorded eg square port’s width/breadth or circular port’s diameter. As port in in the floor has depth recorded as a negative distance; ports in the walls as a positive distance.

Visualisation

The pit polygon is drawn (from hard geometry in Pit table): see black line in the image below.

Port LRS observations are used in a Materialized View to create visualisation objects (squares/circles).
The visualisation is done using the linear references from the pit origin and its rim reference line (actually the origin is the starting point of the pit polygon’s exterior ring, with the referencing line being the exterior ring itself: they are not stored separately) with the ports created as offsets from the pit rim’s reference line.

The flaps are created separately and dynamically using the pit metadata (width/depth/length) and exposed to applications via another Materialized View.

The result is as follows:

I hope this is of interest to someone.

Article Navigation:   Previous