-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE place (

    place_id int(11) NOT NULL,

    reference_id int(11) NOT NULL,

    placecode text,

    canon_place_id int(11),

    matched_place_id int(11),

    coordinates_id int(11),

    placename_id int(11),

    continent text,

    country text,

    stateprovince text,

    county text,

    description text,

    georeference_valid int(1),

    distance_to_country_m double precision,

    distance_to_state_m double precision

);

--

-- Name: TABLE place; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: COLUMN place.matched_place_id; Type: COMMENT; Schema: public; Owner: -

--

--

-- Name: place_set_canon_place_id(place, int(11), int(1)); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: place_set_canon_place_id_on_insert(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: place_set_canon_place_id_on_update(); Type: FUNCTION; Schema: public; Owner: -

--

--

-- Name: place_set_canon_place_id_on_insert; Type: TRIGGER; Schema: public; Owner: -

--

--

-- Name: place_set_canon_place_id_on_update; Type: TRIGGER; Schema: public; Owner: -

--

--

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: place; Type: TABLE; Schema: public; Owner: -; Tablespace: 

--

CREATE TABLE place (

    place_id integer NOT NULL,

    reference_id integer NOT NULL,

    placecode text,

    canon_place_id integer,

    matched_place_id integer,

    coordinates_id integer,

    placename_id integer,

    continent text,

    country text,

    stateprovince text,

    county text,

    description text,

    georeference_valid boolean,

    distance_to_country_m double precision,

    distance_to_state_m double precision,

    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)))

);

--

-- Name: TABLE place; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON TABLE place IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.

To use a custom hierarchy of placenames with no explicit column, point to the lowest-rank placename in placename_id.';

--

-- Name: COLUMN place.matched_place_id; Type: COMMENT; Schema: public; Owner: -

--

COMMENT ON COLUMN place.matched_place_id IS 'The closest match to this place. Places should be linked in a three-level hierarchy of datasource place -> verbatim place -> accepted place.

An accepted place should point to itself in this field. This will happen automatically by setting it to the special value 0.';

--

-- Name: place_set_canon_place_id(place, integer, boolean); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION place_set_canon_place_id(new place, old_matched_place_id integer DEFAULT NULL::integer, is_update boolean DEFAULT false) RETURNS place

    LANGUAGE plpgsql

    AS $$

BEGIN

    IF NOT is_update

        OR new.matched_place_id IS DISTINCT FROM old_matched_place_id THEN

        IF new.matched_place_id IS NOT NULL THEN

            IF new.matched_place_id = new.place_id THEN -- self-reference

                new.canon_place_id := new.place_id; -- make self-reference

            ELSE -- propagate from matched place

                new.canon_place_id := (

                    SELECT canon_place_id

                    FROM place

                    WHERE place_id = new.matched_place_id

                );

            END IF;

            IF is_update THEN

                -- Update canon_place_id on places that resolve to this place

                UPDATE place

                SET canon_place_id = new.canon_place_id

                WHERE matched_place_id = new.place_id

                AND place_id != new.place_id -- no infinite recursion

                ;

            END IF;

        ELSE -- no matched place

            new.canon_place_id := new.place_id; -- make self-reference

        END IF;

    END IF;

    RETURN new;

END;

$$;

--

-- Name: place_set_canon_place_id_on_insert(); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION place_set_canon_place_id_on_insert() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    new := place_set_canon_place_id(new);

    RETURN new;

END;

$$;

--

-- Name: place_set_canon_place_id_on_update(); Type: FUNCTION; Schema: public; Owner: -

--

CREATE FUNCTION place_set_canon_place_id_on_update() RETURNS trigger

    LANGUAGE plpgsql

    AS $$

BEGIN

    new := place_set_canon_place_id(new, old.matched_place_id, true);

    RETURN new;

END;

$$;

--

-- Name: place_set_canon_place_id_on_insert; Type: TRIGGER; Schema: public; Owner: -

--

CREATE TRIGGER place_set_canon_place_id_on_insert BEFORE INSERT ON place FOR EACH ROW EXECUTE PROCEDURE place_set_canon_place_id_on_insert();

--

-- Name: place_set_canon_place_id_on_update; Type: TRIGGER; Schema: public; Owner: -

--

CREATE TRIGGER place_set_canon_place_id_on_update BEFORE UPDATE ON place FOR EACH ROW EXECUTE PROCEDURE place_set_canon_place_id_on_update();

--