PgClosureTreeRebuild

Some time ago I was involved in a project aiming to systemize a pandemonium of databases. Especially hard were the toponym ones. The source data was a deep XML tree with regions and cities at the top and streets and houses at the bottom.

| id         | parent_id  | name       |
| ---------- |------------|------------|
| 1          | 2          | Kremlin    |
| 2          |            | Moscow     |
| 3          | 2          | Red Square |
| …   (1M+ unsorted records)         … |

I had to put this tree into PostgreSQL to allow queries. A typical query was, for example, to fetch all the streets of the cities within a specific region or to fetch the whole path from a particular street at the bottom up to the root node, etc.

Author

In the same orbit

Explore more OSS projects

Contact us

We’d love to hear from you! We’re not really all that evil, and we love discussing potential projects, intriguing ideas, and new opportunities. Complete the form below or drop us a line at surrender@evilmartians.com.

Martians at a glance
16
years in business

A product development consultancy that works with startups and established businesses, while also creating open source-based products and services