Using PostGIS functions in ArcGIS Desktop for Layer Definition Query?

Question

I am connecting ArcGIS Desktop 10.2 to Postgis 2.0.3 with PostgreSQL 9.2.4. No SDE. I can load data with no problems and access the data in ArcMap. However I can't seem to use PostGIS functions within a layer definition query. For example:

ST_DWithin(location, ST_GeomFromText('POINT(-97.5 35.5)', 4326), 0.01);

Which works perfectly from PostgreSQL returns the error:

One or more layers failed to draw: Report: Failed to parse the where clause.

This is strange, as if the exact same query is used to define the underlying source query for the layer it works fine. Am I missing something here. I used to have this working for Oracle no problem. What doesn't it like about PostgreSQL's query?

Answer 1

I'm not sure what you mean by "exact same query is used to define the underlying source query for the layer", but it led me to look into Definition Queries specifically. I've run into issues in the past where something that works in one part of the software doesn't work in another.

The ESRI help page for Definition Queries says:

Definition queries are unavailable if you are working with a table or event layer (x,y or route) data source that does not have an ObjectID (OID) field. This will be the case with text files (.txt files); Excel data (.xls files); and OLE DB data, such as unregistered Oracle files accessed through OLE DB.

As I suspected when I saw you said "no SDE", that database connection falls into that category. And as you found, the solution is to create a suitable (unique and not null) OID field in your database to enable use of Definition Queries.

Answer 2

What you can do is to manufacture an OID column on the fly when you carry out the query. The example below will return the results of the buffer with two columns one the name of the city associated with the buffer and the id field.

SELECT row_number() over(order by geom)::integer as oid,
ST_BUFFER(geom, 100000) AS geom,
name
FROM mygis.public.cities As cities