Go to content Go to navigation Go to search

Featured Articles:

    What sorts of Services do I offer?
    What people say about me or my work...
    Book: "Applying And Extending Oracle Spatial"
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison

Newest Article:

Spatial Representation of a Communications Pit

Saturday November 17 2018 at 11:05

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.

Pit Lids in Pavement

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

Spaghetti in Pit

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.

Ports with Cables in Pit

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

A round manhole in the raw!

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
Butterfly with port in two walls and floor. Three Port Pit Butterfly

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.

Two paths


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.

A pit


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 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 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.


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:

Pit with three ports and referencing

I hope this is of interest to someone.

My Services

I offer the following services to customers:

  1. Integration of GeoSpatial within IT
    Advice and guidance on how to fully integrate geospatial data and processing needs within existing, pure, IT environments (using existing GIS software or without purchasing proprietary GIS software);
  2. Independent Solutions Advice and Sanity Checking
    Solutions not Software ;
    How to efficiently and effectively solve a business requirement via existing technology;
    Does what you have been offered in a tender really stack up? Or do you want independent quality assurance of a tender document before submitting it to a customer?
    When it is best to use Open Source as against Commercial off the shelf (COTS) and vice versa?
    Is buzzword-compliant (SOA, BPEL, J2EE, .NET, WebServices…) technology really needed to solve a business problem? If so, what is the best way to achieve this?
  3. Analyzing ROI of your current GIS software technology
    Matching business problems to existing software investment. Are you getting value from your software investment? How to improve that ROI;
  4. Systems Review
    Does the solution you have been provided actually match your requirements?;
  5. Database Benchmarking:
    How well are you using your Oracle Spatial/SQL Server Spatial/PostGIS database?
    How good is your databases documentation/metadata?
    Is the integrity and quality of the data stored in your databases as good as you think?
    Is your use of Oracle (Spatial), SQL Server Spatial consistent with industry best-practice?
    Get advice on some simple “rules” to follow to improve your database!
  6. Database Consulting through design, test, build and configuration services
    Ensuring your databases reflect your needs via:
    1. Data Quality Rules Documentation, Conformance Testing, Correction and Certification;
    2. Data Conversion, Migration, Loading;
    3. Database Design;
    4. Coding of appropriate in-Database processing:
      Via design and coding services in:
      1. PL/SQL – Oracle,
      2. PL/pgSQL – PostgreSQL,
      3. T-SQLSQL Server,
      4. Java,
      5. C#.
    5. Database Performance Tuning;
    6. Spatially enabling Data Warehouses and Processes;
    7. ArcSDE Configuration and Tuning.
  7. SpatialDB Training Courses and Staff Mentoring
    Spatial SQL Training on your datasets;
    After Training support
  8. Free Software
  9. Commercial Support
    Free PL/SQL packages
    (bug fixes, extensions and new functionality).

I have over 25 years experience with database technologies from mainframes through to personal computers. I have 25 years Oracle use and am recognised by the Oracle Spatial development team as one of the early adopters of Oracle’s Sdo_Geometry data type. In 2011 that experience was recognised when Oracle awarded me the “2011 Oracle Spatial Excellence Award for Education and Research”.

I was on the original beta program for SQL Server 2008 Spatial (aka “Katmai”). While most of my solutions work centres on Oracle, I now have quite a few years experience designing, programming, tuning and working with SQL Server. I make available over 200 TSQL functions to SQL Server Spatial users across multiple versions.

I also have the necessary PostgreSQL/PostGIS and MySQL experience to help you implement the best spatial database solution possible.

I have (next to the original designer and programmer) the longest experience of using ArcSDE in the industry and can help you get the best out of this piece of ESRI software and show you how to make it work with other spatial software (esp open source). Also, with the new ESRI Spatial Type for Oracle, PostgreSQL and SQL Server, I am in a unique position to help you plan your migrations from SDEBINARY to either the ESRI Spatial Type, SQL Server spatial or Oracle’s Sdo_Geometry.

My experience is not limited just to databases. I have managed “GIS” infrastructure management teams, designed and built systems integrating spatial processing within business systems in the areas of mapping, editing, analysis, GPS field data capture etc.

Finally, as a geospatial professional with IT and Oracle (Certified Professional SQL 10gR2; Certified Associate DBA 10gR2) qualifications and experience, I can help guide the uptake of geospatial technologies into business’s IT infrastructures.

Let me help you cost-effectively spatially-enable your enterprise.