2

I have a sanitary sewer line feature class where each line has a unique identifier.

I have a table that lists the date a line was cleaned. It may have been cleaned multiple times so I can have multiple dates for each unique identifier.

I also have another table that lists the date a line was inspected. It may have been inspected multiple times so I also have multiple dates for each unique identifier.

I need to select each cleaned date and find an inspection date that lies within a 30 day range from when it was cleaned.

For Example,

From Table_Clean:

Line A is cleaned 1/1/2014
Line A is cleaned 4/1/2014
Line A is cleaned 8/1/2014

From Table_Inspected:

Line A is inspected 1/2/2014
Line A is inspected 4/2/2014
Line A is inspected 11/2/2014

I need to list that the first two were cleaned and inspected within 30 days. The third was cleaned but not inspected within 30 days.

Is this many to many relationship possible? And does anyone have any suggestions on how to symbolize this on a map? Obviously the same exact line feature may be represented twice being cleaned and inspected on different dates.

  • Judging from this and another question you posted about joins, you seem like a candidate for PostGIS or SQL Server...ie. a real relational database. The kind of data joining you're looking to do is difficult / cumbersome in a "GIS"... – DPSSpatial Aug 18 '14 at 18:30
2

The kind of information relationship you're describing is normally handled with a Relate in ArcGIS. If your three tables are set up with a correct relate, you can click on a line and it will then show all matching records from the other two tables. But this is more a display/access of information rather than a use/analyze. In order to solve the specific problem you're addressing, I can think of one (untested) solution right off. Unfortunately it's manual, brute force in a way, and probably not very efficient.

First, you would need to run Make Query Table on your clean and inspect tables with the line ID as the key field. This addresses the many-to-many issue by creating a table record for all possible combinations of the two table's records. Obviously this generates a huge number of records - if a line has six cleans and four inspects, you get 24 records.

Once you have that table, you could do an attribute select with SQL to determine the target combinations - ie all records where clean.date and inspect.date are within 30 days of each other. I don't know the exact syntax off the top of my head, but it would be something like absolute value of datediff(d,clean.date,inspect.date) < 30. This could/would still produce multiple records for the same line - say if it was inspected 17 days before and 23 days after being cleaned. So from that selection, either by exporting to a new table or using another select from the selection, you'd then want to grab just the unique occurences of line ID.

Once you have that selection, you can again export it to a new table (pass) or if you really wanted to combine it all into a massive single SQL statement used to select records from your line table. Since that involves nested queries, everything has to be in a geodatabase for it to have a chance of working though. At any rate, you'd have your list of line IDs that have been cleaned and inspected within 30 days of each other, and you can then either get that as an attribute in the line table or use it to extract the needed lines to symbolize however you want. The whole process could be modified to also target lines cleaned but not inspected, inspected but not cleaned, or neither inspected or cleaned, so you could create a full map showing all four possible conditions.

This is the foundation I'd start with. It would probably have to be refined to a workable process and then turned into a script or model to be run on any kind of regular or repetative basis. Someone else may know of a more elegant solution and if it's in an SDE you may have other options as mapBaker alludes to.

| improve this answer | |
  • I would still appreciate suggestions on this question, however, I have decided to use ArcGIS geoprocessing tools to help process the data ie Delete identical for duplicate lines on same date, MS Access to relate the two tables and create queries, and finally have a table with results to relate back to the feature class in ArcGIS. I still have a display issue. I can only display by which segments are Cleaned and Inspected as an aggregate, but not by month. I've read that time series allows for visualization, however I haven't tested this. – baconANDeggs Aug 19 '14 at 15:13
  • @baconANDeggs Not sure what you're looking for in terms of display. Should all lines cleaned & inspected in the same month be one color/whatever for each month? What if they are within 30 days but in different months? A time series is a sequence of maps. A time animation can show change over time in a video/animated map, but not a hardcopy. I guess I'm having trouble understanding what you're trying to present in the map (what story it should tell). It's sounding like no matter what you'll have to create some new attributes to symbolize on based on combinations of existing attributes. – Chris W Aug 19 '14 at 17:38
  • Yes a single hardcopy is pretty much impossible for this scenario. I could create a map for each month showing which lines were cleaned that month and inspected no later than 30 days. I could also do a hotspot map if certain areas are being cleaned and inspected within 30 days. I have concluded there is no way display this scenario. Thanks for the help! – baconANDeggs Aug 21 '14 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.