[Gvsig_english] gvSIG, Oracle Spatial and Views

Wolfgang Qual wolfgang.qual at muenchen.de
Mon Feb 22 11:00:00 CET 2010


Dear list,
a few days ago, a colleague asked me, whether it would be possible to 
load database views into gvSIG. My colleague also created a colum "geom" 
in order to make it possible to
directly load the data into gvSIG. We gave it a try using the GeoBD 
connectio manager.
It was possible to connect to the database, but no tables were displayed 
within the GeoBD-add-layer dialogue. When trying to connect directly to 
the oracle view via project manager > table > database,
we were also not successful. The dialogue closed and no table was visible.

I am not a specialist regarding oracle spatial view definition, 
therefore, I just paste here the view definition:

CREATE OR REPLACE VIEW uis_gis_muc_mobilfunkanl_v_r (objekt_id,
                                                     NAME,
                                                     strassehausnr,
                                                     plz,
                                                     ort,
                                                     geom,
                                                     annotationo,
                                                     cityo,
                                                     classificationo,
                                                     eastingo,
                                                     epluso,
                                                     eplusumtso,
                                                     euroo,
                                                     initialoperationo,
                                                     newoperationo,
                                                     northingo,
                                                     officeso,
                                                     otherso,
                                                     o2o,
                                                     o2umtso,
                                                     radiostationskwo,
                                                     radiostationsukwo,
                                                     safetydistanceo,
                                                     tmobileo,
                                                     tmobileumtso,
                                                     tvstationso,
                                                     vodafoneo,
                                                     vodafoneumtso
                                                    )
AS
   SELECT   o.o_id AS objekt_id, o.o_name AS NAME,
            PARENT.o_label AS strassehausnr,
            uis_obj_attribute_value (PARENT.o_id, 'PostalCodeO') AS plz,
            uis_obj_attribute_value (PARENT.o_id, 'PlaceO') AS ort,
            MDSYS.SDO_GEOMETRY
               (3001,
                31468,
                MDSYS.sdo_point_type
                      (REPLACE (uis_obj_attribute_value (o.o_id, 
'EastingO'),
                                '.',
                                ','
                               
)                                          -- x
                                ,
                       REPLACE (uis_obj_attribute_value (o.o_id, 
'NorthingO'),
                                '.',
                                ','
                               
)                                          -- y
                                ,
                       0
                      
)                                                   -- z
                       ,
                NULL,
                NULL
               ) AS geom
                        -- Hier die Merkmale einfügen
            ,
            uis_obj_attribute_value (o.o_id, 'AnnotationO') AS annotationo,
            uis_obj_attribute_value (o.o_id, 'CityO') AS cityo,
            uis_obj_attribute_value (o.o_id,
                                     'ClassificationO'
                                    ) AS classificationo,
            uis_obj_attribute_value (o.o_id, 'EastingO') AS eastingo,
            uis_obj_attribute_value (o.o_id, 'E-PlusO') AS epluso,
            uis_obj_attribute_value (o.o_id, 'E-PlusUMTSO') AS eplusumtso,
            uis_obj_attribute_value (o.o_id, 'EuroO') AS euroo,
            uis_obj_attribute_value (o.o_id,
                                     'InitialOperationO'
                                    ) AS initialoperationo,
            uis_obj_attribute_value (o.o_id,
                                     'NewOperationO') AS newoperationo,
            uis_obj_attribute_value (o.o_id, 'NorthingO') AS northingo,
            uis_obj_attribute_value (o.o_id, 'OfficesO') AS officeso,
            uis_obj_attribute_value (o.o_id, 'OthersO') AS otherso,
            uis_obj_attribute_value (o.o_id, 'O2O') AS o2o,
            uis_obj_attribute_value (o.o_id, 'O2UMTSO') AS o2umtso,
            uis_obj_attribute_value (o.o_id,
                                     'RadioStationsKWO'
                                    ) AS radiostationskwo,
            uis_obj_attribute_value (o.o_id,
                                     'RadioStationsUKWO'
                                    ) AS radiostationsukwo,
            uis_obj_attribute_value (o.o_id,
                                     'SafetyDistanceO'
                                    ) AS safetydistanceo,
            uis_obj_attribute_value (o.o_id, 'T-MobileO') AS tmobileo,
            uis_obj_attribute_value (o.o_id, 'T-MobileUMTSO') AS 
tmobileumtso,
            uis_obj_attribute_value (o.o_id, 'TVStationsO') AS tvstationso,
            uis_obj_attribute_value (o.o_id, 'VodafoneO') AS vodafoneo,
            uis_obj_attribute_value (o.o_id,
                                     'VodafoneUMTSO') AS vodafoneumtso
------------------------------
   FROM     ibkuis_bd_objects o,                                    -- 
Objekte
            ibkuis_co_classifications c,                             -- 
Klasse
            ibkuis_bd_objects PARENT,                              -- 
Standort
            ibkuis_bd_obj_hiera hiera
      WHERE o.o_id = hiera.ohz_o_id
        AND PARENT.o_id = hiera.ohz_high_id          -- den Standort 
bestimmen
        AND o.o_c_id = c.c_id                      -- Verbindung zu der 
Klasse
        AND c.c_name = 'MobilePhoneSystemPlantO'       -- nur 
Mobilfunkanlagen
   ORDER BY o.o_id
/


Maybe there's something wrong with the GEOM definition? The layer is a 
point theme.

I started gvSIG from the command line - and copied the messages left 
there into a textfile. I also attach the logfile of gvSIG to this post.
I would be very happy, if you could help me with the following questions:

1) is it - generally  - possible to use views of oracle spatial 
databases in gvSIG (both via the table- and add-layer dialogue). Or does 
gvSIG only support database tables?
2) if it's possible to load views into gvSIG: are there known problems?
3) if it all should work: is there anything wrong with the view 
definiton above?

Any comments would be great!
Best,
Wolfgang



-- 

	*Wolfgang Qual

* *Referat für Gesundheit und Umwelt*
Umweltschutz
Umweltvorsorge
RGU-UW 11

Bayerstraße 28a
80335 München

Telefon +49 - 89 - 233 - 4 77 17
Telefax +49 - 89 - 233 - 4 77 05

http://www.muenchen.de/umweltatlas
uw11.rgu at muenchen.de
Bitte beachten Sie die Hinweise zur elektronischen
Kommunikation mit der Landeshauptstadt München:
http://www.muenchen.de/ekomm

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserv.gva.es/pipermail/gvsig_internacional/attachments/20100222/c8fbc88e/attachment.htm 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: logo_b.gif
Type: image/gif
Size: 590 bytes
Desc: not available
Url : http://listserv.gva.es/pipermail/gvsig_internacional/attachments/20100222/c8fbc88e/attachment.gif 
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: gvSIG_try_oracle.txt
Url: http://listserv.gva.es/pipermail/gvsig_internacional/attachments/20100222/c8fbc88e/attachment.txt 
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: result_gvsig_command_line.txt
Url: http://listserv.gva.es/pipermail/gvsig_internacional/attachments/20100222/c8fbc88e/attachment-0001.txt 


More information about the Gvsig_internacional mailing list