[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Gnumed-devel] Questions re database schema:street:address:urb:coun
Re: [Gnumed-devel] Questions re database schema:street:address:urb:country
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
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
"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
"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.