I have a large database in ArcGIS 10.2.2, with over 2 500 000 point vectors. Each point has a unique number code. I want to spatially subset my data, so that I can extract only points with a specific codes. I tried to use an expression build by me, but in the Definition Query, i am limited to a certain number of characters. I want to subset points from over 200 000 unique codes.

The codes are numeric. Here is a sample code:

IdRPL in (1607130,1607131,1609481,1609482, ....., 1709809) 
  • Are these codes numeric? Please edit your question and show us an example of what these codes look like.
    – Hornbydd
    Apr 1 '15 at 10:43
  • 1
    How many codes will you have inside IN? There are also some underlying DBMS limits, e.g. Oracle allows up to 1000 codes. Apr 1 '15 at 11:23
  • 1
    ...and what type of geodatabase you store your data in? File geodb or DMBMS (which one?) Apr 1 '15 at 11:25
  • You're trying to create a def query based on unique id. How are you coming up with your subset list? Is it stored in another file or something? Other common attributes? It might be possible to use a nested query if both the list and feature class are in a geodatabase, so instead of saying 'in' and listing the values you say 'in' and point to another table. Otherwise some select by locations or attributes (or manual?) and maybe create layer from selection.
    – Chris W
    Apr 2 '15 at 0:13
  • My Geodatabase is a File one. The unique Id's are stored in a excel file.
    – Litwos
    Apr 2 '15 at 6:13

If you have the unique IDs in an Excel file, you can join this file to your points feature class and then query on the joined file. See Esri's information on joining Excel files to feature classes.

Say that your feature class has a field IdRPL, and your Excel file has IDs in a field called UniqueID.

  1. Right-click on your feature class and choose Joins and Relates/ Join...
  2. Choose Join attributes from a table
  3. Choose the field in this layer that the join will be based on: IdRPL
  4. Choose the table to join to this layer: (select your Excel file)
  5. Choose the field in the table to base the join on: UniqueID (or whatever column name you used in Excel)
  6. Join Options: Keep only matching records

In the sample below, the "matching records" would be only those five records that don't show 'null' in the Excel file. The three null records do not exist in Excel, so they will not display in the layer.

enter image description here


As an alternative to the Join mentioned by Andy, and fleshing out my comment suggestion, you can still use a definition query. First you'd need to bring the Excel spreadsheet into the same geodatabase as a table. You'd also need to make sure it has an OID field - which may or may not be the uniqueid field itself. Then your definition query would be something like "uniqueid" IN (SELECT "uniqueid" FROM excel_table_in_geodatabase). I've used this method myself a few times.


ArcMap Definition Query edit box allows exactly 30000 characters. I'm afraid DBMS won't be able to cope with such long statements anyway; also see the comment about limits of IN statement. I suggest you to transform your data to the form adding field calculated from IdRPL that would give you a simple definition query.


Looking at your example and listening to the responses of others it would seem that your approach of filtering by code will not work as you are exceeding the limits of the underlying database.

Without knowing any more about your data here is one approach:

Create another field that groups your data. You could do a spatial join against some administrative area so that your point data is now encoded with admin name. Your definition query could then use that to filter out your points.

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.