Re: idea for a geographically distributed database: how best to implement? - Mailing list pgsql-sql

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
differentregion 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/zipcode 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,
notsquare.
 

3) There are ways of looking at "approximate nearness" of irregular shapes  (although the same applies to squares)
usingterms such as "centric"  and "centroid", which involve things like drawing the smallest circle  AROUND a region
thatencloses 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
thedatabases of branch  offices in BOTH Vic and NSW, although one office should be considered  authoritative, as
servicesfor 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
whetherit has both convex and  concave bits), and whether any two shapes overlap.
 

Hope these points are useful.
-- 
David T. Bath
dave.bath@unix.net