Street Light Layers

[TimothyLiddell]

     Recently I have been assigned to work on a street light layer for the town I am interning for. The town planner office received a spread sheet from a electrical company that has most of the data. This data lacks accuracy compared to the GIS data attribute table for the street light layer.

     Throughout my internship, I have looked at other examples of street light layers and have wondered how the towns had accomplished their layers. When I try to join data, the transaction becomes difficult because the data does not cross over with accuracy. There become many "nulls" and I am left to try again.

Currently we are considering marking down each individual light in question and returning to GIS to remake the map.

My question is, have any of you worked on a layer similar to what I am talking about? If so, how did you accomplish this layer so that the layer was accurate enough to depict the street lights in your local municipality?

If you have any tips, I would very much appreciate them.

Thank you,

T.Liddell

[ChrisDonohue__GISP]

Can you elaborate on some of the issues you encountered?  This will help folks looking at this to provide suggested solutions.  Here's some questions:

• Can you describe the data you have from the Electric company.  Is the Electric company data in GIS?  Or is it a spreadsheet with coordinates?  What format is it in?
• Can you elaborate on the comment This data lacks accuracy compared to the GIS data attribute table for the street light layer.  Is the accuracy issue spatial or attribute?  Or both?  Can you provide more detail?  For example, "many of the ID's don't match between the Town data and the Electric data."
• Which software and what version of the software are you using?
• Are you working in an enterprise geodatabase (SDE)?  File Geodatabase?  Personal Geodatabase?  Shapefile?
• What is the accuracy desired?

Also, if you have screenshots of the data that you can post here, both in terms of attributes and how it looks spatially, that would be of great help.

Chris Donohue, GISP

[TimothyLiddell]

Thank you Chris for your helpful words. I was distracted by a busy office, thus did not go into detail. I apologize for the lack in detail.

I am currently using ARCMAP 10.3 with a personal database. Accuracy desired: I would like for the map to know exactly where each street light is with X,Y coordinates, a matching street name, and the street lights to have accurate pole numbers.

So when I was first introduced to this project, I was handed a spread sheet from the local electric company. This spread sheet held the information for the street lights that I was to use for my mapping. This spread sheet held information such as: "route and route prefix" (route and then the pole within the specified route), "account number" ( which is used for who pays for the light to be on), "Street name" (which is the name of the street and was accompanied by other arbitrary numbers i was not informed about), "light fixture type", "TD" (also arbitrary to the planning office's knowledge), and the last one i should mention is "Town Name" (just the initials). This spread sheet did not include elevation or an X,Y coordinate system for the light poles.

The GIS attribute table is different. This includes FID, LAYER, HANDLE, ELEVATION, X,Y COORDINATE (when clicked on with the information tool), the rest of the attribute table either has information such as that the street lights are a "point" data and other un-useful data because there are at least 10 fields covered in nothing but zeros.

It became difficult using the information from the spread sheet to use for the GIS layer because the two data sets are very inconsistent with each other. When I join the data I had previously made through MS EXCEL with a current layer such as town roads or the street light layer, the information does not fully join (many of the fields read "NULL").

Please allow a delay for photos of the databases since I am out of the office until Thursday.

Thank you again, Geonet users!

[KevinMayall]

It sounds like you were given (1) a geodatabase attribute table (or point feature class) with XY coordinates that was imported from an AutoCAD drawing; and (2) a spreadsheet with very little location data (a route and a street name) but it does have account and fixture type data that you want in the GIS.  The main question is what field are you going to use to link the two together.  Is there any unique ID number for the poles?  If not, you may have to plot the XY data from the attribute table as points, add an ID field to the point attribute table, create ID numbers in the spreadsheet, manually or auto-populate the ID numbers in the spreadsheet, then enter each appropriate ID number in the attribute table.  Then you can join the spreadsheet to the attribute table.

The scary question is how many poles do you have?  Also, how do you know which pole in the spreadsheet is which point in the GIS?  I assume you'd have a route map (drawing) somewhere that you can follow to identify each pole.

[ChadKopplin]

I agree with Kevin, I do not think you will be able to get away from manually adding or correcting information in either of the tables, but this would save you time in the long run because you will not have to reenter all of the info from the spreadsheet into your attribute table manually. 

[TimothyLiddell]

[ChrisDonohue__GISP]

Something I noticed right away that you want to remedy if you can do it, put underscores in your folders and filenames instead of using spaces.  For example "Tim_GIS_Work" instead of "Tim GIS Work".   There are several processes in GIS that get tripped up when a path includes spaces.  When these processes blow up, they usually don't explicitly provide an error message that this is the cause - instead they just blow up with a generic error or no error at all.  If this can remedied, it will save you many future hassles.  Now, I know it is easier said than done in any environment with multiple users.  Non-GIS users don't understand that this is a constraint of ESRI software.  But it is well worth doing, if only to preserve your sanity.   This won't resolve your current issue, but will help prevent future issues.

Chris Donohue, GISP

[TimothyLiddell]

Thank you, Chris. I meant to change that since I have had problems with this in the past.

[TimothyLiddell]

Kevin, the number of street lights that must be identified is 670. I have no idea which pole from the spread sheet is in the attribute table from ARC MAP. Attached are three screen shots that are the map I am working on, the most important parts of my attribute table (the beginning of it basically), and the spread sheet I was handed. I feel as though you are spot on with the AutoCAD drawing. I cannot find files or anything related to how this map data came to be. I will try with the XY data, the only way I can obtain this is through the Identification tool located on the main tool bar. I have been creating ID numbers through MS Excel recently though.

Thank you both very much.

[ChrisDonohue__GISP]

I looked at the images that were posted and I'm not seeing a unique ID field that provides a means to tie the data together, nor coordinates in the table that could be used to create points.  The bottom table does have an Address column, but it only has the street name, not more detailed information.  The Account Number column looked promising at first glance, but then one sees they all have the same account number.

So it looks like it will be a challenge to figure out how the data is associated.  Is there any other data sources, metadata, or other information sources available to help relate the data?  It may even entail consulting with the town's employees who have been around for a while to find out the scoop on this data and what the backstory is.

Chris Donohue, GISP