1

First, apologies if this has been asked before. I don't really know how/what to search for, and am struggling to just put this question into words.

I'm trying to model aerial utilities in a PostGIS database. I'm getting stuck on how to model what would seem to be a line following sections of multiple other lines and how to represent that.

Most fiber optic cable needs support, and so metal wire rope ("strand") is attached to the poles and then the fiber is attached to that. Other fiber optic cable is designed to support it's own weight and can be directly attached to poles.

Here is a (not to scale!) map that tries to show some of the "test cases" that I need to be able to model:

SVG generated map of poles, strand, fiber, splices cases, slack loops

One of the use cases we want to be able to support is calculating a location on the map when someone wants to find "X meters from the end of fiber cable Y" as some diagnostic/testing equipment locates faults that way. Fiber cables do have footage (or "meterage" - is that a word) printed on them, so that is data that can be collected.

I've tried to figure this out on my own, and here is a screenshot of what I have as a Database schema so far:

screenshot of database diagram

The most basic location related piece seems to be a utility pole, with a location and a few other columns (owner, label/tag, etc).

So normally, a length or piece of metal wire rope is attached to multiple poles, at a (different) height on each pole. So I have a table for pieces of metal wire rope (mostly just an ID column). And I have a table for "strand attachments" that should link that strand to a pole (with a column for the attachment height). I suspect that my strand_attachement table also should have a integer or real to sort the strand attachments for a piece of strand into an order that can be shown as a line (using the location points gathered from the poles).

The place I'm running into trouble is that I have a table with fiber cables. Those cables will follow the stand support lines, BUT they don't necessarily stay attached to only one strand line NOR do they necessarily follow the entire path of a piece of strand line. How should I model & record this in my database? I would assume part of the answer is another many to many table linking strand lines and fiber cables, but how do I represent and store what sections of the strand lines the fiber cable follows? When a cable follows multiple strand lines in its path, how do I record what order it follows the strand line "sections" in (maybe a column representing meters along the fiber cable where it meets the route of the strand section?)

I'm not new to database design, just GIS database design. I'm familiar with foreign keys, many to many vs many to one, etc.

Improve this question
8
  • 1
    Try to attach a screenshot clarifying your question...too much text is a bit confusing...
    – Cyril Mikhalchenko
    Commented Jul 11, 2019 at 7:52
  • @Cyril are you asking for an attempt at a map, or a screenshot of the database model I have so for or something else?
    – Azendale
    Commented Jul 11, 2019 at 14:08
  • The GISness seems like red herring. Are fibre cables associated with poles, just like the strands? And is it known which cables require strand support? If so it seems like those are relevant data. IF order is known or can be inferred from data, that'd be necessary to promote your point data to lines, but it may be the client doesn't need lines...
    – 0mn1
    Commented Jul 11, 2019 at 16:11
  • "Are fiber cables associated with poles, just like the strands?" - I'm trying to figure out if they should be. I would assume so in the case of self supporting fiber. In the case of strand, I would think the fiber is not associated with poles except through the strand it is attached to which is then associated with poles. Yes, we would know what cables need strand support, that would be a flag in the fiber cable table. I'm not sure what you are saying regarding red herring? Are you saying this is a DB schema question, not GIS?
    – Azendale
    Commented Jul 11, 2019 at 20:06
  • 1
    That's my fault, bad wording. I wrote screen shot, and I meant to say shape file. This is a GIS site so it's really difficult to understand what you're talking about unless you provide test data for us, and the desired output of a query. That test data usually looks like either (a) a small shape file, or (b) WKT that we can load that provides an example.
    – Evan Carroll
    Commented Jul 12, 2019 at 0:51

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.