Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
Does Oracle require that all geometries in a table have the same SRID? –  Kirk Kuykendall Sep 16 '11 at 16:10
    
@Kitk yup they do for the spatial index to work –  baens Sep 23 '11 at 18:49
    
and a metadata table record is required also. Just saw this answer below. perhaps the spatial index is not sufficient. –  Brad Nesom Sep 24 '11 at 5:53
add comment

3 Answers 3

up vote 4 down vote accepted
+50

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)

share|improve this answer
    
That looks like one thing I was missing. I did all those steps, and can display a point at 0,0,0 but no where else. –  baens Sep 23 '11 at 18:53
    
I just noticed that you define the Y dimension between -180, 180. Shouldn't that be -90,90? Probably this is where Oracle is getting confused since the SRID is 4326. Other than that, I haven't seen this behavior before. When you add new shapes the spatial index is updated automatically –  mapoholic Sep 24 '11 at 16:32
add comment

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?

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.