Detecting geometry type of Oracle views

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Detecting geometry type of Oracle views

Peilke, Hendrik

Hi,

 

when I added a database view from an Oracle Spatial database as a layer to my map in uDig 1.4, I discovered that the geometry type of that layer wasn’t determined correctly (line instead of polygon). Adding the underlying table directly as a layer to the map gives the right layer type (polygon). This is not a single case scenario, but happened to me every time I tried to add a polygon view. Can I configure something inside the database to let uDig know the type of the view layer?

 

Regards,

Hendrik Peilke



IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter
Vorsitzender des Aufsichtsrates: Dr. Wolfgang Habel

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting geometry type of Oracle views

fgdrf
Hendrik, 
can you confirm that the Geometry Type for the resulting Layer is "Polygon". I'm wondering because Oracle JDBC datastore tries to read the type from Oracle Metadata and I guess nothing can be found for views. If this is the case the generic type "Geometry" is used for Geometry-Mapping (See here :  https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java#L211). 

Not sure about the generator for default styling in uDIG if the type is Geoemtry. Maybe it guesses and uses LineStings as Default.. @Jody: could you jump in ?

HTH,
- Frank


2013/9/4 Peilke, Hendrik <[hidden email]>

Hi,

 

when I added a database view from an Oracle Spatial database as a layer to my map in uDig 1.4, I discovered that the geometry type of that layer wasn’t determined correctly (line instead of polygon). Adding the underlying table directly as a layer to the map gives the right layer type (polygon). This is not a single case scenario, but happened to me every time I tried to add a polygon view. Can I configure something inside the database to let uDig know the type of the view layer?

 

Regards,

Hendrik Peilke



IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter
Vorsitzender des Aufsichtsrates: Dr. Wolfgang Habel

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users



_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting geometry type of Oracle views

Peilke, Hendrik

Frank,

 

sorry, you are right, I was a bit irritated by the layer symbol. The resulting geometry type is indeed “geometry” for the views and “polygon” for the original tables. Following the link in your answer, I set up a geometry metadata table and inserted the values for the views and it worked like a charm.

 

Thanks for your help,

Hendrik

 

Von: [hidden email] [mailto:[hidden email]] Im Auftrag von Frank Gasdorf
Gesendet: Mittwoch, 4. September 2013 09:15
An: Peilke, Hendrik; Jody Garnett
Cc: [hidden email]; Röthig, Tom
Betreff: Re: [udig-users] Detecting geometry type of Oracle views

 

Hendrik, 

can you confirm that the Geometry Type for the resulting Layer is "Polygon". I'm wondering because Oracle JDBC datastore tries to read the type from Oracle Metadata and I guess nothing can be found for views. If this is the case the generic type "Geometry" is used for Geometry-Mapping (See here :  https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java#L211). 

 

Not sure about the generator for default styling in uDIG if the type is Geoemtry. Maybe it guesses and uses LineStings as Default.. @Jody: could you jump in ?

 

HTH,

- Frank

 

2013/9/4 Peilke, Hendrik <[hidden email]>

Hi,

 

when I added a database view from an Oracle Spatial database as a layer to my map in uDig 1.4, I discovered that the geometry type of that layer wasn’t determined correctly (line instead of polygon). Adding the underlying table directly as a layer to the map gives the right layer type (polygon). This is not a single case scenario, but happened to me every time I tried to add a polygon view. Can I configure something inside the database to let uDig know the type of the view layer?

 

Regards,

Hendrik Peilke

 


IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter
Vorsitzender des Aufsichtsrates: Dr. Wolfgang Habel


_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users

 



IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter
Vorsitzender des Aufsichtsrates: Dr. Wolfgang Habel

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting geometry type of Oracle views

Jody Garnett-2
In reply to this post by fgdrf
It is easy to configure Oracle with a generic SDO_GEOMETRY data type, rather than something more specific like SDO_POINT (ie specific GTYPE restriction).
And the GeoTools library we use for data access will only report a specific Geometry type when it can figure it out. I cannot remember where it gets that information from, perhaps the spatial index or 

The default style should be flexible enough to support mixed content (ie if you do have SDO_GEOMETRY you can store points, lines and polygons in the same table).

As this is fairly common, in PostGIS as well as Oracle, we have the radio buttons int the style editor so you can switch your layer to Point if you "know" it only contains points for example.


can you confirm that the Geometry Type for the resulting Layer is "Polygon". I'm wondering because Oracle JDBC datastore tries to read the type from Oracle Metadata and I guess nothing can be found for views. If this is the case the generic type "Geometry" is used for Geometry-Mapping (See here :  https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java#L211). 

Not sure about the generator for default styling in uDIG if the type is Geoemtry. Maybe it guesses and uses LineStings as Default.. @Jody: could you jump in ?

HTH,
- Frank


2013/9/4 Peilke, Hendrik <[hidden email]>

Hi,

 

when I added a database view from an Oracle Spatial database as a layer to my map in uDig 1.4, I discovered that the geometry type of that layer wasn’t determined correctly (line instead of polygon). Adding the underlying table directly as a layer to the map gives the right layer type (polygon). This is not a single case scenario, but happened to me every time I tried to add a polygon view. Can I configure something inside the database to let uDig know the type of the view layer?

 

Regards,

Hendrik Peilke



IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter
Vorsitzender des Aufsichtsrates: Dr. Wolfgang Habel

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users




_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users
Reply | Threaded
Open this post in threaded view
|

Re: Detecting geometry type of Oracle views

Rahkonen Jukka
In reply to this post by Peilke, Hendrik

Hi,

 

Oracle does not by default limit which geometries can be stored into SDO_GEOMETRY. However, there will be a geometry type constraint if spatial index is created like

CREATE INDEX CONSTRAINED_SPATIAL_IDX ON TABLE(GEOM)

INDEXTYPE IS MDSYS.SPATIAL_INDEX

PARAMETERS ('layer_gtype=POLYGON');

 

It is possible to check the geometry type of a table by reading info from the index metadata table.  By the way, DB admins do not necessarily know that uDig/Geoserver user needs such select rights.

 

In case of views, Oracle does not offer any way for checking the geometry type. Well, it might be possible to trace where the geometry comes into the view and check if the mother table has geometry type constraint. But that info is not necessarily true, the view can be like SELECT SDO_GEOM.SDO_CENTROID(GEOM,0.01) and then there are points in the view even the original table may contain polygons. Therefore automatic detection of geometry type of views is impossible or hard or at least difficult and unreliable, also with other datastores. There can always be some tailor made function that is used in a view and who knows what sort of geometries it is sending before having a try.

 

However, it seems that someone has been thinking about this when making Geotools Oracle plugin.

http://docs.geotools.org/latest/userguide/library/jdbc/oracle.html

Follow the instructions and register your view into the geometry metadata table and it should work.

 

-Jukka Rahkonen-

 

 

 

Lähettäjä: [hidden email] [mailto:[hidden email]] Puolesta Jody Garnett
Lähetetty: 10. syyskuuta 2013 3:41
Vastaanottaja: Frank Gasdorf
Kopio: Röthig, Tom; Peilke, Hendrik; [hidden email]
Aihe: Re: [udig-users] Detecting geometry type of Oracle views

 

It is easy to configure Oracle with a generic SDO_GEOMETRY data type, rather than something more specific like SDO_POINT (ie specific GTYPE restriction).

And the GeoTools library we use for data access will only report a specific Geometry type when it can figure it out. I cannot remember where it gets that information from, perhaps the spatial index or 

 

The default style should be flexible enough to support mixed content (ie if you do have SDO_GEOMETRY you can store points, lines and polygons in the same table).

 

As this is fairly common, in PostGIS as well as Oracle, we have the radio buttons int the style editor so you can switch your layer to Point if you "know" it only contains points for example.

 

 

can you confirm that the Geometry Type for the resulting Layer is "Polygon". I'm wondering because Oracle JDBC datastore tries to read the type from Oracle Metadata and I guess nothing can be found for views. If this is the case the generic type "Geometry" is used for Geometry-Mapping (See here :  https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleDialect.java#L211). 

 

Not sure about the generator for default styling in uDIG if the type is Geoemtry. Maybe it guesses and uses LineStings as Default.. @Jody: could you jump in ?

 

HTH,

- Frank

 

2013/9/4 Peilke, Hendrik <[hidden email]>

Hi,

 

when I added a database view from an Oracle Spatial database as a layer to my map in uDig 1.4, I discovered that the geometry type of that layer wasn’t determined correctly (line instead of polygon). Adding the underlying table directly as a layer to the map gives the right layer type (polygon). This is not a single case scenario, but happened to me every time I tried to add a polygon view. Can I configure something inside the database to let uDig know the type of the view layer?

 

Regards,

Hendrik Peilke

 


IBYKUS AG für Informationstechnologie, Erfurt / HRB 108616 - D-Jena / Vorstand: Helmut C. Henkel, Dr. Lutz Richter
Vorsitzender des Aufsichtsrates: Dr. Wolfgang Habel

 

_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users

 

 


_______________________________________________
udig-users mailing list
[hidden email]
http://lists.refractions.net/mailman/listinfo/udig-users