<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.6000.16981" name=GENERATOR></HEAD>
<BODY text=#000000 bgcolor=#ffffff>
<DIV id=idOWAReplyText80772>
<DIV><FONT face="Times New Roman" color=#000000 size=3>Hello, Wolfgang.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>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:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>PERSON</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>0, Peter, 0</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>1, Paul, 1</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>2, Mary, 1</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>CITY</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>0, Berlin</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>1, Munich</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>and we create a view like this:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>CREATE VIEW my_view AS SELECT</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>p.ID, p.NAME, c.ID, c.NAME</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>FROM PERSON p, CITY c</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>WHERE p.BIRTHPLACE_ID = c.ID</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>the content of this view is:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>0, Peter, 0, Berlin</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>1, Paul, 1, Munich</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>2, Mary, 1, Munich</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3></FONT> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>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 </FONT><FONT face="Times New Roman" color=#000000 size=3>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 </FONT><FONT face="Times New Roman" color=#000000 size=3>the third row says that the city with ID = 1 is another thing.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>This problem could be solved in different ways:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>- not allowing edits on some views (this problem does not happen with all views)</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>- allowing edits only on some fields</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>- updating more than one row when a value is changed</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>- not allowing to load certain views in gvSIG</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>- etc.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>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 </FONT><FONT face="Times New Roman" color=#000000 size=3>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 </FONT><FONT face="Times New Roman" color=#000000 size=3>administration application:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>SELECT ROWID FROM <VIEW_NAME></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>If that sentence causes an error, the view will not be available from gvSIG.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>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 </FONT><FONT face="Times New Roman" color=#000000 size=3>tables/views. It would be something like:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>INSERT INTO USER_SDO_GEOM_METADATA ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID ) VALUES ('MY_VIEW', 'GEOMETRY', </FONT><FONT face="Times New Roman" color=#000000 size=3>MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT ('X', <MINX>, <MAXX>, 0.0005 ), MDSYS.SDO_DIM_ELEMENT ('Y', <MINY>, <MAXY>, 0.0005 )), </FONT><FONT face="Times New Roman" color=#000000 size=3><ORACLE_SRS_CODE_OR_NULL> )</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>Example:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>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 )</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>Does this solve all your doubts?</FONT></DIV>
<DIV>Does your knowledge/experience contradict this message?</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3>Regards,</FONT></DIV>
<DIV><FONT face="Times New Roman" color=#000000 size=3></FONT> </DIV></DIV>
<DIV id=idSignature72969>
<DIV><FONT face="Courier New" size=2><FONT face="Times New Roman" size=3>Juan Lucas Domínguez Rubio<BR></FONT>---</FONT></DIV>
<DIV><FONT face="Courier New" size=2><FONT face="Courier New" size=2><FONT face="Courier New" size=2>Prodevelop SL, Valencia (España)</FONT></DIV>
<DIV>
<DIV><FONT face="Courier New" size=2>Tlf.: 96.351.06.12 -- Fax: 96.351.09.68<BR></FONT><A href="http://www.prodevelop.es/"><FONT face="Courier New" size=2>http://www.prodevelop.es</FONT></A><BR><FONT face="Courier New" size=2>---</FONT></DIV></FONT></DIV></FONT></DIV>
<DIV><BR>
<HR>
<FONT face=Tahoma size=2><B>De:</B> gvsig_internacional-bounces@listserv.gva.es en nombre de Wolfgang Qual<BR><B>Enviado el:</B> lun 22/02/2010 11:00<BR><B>Para:</B> Users and Developers mailing list<BR><B>Asunto:</B> [Gvsig_english] gvSIG, Oracle Spatial and Views<BR></FONT><BR></DIV>
<DIV>Dear list,<BR>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 <BR>directly load the data into gvSIG. We gave it a try using the GeoBD connectio manager.<BR>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,<BR>we were also not successful. The dialogue closed and no table was visible.<BR><BR>I am not a specialist regarding oracle spatial view definition, therefore, I just paste here the view definition:<BR><BR>CREATE OR REPLACE VIEW uis_gis_muc_mobilfunkanl_v_r (objekt_id,<BR> NAME,<BR> strassehausnr,<BR> plz,<BR> ort,<BR> geom,<BR> annotationo,<BR> cityo,<BR> classificationo,<BR> eastingo,<BR> epluso,<BR> eplusumtso,<BR> euroo,<BR> initialoperationo,<BR> newoperationo,<BR> northingo,<BR> officeso,<BR> otherso,<BR> o2o,<BR> o2umtso,<BR> radiostationskwo,<BR> radiostationsukwo,<BR> safetydistanceo,<BR> tmobileo,<BR> tmobileumtso,<BR> tvstationso,<BR> vodafoneo,<BR> vodafoneumtso<BR> )<BR>AS<BR> SELECT o.o_id AS objekt_id, o.o_name AS NAME,<BR> PARENT.o_label AS strassehausnr,<BR> uis_obj_attribute_value (PARENT.o_id, 'PostalCodeO') AS plz,<BR> uis_obj_attribute_value (PARENT.o_id, 'PlaceO') AS ort,<BR> MDSYS.SDO_GEOMETRY<BR> (3001,<BR> 31468,<BR> MDSYS.sdo_point_type<BR> (REPLACE (uis_obj_attribute_value (o.o_id, 'EastingO'),<BR> '.',<BR> ','<BR> ) -- x<BR> ,<BR> REPLACE (uis_obj_attribute_value (o.o_id, 'NorthingO'),<BR> '.',<BR> ','<BR> ) -- y<BR> ,<BR> 0<BR> ) -- z<BR> ,<BR> NULL,<BR> NULL<BR> ) AS geom<BR> -- Hier die Merkmale einfügen<BR> ,<BR> uis_obj_attribute_value (o.o_id, 'AnnotationO') AS annotationo,<BR> uis_obj_attribute_value (o.o_id, 'CityO') AS cityo,<BR> uis_obj_attribute_value (o.o_id,<BR> 'ClassificationO'<BR> ) AS classificationo,<BR> uis_obj_attribute_value (o.o_id, 'EastingO') AS eastingo,<BR> uis_obj_attribute_value (o.o_id, 'E-PlusO') AS epluso,<BR> uis_obj_attribute_value (o.o_id, 'E-PlusUMTSO') AS eplusumtso,<BR> uis_obj_attribute_value (o.o_id, 'EuroO') AS euroo,<BR> uis_obj_attribute_value (o.o_id,<BR> 'InitialOperationO'<BR> ) AS initialoperationo,<BR> uis_obj_attribute_value (o.o_id,<BR> 'NewOperationO') AS newoperationo,<BR> uis_obj_attribute_value (o.o_id, 'NorthingO') AS northingo,<BR> uis_obj_attribute_value (o.o_id, 'OfficesO') AS officeso,<BR> uis_obj_attribute_value (o.o_id, 'OthersO') AS otherso,<BR> uis_obj_attribute_value (o.o_id, 'O2O') AS o2o,<BR> uis_obj_attribute_value (o.o_id, 'O2UMTSO') AS o2umtso,<BR> uis_obj_attribute_value (o.o_id,<BR> 'RadioStationsKWO'<BR> ) AS radiostationskwo,<BR> uis_obj_attribute_value (o.o_id,<BR> 'RadioStationsUKWO'<BR> ) AS radiostationsukwo,<BR> uis_obj_attribute_value (o.o_id,<BR> 'SafetyDistanceO'<BR> ) AS safetydistanceo,<BR> uis_obj_attribute_value (o.o_id, 'T-MobileO') AS tmobileo,<BR> uis_obj_attribute_value (o.o_id, 'T-MobileUMTSO') AS tmobileumtso,<BR> uis_obj_attribute_value (o.o_id, 'TVStationsO') AS tvstationso,<BR> uis_obj_attribute_value (o.o_id, 'VodafoneO') AS vodafoneo,<BR> uis_obj_attribute_value (o.o_id,<BR> 'VodafoneUMTSO') AS vodafoneumtso<BR>------------------------------<BR> FROM ibkuis_bd_objects o, -- Objekte<BR> ibkuis_co_classifications c, -- Klasse<BR> ibkuis_bd_objects PARENT, -- Standort<BR> ibkuis_bd_obj_hiera hiera<BR> WHERE o.o_id = hiera.ohz_o_id<BR> AND PARENT.o_id = hiera.ohz_high_id -- den Standort bestimmen<BR> AND o.o_c_id = c.c_id -- Verbindung zu der Klasse<BR> AND c.c_name = 'MobilePhoneSystemPlantO' -- nur Mobilfunkanlagen<BR> ORDER BY o.o_id<BR>/<BR><BR><BR>Maybe there's something wrong with the GEOM definition? The layer is a point theme.<BR><BR>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. <BR>I would be very happy, if you could help me with the following questions:<BR><BR>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?<BR>2) if it's possible to load views into gvSIG: are there known problems?<BR>3) if it all should work: is there anything wrong with the view definiton above?<BR><BR>Any comments would be great!<BR>Best,<BR>Wolfgang<BR><BR><BR><BR>
<DIV class=moz-signature>-- <BR>
<TABLE cellspacing=0 cellpadding=3 width=350 bgcolor=white border=0>
<TBODY>
<TR>
<TD bgcolor=#330066 colspan=2><IMG height=35 src="https://correo.prodevelop.es/exchange/jldominguez/Borradores/RE:%20[Gvsig_english]%20gvSIG,%20Oracle%20Spatial%20and%20Views.EML/1_multipart/logo_b.gif" width=170 border=0></TD></TR>
<TR>
<TD valign=bottom align=left width=3 bgcolor=#330066><BR></TD>
<TD valign=top><FONT face=Arial,Helvetica,Geneva,Swiss,SunSans-Regular size=2><B>Wolfgang Qual<BR><BR></B></FONT><FONT face=Arial,Helvetica,Geneva,Swiss,SunSans-Regular size=1><BIG><B>Referat für Gesundheit und Umwelt</B></BIG><BR>Umweltschutz<BR>Umweltvorsorge<BR>RGU-UW 11<BR><BR>Bayerstraße 28a<BR>80335 München<BR><BR>Telefon +49 - 89 - 233 - 4 77 17<BR>Telefax +49 - 89 - 233 - 4 77 05<BR><BR><A class=moz-txt-link-freetext href="http://www.muenchen.de/umweltatlas">http://www.muenchen.de/umweltatlas</A><BR><A class=moz-txt-link-abbreviated href="mailto:uw11.rgu@muenchen.de">uw11.rgu@muenchen.de</A><BR></FONT></TD></TR>
<TR>
<TD valign=top colspan=2><SMALL><FONT face="Helvetica, Arial, sans-serif">Bitte beachten Sie die Hinweise zur elektronischen<BR>Kommunikation mit der Landeshauptstadt München:<BR><A class=moz-txt-link-freetext href="http://www.muenchen.de/ekomm">http://www.muenchen.de/ekomm</A></FONT></SMALL></TD></TR></TBODY></TABLE></DIV></DIV></BODY></HTML>