[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