Set missing pipe network data (age, size) based on nearest neighboring or connecting pipes

Question

I have pipe network data (polylines) with missing age and size attributes. For pipe age, I have 10779 pipes with 1034 of them having NODATA values. For pipe size, I have 10779 pipes with 738 of them having NODATA values.

What tool can I use to set the missing values based on the nearest (or connecting) pipe attribute information?

EDIT 1: In my mind, I feel like I could set up something like this pseudo code:

for each pipe
  if the pipe[age]==NODATA
    find the pipe_nearest
    pipe[age] = pipe_nearest[age]
  if the pipe[size]==NODATA
    find the pipe_nearest
    pipe[size] = pipe_nearest[size]

And then it would be all done. I just can't figure out how to determine the polyline nearest the current polyline.

Edit 2: Attempting to use FelixIP's solution. First off, I got access to another copy of the data set with the pipes clearly connected to each other - I have from and to node fields (FNODE_1, and TNODE_1), and the pipes are clearly snapped to each other. I have copied my initial layer twice, creating has_X and Missing_X layers for both age (YEAR_) and diameter (DIAM) information.

Starting with diameter first, I adjusted the script for the names of my own layers, so it reads:

def fillGap (d,fr):
 mxd = arcpy.mapping.MapDocument("CURRENT")
 lyr=arcpy.mapping.ListLayers(mxd,"Has_Diam")[0]
 q='"TNODE_1"=%s%s%s' %(r"'",fr,"'")
 tbl=arcpy.da.TableToNumPyArray(lyr,"DIAM",q)
 if len(tbl)==0:return d
 return tbl[0][0]

fillGap (!DIAM!,!FNODE_1!)

but for some reason I am getting this error on execution:

I am trying to sort it out, but since I don't completely the Python code that is used in calculating the expression, it is proving to be a little difficult.

They do not. So, I imagine that setting the age or size based on connected pipes is therefore impossible. — traggatmot, Oct 06 '15 at 02:11
You might try spatial join. Create a layer(or just definition query e.g. PipeDiam<>0) and another one (Diam=0). Join to original using ZeroDiam ids. Add another join using <> Pipes — FelixIP, Oct 06 '15 at 02:23
Sometimes no_data pipe connected to 2 or more with data, thus it's good idea to summarise sp.join table to get first and last diameter and use this table afterwards — FelixIP, Oct 06 '15 at 02:25
The correct way to do so is using nodes, because hydraulically disconnected pipes sometimes intersect each other. Creating from and to nodes is truly simple procedure http://gis.stackexchange.com/questions/125090/assign-point-ids-to-respective-start-and-end-attributes-of-a-polyline/125091#125091 — FelixIP, Oct 06 '15 at 02:48
Assuming I don't want to create another layer (as it doesn't have any nodes now), do you have any other recommendations? If I just wanted to replace the NODATA values with the values from the nearest pipe, how would I go about doing that? — traggatmot, Oct 06 '15 at 02:52
Use definition queries. That way you are not creating anything new — FelixIP, Oct 06 '15 at 02:57
FelixIP, I can't really follow what you are saying. If you want to string all of your comments into a coherent answer I would be very grateful. — traggatmot, Oct 06 '15 at 03:24
The query I suggested is for SHAPEFILE ONLY. Query for FGDB is very different, mate. Read the beginning of my answer again — FelixIP, Oct 06 '15 at 23:53
How did you create a chat room? Was it automatic, or did you simply go through the normal process? — traggatmot, Oct 07 '15 at 23:16
At some stage I received this invitation "Let us continue this discussion in chat. " I did nothing — FelixIP, Oct 07 '15 at 23:33

score 1 · Accepted Answer · answered Oct 06 '15 at 20:48

The method below works for pipes with ends snapped to other pipe ends, I am using shapefile as an example.

Add 2 text fields (FROMN, TON), 50 characters long to your table and run this script on field FROMN

def truncate(f, n):
 s = '{}'.format(f)
 i, p, d = s.partition('.')
 return '.'.join([i, (d+'0'*n)[:n]])
def fromto(shp,ft):
 p=shp.firstPoint
 if ft>0:p=shp.lastPoint
 return truncate(p.X,2)+"-"+truncate(p.Y,2)

Using

fromto( !Shape!,0 )

Run the same script on field TON, using

fromto( !Shape!,1 )

Your table will look similar to this.

Note that missing diameter has value of -1 in my example.

Create clones of your pipes layer in table of content using copy, paste. Call one layer MISSING and use definition query

"DIAMETER" = -1

Call one layer AVAILABLE and use definition query

"DIAMETER" <> -1

Your table of content will look like that:

Note number of records in MISSING layer and run this script on field DIAMETER of MISSING layer:

def fillGap (d,fr):
 mxd = arcpy.mapping.MapDocument("CURRENT")
 lyr=arcpy.mapping.ListLayers(mxd,"AVAILABLE")[0]
 q='"TON"=%s%s%s' %(r"'",fr,"'")
 tbl=arcpy.da.TableToNumPyArray(lyr,"DIAMETER",q)
 if len(tbl)==0:return d
 return tbl[0][0]

fillGap (!DIAMETER!,!FROMN!)

Close table and open it again. You’ll notice reduction of records in MISSING table. Run script again and again until no reduction occurs. This is filling gaps in downstream direction. In general it is a good idea to find maximum of IN diameters, sort tbl in script in descending order, but it only applicable for topologically correct network, i.e. line direction = flow direction

What script does is saving you time on joining MISSING and AVAILABLE tables using FROMN and TON fields accordingly.

If after multiple runs you still have records in MISSING layer, you have to fill gaps in upstream direction. To do so, replace query line in script

q='"FROMN"=%s%s%s' %(r"'",fr,"'")

and run it using

fillGap (!DIAMETER!, !ton! )

If your pipes aren’t snapped at the ends, let me know I’ll update my answer.

In the meantime try this:

Output table will look like this

It is the answer to your question:

for each (missing) pipe find nearest pipe with available size

isn’t it?

Very fast and very dirty approach is to join it to MISSING table (PIPE_ID to PIPE_ID) and calculate DIAMETER in MISSING table using DIAMETER_1 And this is equivalent to

if the pipe[size]==NODATA: pipe[size] = pipe_nearest[size]

"The method below works for pipes with ends snapped to other pipe ends" - how do I know if this is true? I am not sure how to determine if the polylines are snapped to one another? — traggatmot, Oct 06 '15 at 22:48
Just give it a go. If you see reduction in number of records in MISSING, they are snapped. Also zoom in in a few junction to check. Also try simple joint of 2 tables using FROMN and TON. Remove join before running the field calculator — FelixIP, Oct 06 '15 at 22:50

Set missing pipe network data (age, size) based on nearest neighboring or connecting pipes

1 Answers1

Linked