[Gvsig_english] gvSIG, Oracle Spatial and Views

Juan Lucas Dominguez Rubio jldominguez at prodevelop.es
Mon Feb 22 12:22:31 CET 2010


Hello, Wolfgang.
 
Imagine that we have a table called PERSON with attributes (ID, NAME, BIRTHPLACE_ID) and another table called CITY with attributes  (ID, NAME), like this:
 
PERSON
0, Peter, 0
1, Paul, 1
2, Mary, 1
 
CITY
0, Berlin
1, Munich
 
and we create a view like this:
 
CREATE VIEW my_view AS SELECT
p.ID, p.NAME, c.ID, c.NAME
FROM PERSON p, CITY c
WHERE p.BIRTHPLACE_ID = c.ID
 
the content of this view is:
 
0, Peter, 0, Berlin
1, Paul, 1, Munich
2, Mary, 1, Munich
 
Let's suppose that this view has also a GEOMETRY field and the user adds this view to gvSIG's view, then starts an editing session on it and changes the value "Munich" in the third row and saves the changes. This would cause an inconsistency, because the second row says that the city with ID = 1 is Munich, but the third row says that the city with ID = 1 is another thing.
 
This problem could be solved in different ways:
 
- not allowing edits on some views (this problem does not happen with all views)
- allowing edits only on some fields
- updating more than one row when a value is changed
- not allowing to load certain views in gvSIG
- etc.
 
The current implementation in gvSIG is the fourth (not allowing to load certain views in gvSIG). One way to find out if this problem occurs is by querying for the ROWID field (automatically generated by Oracle when it is possible). So, if you want to know if a view will be available in gvSIG, you should execute this in your DB administration application:
 
SELECT ROWID FROM <VIEW_NAME>
 
If that sentence causes an error, the view will not be available from gvSIG.
 
Apart from this, you'll have to manually add an entry to the USER_SDO_GEOM_METADATA view, because that's where gvSIG gets the list of available tables/views. It would be something like:
 
INSERT INTO USER_SDO_GEOM_METADATA  ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )  VALUES ('MY_VIEW', 'GEOMETRY', MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT ('X', <MINX>, <MAXX>, 0.0005 ), MDSYS.SDO_DIM_ELEMENT ('Y', <MINY>, <MAXY>, 0.0005 )), <ORACLE_SRS_CODE_OR_NULL> )
 
Example:
 
INSERT INTO USER_SDO_GEOM_METADATA  ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )  VALUES ('GVSIG_1212', 'GEOMETRY', MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT ('X', -500000, 2000000, 0.0005 ), MDSYS.SDO_DIM_ELEMENT ('Y', 3000000, 4000000, 0.0005 )), 82337 )
 
Does this solve all your doubts?
Does your knowledge/experience contradict this message?
 
 
Regards,
 
Juan Lucas Domínguez Rubio
---
Prodevelop SL, Valencia (España)
Tlf.: 96.351.06.12 -- Fax: 96.351.09.68
http://www.prodevelop.es <http://www.prodevelop.es/> 
---

________________________________

De: gvsig_internacional-bounces at listserv.gva.es en nombre de Wolfgang Qual
Enviado el: lun 22/02/2010 11:00
Para: Users and Developers mailing list
Asunto: [Gvsig_english] gvSIG, Oracle Spatial and Views


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




-- 

 <https://correo.prodevelop.es/exchange/jldominguez/Borradores/RE:%20[Gvsig_english]%20gvSIG,%20Oracle%20Spatial%20and%20Views.EML/1_multipart/logo_b.gif> 	

	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/dddd4269/attachment.htm 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: logo_b.gif
Type: image/gif
Size: 590 bytes
Desc: logo_b.gif
Url : http://listserv.gva.es/pipermail/gvsig_internacional/attachments/20100222/dddd4269/attachment.gif 


More information about the Gvsig_internacional mailing list