3

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?

  • 1
    This may be related gis.stackexchange.com/questions/68288/… but uses Oracle Spatial rather than PostGIS. – PolyGeo May 1 '14 at 23:20
  • 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, and I found this page which says "Definition queries are unavailable if you are working with a... ...data source that does not have an ObjectID (OID) field. This will be the case with... ...OLE DB data, such as unregistered Oracle files accessed through OLE DB." Does that apply to your situation? – Chris W May 2 '14 at 0:27
  • I'm using a Postgres table but it doesn't have an oid field. Now that I think about it I think I got around this by specifically creating an OID field. I'll have another look thanks. – mxcolin May 2 '14 at 21:08
  • OK so I added an oid column but it still doesn't think it's an ObjectID. I need to find out how to register that column as an ObjectID. – mxcolin May 2 '14 at 21:32
  • That was it. Add an oid field that is not null, unique and hey presto it works. – mxcolin May 2 '14 at 21:54
2

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.

-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
  • 1
    Actually, this is not safe practice. The rowid returned to ArcGIS must be reproducible on subsequent queries, or it will result in undefined behavior. You could use this to create a MATERIALIZED VIEW, but as a standalone query it could cause the application to crash or hang. – Vince Oct 16 '18 at 0:41

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.