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

[J Busser]

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.