A Trip Down Console Lane: Mapping Troy Properties

Last Thursday, Professor Moorthy asked me if I could map the foreclosed property listings in Troy, NY.  This seemed like the perfect kind of data to be displayed on a map, so it works well for an application like Flagship Safety.  The problem is that I haven't deployed it yet, and don't really want to host it on my development server.  So I decided to make it a project in Community Mapping, which is also powered by Flagship Geo.


Now that I had figured out where to put it, I needed to figure out how to get it there.  This month, there are 85 properties up for auction, so I knew that entering them manually would be tedious.  Plus, being a Computer Science major, I prefer more technical, less repetitive solutions.  So I tried to design a solution that would allow me to import the data into the database.  The easiest way to do this would be to create a CSV file that mirrors the destination table.


I've previously done this, but used Rails to allow for uploading and mapping of imported data,  Since I don't control the instance of Community Mapper that I wanted to use, I decided a simpler solution would be better.  SQLite can import simple CSV files, as long as the data fields are in the correct order.  So now I knew what to do, I had to create a CSV file from the PDF that mimicked rows of data in the Points table.


The first task was to extract the relevant data from the PDF.  This proved to be a challenge, not because the text was jumbled, as it had been from the list of call boxes, but because the text copied out on separate lines.  I pasted it into a spreadsheet and used cell references and concatenation to massage it into a tabular format.  This still required checking every line, since the text wasn't uniformly split into separate lines.  


The next step was to geocode the addresses.  Again, since their are 85 of them, an automated solution would probably be best.  I found a batch geocoder by Stephen P. Morse that uses the Yahoo Geocoding API by default.  I copied the addresses in, appending "Troy, NY" to them, and after a little while got the coordinates out.


The last step was to align the columns to allow for importing.  This included inserting canonical data to create a complete dataset.  Now it was time to test the import.


rails dbconsole
.seperator ,
.import TroyProperty.csv Poimts


After correcting a few glitches in my CSV file (mostly having commas in my data, which aren't escaped), I could finally view the listings on a map.  I realized I had a few points to correct that fell outside Troy's borders.  I manually reran the geocoding in Google Maps, and was ready to go.  I then emailed my file to Brian Michalski, who was able to import them for me.  The final result can be found on mymapper.org.