Обсуждение: Re: Location Data

* Adarsh Sharma wrote:

> Today I am facing a simple problem that I fail to solve after 2 day try.
>
> I have a places table in database whose structure is as :
>
> CREATE TABLE places
> (
> woe_id character varying(15) NOT NULL,
> iso character varying(6),
> "name" text,
> "language" character varying(6),
> place_type character varying,
> parent_woe_id character varying(15),
> lat numeric(12,8),
> lon numeric(12,8)
> CONSTRAINT places_pkey PRIMARY KEY (woe_id)
> )WITH ( OIDS=FALSE);
>
> It's simple *name *column contains the name of places in a hierarchical
> order.
> fore.g
>
> *woe_id iso name language places_type parent_woe_id lat lon
>
> 1 ZZ Earth ENG Supername 0 13.3445 234.666
> 10 IN INDIA ENG Country 1 12.44 234.667
> 11 IN J&K ENG State 10 4535.56 3453.77
> 12 IN Udhanput ENG District 11 1222 3443.8
> 15 IN Parth ENG Town 12 111.6 1222.5
>
> *I hope U understand what i am trying to explain.
> Now I want this data in the same table in extra columns fore.g
>
> *woe_id iso name language places_type parent_woe_id lat lon town
> district state country
>
> 1 ZZ Earth ENG Supername 0 13.3445 234.666
> 10 IN INDIA ENG Country 1 12.44 234.667
> 11 IN J&K ENG State 10 4535.56 3453.77 **INDIA*
> *12 IN Udhanput ENG District 11 1222 3443.8 **J&K **INDIA*
> *15 IN Parth ENG Town 12 111.6 1222.5 **Udhanput **J&K **INDIA*

Write a set of functions to get the higher-level structures (country for
states, etc.) for any given record, and put a trigger on the table that
populates the fields on insert and update. Sort of a materialized view.
If you have little query activity on the table, create a view that calls
the functions.

--
Christian

* Adarsh Sharma wrote:

> Christian Ullrich wrote:

>> Write a set of functions to get the higher-level structures (country
>> for states, etc.) for any given record, and put a trigger on the table
>> that populates the fields on insert and update.

>    All the world data is populated in the places table. Now I don't think
> insert occurs anymore now.

Then add the new fields to the table and update them from the function
results.

>> Sort of a materialized view. If you have little query activity on the
>> table, create a view that calls the functions.
>
> yes a function is must needed for this problem, bt any idea about the
> flow of the function.

Simple version:

create function get_ancestor(p_woe_id varchar, p_level varchar) returns
text language plpgsql as $$
declare
   v_woe_id varchar;
   v_name text;
   v_place_type varchar;
   v_parent varchar;
begin
   select woe_id, place_type, parent_woe_id into v_woe_id, v_place_type,
v_parent from places where woe_id = p_woe_id;
   if (not found or v_place_type = p_level) then
     return null;
   else
     while (found and v_place_type != p_level) loop
       select "name", place_type, parent_woe_id into v_name,
v_place_type, v_parent from places where woe_id = v_parent;
     end loop;
     if found then
       return v_name;
     else
       return null;
     end if;
   end if;
end;$$;

select get_ancestor('15', 'State') as state, get_ancestor('15',
'District') as district;

You could produce a more refined version using WITH RECURSIVE, but if
your table is already complete and all you need is to put in the
denormalized data, this will work just as well. Beware of cyclical
references.

--
Christian