Project

General

Profile

« Previous | Next » 

Revision 582

Added by Aaron Marcuse-Kubitza about 13 years ago

vegbien.sql: Do location duplicate elimination independently on code or lat/long, allowing duplicate entries with NULLs to exist when a location is incompletely specified

View differences:

schemas/vegbien.for_ERD.my.sql
111 111 
CREATE TABLE location
112 112 
(
113 113 
  location_id int(11) NOT NULL AUTO_INCREMENT,
114 
  authorlocationcode character varying(30) NOT NULL DEFAULT '',
114 
  authorlocationcode character varying(30),
115 115 
  reference_id int(11),
116 116 
  parent_id int(11),
117 
  reallatitude double precision NOT NULL DEFAULT 'NaN',
118 
  reallongitude double precision NOT NULL DEFAULT 'NaN',
117 
  reallatitude double precision,
118 
  reallongitude double precision,
119 119 
  locationaccuracy double precision,
120 120 
  confidentialitystatus int(11) NOT NULL DEFAULT 0,
121 121 
  confidentialityreason character varying(200),
......
136 136 
  CONSTRAINT location_reference_id FOREIGN KEY (reference_id)
137 137 
      REFERENCES reference (reference_id) MATCH SIMPLE
138 138 
      ON UPDATE CASCADE ON DELETE CASCADE,
139 
  CONSTRAINT location_keys UNIQUE (reference_id , authorlocationcode , reallatitude , reallongitude ),
140 
  CONSTRAINT location_keys_subplot UNIQUE (parent_id , authorlocationcode , reallatitude , reallongitude )
139 
  CONSTRAINT location_keys_code UNIQUE (reference_id , authorlocationcode ),
140 
  CONSTRAINT location_keys_coords UNIQUE (reference_id , reallatitude , reallongitude ),
141 
  CONSTRAINT location_keys_subplot_code UNIQUE (parent_id , authorlocationcode ),
142 
  CONSTRAINT location_keys_subplot_coords UNIQUE (parent_id , sublocationxposition , sublocationyposition )
141 143 
);
142 144 

  		




  143
  145
  
    
CREATE TABLE locationevent -- VegBank's observation table.


  		












  

    
      schemas/vegbien.for_wiki.sql
    
  





  1
  1
  
    
CREATE TABLE location


  		




  2
  2
  
    
(


  		




  3
  3
  
    
  location_id serial NOT NULL,


  		




  4
  
  
    
  authorlocationcode character varying(30) NOT NULL DEFAULT ''::character varying,


  		




  
  4
  
    
  authorlocationcode character varying(30),


  		




  5
  5
  
    
  reference_id integer,


  		




  6
  6
  
    
  parent_id integer,


  		




  7
  
  
    
  reallatitude double precision NOT NULL DEFAULT 'NaN'::double precision,


  		




  8
  
  
    
  reallongitude double precision NOT NULL DEFAULT 'NaN'::double precision,


  		




  
  7
  
    
  reallatitude double precision,


  		




  
  8
  
    
  reallongitude double precision,


  		




  9
  9
  
    
  locationaccuracy double precision,


  		




  10
  10
  
    
  confidentialitystatus integer NOT NULL DEFAULT 0,


  		




  11
  11
  
    
  confidentialityreason character varying(200),


  		












  

    
      schemas/vegbien.for_ERD.sql
    
  





  111
  111
  
    
CREATE TABLE location


  		




  112
  112
  
    
(


  		




  113
  113
  
    
  location_id serial NOT NULL,


  		




  114
  
  
    
  authorlocationcode character varying(30) NOT NULL DEFAULT ''::character varying,


  		




  
  114
  
    
  authorlocationcode character varying(30),


  		




  115
  115
  
    
  reference_id integer,


  		




  116
  116
  
    
  parent_id integer,


  		




  117
  
  
    
  reallatitude double precision NOT NULL DEFAULT 'NaN'::double precision,


  		




  118
  
  
    
  reallongitude double precision NOT NULL DEFAULT 'NaN'::double precision,


  		




  
  117
  
    
  reallatitude double precision,


  		




  
  118
  
    
  reallongitude double precision,


  		




  119
  119
  
    
  locationaccuracy double precision,


  		




  120
  120
  
    
  confidentialitystatus integer NOT NULL DEFAULT 0,


  		




  121
  121
  
    
  confidentialityreason character varying(200),


  		




  ......




  136
  136
  
    
  CONSTRAINT location_reference_id FOREIGN KEY (reference_id)


  		




  137
  137
  
    
      REFERENCES reference (reference_id) MATCH SIMPLE


  		




  138
  138
  
    
      ON UPDATE CASCADE ON DELETE CASCADE,


  		




  139
  
  
    
  CONSTRAINT location_keys UNIQUE (reference_id , authorlocationcode , reallatitude , reallongitude ),


  		




  140
  
  
    
  CONSTRAINT location_keys_subplot UNIQUE (parent_id , authorlocationcode , reallatitude , reallongitude )


  		




  
  139
  
    
  CONSTRAINT location_keys_code UNIQUE (reference_id , authorlocationcode ),


  		




  
  140
  
    
  CONSTRAINT location_keys_coords UNIQUE (reference_id , reallatitude , reallongitude ),


  		




  
  141
  
    
  CONSTRAINT location_keys_subplot_code UNIQUE (parent_id , authorlocationcode ),


  		




  
  142
  
    
  CONSTRAINT location_keys_subplot_coords UNIQUE (parent_id , sublocationxposition , sublocationyposition )


  		




  141
  143
  
    
);


  		




  142
  144
  
    

  		




  143
  145
  
    
CREATE TABLE locationevent -- VegBank's observation table.


  		












  

    
      schemas/vegbien.sql
    
  





  1124
  1124
  
    

  		




  1125
  1125
  
    
CREATE TABLE location (


  		




  1126
  1126
  
    
    location_id integer NOT NULL,


  		




  1127
  
  
    
    authorlocationcode character varying(30) DEFAULT ''::character varying NOT NULL,


  		




  
  1127
  
    
    authorlocationcode character varying(30),


  		




  1128
  1128
  
    
    reference_id integer,


  		




  1129
  1129
  
    
    parent_id integer,


  		




  1130
  
  
    
    reallatitude double precision DEFAULT 'NaN'::double precision NOT NULL,


  		




  1131
  
  
    
    reallongitude double precision DEFAULT 'NaN'::double precision NOT NULL,


  		




  
  1130
  
    
    reallatitude double precision,


  		




  
  1131
  
    
    reallongitude double precision,


  		




  1132
  1132
  
    
    locationaccuracy double precision,


  		




  1133
  1133
  
    
    confidentialitystatus integer DEFAULT 0 NOT NULL,


  		




  1134
  1134
  
    
    confidentialityreason character varying(200),


  		




  ......




  4051
  4051
  
    

  		




  4052
  4052
  
    

  		




  4053
  4053
  
    
--


  		




  4054
  
  
    
-- Name: location_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 


  		




  
  4054
  
    
-- Name: location_keys_code; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 


  		




  4055
  4055
  
    
--


  		




  4056
  4056
  
    

  		




  4057
  4057
  
    
ALTER TABLE ONLY location


  		




  4058
  
  
    
    ADD CONSTRAINT location_keys UNIQUE (reference_id, authorlocationcode, reallatitude, reallongitude);


  		




  
  4058
  
    
    ADD CONSTRAINT location_keys_code UNIQUE (reference_id, authorlocationcode);


  		




  4059
  4059
  
    

  		




  4060
  4060
  
    

  		




  4061
  4061
  
    
--


  		




  4062
  
  
    
-- Name: location_keys_subplot; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 


  		




  
  4062
  
    
-- Name: location_keys_coords; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 


  		




  4063
  4063
  
    
--


  		




  4064
  4064
  
    

  		




  4065
  4065
  
    
ALTER TABLE ONLY location


  		




  4066
  
  
    
    ADD CONSTRAINT location_keys_subplot UNIQUE (parent_id, authorlocationcode, reallatitude, reallongitude);


  		




  
  4066
  
    
    ADD CONSTRAINT location_keys_coords UNIQUE (reference_id, reallatitude, reallongitude);


  		




  4067
  4067
  
    

  		




  4068
  4068
  
    

  		




  4069
  4069
  
    
--


  		




  
  4070
  
    
-- Name: location_keys_subplot_code; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 


  		




  
  4071
  
    
--


  		




  
  4072
  
    

  		




  
  4073
  
    
ALTER TABLE ONLY location


  		




  
  4074
  
    
    ADD CONSTRAINT location_keys_subplot_code UNIQUE (parent_id, authorlocationcode);


  		




  
  4075
  
    

  		




  
  4076
  
    

  		




  
  4077
  
    
--


  		




  
  4078
  
    
-- Name: location_keys_subplot_coords; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 


  		




  
  4079
  
    
--


  		




  
  4080
  
    

  		




  
  4081
  
    
ALTER TABLE ONLY location


  		




  
  4082
  
    
    ADD CONSTRAINT location_keys_subplot_coords UNIQUE (parent_id, sublocationxposition, sublocationyposition);


  		




  
  4083
  
    

  		




  
  4084
  
    

  		




  
  4085
  
    
--


  		




  4070
  4086
  
    
-- Name: location_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 


  		




  4071
  4087
  
    
--


  		




  4072
  4088
  
    

  		












Also available in:  Unified diff