Bug report #7357

Adding SDO_GEOMETRY in Oracle to visualize in Qgis

Added by Timothy Ellersiek over 11 years ago. Updated over 11 years ago.

Status:Closed
Priority:High
Assignee:-
Category:-
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:16332

Description

Hi there,

I am having a problem with Qgis when connecting to my Oracle Spatial Database. My tables contain the SDO_Geometry column, however, Qgis doesn't list these tables as "without Geometries". If I use this simple Oracle Tutorial it doesn't work either. Could someone please help me on this? Thanks in advance!

DROP TABLE customers;
DROP TABLE stores;
DROP INDEX customers_sidx;
DROP INDEX stores_sidx;
DELETE FROM USER_SDO_GEOM_METADATA
   WHERE TABLE_NAME = 'CUSTOMERS' AND COLUMN_NAME = 'CUST_GEO_LOCATION';
DELETE FROM USER_SDO_GEOM_METADATA
   WHERE TABLE_NAME = 'STORES' AND COLUMN_NAME = 'STORE_GEO_LOCATION';

-- Create table for customer information.

CREATE TABLE customers (
  customer_id NUMBER,
  last_name VARCHAR2(30),
  first_name VARCHAR2(30),
  street_address VARCHAR2(40),
  city VARCHAR2(30),
  state_province_code VARCHAR2(2),
  postal_code VARCHAR2(9),
  cust_geo_location SDO_GEOMETRY);

-- Create table for store information.

CREATE TABLE stores (
  store_id NUMBER,
  description VARCHAR2(100),
  street_address VARCHAR2(40),
  city VARCHAR2(30),
  state_province_code VARCHAR2(2),
  postal_code VARCHAR2(9),
  store_geo_location SDO_GEOMETRY);

-- Insert customer data.

INSERT INTO customers VALUES
  (1001,'Nichols', 'Alexandra',
  '17 Maple Drive', 'Nashua', 'NH','03062',
   SDO_GEOMETRY(2001, 8307,
     SDO_POINT_TYPE (-71.48923,42.72347,NULL), NULL, NULL));

INSERT INTO customers VALUES
  (1002,'Harris', 'Melvin',
  '5543 Harrison Blvd', 'Reston', 'VA', '20190',
  SDO_GEOMETRY(2001, 8307,
    SDO_POINT_TYPE(-70.120133,44.795766,NULL), NULL, NULL));

INSERT INTO customers VALUES
  (1003,'Chang', 'Marian',
  '294 Main St', 'Concord', 'MA','01742',
   SDO_GEOMETRY(2001, 8307,
     SDO_POINT_TYPE (-71.351,42.4598,NULL), NULL, NULL));

INSERT INTO customers VALUES
  (1004,'Williams', 'Thomas',
  '84 Hayward Rd', 'Acton', 'MA','01720',
   SDO_GEOMETRY(2001, 8307,
     SDO_POINT_TYPE (-71.4559,42.4748,NULL), NULL, NULL));

INSERT INTO customers VALUES
  (1005,'Rodriguez', 'Carla',
  '9876 Pine Lane', 'Sudbury', 'MA','01776',
   SDO_GEOMETRY(2001, 8307,
     SDO_POINT_TYPE (-71.4242,42.3826,NULL), NULL, NULL));

INSERT INTO customers VALUES
  (1006,'Adnani', 'Ramesh',
  '1357 Appletree Ct', 'Falls Church', 'VA','22042 ',
   SDO_GEOMETRY(2001, 8307,
     SDO_POINT_TYPE (-77.1745,38.88505,NULL),NULL,NULL));

-- Insert stores data.

INSERT INTO stores VALUES
  (101,'Nashua megastore',
  '123 Commercial Way', 'Nashua', 'NH','03062',
   SDO_GEOMETRY(2001, 8307,
     SDO_POINT_TYPE (-71.49074,42.7229,NULL),NULL,NULL));

INSERT INTO stores VALUES
  (102,'Reston store',
  '99 Main Blvd', 'Reston', 'VA','22070',
   SDO_GEOMETRY(2001, 8307,
     SDO_POINT_TYPE (-77.34511,38.9521,NULL),NULL,NULL));

-- Add metadata to spatial view USER_SDO_GEOM_METADATA.

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
   VALUES ('CUSTOMERS', 'CUST_GEO_LOCATION',
   SDO_DIM_ARRAY
     (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
     SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
   8307);

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
   VALUES ('STORES', 'STORE_GEO_LOCATION',
   SDO_DIM_ARRAY
     (SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.5),
     SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.5)),
   8307);

-- Create spatial indexes.

CREATE INDEX customers_sidx ON customers(cust_geo_location)
  INDEXTYPE IS mdsys.spatial_index;

CREATE INDEX stores_sidx ON stores(store_geo_location)
  INDEXTYPE IS mdsys.spatial_index;

Associated revisions

History

#1 Updated by Jürgen Fischer over 11 years ago

  • Status changed from Open to Closed

Also available in: Atom PDF