[Qgis-user] QGIS and Oracle Views

[Jonathan Moules]

Hi List,
I'm trying to get QGIS to connect to an Oracle View but as best I can tell QGIS is requiring a Primary key. The problem is that I don't believe Oracle formally supports these for views, only base tables or materialised views, and creating one isn't necessarily that easy.

Is it possible for QGIS to use Views without this restriction?

Thanks,
Jonathan

________________________________

HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

________________________________
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20141008/deb474ab/attachment.html>

[Rob L]

Jonathan,

I think as long as the view is registered in the `USER_SDO_GEOM_METADATA`
table then you should be able to add them in QGIS.

Think at the "Add Oracle Table(s)" dialog you just have to select a geometry
"Type" and a "Primary key column" using the drop downs.

Let me know how you get on.

Rob



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS-and-Oracle-Views-tp5166520p5167358.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.

[Jonathan Moules]

Hi Rob,
Thanks for the suggestion. I've already registered with USER_SDO_GEOM_METADATA to even get the table listed in the "Add table" dialog.

Selecting a geometry and/or primary key column don't work I'm afraid. I've also tried the "Select at id" checkbox on and off, but to no avail. Hence my wondering if QGIS supports views.

I get this back:

dbname='production.hw-uk.local' host=host port=1521 user='USER' password='PASSWORD' key='PKEY' estimatedmetadata=true srid=8307 type=POLYGON table="SZACCOUNTS"."JMO_TEMP_VIEW" (GEOMETRY) sql= is an invalid layer - not loaded

Has anyone else had success pointing to Oracle Views? How about PostGIS views, do they work?

Cheers,
Jonathan

-----Original Message-----
From: qgis-user-bounces at lists.osgeo.org [mailto:qgis-user-bounces at lists.osgeo.org] On Behalf Of Rob L
Sent: Tuesday, October 14, 2014 8:25 AM
To: qgis-user at lists.osgeo.org
Subject: Re: [Qgis-user] QGIS and Oracle Views

Jonathan,

I think as long as the view is registered in the `USER_SDO_GEOM_METADATA` table then you should be able to add them in QGIS.

Think at the "Add Oracle Table(s)" dialog you just have to select a geometry "Type" and a "Primary key column" using the drop downs.

Let me know how you get on.

Rob



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS-and-Oracle-Views-tp5166520p5167358.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.
_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-user


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click https://www.mailcontrol.com/sr/yhxqFe!!ur!GX2PQPOmvUhStAXlT3N1zBgU!bku7KNXHzabdgX!LQrHMmaV05UyNdme+0SYzKNn1VsBVsOva5A== to report this email as spam.

________________________________

HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

________________________________

[Richard Duivenvoorde]

Post by Jonathan Moules
Has anyone else had success pointing to Oracle Views? How about PostGIS views, do they work?

hi Jonathan,

tried it here.

I'm able to see an Oracle View, but only after:

- create a simple (point view) via 'create view as select * from ....)

at that time the view is not visible in QGIS, so

- added the view to the spatial metadata table

now I could see it, but it was still show as 'grey' text lines and not
addable

- then in the QGIS-add oracle dialog, there is a column 'Primary key
column' in which you can select a column as primary column (probably
this should be an unique one...). After doing that:

- NOW the layer is black, and I'm able to add it to QGIS

If you did the same steps, maybe your primary key column is not unique?
Or have some invalid data?
Maybe first try this with some simple test data?

Regards,

Richard Duivenvoorde

[Langford, Robert]

I can confirm that I can access Oracle Views in my system using the steps as described by Richard.

Rob

-----Original Message-----
From: Richard Duivenvoorde [mailto:rdmailings at duif.net]
Sent: 16 October 2014 11:23
To: Jonathan Moules; Langford, Robert; 'qgis-user at lists.osgeo.org'
Subject: Re: [Qgis-user] QGIS and Oracle Views

Post by Jonathan Moules
Has anyone else had success pointing to Oracle Views? How about PostGIS views, do they work?

hi Jonathan,

tried it here.

I'm able to see an Oracle View, but only after:

- create a simple (point view) via 'create view as select * from ....)

at that time the view is not visible in QGIS, so

- added the view to the spatial metadata table

now I could see it, but it was still show as 'grey' text lines and not addable

- then in the QGIS-add oracle dialog, there is a column 'Primary key column' in which you can select a column as primary column (probably this should be an unique one...). After doing that:

- NOW the layer is black, and I'm able to add it to QGIS

If you did the same steps, maybe your primary key column is not unique?
Or have some invalid data?
Maybe first try this with some simple test data?

Regards,

Richard Duivenvoorde


DISCLAIMER: The information contained in this communication/message from Robert.Langford at salford.gov.uk sent on Thu Oct 16 12:20:26 2014 is confidential. It is intended solely for the addressee(s) richard at duif.net;J.Moules at hrwallingford.com;qgis-user at lists.osgeo.org

Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful.
As a public body, Salford City Council may be required to disclose this email [or any response to it] under the Freedom of Information Act 2000, unless the information in it is covered by one of the exemptions in the Act.
Please immediately contact the sender, Robert.Langford at salford.gov.uk if you have received this message in error.

For the full disclaimer please access http://www.salford.gov.uk/e-mail. Thank you.

[derrickb52]

Along the same vein, I have an oracle view with coordinates (lat and lon) but
no spatial column. I can't add a row to user_sdo_geom_metadata (no spatial
column to "register"). I'm guessing I am out of luck here...



--
View this message in context: http://osgeo-org.1560.x6.nabble.com/QGIS-and-Oracle-Views-tp5166520p5183115.html
Sent from the Quantum GIS - User mailing list archive at Nabble.com.