I want to store duplicate geometry intentionally, will it have any problems ??.... I already posted this question in this forum, but i am posting again to get more suggestions.If you didn't get enough suggestions first time around, it is probably because you are really the only person who can answer that question. Depending on your exact circumstances the answer could be "Yes", "No" or "It won't make much difference".
Because same data comes from different sources and going to be big database systemHow about you give us some more useful context.
So the issue about what constitutes valid duplication or not is the way you implement constraints on the table.
ALTER TABLE flora ADD CONSTRAINT flora_plant_uq UNIQUE(plant_id,obs_date);
SQL Developer 3.x has a free data modelling tool built in: it is an excellent tool for designing basic data models.
alter table flora add constraint flora_obs_date_ck check(obs_date >= '01/JAN/2005');
However, this would not work for linestring/polygon data. For example, let's assume the FLORA table allowed observations of a plant to include a patch ie not a single plant (eg a tree) but an area of blackberries. We can't do the following because one cannot reference a function in the constraint:
alter table flora add constraint flora_location_uq unique (location.sdo_point.x,location.sdo_point.y); -- or with other columns alter table flora add constraint flora_location_uq unique (plant_id,obs_date,location.sdo_point.x,location.sdo_point.y);
OK, so how can we do this?
alter table flora add constraint flora_location_uq unique (plant_id,obs_date,location.get_wkt());
create or replace trigger flora_location_ai after insert on flora for each row Declare v_ok pls_integer; begin SELECT 1 INTO v_ok FROM flora g WHERE g.plant_id = new.plant_id AND g.obs_date = new.obs_date AND SDO_EQUAL(g.geom,:new.geom) = 'TRUE'; -- We found a duplicate, so raise an error raise_application_error(-20001,'Duplicate location found for (' || :new.plant_id || ',' || new.obs_date || ')'); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; -- No duplicates End; /
data source is priority than duplicationIn that case it sounds like you might need to store the duplicates. An alternative would be to store one version of each geometry and add metadata that indicates all the data sources that provided that geometry. However that then complicates your loading process as you need to detect duplicates, set the metadata appropriately and then deal with it again when data is supplied again.