topic Re: AcGIS Pro 2.4 | How to display X,Y point coordinates in Query Layer? in ArcGIS Pro Questions

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

VincentLaunstorfer — Wed, 11 Dec 2019 16:13:17 GMT

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

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

George_Thompson — Thu, 12 Dec 2019 13:29:48 GMT

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‌

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

VincentLaunstorfer — Thu, 12 Dec 2019 13:36:31 GMT

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

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

George_Thompson — Thu, 12 Dec 2019 13:53:11 GMT

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

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

VincentLaunstorfer — Thu, 12 Dec 2019 14:25:12 GMT

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,

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...

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

George_Thompson — Thu, 12 Dec 2019 14:31:44 GMT

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

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

VincentLaunstorfer — Fri, 13 Dec 2019 15:21:23 GMT

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?

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

George_Thompson — Fri, 13 Dec 2019 15:59:53 GMT

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?

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

VincentLaunstorfer — Fri, 13 Dec 2019 16:11:32 GMT

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!

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

George_Thompson — Fri, 13 Dec 2019 18:21:16 GMT

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

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

VincentLaunstorfer — Mon, 16 Dec 2019 17:17:45 GMT

In ArcMap 10.5.1, the Catalog does show the proper Point icon for points which is better than an empty icon in ArcGIS Pro 2.4.2.Right-clicking the view, either in ArcMap or ArcGIS Pro, both describes the views correctly as:

However, the "Let ArcGIS Pro discover spatial properties for the layer" does not seem to work. However, if you leave the spatial field in the Query box and select "Define spatial properties for the layer", it does Validate and in the Next section, it is possible to specify Spatial Properties manually (it is not greyed out) as Point with proper CRS and thus use the spatial field from the Query.

But unfortunately, it ends up with the following error

Not nice!

It probably becomes an ESRI issue to fix...

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

VincentLaunstorfer — Fri, 20 Dec 2019 10:08:19 GMT

Hi,

AS a follow-up, I finally managed to use Query Layer with a sde.st_geometry spatial field as explained above and use "Let ArcGIS Pro discover spatial properties for the layer" when setting the Query Layer propoerties. It all works.

The problem was in unique identifier! My Oracle views have 12 digits number and ESRI can map up to 10 digits as explained here:

FAQ: Does ArcSDE support ObjectID field as 64-bit integers in Oracle?

ArcGIS was confused and lost to display my query layers. I had to convert my unique identifier to text (instead of Long or Integer as ArcGIS would prefer) in Oracles views in order to fix the problem...