Current Position:Home > Creating a combined view of two spatially indexed tables

Creating a combined view of two spatially indexed tables

Update:10-11Source: network consolidation
Advertisement
Hi All,
I'm using oracle 10g and C++ occi to store and retrieve data. I have two tables that are identical in structure, they have an SDO_GEOM column where I store lat/long/altitude info. When I store the data using a stored procedure, the data is put into the correct table. I now want to retrieve the data using a spatial operator - I use SDO_NN to retrive data within a given distance of a lat/long/altitude point. This works fine for a single or multiple tables as I use a stored function to give me the data back as an object. I now have a requirement to list all the data from both tables - I thought I could do this by creating a combined view but I understand this cannot be done with spatial data - I habe also tried using the join operator but I am having problems since the columns for each table are identical. Is there any workaround for this - the combined view will not have any spatial operators run on it, I just need to return each row (the spatial data can be returned as individual lat/long/alt instead of as a SDO_GEOM. A second idea I had would be to return all the data using a ref cursor - this works for a single table but I do not understand how I can open the cursor with a select from two tables with identical column names.
Unfortunately it is a requirement that the tables are seperate so combining the two tables into one is not an option.
Thanks in advance for any help anyone can offer,
Cheers,
Rob

The Best Answer

Advertisement
You can create a UNION ALL view:
CREATE TABLE cola_markets_1 (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);
CREATE TABLE cola_markets_2 (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);
CREATE VIEW v1 AS
SELECT * FROM cola_markets_1 UNION ALL SELECT * FROM cola_markets_2;
If both tables have a spatial index on their shape column, a query plan will look
like:
explain plan for SELECT c.mkt_id, c.name FROM v1 c WHERE SDO_NN(c.shape, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,6, 8,8)) , 'sdo_num_res=1') = 'TRUE';
0 SELECT STATEMENT     |           |
1 VIEW               | V1          |
2 UNION-ALL          |           |
3 TABLE ACCESS BY INDEX ROWID| COLA_MARKETS_1
4 DOMAIN INDEX     | COLA_SPATIAL_IDX_1
5 TABLE ACCESS BY INDEX ROWID| COLA_MARKETS_2
6 DOMAIN INDEX     | COLA_SPATIAL_IDX_2
However, the above SDO_NN query will return 2 rows (one from each table),
because it can only work on one table, it won't return the nearest neighbor
from the combined view without some tweaks. For example, to return the
top one, you may try:
select * from (SELECT c.mkt_id, c.name FROM v1 c WHERE SDO_NN(c.shape, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,6, 8,8)) , 'sdo_num_res=1') = 'TRUE' order by sdo_geom.sdo_distance(c.shape, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3), SDO_ORDINATE_ARRAY(4,6, 8,8)), 0.0001)) where rownum < 2;
Note that you can only pass literals or bind variables into the second input parameter
of spatial operators (including SDO_NN), when a UNION ALL view is used. i.e. the following
query won't work right now:
SELECT c.* FROM v1 c, another_table b WHERE b.id =1 and SDO_NN(c.shape, b.shape, 'sdo_num_res=1')= 'TRUE';
  • Creating a combined view of two spatially indexed tables Update:10-11

    Hi All, I'm using oracle 10g and C++ occi to store and retrieve data. I have two tables that are identical in structure, they have an SDO_GEOM column where I store lat/long/altitude info. When I store the data using a stored procedure, the data is pu

  • Performance issue when inserting into spatial indexed table with JDBC Update:11-30

    We have a table named 'feature' which has a "sdo_geometry" column, and we created spatial index on that column, CREATE TABLE feature ( id number, desc varchar, oshape sdo_gemotry) CREATE INDEX feature_sp_idx ON feature(oshape) INDEXTYPE IS MDSYS

  • How to create a relational view base on an xmltype table which included sev Update:10-11

    Hi, I am using oracle 11.2.0.1.0. how to create a relational view base on an xmltype table which content several different .xml files? Thanks. for examle: SQL> SELECT OBJECT_VALUE FROM document; Edited by: Cow on Jan 6, 2011 7:57 PMFor example I alre

  • Partioning spatially indexed tables Update:11-30

    I am a complete novice when it comes to partitioning, so can someone tell me any advantages/disadvantages/problems in partitioning a large RTREE spatially indexed table (using an SDO_GEOMETRY column) containing several million rows?Just to make sure

  • Materialized view issue with spatial index and UNION all. Update:11-30

    Hi guys, I'm trying to build the following materialized view: create materialized VIEW MV_ElectricalStuffs   refresh fast   AS   SELECT jb.ROWID,     jb.FID,     JB.NAME_NUMBER   FROM EL_BUS_BAR jb   UNION ALL   SELECT INC.ROWID,     INC.FID,     NUL

  • Specifying nologging while creating spatial indexes Update:11-30

    Hello Is it possible to specify NOLOGGING parameter while creating spatial indexes? When i am trying to specify this i get the following error message SQL> create index BUSH_sx on BUSH(BUSHLOCATION) indextype is mdsys.spatial_index nologging; create

  • View joining two spatial tables and strange CBO behaviour Update:11-30

    Hi all, I've following view in my database: CREATE OR REPLACE VIEW my_view AS SELECT id, 'table1' AS source, location FROM my_table1 UNION ALL SELECT id, 'table2' AS source, location FROM my_table2; When I execute query: SELECT * FROM my_view WHERE S

  • How to get a value from  select one choice (created by static view) Update:10-11

    Hi, Whene ever Iam trying to get value from select one choice which is created by static view iam getting only index.How to get the actual value in 11g .please help me anybody .Thanx in advance.... Edited by: 874530 on Jul 22, 2011 11:05 PMThnax for

  • Spatial Index problem with query Update:11-30

    I created a spatial index with user A. When i do a spatail query, i receive a good result. But, when i execute the same query with user B, i receive the following error : ERROR at line 1: ORA-13226: interface not supported without a spatial index ORA

  • How to create a triangle view with a select query? Update:10-11

    I need help to build a select query that will create a triangle view. Below is the table I have to query *{color:#ff0000}INITIAL TABLE{color}* *{color:#008000}AMOUNT | TRANSACTION_DATE | OPEN_DATE | TYPE{color}* 5 | 30-JAN-09 | 10-JAN-09 | A 10 | 12-

Recommended Content

Database error: [Table 'ac_posts' is marked as crashed and should be repaired]

SELECT * FROM ac_posts WHERE `ID` IN (2280901,851626,2538107,4752643,2749346,4434684,397046,2187355,4998258,1027585,778699,4536737,1652260,2735975,4523102,3831449,766127,4018527,27776,4623801,1748186,4488125,4148205,116533,966071,1610913,101203,5162668,876922,2915784,204200) LIMIT 15;