CTC #167a: Geographic Coordinate Correction Management Software

**Do you want to change the world?** How about changing just one coordinate at a time? :-) We have a *ton* of latitude/longitude pairs which have varying degrees of accuracy, from correct-within-inches all the way to absolute garbage. Furthermore, the database is constantly getting polluted with *new* garbage data, so the correction process will be an ongoing one. You'll be writing **very simple** PHP/MySQL software which only *manages* the flow of people who are going to manually make the actual corrections; _you are not going to be the one to correct the data yourself!_ Here are the four files that we need: **cron/[login to view URL]**: A task already exists which is identifying possible bad coordinates as they appear. Your software will start by capturing these potential problem coordinates to the geo_outliers table, and email an admin that they need to be addressed. **admin/[login to view URL]**: The next step is that the admin goes to a screen which states how many potential problem coordinates there are currently, and lets them select a certain number to be assigned to a certain worker. A text file is generated which itemizes all work to be performed. (This text file isn't truly needed for this software, as it is a duplicate of what was assigned in geo_outliers, but it performs a legal function of specifying the assignment in full.) **worker/[login to view URL]**: Each worker is presented with a screen that shows the next street address which needs to be considered. A Google Maps interface is presented with a pin showing the current geographic coordinate in our database. The worker can choose to let it remain where it stands (i.e., it's completely correct), or, more commonly, they will move it to where it needs to be to best represent the street address shown. (We may also show different (unmovable) pins which show where Google Maps and other data sources feel that that address belongs.) Once the worker hits a submit button, that work is captured, and they are presented with the next coordinate to correct. A text file is built during each session, showing all the work that was performed. (Again, this file can be considered more of a "receipt", as it duplicates what happened in the database, but it serves a legal function of recording the work performed.) (Continued below.) ## Deliverables (Continued from above.) To correct a coordinate, workers can drag pins on the Google Maps interface, from the initial wrong location to the correct location. Since we don't initially know the scale of how wrong a point is, we have geo_address.gmaps_latitude and geo_address.gmaps_longitude, which reflect Google Maps' own best guess. (Maybe this could be shown as a different color, but *unmovable*, pin.) The distance between the coordinate's initial position and the gmaps_* position can be used to calculate an initial scale for presenting the map to the worker. i.e., The initial amount of correction might be one city block, or the full width of the earth. Similarly, there is a geo_address.predicted_latitude and geo_address.predicted_longitude; maybe this can be shown as a third color pin, also unmovable. **admin/[login to view URL]**: Once the worker communicates that their segment of the work is complete, the admin then is able to view Google Maps interface screens that show the before-and-after pictures of what that worker did, and then accept or reject the work. **Important:** You do *not* need to manage authentication *at all!!* You will not see users logging in/out, user creation, password creation/forgetting/retrieving, etc. This will all be managed *outside* of the scope of this project! You need to assume that if a person is in the **admin** or **worker** areas, then they belong there, period. The closest you will need to get to this is to tie $_SERVER['REMOTE_USER'] (or $_SERVER['PHP_AUTH_USER'] ???) to [login to view URL] and thus [login to view URL] . (We will use phpMyAdmin and the hosting company's cpanel to manage the sys_workers table and worker/admin authentication.) **Important:** Please understand that there is really no need to manage a web "session" (state of data over time) in worker/[login to view URL] . You can simply capture the prior page's "receipt" in a hidden field, which keeps getting supplemented and passed to the next iteration, growing by one line at a time. There is no concept of logging in/out or maintaining a server-side session here. Just do this *on the cheap* with the hidden field. If the worker's browser crashes, that's just too bad, i.e., the work would be captured in geo_outliers, but the receipt would be lost. **Important:** It is outside the scope of this project as to *how* we determine which coordinates are outliers. While it is an interesting question, it does not affect your software. You can assume that you will have an **is_potential_outlier()** function which determines whether or not a coordinate pair is a potential outlier; do not worry *how* that function is written. For testing, just substitute a very simple "rectangle": ((SOUTH_EDGE ≤ latitude ≤ NORTH_EDGE) and (WEST_EDGE ≤ longitude ≤ EAST_EDGE)) **Important:** You do not need to code *any* communication system between the admins and the workers. This will be performed by the worker marketplace itself, and is thus outside of the scope of this project. It is the marketplace that requires the two textfiles for legal purposes; they are somewhat superfluous (i.e., not needed by your software), as the data will go directly into geo_outliers. The **sys_workers** table will start simple, just tracking workers (and admins). But we intend to build on this later with the concept of reputation, where workers with higher reputation will have their correction work valued more in cases where there's a potential conflict between workers. Admins are just one or more workers in the sys_workers table; there is no separate table. // Enumerations define( "ENUM_MARKET_VWORKER", "'MARKET_VWORKER'" ); define( "LEN_HTTP_USER", "20" ); define( "LEN_MARKET_USER", "64" ); CREATE TABLE sys_workers ( WorkerID int NOT NULL auto_increment, PRIMARY KEY (WorkerID), // Possibly just use [login to view URL] as primary key // and ditch WorkerID altogether? Market enum(ENUM_MARKET_VWORKER) NOT NULL COMMENT = ’Which marketplace yielded this worker.’, MarketWorkerID char(LEN_MARKET_USER) NOT NULL COMMENT ='ID of this worker in this marketplace.', UNIQUE KEY (Market, MarketWorkerID), User char(LEN_HTTP_USER) NOT NULL COMMENT ='local http username', UNIQUE KEY (User) ) ENGINE = InnoDB, COMMENT = 'Track workers and admins.'; The **geo_outliers** table will have a many-to-one relationship with the current **geo_address** table, with an autoincremented OutlierID and [login to view URL] mapping to [login to view URL] . The State will start as OUTLST_POTENTIAL as potential problems are identified, and then it turns to OUTLST_ASSIGNED. An UpdateTime indicates the last time a row was updated. A WorkerID and an AdminID both point into the sys_workers table. All address info (house number, compass direction, street name, etc.) comes from the geo_address table. The original (possibly incorrect) lat/long pair comes from geo_address . (latitude, longitude) . When the worker submits a new Latitude and Longitude, they are stored in the geo_outliers table, and the State changes to OUTLST_SUBMITTED. When the admin approves the work, it is important to copy the *old* lat/long pair from geo_addresses to a *second* row in geo_outliers (with a State of OUTLST_REPLACED) before it is replaced in geo_addresses with the new data, (geo_address . (latitude, longitude)), and the original row in geo_outliers is marked with OUTLST_APPROVED. i.e., Lat/long data should _never be destroyed_, so we could always theoretically roll back to earlier data. // Enumerations define( "ENUM_OUTL_STATE_POTENTIAL", "'OUTLST_POTENTIAL'" ); define( "ENUM_OUTL_STATE_ASSIGNED", "'OUTLST_ASSIGNED'" ); define( "ENUM_OUTL_STATE_SUBMITTED", "'OUTLST_SUBMITTED'" ); define( "ENUM_OUTL_STATE_REPLACED", "'OUTLST_REPLACED'" ); define( "ENUM_OUTL_STATE_APPROVED", "'OUTLST_APPROVED'" ); // Constants define( "LEN_GEO_ADDRESS_ID", "10" ); define( "COORDINATE_PRECISION", "9" ); define( "COORDINATE_SCALE", "6" ); CREATE TABLE geo_outliers ( OutlierID int NOT NULL auto_increment, PRIMARY KEY (WorkerID), AddressID int(LEN_GEO_ADDRESS_ID) UNSIGNED NOT NULL COMMENT = 'many-to-one mapping to [login to view URL]', State enum(ENUM_OUTL_STATE_POTENTIAL, ENUM_OUTL_STATE_ASSIGNED, ENUM_OUTL_STATE_SUBMITTED, ENUM_OUTL_STATE_REPLACED, ENUM_OUTL_STATE_APPROVED ) NOT NULL COMMENT = 'state of this outlier', UpdateTime datetime NULL COMMENT = 'last time this row was updated', WorkerID int NULL COMMENT = 'worker who provided data ([login to view URL])', AdminID int NULL COMMENT = 'admin who managed worker ([login to view URL])', Latitude decimal(COORDINATE_PRECISION, COORDINATE_SCALE) NULL, Longitude decimal(COORDINATE_PRECISION, COORDINATE_SCALE) NULL, ) ENGINE = InnoDB, COMMENT = 'Possible and actual outlier coordinate corrections.'; You will be expected to deliver on this project using two milestones: **Development:** Prove you have a stand-alone working solution by presenting a live demonstration and/or some screen-shots, and deliver full source code. This development can be on your own server if you wish, or we can grant you access to a clean Rackspace Cloud Linux Server at our cost. If you use your own server, be aware that our database (and the corresponding database dump) is currently about five gig, but can be compressed down to 100MB using LRZIP. (You must of course have LRZIP installed.) **Integration:** You will be granted access to our integration server, where you will prove that your code works well with the rest of the system. **Environment:** PHP 5.3.2, MySQL 5.1.41, Ubuntu 10.04 LTS.