[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Gnumed-devel] Questions re database schema:street:address:urb:coun

From: J Busser
Subject: Re: [Gnumed-devel] Questions re database schema:street:address:urb:country
Date: Wed, 1 Sep 2004 00:16:36 -0700

Concerning street normalization and suburbs:

At 6:38 PM +0200 8/31/04, Karsten Hilbert wrote:
1) make sure what is *meant* by suburb, eg. "a part of a larger
   locality, used to be a separate locality however far back
   in time"
2) make [suburb] an attribute of street since street+urb+postcode
   definitely defines which suburb we are talking about

At 7:26 AM +1000 9/1/04, E Dodd wrote:
We definitely have sub-urbs in our town as well as villages outside the town
They exist on signs and people use them to describe which part
of the town, but there are no overlapping road names within the town....

There exist roadways that are continuous from one town to the next.

In town A it may be named Lakeshore Road

As one leaves town A, the roadway may be named Highway 1A
(e.g. a "scenic" alternate to highway 1)
People may reside along this roadway!

Crossing into town B the roadway may still bear the name Lakeshore Road. In truth - - - e.g. because town B residents have sloped views and higher property values - - - the roadway might be called Lakeside Drive ;-) but we will assume Lakeshore Road for this example and assume some other patient in the practice has already been registered as living on Lakeshore Road in town A.

When asking/entering/editing a new patient's address, they will typically say "I live at 2155 Lakeshore".

We would presumably skip the 2155 initially in order to input Lakeshore, and observe (based on an existing value) we can ask the patient:
"That's Lakeshore here in town A right?" and the patient could say
"No, I'm on Lakeshore over in town B" and if this combination did not yet exist, the user would, after inputting 2155 Lakeshore, advance into the URB field where they might easily be offered town B because there are patients from town B in the practice - - - they just don't live on Lakeshore Road.

Existing street/urb combinations would save us having to bother inputting the state (or, in Canada, the "Province") code and the country code which could be derived automatically from street/urb.

I am not sure whether free databases are reliably available to offer valid postcodes for our patients' street addresses. Still, we *could* pick postcodes from existing values for street/urb combinations in the practice or, if these are unsuitable, the user can add one.

In the example of Lakeshore above, if the roadway should ever be renamed, the change would likely only occur at the URB level i.e. one URB has no authority to change the roadway's name outside of its URB boundaries. There exist super- (regional) authorities which sometimes rename major connecting roadways (such as highways) but in this case, the name could be updated for each of the affected road "segments" across multiple affected URBs.

Now within an URB - - - e.g. within town A - - - a roadway *could* have more than one segment e.g. where a road curves, or bends, or becomes angled at an intersection. Each segment may have its own name. Across segments, the numbering of the buildings could simply continue, or the numbering could abruptly change to match the adjacent streets, or to respect a north-south or east-west "divide". This seems just a case of each segment being able to be treated as a separate "street", each traveling through only a portion of the town.

What *was* an URB (Karsten's example 1) can merge with/into a larger URB. This larger URB might take on a new name, or might keep one of the original names. Yet a longtime resident may persist using the name to which they are accustomed. If we did not want to get "rid" of the old town name, we could swap it into the street/urb/postal "suburb" value before overwriting with the "new" URB name or could "adjust" the URB name to append the new name in parentheses or vice versa:
"old town name (new URB name)".

I remain foggy on how OPERATIONALLY to use suburbs. In my URB of Vancouver, names "like" suburbs are used to describe or define:
- the distribution of community centres
- elementary and secondary school catchment/eligibility boundaries
- voting districts (municipal, provincial, federal and these are non-identical)
- real estate administration...

The above uses do not all share a single set of names / boundaries!

We might be most interested in health-related usage for example the City is divided into "health units" again a problem as these do not match the traditional suburb names or boundaries.

Maybe the biggest problem: suburb boundaries tend to be defined by road and street intersections such as 1st avenue through 16th avenue between Burrard St and MacDonald St. This may correspond to a range of street building addresses, and a range of postal codes, yet whichever administrative body defined the boundaries would not have mapped them to specific ranges of addresses or postal codes.

I do agree that the suburb could be stored/moved in the schema to the table "street" because this table breaks each street into rows based on postcode and any one street name/id_urb/postcode seems small enough not to fall across two or more suburbs.

reply via email to

[Prev in Thread] Current Thread [Next in Thread]