[Geocoding] Local Mirror of OSM Data...

[Anthony]

On Fri, Oct 23, 2009 at 1:42 PM, Tom Hughes <tom at compton.nu> wrote:
> On 23/10/09 18:25, Anthony wrote:
>
>> On Fri, Oct 23, 2009 at 1:11 PM, Tom Hughes<tom at compton.nu>  wrote:
>>>
>>> In the real world we don't all have infinite CPU time and I/O bandwidth
>>
>> Hence my point about using a modern database, where you can create
>> things like materialized views to make accessing the database
>> efficient without making the core schema itself a mess.
>
> I understand the concept of a materialized view, but I've never actually
> used one myself. There is of course a significant cost in terms of disk
> space if you create materialized views with a large data set like ours.

You're likely already using that disk space, though.  If you used a
materialized view for the current_nodes table, you wouldn't need the
current_nodes table.  You're basically already implementing
materialized views, you're just doing so in the code rather than in
the database, which if nothing else makes it more prone to mistakes.

Anyway, I don't care what you do with your database.  I'm merely
explaining why I do what I do with my databases, and why I've answered
Peter's question the way I have (which is the way I know how to do
things, not the way you or anyone else does things).

> One other obvious question is how materialized views interact with updates
> to the underlying tables? Do they have to be recomputed from scratch every
> time an underlying record is changed? Only it seems that solving the
> generalised problem of working out how to update a view given a specific
> change in the underlying data would be a hard one to solve.

If you want the answers to those questions, follow the link I gave to
Frederick (or buy Enterprise Rails by Dan Chak) and read chapter 14.
Mr. Chak goes into all the details of how to implement materialized
views in PostgreSQL much better than I can repeat them here.

I'm sorry if I'm being off topic.  As these are direct questions being
posed to me, I hope the list members can forgive me.