AcGIS Pro 2.4 | How to display X,Y point coordinates in Query Layer?

[VincentLaunstorfer]

Hi,

In an Oracle DBMS, I created views with X and Y coordinates in columns, in WGS84. In ArcGIS Pro, I tried to create a Query Layer in order to display live and up-to-date datasets. I managed to select an integer unique identifier and the query layer opens as a Table in the Content. However, I'd like to display it as a point layer in the Content. If I try to right-click the table and Display XY, it opens the geoprocessing tool XY Table to Point which would create a copy of the data. This is not what I am expecting. How to display point layer XY, stored in columns, in a Query Layer based on an Oracle view (with no spatial column) ?

Thanks 

PS: I changed this question to ArcGIS Pro instead of Managing Data as it looks more appropriate

[George_Thompson]

I would think that you need to have a spatial (shape) field and that the client will not add the shape (based on X / Y) on the fly. Is that what you are looking for?

"Geometry SRID property

This property is the spatial reference identification value that is set on the shape objects in the database. It will be blank if the query layer does not have a spatial field. If this value is set, all features that do not have the specified geometry SRID values will be excluded from the result set."

Defining the spatial reference of the query layer—Query layers | ArcGIS Desktop 

What is a query layer?—Query layers | ArcGIS Desktop 

Geodatabase‌ Managing Data‌

[VincentLaunstorfer]

In addition to my question posted yesterday using the geoprocessing tool XY Table to Point to create a Point Feature Class, I tried the Make XY Event Layer tool to keep the layer live and connection to my Query Layer.

Unfortunately, my Event layer in the Table of Content do not display anything and if I try to open Attributes Table, I get a DBMS error ORA-01455: converting column overflows integer datatype. Perhaps, I need to ensure all my number column in my views are readable by ArcGIS clients...

George, you mentioned to have a spatial field in my Oracle table. Why not. Can I build a ST_Geometry or SDO_Geometry spatial field from my X and Y coordinates columns in my views? What would be the syntax?

Thanks

[George_Thompson]

You can do this in the view on the Oracle side of the view, then bring it into the ArcGIS client; ST_Geometry—Help | ArcGIS Desktop 

Generally speaking you want to make sure that your Oracle instance can support the ST functions via EXTPROC; Configure the extproc to access ST_Geometry in Oracle—Help | ArcGIS Desktop 

[VincentLaunstorfer]

My Oracle instance is EXTPROC enabled and I can build ST_Geometry objects.

select

  sde.st_geometry('point('amh.LONGITUDE_DEC_DEG','amh.LATITUDE_DEC_DEG'),4326') SHAPE,

OR

select
  sde.st_geometry(amh.LONGITUDE_DEC_DEG,amh.LATITUDE_DEC_DEG,null,null,4326) SHAPE,

...should work to create the spatial column as Point in my view, but I still get an Oracle ORA-00907 silling error on "missing right parenthesis". Should I cast my X and Y number column to NUMBER(38,8) for example? Or is there anything alse wrong in the syntax?

Thanks for you help, I am sure I will get this one sorted out today...

[George_Thompson]

I would guess that the X/Y fields would need to be numeric fields. Try that and then test.

[VincentLaunstorfer]

Today:

sde.st_geometry(amh.LONGITUDE_DEC_DEG,amh.LATITUDE_DEC_DEG,null,null,4326) SHAPE,

did work and it looks like the SHAPE spatial field is created with my X and Y coordinates:

When creating a Query Layer in ArcGIS Pro, I can validate my query, choose Unique Identifier in the next step and the view opens in ArcGIS like a table. It doesn't read the SHAPE spatial column to display the data! Should I create a spatial index on this field to use it? Or if the syntax to build the SHAPE point spatial field incorrect?

For info, the Catalog display the view as:

Meaning ArcGIS is not able to determine the content of the view... Table of Feature Class...

Moreover, if I try to CAST the coordinates as:

sde.st_geometry(CAST(amh.LONGITUDE_DEC_DEG) as decimal(38,8),CAST(amh.LATITUDE_DEC_DEG) as decimal(38,8),null,null,4326) SHAPE,

I get ORA-00905: missing keyword error in SQL Developper... I wonder if specifying NUMBER(38,8) for coordinates would help to use them as double to get a valid spatial field...

Any guess?

[George_Thompson]

Not sure about the casting, but Catalog does not know the geometry till the it is brought into a map. Once it is added (like a query layer) it will find the geometry and make the icon match.

I do not have an Oracle DB to test on at this time, but it should work. Can you see the shape in SQL Developer?

[VincentLaunstorfer]

Yes, I can see the SHAPE spatial field in SQL Developper, as shown in the screenshot above, as ST_GEOMETRY(). But from the Catalog, I cannot right-click and Add to Current Map. I get an error. It's bizarre!

[George_Thompson]

That is weird, I would recommend contacting Esri Technical Support and work with them.