From: "Bath, David" <dave.bath@unix.net>
Reply-To: dave.bath@unix.net
To: pgsql-sql@postgresql.org
Subject: Re: idea for a geographically distributed database: how best to
	implement?
Date: Tue, 22 Nov 2005 11:02:47 +1100
User-Agent: KMail/1.8
References: <ECOWS04Mp8nkfZyufzT00010fe4@smtp-out4.blueyonder.co.uk>
In-Reply-To: <ECOWS04Mp8nkfZyufzT00010fe4@smtp-out4.blueyonder.co.uk>
Cc: andy@areyoulocal.co.uk
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200511221102.49528.dave.bath@unix.net>

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