1

I have a dataset of 200 points (in shp/f gdb/p gdb format) in ArcGIS.

I am trying to use the MOD() function within a GROUP BY subquery as a definition query on the layer to thin out the data. What I need is to group the data by the 'Name' column, and then filter each 'Name' by a specific amount or percentage. Ideally I would like to use a MOD value of 5 or 10.

The table below is an example.

ID | Time | Name
---|------|-----
01 | 0100 | FOX
02 | 0200 | FOX
03 | 0300 | FOX
04 | 0400 | FOX
05 | 0600 | FOX
-
06 | 0100 | CAT
07 | 0200 | CAT
08 | 0300 | CAT
-
09 | 0100 | DOG
10 | 0200 | DOG
11 | 0300 | DOG
12 | 0400 | DOG
13 | 0500 | DOG
14 | 0600 | DOG
15 | 0700 | DOG

The below MOD works on the entire layer, but there is a risk of it skipping data like CAT where the query only shows rows 1, 5, 10, and 15 (file gdb format).

MOD("ID", 5) = 0

The below also works, but to a lesser degree. I need to thin the data, instead of just using the MIN and MAX values (personal gdb format).

[Time] in (SELECT MAX( [Time] ) 
FROM data_table GROUP BY [Name])

What would be the best way to achieve this? Is there a simpler way of doing it that I have overlooked?

1

You can use ArcMap's [Make Query Table] geoprocessing tool to:

  1. Set input table to your points data.
  2. Select need fields(you must select Shape field if you want get result with feature)
  3. In the where_clause,input as your question([Time] in (SELECT MAX( [Time] ) FROM data_table GROUP BY [Name]))
  4. Set table name if you not need default out table name.
  5. Click Ok button,you should find a new layer that you want thin out.
  6. You can export it to shp/f gdb/p gdb format.

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.