sort of a backwards find - not sure how...

[John May]

I have been asked to create a "neighborhood school finder" system for my local school district. Users want to enter a street address and be told if that address falls within the school district boundaries, and if so, which schools (elementary, middle) that address relates to. They have given me a file of address ranges that I am scrubbing that has the following discrete data elements:

(1) street number range - minimum

(2) street number range - maximum

(3) odd or even indicator

(4) street direction (NE, SW, etc)

(5) Street Name

(6) elementary school code

(7) middle school code.

(There is only one high school)

The "odd or even" indicator is important because boundaries tend to run down the middle of streets. I don

[Vaughan]

And how many $million have they allocated for the budget?

OK, some useful advice. I'd see the easiest way of doing this would be to create a database where each record is a house (maybe a field for number, street, suburb) with the neighborhood school already worked out. People then basically find their house in the database and it tells them the school. The smart bit would be focussed on ways to eficiently generate all the records invloved. The technology used to perform the find is low and hence easy to implement.

The alternative method (to realise something like what that you suggest) might require less data entry, but the technology to perform the find would require some sort of artificial intelligence. Seriously.

What's wrong with a map that has thick coloured boundary lines drawn on it?

[danjacoby]

Seems to me that once you define the parameters to be met for inclusion in the school district, the next step is to script the entered info to see if it meets the parameters.

I highly recommend separating the house number from the street name -- use separate fields; it'll make it easier.

[John May]

Thanks for the responses. Given our fiscal constraints, the big map with thick colored lines may win out as the solution.

I can understand the simplicity of listing every household and mapping it to the schools. The problem there is finding a valid and current data source and creating the file with probably 25,000 records and then maintaining it when a new dwelling or development is added to the area. Being able to determine which valid range a specific street number belongs in would greatly reduce the number of records (to probably about 1,000) and be much easier to maintain.

So you think this is too big a nut to try to crack with my limited skills and resources?

[Vaughan]

The big map with thick coloured lines is much easier to slap on a photocopier and hand out to people or pop into a letterbox!

I dunno if it's too big a job for you -- it could be a great "baptism of fire" learning experience for you. A lot depends on whether the clients set a deadline for completion, and how serious they are at getting it working perfectly.

If it were me I'd try to get two working prototypes made -- one for each way of doing the job as discussed earlier: use them to determine the pros and cons of each method, then make a decision as to which is best. Yeah, that's a lot of work, which is why I asked about the budget. Most people have no idea how expensive database development is.

[Jack]

I'll toss this out as a starting point to those who have a lot more experience than I do.

Create a lookup key based on the number range,(using the minimum and maximum) street name,direction and even/odd.

For example- 100-500 Alder SW even

100-500 Alder SW odd

A conditional value list based on Street and mimimum / max range would prevent the selection outside of that range.

Jack

[Vaughan]

My concern with any system like this is that even small differences in typing (eg, "43a Smith Rd" vs "43 Smith Road" or "43a Smith Rd." or "43 a Smith Rd") will ender the whole system inopperative. This makes the whole thing pretty fragile.

Are text-based keys case sensitive? ie will it match A and a?

[John May]

The street number has to be numeric, because I have to determine if it is even or odd (which side of the street). So I will have to validate that for numeric only.

I can control the Direction (SE, NW, etc) in a value list, and I can control the street name the same way.

By concatenating the odd/even flag, the direction field, and the street name, I could search for a similarly concatenated field in the database, and get back a list of 1-10 or so possible matches (wouldn't ever be more than that). From that the user could select the one that has the range that contains their street number and get to proper record. I guess I am wondering if there is a way to script something that loops through that found set and shows only the final result, instead of the user having to pick it from a list. That would be aq 100% solution instead of a 80% solution.

[BobWeaver]

I'm not sure that this is such a difficult problem. But it depends how accurate the data is. Since you would have one record for every street segment in the city, the user is simply searching for a single record. Using value lists to restrict user input to proper format of addresses should allow a system to work. I agree with previous comments about breaking up the address into separate fields for street name, street number and direction etc. When the user wants to find a school, the address is entered via value lists. Assume the following typical records

Record 1:

StreetName: Maple

StartAdr: 121

EndAdr: 389

Even/Odd: odd

Direction: NE

ElementarySch: Bob's house-o-lernin'

Record 2:

StreetName: Maple

StartAdr: 120

EndAdr: 390

Even/Odd: even

Direction: NE

ElementarySch: Happy Puppy Obedience School

Record 3:

StreetName: Maple

StartAdr: 391

EndAdr: 899

Even/Odd: odd

Direction: NE

ElementarySch = Bob's house-o-lernin'

Record 4:

StreetName: Maple

StartAdr: 121

EndAdr: 389

Even/Odd: odd

Direction: NE

ElementarySch: Bob's house-o-lernin'

If I live at 220 Maple street I would perform the following find:

StreetName = Maple (selected from value list)

StartAdr >= 220 (manually entered and validated)

EndAdr <= 220 (ditto)

Even/Odd = even

Direction = NE

That should bring up only record #2 which is the correct school for my address, and hence, I send my kids to Happy Puppy Obedience School.

For street numbers like "123A," simply strip off the non-numeric part since school divisions won't be split between 123, 123A, and 123B anyway.

You can determine if the street address is even or odd with this formula:

choose(mod(StreetNumber,2),"even","odd")

HTH