Re: idea for a geographically distributed database: how best to implement?

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(dot)bath(at)unix(dot)net