SWIS code help

ChrisHutsko · ‎07-31-2013

I work for a company that has a list of 35,000+ jobs over 30+ years that they would like to reference easily in a GIS database. They are sorted by the New York State SWIS Codes that can be found here: http://gis.ny.gov/coordinationprogram/workgroups/wg_1/related/spcodes/swis.htm

So for example, I would have: Job A, SWIS Index #, XXXXXX, XXXXXX in an excel file, import to ArcGIS, (IE: Job A, 3720, XXXXXX, XXXXXX = Job A is located in Putnam County, in the town of Carmel, with XXXXXX, XXXXXX GPS coordinates), and have all the jobs plotted on a map by SWIS code using the GPS coordinate assigned to it.

That part is easy. The issue I'm having though, is that I would say between 15-20% of our jobs are referenced wrong. So for instance, Job A might have the Carmel/Putnam County SWIS code (3720) but the GPS coordinates might put it somewhere in New York City (easy to locate, since I would have only Putnam County/Carmel viewable and the dot is way off the map) or vice versa, where a job has a New York City SWIS code but plotting in Carmel (very difficult to locate, since it would be a essentially a needle in a hay-stack with thousands of other jobs in Carmel).

Finding the ones that are outside of my selected city/town/village are easy. Finding the harder ones is tough. I can go through manually and do jobs by town/city using the SWIS index but considering I have a dozen+ counties to go through and well over 100+ cities/towns/villages, my boss doesn't find that very economical and doesn't want me to do that, and I agree. I'm not that expierenced enough yet with ArcGIS where I can import the coodinates and be able to search "3720" and have all the "error jobs" pop up, which is why I'm asking for help.

To sum up, I need to find these two errors:
1) Right SWIS code, wrong GPS coordinate
2) Wrong SWIS code, right GPS cooridinate
Easily, but just searching the SWIS code. However if that's not possible I am open to all other suggestions. My end solution is to find the two errors listed about easily

I am also using ArcGIS 9.2 Build 1234, with not many licenses/features to play with. So any help would be much appreciated.

Thank you

ChrisHutsko · ‎08-01-2013

How would you format your excel file of all jobs and polygon file of SWIS codes in order to have a universal attribute table? That way maybe I could do this better

RichardFairhurst · ‎08-01-2013

The errors are 100% on our end involving data entry. We've already discovered a few patterns in errors, and yes the SWIS codes have changed. The list I'm using is the old SWIS codes, the SWIS codes I've used in my examples are the new ones.

I think I'm finally getting it. However, I would have to go through and make a polygon layer for all the SWIS codes that I would be using which is going to take some time, or could I bypass that?

I hope that you are now at least able to select the errors and that is helping you detect the error patterns. By the way, for the correction process I would actually create another set of SWIS code, X Coordinate and Y Coordinate fields called SWIS_NEW X_NEW, and Y_NEW and initially populate them with the original point values for these fields. Then leave the old fields alone and update these new fields. If you decide you made a mistake in updating you can restore the original values from the old fields.

Is there any information, such as the number series of the Job codes, that would help you select entries made before the boundaries changed? If so then you could isolate those jobs and assume the point was correct, but that the SWIS code is out of date needs to be updated based on the current boundary it falls within. Then you could overwrite the SWIS_NEW field with the SWIS values obtained from the boundary join.

If you can select this set of jobs you can also create a rough old boundary shape from the points by creating the Convex Hull shape using the Minimum Bounding Geometry and with the grouping option on the old SWIS number.

I also am confused about the SWIS boundaries you have. Are they the new boundaries or the old boundaries? I would have thought you had the new boundaries, but the old codes in the points. Is that correct? If so, then wouldn't you wan the codes to match the current boundaries? What SWIS code do you want the points to have, new or old?

What format are the new boundaries shown on? Only a paper map? You could scan a paper map and convert it to lines with Photoshop trace and bring it into ArcGIS as a CAD file to clean up. It will be far from perfect, but a good enough start to get the job done.

ChrisHutsko · ‎08-01-2013

I hope that you are now at least able to select the errors and that is helping you detect the error patterns. By the way, for the correction process I would actually create another set of SWIS code, X Coordinate and Y Coordinate fields called SWIS_NEW X_NEW, and Y_NEW and initially populate them with the original point values for these fields. Then leave the old fields alone and update these new fields. If you decide you made a mistake in updating you can restore the original values from the old fields.

Is there any information, such as the number series of the Job codes, that would help you select entries made before the boundaries changed? If so then you could isolate those jobs and assume the point was correct, but that the SWIS code is out of date needs to be updated based on the current boundary it falls within. Then you could overwrite the SWIS_NEW field with the SWIS values obtained from the boundary join.

If you can select this set of jobs you can also create a rough old boundary shape from the points by creating the Convex Hull shape using the Minimum Bounding Geometry and with the grouping option on the old SWIS number.

I also am confused about the SWIS boundaries you have. Are they the new boundaries or the old boundaries? I would have thought you had the new boundaries, but the old codes in the points. Is that correct? If so, then wouldn't you wan the codes to match the current boundaries? What SWIS code do you want the points to have, new or old?

What format are the new boundaries shown on? Only a paper map? You could scan a paper map and convert it to lines with Photoshop trace and bring it into ArcGIS as a CAD file to clean up. It will be far from perfect, but a good enough start to get the job done.

I can't easily convert from the old to the new and I'm still not able to see the errors through spatial join. I have literally no idea what is wrong at this point which is beyond frustrating and is why I think it's best I just start over with a correctly formatted excel file

RichardFairhurst · ‎08-01-2013

How would you format your excel file of all jobs and polygon file of SWIS codes in order to have a universal attribute table? That way maybe I could do this better

I don't know enough about the two to tell you other than that I would import the Excel file to a file geodatabase table and bring the boundaries into a file geodatabase table. I hate working in GIS with shapefiles/dbf tables/Excel tables and love fgdb performance and stability.

As far as attributes it is important to parse the SWIS code into its own field so you can do attribute joins between the points and the boundaries. A screen shot I can read of the table views of each would be nice to see. You may need to clean up field names and reproject the points to match the boundaries.

Screen shot the Spatial Join tool set up you used also please.

ChrisHutsko · ‎08-01-2013

I don't know enough about the two to tell you other than that I would import the Excel file to a file geodatabase table and bring the boundaries into a file geodatabase table. I hate working in GIS with shapefiles/dbf tables/Excel tables and love fgdb performance and stability.

As far as attributes it is important to parse the SWIS code into its own field so you can do attribute joins between the points and the boundaries. A screen shot I can read of the table views of each would be nice to see. You may need to clean up field names and reproject the points to match the boundaries.

Screen shot the Spatial Join tool set up you used also please.

I would have to create a polygon of the boundaries, which would take a while, unless I can just edit the shapefiles I have right now to assign them their SWIS code easily. I've been using state/county/city/town/village shapefiles that I've downloaded from NYS GIS clearing house, and added the SWIS code to the town I was experimenting with. Here's a SS of the table I've been using that I just converted to a geodatabase table. The "OBJECTID *" was added by ESRI, "ID__" was randomly assigned by me, "FILE" is what we have it stored as (important), SWIS_Code is self-explanatory (obviously in this SS these ones are wrong and need to be fixed) and the GPS coordinates with 2 more comment fields.[ATTACH=CONFIG]26396[/ATTACH]

RichardFairhurst · ‎08-01-2013

I would have to create a polygon of the boundaries, which would take a while, unless I can just edit the shapefiles I have right now to assign them their SWIS code easily. I've been using state/county/city/town/village shapefiles that I've downloaded from NYS GIS clearing house, and added the SWIS code to the town I was experimenting with. Here's a SS of the table I've been using that I just converted to a geodatabase table. The "OBJECTID *" was added by ESRI, "ID__" was randomly assigned by me, "FILE" is what we have it stored as (important), SWIS_Code is self-explanatory (obviously in this SS these ones are wrong and need to be fixed) and the GPS coordinates with 2 more comment fields.[ATTACH=CONFIG]26396[/ATTACH]

I actually have no idea what SWIS stands for or how it relates to jurisdictional boundaries. How well do the layers you have downloaded conform to the actual SWIS boundaries you would need to create? For example, can you create an SWIS field in the town layer and assign a SWIS code each town in a reasonable amount of time? If SWIS boundaries are made up of multiple towns you could dissolve the towns to create a new SWIS layer with one polygon per SWIS area. How many SWIS areas do you need to create total?

Lets say that an SWIS is made up of several villages that are next to each other. You can manually select the group of villages that fall within an SWIS boundary and fill them all in with the Attribute editor fairly easily. You only have to type the SWIS number once for all of the selected villages if you use the Attributes dialog correctly by selecting the layer name, not the individual features. Assuming you are dealing with under a 1000 villages to group into a few 100 SWIS boundaries, that is doable.

perfection is not the aim, just good enough to get this task done. Spend more time on the boundaries later, since it looks like this layer is important to you ultimately.

RichardFairhurst · ‎08-01-2013

By the way, when you did the Spatial Join, make sure no points were selected. The Spatial Join would only use selected points, so if you had selected the points inside the boundaries, only they would end up in the output points.

ChrisHutsko · ‎08-01-2013

SWIS codes were set up for tax purposes by New York State to easily identify cities/towns/villages by their own code. A SWIS Code's boundary is the boundary of the city/town/village it is assigned to. The code's boundary won't have multiple city/town/village just the one it is assigned too.

It's a six digit code formated like this: XX XX XX (no spaces). Where the first double X would be the county, the second double X would be the city/town and the last double XX would be the village. If you don't have a village within the city/town technically we only use the first four digits then.

So since I was doing Carmel located in Putnam county, the *OLD* code is 4001, where 40 is Putnam County's code and 01 is Carmel's code inside Putnam County. There is only one 4001 in New York and it's extent is the extent in which Carmel's town boundary ends.

It's generally not important/never used but my boss and I feel it's the best way to solve the issue we have with the two errors I get when I plot all the jobs since we've been assigning every job its appropiate SWIS code since the company started back in the mid 70s.

Also I'm 99.9% sure I had nothing selected when I did the spatial join tool multiple times

RichardFairhurst · ‎08-01-2013

SWIS codes were set up for tax purposes by New York State to easily identify cities/towns/villages by their own code. A SWIS Code's boundary is the boundary of the city/town/village it is assigned to. The code's boundary won't have multiple city/town/village just the one it is assigned too.

It's a six digit code formated like this: XX XX XX (no spaces). Where the first double X would be the county, the second double X would be the city/town and the last double XX would be the village. If you don't have a village within the city/town technically we only use the first four digits then.

So since I was doing Carmel located in Putnam county, the *OLD* code is 4001, where 40 is Putnam County's code and 01 is Carmel's code inside Putnam County. There is only one 4001 in New York and it's extent is the extent in which Carmel's town boundary ends.

It's generally not important/never used but my boss and I feel it's the best way to solve the issue we have with the two errors I get when I plot all the jobs since we've been assigning every job its appropiate SWIS code since the company started back in the mid 70s.

Also I'm 99.9% sure I had nothing selected when I did the spatial join tool multiple times

Got it. So do these imported layers have the codes already? If not would you know what they should be? Is there a pattern to how they got assigned?

If not I would start with the Counties and assign them their County Code in a separate County Code feild. Next just create the City_Town code field in both the Cities and the Towns layers and fill just that part in (do not create a County Code field). For the Villages Layer just assign the Villages part of the Code (do not create a County or City_Town code field).

Use the Integrate tool on each layer against the others to match fuzzy boundaries better to avoid slivers. Spatial Join the Counties to the Cities, Towns layers (County is the Join layer). Spatial Join the Cities and Towns layers that were Spatially Joined to the Counties to the Villages layer (Cities and Towns are the Join Layers).

Merge the Counties, with the Spatial Joined Cities, Towns and Villages into one layer with the Merge Tool. Make sure that there is only one field each with the County code, City_Town code and Village code. If there are two or more fields with these values let me know.

Now Intersect that merged layer with the Integrated Counties layer again. This will cause the layers to cut against each other.

Now Select all Village code values that are null with Select by Attribute. Then select from the current selection using Select by Location against the Integrated Villages layer with a small negative buffer. Delete these polygons.

Now Select all City_Town code values that are null with Select by Attribute. Then select from the current selection using Select by Location against the Integrated Cities layer with a small negative buffer. Delete these polygons.

Now Select all City_Town code values that are null with Select by Attribute. Then select from the current selection using Select by Location against the Integrated Towns layer with a small negative buffer. Delete these polygons.

That should be your best way to create your SWIS layer as far as I know given your available license tools. If you had the ArcInfo/Advanced license you would use Erase and Union instead of most of these last steps.

ChrisHutsko · ‎08-01-2013

Got it. So do these imported layers have the codes already? If not would you know what they should be? Is there a pattern to how they got assigned?

If not I would start with the Counties and assign them their County Code in a separate County Code feild. Next just create the City_Town code field in both the Cities and the Towns layers and fill just that part in (do not create a County Code field). For the Villages Layer just assign the Villages part of the Code (do not create a County or City_Town code field).

Use the Integrate tool on each layer against the others to match fuzzy boundaries better to avoid slivers. Spatial Join the Counties to the Cities, Towns and Villages layers (County is the Join layer). Spatial Join the Cities and Towns layers to the Villages layer (Cities and Towns are the Join Layers).

Merge the Counties, with the Spatial Joined Cities, Towns and Villages into one layer with the Merge Tool. Now Intersect that merged layer with the Integrated Counties layer again. This will cause the layers to cut against each other.

Now Select all Village code values that are null with Select by Attribute. Then select from the current selection using Select by Location against the Integrated Villages layer with a small negative buffer. Delete these polygons.

Now Select all City_Town code values that are null with Select by Attribute. Then select from the current selection using Select by Location against the Integrated Cities layer with a small negative buffer. Delete these polygons.

Now Select all City_Town code values that are null with Select by Attribute. Then select from the current selection using Select by Location against the Integrated Towns layer with a small negative buffer. Delete these polygons.

That should be your best way to create your SWIS layer as far as I know given your available license tools. If you had the ArcInfo/Advanced license you would use Erase and Union instead of most of these last steps.

Alright, I'll do all this first thing tomorrow and see how it comes out

Thanks for all the help, it's much appreciated