ArcMap query layer with Oracle Locator displaying no results

Question

I have an Oracle 11g database setup with Oracle Locator.

I have created a table with the this query:

CREATE TABLE geotest (id NUMBER(38) NOT NULL, shape SDO_GEOMETRY);

Then I inserted one point with the this query:

INSERT INTO geotest VALUES(1,SDO_GEOMETRY(2001,3395,SDO_POINT_TYPE(-111.949439,40.722283,NULL),NULL,NULL));

The result is no data in the query layer in ArcMap. It allows me to create the layer, and validates. But then it makes me go through the advanced properties which I suspect means it can't see any of the rows to automatically pull the SRID.

Any guidance would be appreciated!

Update: So when I insert the metadata into the table using this:

insert into user_sdo_geom_metadata values ('geotest','shape',sdo_dim_array(sdo_dim_element('X',-180,180,0.005),sdo_dim_element('Y',-180,180,0.005)),4326);

And creating the spatial index, I can see the following point inserted into the database using this query:

insert into geotest values(1,'test',sdo_geometry(2001,4326,sdo_point_type(0,0,0),null,null));

So it would appear that a point with all 0s work, but anything beside that doesn't. I haven't found anything in the documentation that relates to this, or even any users complaining about that. Any either tricks that may be useful?

Update 2: Alright it appears that after I drop the index, and create it again, the points then show up. I am not an Oracle expert, so this confuses me, anyone have an explanation for this behavior? I thought once an index was created, as soon as a point is inserted, the index updates.

Answer 1

I haven't used the Query layer but I assume it needs to be a valid oracle layer? Which means you would have to add an entry for the layer to the user_sdo_geom_metadata table (see here) and also creating a spatial index (see there)

Answer 2

The first insert is using SRID 3395 (mercator), but the coords you are passing look suspiciously like lat/longs. The second insert you're passing 4326 (which is WGS84).

Did you try the second insert with lat/long values and SRID 4326?

Answer 3

If the query layer is in the same coordinate system as the view, then it should still display.

Unfortunately, the biggest draw-back (IMO) of query layers is that the source geometry needs to have a spatial index to display. There are all sorts of derived queries I would love to do on the Oracle side, and the query layer will do them, but the results don't show. For example, Oracle's linear referencing on the fly. "Flashing" a given record in the table will display the geometry briefly, which proves it's there.

You can export the query layer and then you will get the results. Of course, that isn't optimal.