topic how to get the lat and long from feature table in Oracle DB in Data Management Questions

how to get the lat and long from feature table in Oracle DB

DRae — Fri, 14 Jun 2024 12:40:43 GMT

Hi I heard that in MS SQL, we can do the following

select shape, shape.STX as LONG, shape.STY as LAT from sde.fc

If my Geodatabase is in Oracle, and how can I get the STX and STY from the shape column. I tried the above and it return

ORA-00904: "SHAPE"."STX": invalid identifier
00904. 00000 - "%s: invalid identifier"

Re: how to get the lat and long from feature table in Oracle DB

George_Thompson — Fri, 14 Jun 2024 13:21:49 GMT

If you have ST_Geometry configured (https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/configure-oracle-extproc.htm) you could use ST functions to do this: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/stgeometry-oracle.htm (reference doc: https://pro.arcgis.com/en/pro-app/latest/help/data/databases/pdf/stgeometry_reference.pdf - look at p336 (ST_X / ST_Y).

That would be where I would start.

Re: how to get the lat and long from feature table in Oracle DB

DRae — Fri, 14 Jun 2024 13:35:33 GMT

Thanks. I am able to do as below

select sde.st_x(shape) from fc

Re: how to get the lat and long from feature table in Oracle DB

VinceAngelo — Fri, 14 Jun 2024 14:35:36 GMT

Note that you should never store user data in the SDE schema, so the "from sde.fc" part is a problem. Best practice is to create a new login to own spatial data (and a new tablespace to store the data for that login). Using the SDE login for this is akin to using SYSTEM or DBO for noodling, or web surfing the dark net as administrator -- It's not likely to end well.

- V