Rebuilt database

Finished rebuilding database and loading data into tables.

Need to test program tomorrow.

Created and loaded data into tables

Done:

1) Created two tables:

Stores IP numbers and locId:
CREATE TABLE ipNumbers (ipNum BIGINT NOT NULL,
locId INTEGER NOT NULL,
PRIMARY KEY (ipNum))

Stores test IP addresses and IP numbers:
CREATE TABLE testCensusIPs (ipAddr VARCHAR(15) NOT NULL,
ipNum BIGINT NOT NULL,
PRIMARY KEY (ipAddr))

2) Loaded test data into table testCensusIPs

3) Wrote Java program to test data (insert IP numbers and locId into table ipNumbers). Need debug.

Today's meeting

1) Use data from Dylan.
2) Build table to store IP addresses and their related information (eg. country, region and city).
3) Geographically Mark IP addresses on the map based on their latitude and longitude. Using different colors to distinguish among cities. Radius of a circle (or dot) marked on a city represents the amount of IP addresses located in the city.
4) Meeting next week canceled.

Corrected a typo in table location. ("longtitude" -> "longitude") :)

Read Amanda's works and programmed cities distribution

Read and tested geo.countrydistribution from Amanda's code. Designed and programmed cities distribution based on previous works.

Test

Done:

1) Changed DECIMAL to BIGINT for startIpNum and endIpNum in table blocks

2) Tested cities in Nova Scotia. It returns 206 distinct cities in Nova Scotia, including Wolfville.

Create tables and load data into database

Done:

1) Solved the problem which I got yesterday. Thanks to Darcy and Dylan.

2) Create two tables: blocks and location. Due to the oversized integer of startIpNum and endIpNum in blocks, I used DECIMAL(10,0) as their data type instead of INTEGER.

3) Load csv files into database. Need to delete all double-quote from the original files, then load into database. (Thanks to Darcy)

Need to test all cities in Nova Scotia tomorrow.

Setup database

Here are what I have done today:

1) Created a new database named GEOCITY in server machine

Problems:

1) Got a problem while I was creating tables in database. For example, typing the query: db2 CREATE TABLE blocks (startIpNum INTEGER NOT NULL, endIpNum INTEGER NOT NULL, locId INTEGER NOT NULL, PRIMARY KEY (endIpNum)). It returns syntax error near unexpected token'('. I tried the same query on my machine which was running under Windows XP. It run well and created the table in database. I am trying to figure out the problem.