[postgis-users] Mantaining a live data set

[Matthew Pulis]

Hi,

 

I am working on a routing application (using pgRouting). From a database
view what is the best way to keep a live data set please?

 

I am taking into consideration that *apparently* you cannot pass a WHERE
clause in a routing query in pgRouting, it could be I am wrong and will make
things much easier in that case (but documentation shows otherwise).

 

Assumptions : 

- Table name I am working upon is : streets (50,000 rows) ( Structure :
http://yancho.pastebin.com/f1a40021c )

 

- PostgreSQL Version : "PostgreSQL 8.1.10 on i486-pc-linux-gnu, compiled by
GCC gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)"

 

- PostGIS Version : "POSTGIS="1.1.3" GEOS="2.1.4" PROJ="Rel. 4.4.9, 29 Oct
2004" USE_STATS"

 

- I create a new column in the streets table : STATUS default : 1, but if
street is unavailable I set it to 0.

 

 

 

My idea :

 

1. Create a TRIGGER on UPDATE (i.e. when status is set to 0)

 

2. Create a 2nd table : streets_2

 

3. Do a : SELECT * INTO streets_2 from streets WHERE status = 1

 

4. Recreate the Indexes on streets_2 ( Indexes found here :
http://yancho.pastebin.com/f2577f0b8 )

 

5. Rename streets to streets_3

 

6. Rename streets_2 to streets

 

7. Rename streets_3 to streets_2

 

 

Do you find anything wrong / things that can improve in this algorithm
please? Any other work around you suggest I take?

 

Thanks a lot beforehand for your help

 

Matthew

 

  _____  

I am using the free version of SPAMfighter for private users.
It has removed 22511 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter <http://www.spamfighter.com/len>  for free now!