0

We have database schema imported in Oracle, but when we want to import it into Geomedia Smart Client (GMSC) certain tables are not visible in GMSC.

On the other hand, VIEW created from these tables has the MDO.SYS Geometry column, is visible in GMSC, but we cannot perform any queries on it.

It seems that the topic: How does GeoMedia know what feature classes are in a database?

is maybe connected with our problem but we don't now how? Is there a problem with geomedia's metadata and what? Why can't GMSC see all the features with geometry columns?

1

What do you mean by "complex geometry column" ? Do you mean that you have tables with "simple geometry columns" and some with "complex geometry columns" and the latter are not visible while the former are ?

Did you actually confirm that all your spatial tables are recorded inside Geomedia's own metadata dictionary (GDOSYS) as suggested in the other topic you mention ?

Are all tables also defined in Oracle Spatial's own metadata tables (USER_SDO_GEOM_METADATA) ?

Are the tables in multiple database schemas ? If so did you grant the proper privileges to the database user you use for connecting Geomedia ? In other words, does that user see all the spatial tables you want it to see ? Try connecting as that user and do the following:

select owner,table_name from all_table_names;

will show all the tables (spatial and others) this user can access (= select from)

select owner,table_name from all_sdo_geom_metadata;

will show all the spatial tables this user can access. Are all the tables you want to access in those lists ? If any is missing from the first list, it means that you did not grant the proper privilege (SELECT) on those tables to the user Geomedia connects as. If they are in the first list, but not in the second, it means that those tables do not have any corresponding definition in Oracle Spatial's metadata.

To complete the answer: most GIS tools have restrictions that limit what the database allows. Here are the only database-side requirements:

  • A spatial column must be properly defined in USER_SDO_GEOM_METADATA
  • A spatial column must have a spatial index
  • All geometries in a spatial column must be in the same coordinate system and the same dimensionality (all 2D or all 3D)

Beyond that, no other requirements exist. In particular, the following are all possible:

  • A table can contain any number of spatial column (each then has its own metadata definition and spatial index)
  • A spatial column can contain any mix of shapes (points, lines, polygons, solids ...)
  • Views and synonyms can be freely used
  • Tables can exist in any combination of schemas
  • Queries operate on any mix of coordinate systems
  • Tables can have any kind of referential integrity (primary and foreign keys)

But most GIS tools have the following common restrictions:

  • Only one spatial column per table
  • Only one kind of shape per spatial column (points OR lines OR polygons)
  • Views may work, but require their own metadata definition (not used by Oracle)
  • Synonyms rarely work
  • Single-column numeric primary key (no composite primary keys)
  • Our mistake for complex geometry column. All the tables with SDO Geometry are now visible in GMSC and can be imported. – frapelo May 16 '15 at 9:45
  • Tables are in one database schema. But there still is a problem. We have created a new table from several other tables in our schema. We have a geometry column and the query works just fine when run from SQL Developer, but when the table is imported in GMSC something is wrong, query cant be performed. I dont know if you worked with GMSC but when we test our settings on that feature (table), an error returns. Thanks for your previous answer. – frapelo May 16 '15 at 9:52
  • Maybe there is a problem with primary key, locatable column. We first did not use a primary key in other database schema and queries worked just fine. Now, when we use rownumber as a unique PK something blows up... – frapelo May 16 '15 at 10:11
  • select owner,table_name from all_sdo_geom_metadata; resolved the issue. Thanks again – frapelo May 16 '15 at 19:40
  • 1
    See my update to the answer. GIS tools do have their own specific constraints. Geomedia is typically more flexible than other, but I believe is still need a single-column numeric primary key. That cannot be ROWNUM since that is a virtual column just used for limiting SELECT results. – Albert Godfrind May 18 '15 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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