Project

General

Profile

« Previous | Next » 

Revision 6265

Added by Aaron Marcuse-Kubitza about 12 years ago

schemas/vegbien.sql: place: Removed placecode to prevent datasources from creating duplicate entries for the same place, with different placecodes. This was a problem with the original BIEN2 geoscrub dataset, which contained duplicates.

View differences:

vegbien.sql
628 628 
CREATE TABLE place (
629 629 
    place_id integer NOT NULL,
630 630 
    source_id integer NOT NULL,
631 
    placecode text,
632 631 
    canon_place_id integer,
633 632 
    matched_place_id integer,
634 633 
    coordinates_id integer,
......
641 640 
    geovalid boolean,
642 641 
    distance_to_country_m double precision,
643 642 
    distance_to_state_m double precision,
644 
    CONSTRAINT place_required_key CHECK (((((((placecode IS NOT NULL) OR (coordinates_id IS NOT NULL)) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)))
643 
    CONSTRAINT place_required_key CHECK ((((((coordinates_id IS NOT NULL) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)))
645 644 
);
646 645 

  		




  647
  646
  
    

  		




  ......




  6004
  6003
  
    

  		




  6005
  6004
  
    

  		




  6006
  6005
  
    
--


  		




  6007
  
  
    
-- Name: place_unique_within_creator_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  6008
  
  
    
--


  		




  6009
  
  
    

  		




  6010
  
  
    
CREATE UNIQUE INDEX place_unique_within_creator_by_code ON place USING btree (source_id, (COALESCE(placecode, '\N'::text))) WHERE (placecode IS NOT NULL);


  		




  6011
  
  
    

  		




  6012
  
  
    

  		




  6013
  
  
    
--


  		




  6014
  6006
  
    
-- Name: place_unique_within_creator_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  6015
  6007
  
    
--


  		




  6016
  6008
  
    

  		




  6017
  
  
    
CREATE UNIQUE INDEX place_unique_within_creator_by_name ON place USING btree (source_id, (COALESCE(continent, '\N'::text)), (COALESCE(country, '\N'::text)), (COALESCE(stateprovince, '\N'::text)), (COALESCE(county, '\N'::text)), (COALESCE(coordinates_id, 2147483647))) WHERE (placecode IS NULL);


  		




  
  6009
  
    
CREATE UNIQUE INDEX place_unique_within_creator_by_name ON place USING btree (source_id, (COALESCE(continent, '\N'::text)), (COALESCE(country, '\N'::text)), (COALESCE(stateprovince, '\N'::text)), (COALESCE(county, '\N'::text)), (COALESCE(coordinates_id, 2147483647)));


  		




  6018
  6010
  
    

  		




  6019
  6011
  
    

  		




  6020
  6012
  
    
--


  		












Also available in:  Unified diff