3

In ArcGIS Dektop, I can use SQL expressions to query enterprise geodatabase data (expressions are essentially the WHERE clause in a query).

I'd like to do more than this. I want to use the full capabilities of SQL, such as group by, subqueries, st_geometry functions and PL/SQL. I'm really only interested in accessing live data in the database; not exporting the data as a copy.

What are the options for accessing Oracle geodatabase data using full-blown SQL?


Here's what I know of so far:

ESRI tools:

  1. Database View
  2. ArcSDESQLExecute (ArcPy)
  3. Query Layer
  4. Subqueries in an SQL expression (credit: @Brent Edwards)
    • Ex. objectid IN (SELECT objectid FROM...more complex SQL here...)

Non-ESRI tools:

  1. Microsoft Access Pass-through Query

  2. Microsoft Query

    • Use Windows >> Start >> Find to locate it by searching MSQRY32.EXE or MSQUERY.EXE
  3. SQL Plus (Credit: @Dowlers)
  4. Oracle SQL Developer
  5. TOAD
  6. Python libraries
    • pyodbc
    • cx_oracle
  7. Java (via JDBC) (credit: @Albert Godfrind)

Environment:

  • ArcGIS Desktop 10.3.1
  • SDE 10.3.1
  • Oracle 12c
  • SDE.ST_GEOMETRY
  • That question is very broad and i think without a specific task it is either hard to answer as it will be nearly bottomless to discuss or on the other hand will be to specific regarding some pieces of the whole topic. What i would suggest is that you specifiy what database you mean? Filegeodatabase, Oracale, Postgres, Microsoft? You are throwing to many things together. And do you want to access them? The tools you mention leave the question open what you want? For example you can access and throw any sql statement (also procedural) into postgres from python, and from there into esri products. – Matte Aug 30 '16 at 17:50
  • @Matte Point taken. I've changed the question to be specific to Oracle, and will work on making it specific to a certain use-case. – Wilson Aug 30 '16 at 18:01
  • You will have to consider how versioning will affect workflows. Non ESRI tools will not be able to see edits in versioned data with the exception of the Default version, if you look at the automatically generated versioned view. But in general SQL Plus has always meat my needs for accessing the database part of the geodatabase. – Dowlers Sep 8 '16 at 5:26
  • @Dowlers Good point. In my case, I think versioned views will take care of any versioning concerns. – Wilson Dec 25 '16 at 2:12
  • 1
    You can do quite a bit with the WHERE clause "Definition Query" of layers using subqueries. Ex. objectid IN (SELECT objectid FROM...more complex SQL here...). – Brent Edwards Jan 9 '17 at 16:37
3

You said your database is Oracle 12c. But what storage type do you use for your data ? Is that the SDE binary type ? Or is it ESRI's ST_GEOMETRY types ?

If the former, then there is nothing you can do at the database level.

If the latter, then you can use ESRI's SQL extensions to write SQL queries over the data, including spatial functions (st_contains, st_intersect, st_buffer, st_distance etc).

To run those SQL queries, you can use any development environment: Java code (via JDBC), Python (via cx_oracle) ...

The pre-condition for all this is to convert your storage type to ESRI's ST_GEOMETRY type. Then again, if you do that, you could also convert to Oracle's native spatial types (SDO_GEOMETRY) and run your spatial analysis over those. Being native to the database, they are available on all platforms and typically perform more efficiently than ESRI's implementation.

  • Thanks Wilson. Good reading. We have extended the Oracle RDBMS with object techniques (object types, methods etc) way back in Oracle 8i (mid-90's). Nobody (well very very few) used the object techniques as in an OO database. The only real practical uses is for extending the database with new datatypes: we do this for our spatial types (SDO_GEOMETRY, ST_GEOMETRY, SDO_RASTER), semantics, XML, multi-media ... ESRI is using this for their own ST_GEOMETRY. But fundamentally, the Oracle database is relational with an object lining ... – Albert Godfrind Mar 27 '17 at 14:43

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.