Latest Contributions by RichardFairhurst

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. ... View more

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. ... View more

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. ... View more

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. ... View more

Actually, thinking about your problem I wonder whether the SWIS boundaries changed during that 30+ years.  If they did that could account for the errors.  Are you observing errors only on older job data?  If so, then perhaps researching the previous boundaries might make it clear that the points are correctly located and just need to be updated to the current SWIS codes. If the errors are spread out over all years, then the problem is likely to be either data entry input errors (transposing digits or misreading hand written numbers that can appear similar like 5/6 or 8/9 or 2/7 for example), or perhaps some other pattern.  Discovering the patterns to the errors is key to selecting and fixing them quickly, and probably does not require examining each individual subarea to sort them out. ... View more

Even if the Spatial Join did not include the SWIS points that fell outside the SWIS bounaries, they can be easily selected by selecting all of the points inside the boundary and then switching the selection.  Then Select from the current Selection where the SWIS value is supposed to be inside your area.  A join on the SWIS code to the boundaries can tell you that. Anyway, no programming is needed, just some willingness to work it out in a logical manner.  You already have the abilities to find all of the errors at this stage.   Now you just need to tell me more about what information you have that would help you decide which aspect of the point data you should believe: the SWIS code or the point location.  How would you decide that?  I am assuming there has to be some other information connected with the point that would tell you more about where it should end up.  If not, then what basis would you use to decide whether or not to trust the code or the point location for any given example error?  Walk me through the information available to you that you could check to make that decision. You should be findng this helpful and giving me at least up vote somewhere for my responses.  They are on target for your problem.  Click the up arrow on the right hand side of one of my posts to give me a point, assuming you agree. ... View more

I still must be doing it wrong, because all I'm getting are the points within the SWIS code boundary and not the points outside the SWIS code area with the SWIS code (wrong GPS coordinates). I get what you're saying now, but I'm sure there's a attribute error with my shapefiles that's messing it up. Regardless, this way is still time consuming because I would have to go through each city/town/village to get it completed. My boss really just wants it simple: Plug in a code, and it spits out all the errors. Only way I could do that is if I had GIS programming knowledge which I don't... Another issue I'm having is that my points are NAD27 and the shapefiles are NAD83 but yet they're projecting together which makes no sense, but that's for another time haha You need to reproject the points to match the boundary or the other way around first.  Probably there is an error in the extent allowed by the output due to the different projections. Screen shot the Spatial Join tool screen inputs after the project is completed if the problem is not solved.  I assure you all of the points will be preserved in the output with the settings I showed. Your thoughts about not being able to select all of the errors is wrong also.  All you need is a single query: SWIS <> SWIS_1 OR (Not SWIS is Null and SWIS_1 is Null) OR (SWIS is Null and Not SWIS_1 is Null) This will select all of the values that are in error.  However, you need to determine if a pattern or corroborating data value exists that would tell you which SWIS value is correct and which is incorrect.  Do you have any other independent field on the point that would indicate what the SWIS value should be, such as a ZIP code or City Name?  If you did that could be incorporated into the selection logic to separate the error into 2 categories:  wrong SWIS, wrong point. Assuming you think the point location is more correct, calculate the original table SWIS value to equal the SWIS_1 value from the boundaries.  If you are sure the point is wrong, calculate the X/Y coordinates from the centroid of the SWIS boundary into the coordinates of the point layer.  Export the table view of the points to a table and then use the Make X/Y Event Layer to see all the points appear in their correct location. If there is no simple way or pattern to help you decide between what is right and what is wrong, then you boss has to learn to live with reality and disappointment.  Quality control at the start would have been the only way to save time.  Screwed up data is always a time suck if there is no pattern to the error and each error has to be inspected individually.  In severe cases, the data has to be discarded, because the value of the data is not worth the time it would take to clean up the mess.  Garbage in/garbage out exempts no one from its effects, not even your boss. ... View more

Here is an example of the Spatial Join settings that would get you what you want.  The important thing is that the points are the target, the SWIS boundary polygons are the join and that the Keep All Target Features option is checked.  I suggest also using the JOIN_ONE_TO_MANY option as well for your problem and just using the default INTERSECT option.  The output will be all of the original points with the attributes of the original points themselves and the attributes of the intersected SWIS boundaries combined in one point feature class. ... View more

If I'm reading you/doing it right, this only gives me the jobs outside the SWIS code. I still need the jobs that fall within a SWIS code that shouldn't be there You are reading me wrong.  If you follow my suggestion the output will give you all points, whether a SWIS boundary exist or not.  In fact if you did not use the keep all features option the exact opposite of your assumption would occur.  In that case only points that fell inside SWIS areas would be in the output and you would only be able to detect the jobs that should fall outside.  Since you want all of the jobs check the keep all features option.  Just make sure the points are the first layer and the SWIS boundaries are the second layer for the join.  The output will be the full set of original points. I think you also should check the One to many option.  Then if no SWIS area exists its Join FID will be -1, meaning the point is outside the area.  The SWIS value of the join will be NULL, which is also easy to select for SWIS_1 IS NULL Otherwise the point will have a real SWIS join FID and falls inside the area. You call also find jobs that fall within an SWIS boundary area, but where the SWIS of the point spatial location not match the expected SWIS of the points attributes, meaning that one or the other is incorrect despite both the attribute and the point itself falling inside the SWIS area. ... View more

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 Do you have a polygon layer with your SWIS boundaries? If so you can Spatial Join the points to the SWIS boundaries.  Make sure the option to keep all target features is checked.   The output would have the original point SWIS code and the actual SWIS code location the point fell within.  You can then easily select where the two SWIS code fields do not match.  Determining whether the SWIS code is wrong or the coordinate is wrong would have to be determined by some other field data, but assuming there is another field that could indicate whether a given SWIS code was correct or not, you could check the two SWIS codes against that field. ... View more

Ok,  I'm having issues with what seems like a simple task.  I 'm trying to select the latest record from multiple facilities in a Table.  For example... the latest inspection record for City electrical transformers.  Ive tried the Sort Tool and Summarize tool - both are not working within the Model I'm building.  I was excited to see the expression for the select  by attributes tool below.  If anyone knows how to get this expression to work in Arc10, I would really be interested to hear how!  Ultimately I'll create a Feature Class Layer from the selection. [mydate] in (SELECT max( [mydate] ) FROM table_name GROUP BY [groupID]) Regards. It is not supported.  Subqueries cannot preserve groups and queries have to be to an external table, not the table itself.  So if you could get the maximum date for an external table, it would be a single date for the entire table, not several dates based on groupings by another field.  The help for subqueries says this about summery queries, which are called scalar subqueries: Scalar subqueries with comparison operators. A scalar subquery returns a single value. I also do not know if MAX works with dates anyway, with the reworking of SQL 92 done by ESRI.  I think only First works with dates.  Max may be limited to Numeric fields.  I have asked the person who programs the SQL underlying file geodatabases to rewrite the SQL reference to show what SQL 92 methods are supported, and he told me he is working on it, but the subquery limitations will most likely remain. I agree it is a pain that you cannot do summary statistics on a date. There are two work arounds.  You can convert your date to a numberic field in the form "YYYYMMDD" and get the maximum value by group for that number using the Summary Statistics tool.  Using the Field calculator you can convert the number back to a date using python or VB Script parsing and/or formatting to do real date logic calculations.  A terrible work around, but fewest steps. The other way involves more steps but it would preserves the actual date values: 1. Use the Sort tool and sort by GroupID and Date descending.  Subsort on any other fields to make them be the first record in the GroupID and Date set if you want a specific record (like the maximum UniqueID for a given GroupID and Date). I will call the output SortTable. 2. Summarize by GroupID again, but this time get the Min value of the ObjectID field for each GroupID.  I will call the output table SummaryTable 3.  Add and Attribute index to the MIN_OBJECTID field. 4  Join that output to the previous summary on the ObecjtID and MIN_ObjectID and Select records where: NOT SummaryTable.MIN_OBJECTID_Min IS NULL 5.  Break the join. 6.  Export the selection to a new table. 7.  Add an Attribute Index on the exported table's GroupID, Date, and any other field you want to use in a join or select.  You now have a table containing the latest date for every GroupId in your original data which can be joined back to the original data to do date selections or calculations. I hope this helps. (Note, ArcMap crashed when I attempted the selection the way I originally wrote this post.  So I rewrote the join and SQL and added a step to get create an Attribute Index on each table. ... View more

So much time has passed on this thread and I now sit in 10.1 with joined field names that will only 'Maintain Fully...' Is there a practical use for this? Why is it the default? The joins are still a mess and now Model Builder likes to lock itself. There's only one instance of ArcMap open but if I don't manually refresh the table views (by opening the tables from the TOC) it'll lock up on me. Could someone please tell me why you'd want your field name to be something like [TEMP_PIVOT_GROUPBYFID.FID_Buildings] instead of [FID_Buildings]? I'm at a loss as to how anyone who'd be doing a join in Model Builder wouldn't know the source tables nor could look at the inputs to Model Builder to figure them out. Why is this the default since 10? What big ESRI customer asked for this change and why does it persist? Enough ranting. Cheers to those that feel my pain. It is the default because I join tables with identical field names all the time and the only way to tell which field I am referencing is by using the full name.  Also, ObjectID, which I also use in queries, is identically named in both tables.  Context would not make the name clear to anyone without the fully qualified name.  Beside, computers know nothing of context and suck at ambiguity, unless a programmer, who has no idea what your join actually looks like, programs it with intelligence that matches your logic if you are allowed to input ambiguous data.  Every time such logic has to be added the speed of the program decreases, sometimes significantly. Joins work wonderfully for me, but probably because I only use file geodatabases or SDE and not shapefiles or Excel.  The Model Builder Environment can set outputs to be without the fully qualified name, which is necessary if you insist on being a dinosaur and using shapefiles.  It is found in the Fields Environment settings in Model Builder.  If you change that setting using the Geoprocessing > Environments menu that setting will become your default. ... View more

I have a polyline and want to construct lines perpendicular to the polyline with a set spacing (make a line every n-meters). The polyline in question is not a straight line but it should still be possible to generate a line that crosses the polyline at a right angle. I tried a work-around using 'construct parallel' and 'construct points' but it did not work as I had hoped. Am using 10.1. Using Linear Referencing you can create two point events at each location along the line with a positive and negative side offset of the same distance.  Then use those two points with the Create Line From Points tool.  Excel ould probably build the table fairly quickly for one set of points with an even spacing down the line, then copy and past that and alter the side offset by making the negative value of the previous point offsets. ... View more

You can calculate the length of the line in miles by opening the attribute table and adding a field "MILES" and then right click the miles field and click "calculate geometry" select "miles" as units and then it will populate the length for you. Then you can create a route using "length" as your measure source. (I don't think you will need a from and to field but if you get an error, just add a field "FROMMILES" or something like that and use the field calculator to populate it with 0's (your TO field is your "MILES" field)) There might be a tool that can do this for you but manually you can use the  "identify route location" on the Linear Referencing toolbar to get the milepost value at each point and use calculate field (or a model builder tool with input parameters) to add it to your milepost field in your point layer. NOTE: If your river layer contains drastic changed in elevation you will need to find the geodesic distance, I do this by overlaying my lines on a DEM but there might be an easier way. The Create Route tool can create routes in the method mentioned above.  Your rivers must be correctly oriented with flow to work correctly using that method and you will have some decisions to make at branches, unless each branch constitutes its own route.  Fully branched river networks will create issues because they are non-monotonic (measures do not increase with each successive vertex of the route).  In a branching route network for a branch you would typically add the start of flow measure obtained from the mainline river at the upstream connection as the From measure and the To measure would normally be the branch distance plus that initial measure value. ... View more