Project

General

Profile

« Previous | Next » 

Revision 9922

schemas/vegbien.sql: geoscrub_input_view: exclude rows that have already been geoscrubbed, by anti-joining on geoscrub_output

View differences:

schemas/vegbien.sql
3080 3080
--
3081 3081

  
3082 3082
CREATE VIEW geoscrub_input_view AS
3083
    SELECT DISTINCT place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude" FROM (place JOIN coordinates USING (coordinates_id)) WHERE (place.country IS NOT NULL);
3083
    SELECT DISTINCT place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude" FROM ((place JOIN coordinates USING (coordinates_id)) LEFT JOIN geoscrub.geoscrub_output ON ((((((geoscrub_output.decimallatitude = coordinates.latitude_deg) AND (geoscrub_output.decimallongitude = coordinates.longitude_deg)) AND (geoscrub_output.country = place.country)) AND (geoscrub_output.stateprovince = place.stateprovince)) AND (geoscrub_output.county = place.county)))) WHERE ((place.country IS NOT NULL) AND (geoscrub_output.row_num IS NULL));
3084 3084

  
3085 3085

  
3086 3086
--

Also available in: Unified diff