Hi,
Since Canada Post hasn't programmed their automated web tools for calculating transportation costs to work with anything outside of Windows Internet Explorer, I'm obliged to write a web based program optimized for all those *other* browsers, myself. Part of this task requires that I set up tables in postgresql that match postal codes to transportation cost.
Canada Post provides a booklet for calculating transportation cost based on package weight and the first three characters of the postal code sequence. For instance, if I want to send a package to an address that includes G8F 1X1 as the postal code, I take the first 3 characters G8F and look them up in table 1.
Table 1
PostalCode Tarrif number
---------------------------
G4V 14
G8E-G8G 14
G4R-G4S 13
Since G8F falls in the G8E-G8G range, I now know that the tarrif number is 14. Taking the number 14, I go to table 2
Table 2
For tarrif Code 14
Weight(kg) Price
----------------------
1.0 5.37
1.5 5.61
2.0 5.82
If the weight of my package is 1kg, the price is 5.37 to send the package to the address bearing G8F as the first 3 characters of the postal code.
To render this in the database, I have done the following:
_____________________________
p_code |
=============================
pcode_id | tarrif |
-----------------------------
G4V | 14 |
-----------------------------
G8E | 14 |
-----------------------------
G8F | 14 |
-----------------------------
G8G | 14 |
-----------------------------
G4R | 13 |
-----------------------------
G4S | 13 |
-----------------------------
__________________________________
price_weight |
==================================
tarrif | weight(kg)| price |
----------------------------------
14 | 1.0 | 5.37 |
----------------------------------
14 | 1.5 | 5.61 |
----------------------------------
14 | 2.0 | 5.82 |
----------------------------------
13 | 1.0 | 5.20 |
----------------------------------
13 | 1.5 | 5.32 |
----------------------------------
13 | 2.0 | 5.42 |
Therefore my sql statement would look something like this:
SELECT price FROM price_weight
WHERE p_code.tarrif = price_weight.tarrif
AND pcode = 'G8F'
AND weight = '1.0';
I think this will work, but before I proceed, I'd like to ask 2 questions:
1.
Is it possible to further normalize the data in the p_code and price_weight tables above?
2.
Is it possible to abbreviate the number of records in the p_code table using regular expressions. For instance, to avoid repetition, I thought I'd use regular expressions, so that instead of entering the postal code into separate rows as such:
G4V 14
G8E 14
G8F 14
G8G 14
I could do something like this:
(G4V | G8[E-G]) 14
Somehow I don't think this is possible, but I'm looking for any way to minimize the number of postal codes that I have to enter, since there's a lot of them.
Anyway, I realize these questions may have more to do with database design than postgresql per se. If there's a better place to ask them, please point me in the right direction.
Thanks,
Mark
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings 1 1941
In order to reduce the number of rows in the p_code table, you could
modify it so that it had the following columns:
create table p_code (
pCode_Start varchar(3),
pCode_End varchar(3),
tariff integer,
primary key(pCode_Start, pCode_End)
);
If you need to insert a single code, use the following:
insert into pCode('G4V','G4V',14);
and a range as follows:
insert into pCode('G8E', 'G8G',14);
Your select statement is now:
SELECT price FROM price_weight pw, p_code pc
WHERE pc.tarrif = pw.tarrif
AND pCode_Start <= 'G8F'
AND pCode_End >= 'G8F'
AND weight = '1.0';
I think that should work OK...
John Sidney-Woollett
MT said: Hi,
Since Canada Post hasn't programmed their automated web tools for calculating transportation costs to work with anything outside of Windows Internet Explorer, I'm obliged to write a web based program optimized for all those *other* browsers, myself. Part of this task requires that I set up tables in postgresql that match postal codes to transportation cost.
Canada Post provides a booklet for calculating transportation cost based on package weight and the first three characters of the postal code sequence. For instance, if I want to send a package to an address that includes G8F 1X1 as the postal code, I take the first 3 characters G8F and look them up in table 1.
Table 1
PostalCode Tarrif number --------------------------- G4V 14 G8E-G8G 14 G4R-G4S 13
Since G8F falls in the G8E-G8G range, I now know that the tarrif number is 14. Taking the number 14, I go to table 2
Table 2
For tarrif Code 14 Weight(kg) Price ---------------------- 1.0 5.37 1.5 5.61 2.0 5.82
If the weight of my package is 1kg, the price is 5.37 to send the package to the address bearing G8F as the first 3 characters of the postal code.
To render this in the database, I have done the following:
_____________________________ p_code | ============================= pcode_id | tarrif | ----------------------------- G4V | 14 | ----------------------------- G8E | 14 | ----------------------------- G8F | 14 | ----------------------------- G8G | 14 | ----------------------------- G4R | 13 | ----------------------------- G4S | 13 | -----------------------------
__________________________________ price_weight | ================================== tarrif | weight(kg)| price | ---------------------------------- 14 | 1.0 | 5.37 | ---------------------------------- 14 | 1.5 | 5.61 | ---------------------------------- 14 | 2.0 | 5.82 | ---------------------------------- 13 | 1.0 | 5.20 | ---------------------------------- 13 | 1.5 | 5.32 | ---------------------------------- 13 | 2.0 | 5.42 |
Therefore my sql statement would look something like this:
SELECT price FROM price_weight WHERE p_code.tarrif = price_weight.tarrif AND pcode = 'G8F' AND weight = '1.0';
I think this will work, but before I proceed, I'd like to ask 2 questions:
1. Is it possible to further normalize the data in the p_code and price_weight tables above?
2. Is it possible to abbreviate the number of records in the p_code table using regular expressions. For instance, to avoid repetition, I thought I'd use regular expressions, so that instead of entering the postal code into separate rows as such:
G4V 14 G8E 14 G8F 14 G8G 14
I could do something like this:
(G4V | G8[E-G]) 14
Somehow I don't think this is possible, but I'm looking for any way to minimize the number of postal codes that I have to enter, since there's a lot of them.
Anyway, I realize these questions may have more to do with database design than postgresql per se. If there's a better place to ask them, please point me in the right direction.
Thanks,
Mark
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Anand Pillai |
last post by:
To search a word in a group of words, say a paragraph or a web page,
would a string search or a regexp search be faster?
The string search would of course be,
if str.find(substr) != -1:...
|
by: Lukas Holcik |
last post by:
Hi everyone!
How can I simply search text for regexps (lets say <a
href="(.*?)">(.*?)</a>) and save all URLs(1) and link contents(2) in a
dictionary { name : URL}? In a single pass if it could....
|
by: Chris Croughton |
last post by:
I'm trying to use the EXSLT regexp package from
http://www.exslt.org/regexp/functions/match/index.html
(specifically the match function) with the libxml xltproc (which
supports EXSLT), but...
|
by: John Welch |
last post by:
I have three tables and the way they are currently set up violates good
normalization, but I'm having trouble figuring out a better way. Can someone
suggest a more elegant solution?
My tables...
|
by: Jon Maz |
last post by:
Hi All,
I want to strip the accents off characters in a string so that, for example,
the (Spanish) word "práctico" comes out as "practico" - but ignoring case,
so that "PRÁCTICO" comes out as...
|
by: Matt Kruse |
last post by:
Are there any current browsers that have Javascript support, but not RegExp
support?
For example, cell phone browsers, blackberrys, or other "minimal" browsers?
I know that someone using Netscape...
|
by: Csaba Gabor |
last post by:
I need to come up with a function
function regExpPos (text, re, parenNum) { ... }
that will return the position within text of RegExp.$parenNum if there
is a match, and -1 otherwise.
For...
|
by: runsun pan |
last post by:
Hi
I am wondering why I couldn't get what I want in the following 3 cases
of re:
(A)
var p=/(+-?+):(+)/g
p.exec("style='font-size:12'")
-- // expected
|
by: hippomedon |
last post by:
Hello everyone,
I'm looking for some advice on whether I should break the
normalization rule. Normally, I would not consider it, but this seems
to be a special case.
I have created an...
|
by: Matt |
last post by:
Hello all,
I have just discovered (the long way) that using a RegExp object with
the 'global' flag set produces inconsistent results when its test()
method is executed. I realize that 'global'...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |