Re: Location Data

From: Christian Ullrich <chris(at)chrullrich(dot)net>
To: Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
Cc: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Location Data
Date: 2011-06-20 06:31:57
Message-ID: 4DFEE95D.1020008@chrullrich.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

* 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message BangarRaju Vadapalli 2011-06-20 06:33:23 Re: Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
Previous Message Tom Lane 2011-06-20 05:59:53 Re: Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )