spatial query in Oracle

KhoaTran1 · ‎10-19-2011

I'm trying to get the latitude and longitude of a geometry type point from an Oracle database. I've successfully set up the Oracle client to use sqlplus, get connected to the database through command line.

Using ArcCatalog, the data type of my column (stop_pos) is GEOMETRY (Geometry Type: Point). However, when I do a 'DESCRIBE' through sqlplus, the type of the stop_pos column is NUMBER(38), which according to my understanding is the stop_pos.fid since I did a "SELECT stop_pos from MY_TABLE;", it gave me a list of id, equivalent to the stop_pos.fid in ArcCatalog.

According to my understanding, there should be another table other than MY_TABLE in charge of handling spatial data. I've looked at "ST_GEOMETRY" and "SDO_GEOMETRY." Some posts even mentioned about USER_SDO_GEOM_METADATA but I'm unable to find any table or column with those names. I'm new to these stuff and wonder if anyone can give me a SELECT statement example query which I can get the lat and lon of the point (in my example, it was stop_pos).

VinceAngelo · ‎10-19-2011

In order to use the coordinate properties of an object-relational geometry type you need to *use*
an object-relational geometry type. Your layer is using SDEBINARY or SDELOB storage (which use
an integer key to Fn and Sn tables), not ST_GEOMETRY or SDO_GEOMETRY. If you execute the
'sdelayer -o describe_long' command on the table, you will see the storage format.

The default storage option is set in the DEFAULTS keyword of the DBTUNE table, but the default
DBTUNE contains keywords which will generate all supported storage types (this can be changed
by the administrator, so there's no guarantee that's still the case in your instance). While it's
possible to convert storage type to SDELOB, ST_GEOMETRY, or SDO_GEOMETRY, I generally
recommend creating a new table and transferring the contents to this table (there are many
ways to do this, but you options may be limited by whether the table is registered with the
geodatabase or if it's versioned).

- V

KhoaTran1 · ‎10-20-2011

Thanks, Vince. I followed your suggestions and re-created the table using ST_GEOMETRY. Now, I did a 'DESCRIBE' in sqlplus and the stop_pos column type is SDE.ST_GEOMETRY, followed by an OBJECTID. I did an SELECT query "SELECT stop_pos from MY_TABLE where OBJECTID='1'" and the return message was "SP2-0678: Column or attribute type can not be displayed by SQL*Plus." It's very self-explanatory message so I went ahead and looked for ST_Geometry function calls. One of them was ST_AsText (http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/st_astext.htm). However, once I queried "SELECT [sde.]st_astext(stop_pos)", the error message was "ORA-00942: table or view does not exist". Any pointers?

VinceAngelo · ‎10-21-2011

Works fine for me:

 
SQL> select sde.st_astext(shape) from envtest2;
 
SDE.ST_ASTEXT(SHAPE)
--------------------------------------------------------------------------------
POINT  ( 4.97444600 12.51409000)
POINT  ( 14.33015500 -12.69905900)
POINT  ( -5.24724800 -7.37888000)
POINT  ( 0.17672500 17.48991500)
POINT  ( 11.33672200 -4.02122600)
POINT  ( 18.29875200 19.66650600)
POINT  ( -13.42461700 8.07447900)
POINT  ( 5.70494200 -16.03418000)
POINT  ( 0.77471700 -18.01138300)
POINT  ( -19.03184400 12.74311800)
POINT  ( -15.18393200 9.95293200)
...
 
50 rows selected.

The last time I had a customer getting ORA-00942 on ST functions, they had somehow managed
to revoke permissions from most of the SDE operators and deleted half the triggers. It was pretty
ugly to get that instance running again.

- V

KhoaTran1 · ‎10-21-2011

I don't have access to the administrator account (sde). All tables and layers I created was under "MY_ACCOUNT.[table_name]" not "SDE.[table_name]". I also log in with "MY_ACCOUNT" in sqlplus. Do you think it has something to do with calling sde.st_astext(geometry)?

VinceAngelo · ‎10-21-2011

The spatial type functions are owned by SDE and granted to PUBLIC. You should never
load spatial data as the SDE user (in my example, I wasn't SDE, which was why the
prefix was required). If you can't use the ST functions, you can't use ST_GEOMETRY.

- V

KhoaTran1 · ‎10-21-2011

I tried again and still not working. The weird thing is I'm able to execute "SELECT st_x(stop_pos) from MY_TABLE where OBJECTID='1'" and it returns the correct x-value in double. Does it make sense to you? I don't.

Is there anything I can do to use ST_Geometry then? On another note, I was about to post another NEW thread but I guess my table creation can be wrong, so I'll post it here in the same thread. So, on previous posts, you suggested me to use ST_GEOMETRY instead of SDEBINARY/SDELOB (I can't execute sdelayer -o describe to know the exact type). So I created a new table with the ST_GEOMETRY storage. Below is my code using ArcSDE Java API.

        SeLayer layer = new SeLayer(conn);
 layer.setSpatialColumnName(colName);
 layer.setTableName(tableName);
 layer.setShapeTypes(shapeType);
 layer.setGridSizes(1100.0, 0.0, 0.0);;
 layer.setDescription(description);

// layer.setStorageTypes(SeLayer.SE_STORAGE_SQL_TYPE);
   
        SeExtent ext = new SeExtent(-180, -90, 180, 90);
        layer.setExtent(ext);
        
        layer.setCreationKeyword("DEFAULTS");

        /*
         *   Define the layer's Coordinate Reference
         */
        SeCoordinateReference coordref = new SeCoordinateReference();
        long wgs84 = new Long(4326);
        coordref.setCoordSysByID(new SeObjectId(wgs84));
        coordref.setXY(-210,-120,1000000);
        layer.setCoordRef(coordref);
  
        layer.create(3, 4);

I've read many of your posts in this forum regarding setting the correct parameters, as well as ESRI documentation. And the code works fine if I use the 'DEFAULTS' storage. After adding the commented line in the code (i.e. layer.setStorageTypes(SeLayer.SE_STORAGE_SQL_TYPE);), the type of colName is 'SDE.ST_GEOMETRY' when I did a 'DESCRIBE' in sqlplus. I can get the x and y coor using st_x and st_y. However, I can't view those points in ArcCatalog, which I suspect my parameters are wrong, leading to the sde.st_astext error???? Maybe? So, could you please tell me if my parameters are correct and if not, what should I change?

PS: I also tried layer.setCreationKeyword("ST_GEOMETRY") and the result is the same as setStorageTypes

VinceAngelo · ‎10-21-2011

Creating a layer through the ArcSDE API is an advanced skill that I don't recommend until you
can use the command-line tools in your sleep to create layers that are visible by ArcGIS (and
the command-line tools themselves are an advanced skill over the ArcCatalog GUI). Given a
spatial index grid size of 1100 degrees, you've still got some learning curve yet to climb.

I prefer to create ST_GEOMETRY or SDO_GEOMETRY storage layers using SQL, then add them
to ArcSDE with 'sdelayer -o register'.

If the ST functions aren't working, you'll need to ask with the keeper of the SDE user keys to
contact Tech Support for help in straightening that out. I don't have an example of the SQL
query done by ArcGIS handy, but if some ST queries aren't functioning, then the draw failure
is likely due to the lack of function support, not spatial index grid size or coordref precision.

- V