From: Bath, David <dave.bath@unix.net>
To: pgsql-sql@postgresql.org
Cc: andy@areyoulocal.co.uk
Subject: Re: idea for a geographically distributed database: how best to implement?
Date: Tue, 22 Nov 2005 11:02:47 +1100
Message-ID: <200511221102.49528.dave.bath@unix.net> (raw)
In-Reply-To: <ECOWS04Mp8nkfZyufzT00010fe4@smtp-out4.blueyonder.co.uk>
References: <ECOWS04Mp8nkfZyufzT00010fe4@smtp-out4.blueyonder.co.uk>
On Thu, 17 Nov 2005 19:44, Andy Ballingall wrote:
> I've got a database for a website which is a variant of the 'show stuff near
> to me' sort of thing.
>
> Rather than host this database on a single server, I have a scheme in mind
> to break the database up geographically so that each one can run comfortably
> on a small server, but I'm not sure about the best way of implementing it.
There are a couple of philosophical perspectives I've come across in previous
work with cadastral data that may be useful.
1) A given point may be in more than one "hierarchy" of geographical units
(ofbiz.org implements this quite well with different region types and
a "region relationship type" table).
2) Some relevant hierarchies involve irregular shapes.
e.g. a) postal/zip codes are not squares
e.g. b) Often a postal/zip code system for a country is only loosely
based on states/province, as sometimes "twin cities" can
straddle a river and state/province boundary, but the postal
centre for both sides is handled from one side. (The same
often applies for business servicing). An example that comes
to mind is Mildura (Victoria/Australia) and its smaller
sibling across the Murray River, Wentworth (New South
Wales/Australia).
e.g. c) the "capture" area for many offices are often based on the
state/province, again, not square.
3) There are ways of looking at "approximate nearness" of irregular shapes
(although the same applies to squares) using terms such as "centric"
and "centroid", which involve things like drawing the smallest circle
AROUND a region that encloses ALL of it, or the largest circle INSIDE
a region. It might be relevant to consider the "slightly outside but
close" points in your "replication" as well. (In my previous example,
Mildura and Wentworth would be included in the databases of branch
offices in BOTH Vic and NSW, although one office should be considered
authoritative, as services for both might be through Victorian branch
offices, although Wentworth customers are likely to contact the NSW
branch office). It is useful to consider "extending" the diameter
of the circle enclosing the entire region by x%. Yep, "squaring the
circle" and "circling the square" are useful in the real world.
4) There are a lot of useful bits and pieces at opengis.org and postgis.org
5) There are many useful GIS functions in postgres to decide if a point is
inside an irregular polygon (including whether it has both convex and
concave bits), and whether any two shapes overlap.
Hope these points are useful.
--
David T. Bath
dave.bath@unix.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dave.bath@unix.net, andy@areyoulocal.co.uk
Subject: Re: idea for a geographically distributed database: how best to implement?
In-Reply-To: <200511221102.49528.dave.bath@unix.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox