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