2

In ArcMap 10 i'm working in a Personal Geodatabase where the points (see screenshot below) represent the number of each house in the street. Each point has an ObjectID. This ObjectID is an unique value for each "link" of the street to which all the points are related.enter image description here

I have a total of 35,000 points (house numbers), which are all related to 14,000 different links. What I want is to select both the MAX and MIN value in each side of the street. Each side of the street also has a unique ID, which appears in my attribute table as CVEFT. On every link, the CVEFT (for both sides) is different.

This is what my Table looks like: enter image description here

As you can see in the screenshot above, there are several points (NUMEXT) related to the same link (OBJECTID). You can also see that all the points related to a single link may have 2 different street-side codes (CVEFT).

I want to build a SQL query that allows me to select the MIN and MAX values for every Link, for each side of the street.

Does anyone have any idea of how to do this?

Improve this question

1 Answer 1

Reset to default
2

It sounds like you want to do a summary statistics with a min and max of NUMEXT and a case field of CVEFT. This will give you a table with every instance of CVEFT and the minimum and maximum NUMEXT.

Join this table to the PAs_Geodatabase using CVEFT then select where NUMEXT = SumTable.NUMEXT_MAX and NUMEXT = SumTable.NUMEXT_MIN to get the maximum and minimum house numbers for that side of the street.

Improve this answer
5
  • I did what you said and it worked exactly as I wanted, thank you very much!!
    – astro.comma
    Jun 25, 2014 at 21:06
  • One more question, after a while working with the solution that you provided me, I noticed that in some certain cases, the CVEFT (ID for each side of the street) is repeated among several consecutive links. This means that the Summary statistics tool doesn't give me what I expect for these certain cases. How can I run the summary statistics for every Object ID AND each side of the street at the same time to avoid this situation?? Thanks in advance.
    – astro.comma
    Jul 26, 2014 at 22:07
  • If you add a case field of ObjectID then each entry will be unique as ObjectIDs are unique. In this case the ObjectID field of this feature class is ObjectID_1 but I am thinking you want ObjectID. Using the ObjectID of the feature clas will however counteract the statistics and just return the same table again... Case fields are used by the summary statistics tool to produce a summary for each unique combination of the Case field(s), if no case fields are provided it produces one row for the whole table with just the statistics, the more case fields the more unique combinations are produced
    – Michael Stimson
    Jul 27, 2014 at 21:48
  • Actually that's what I did, but it didn't work, maybe i'm doing it wrong. So, if I understand correctly, if I add another Case Field (ObjectID + CVEFT) it should give me a summary statistics for every link and each side of the street regardless of CVEFT value appearing in more than one link? And also, if this is true, does it matter if ObjectID is first or last in the Case Field(s) list?
    – astro.comma
    Jul 28, 2014 at 5:16
  • It doesn't matter which order. With Case Field ObjectID + CVEFT the summary will be for each unique combination of ObjectID and CVEFT list the minimum and maximum NUMEXT.
    – Michael Stimson
    Jul 28, 2014 at 5:19

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.