• Table of contents
• Tools
  • Software purchasing
  • Books
  • Posters
  • Programming languages
    • Java
    • Python
  • Databases
    • PostgreSQL
    • MySQL
    • MS Access database (MDB)
    • Google Fusion Tables
    • Google Refine
    • MongoDB
  • Data formats
    • Diagrams
    • XML
    • XML-database mapping
    • BSON: Bin­ary JSON
    • OLAP
    • Maps

Tools¶

• MySQL queries
  • MySQL snippets
• Postgres queries
  • Postgres snippets
• Python code
• Regexps
• Shell commands
• Wiki syntax

Software purchasing¶

• Ways to purchase software
  1. ask Thomas to order it with a p-card
  2. pay for it out-of-pocket, get receipt, and request reimbursement
     • for expenses < $100

Books¶

• UCSB has access to O'Reilly collection through Safari Books Online

Posters¶

• Kinko's sells color posters for $7/sq ft and 3x4 ft B&W posters for $12 each

Programming languages¶

Java¶

• Comparison of Web application frameworks
• Play framework: MVC web app framework
  • Scala based template engine
  • usability vs. Spring MVC (2010-8-27): "Spring seems to offer all the same features and more"
  • performance vs. Spring MVC (2011-4-14): "Spring is 20% ahead of Play" for apps with request parameters
  • usability: "much of the quality turns out to be in the details: they may be small things individually, rather than big important features, but they add up to result in a more comfortable and more productive development experience"
• Spring Web MVC
• Spring Roo
  • Rapid Prototyping (2011-6-16)
• GeoTools

Python¶

• Django Models
• GeoDjango
  • Installation
• GEOS API
• Jython
  • "Jython is the successor to JPython. [...] JPython is an implementation of the Python programming language which is designed to run on the Java(tm) Platform" (JythonFaq)
• LanguageParsing
• Useful Python code

Databases¶

PostgreSQL¶

PostgreSQL is probably the best database engine out there, much better than MySQL and very likely better than Oracle. In particular, it has many more features and more consistent semantics than MySQL, and it's free and open-source compared to Oracle.

• phpPgAdmin
• PostGIS
• GUI Database Design Tools
• Psycopg Python driver
  • installing on Ubuntu Linux (may work on other Linux flavors):
    1. Run sudo apt-get install python-dev python-pip libpq-dev
    2. Run sudo pip install psycopg2
• Useful Postgres queries

MySQL¶

• MySQL vs PostgreSQL
• mysql2pgsql
• MySQL to PostgreSQL
• MySQL command-line client/server
  • installing on Ubuntu Linux (may work on other Linux flavors):
    1. Run sudo apt-get install mysql-server mysql-client
• mysqldump command line usage:
  • mysqldump --password --skip-lock-tables --extended-insert --hex-blob --compact database [tables] >out.sql
• MySQLWorkbench
  • offers the ability to graphically edit a database schema and then export the changes as a SQL script with ALTER TABLE statements
  • can use with PostgreSQL by converting DDL to a MySQL DDL:
    1. Replace regexp "^--(?=\S)" with "$0 "
    2. Replace text "serial" with "int(11) NOT NULL AUTO_INCREMENT"
    3. Replace text "Integer" with "int(11)"
    4. Replace text "Boolean" with "int(1)"
• MySQLdb Python driver
  • installing on Ubuntu Linux (may work on other Linux flavors):
    1. Run sudo apt-get install python-mysqldb
  • installing on Mac OS X 10.7 (may work on other versions):
    1. Download it using "latest version" link at http://sourceforge.net/projects/mysql-python/files/
    2. Extract the archive
    3. Follow the instructions at http://www.rustyrazorblade.com/2011/11/installing-mysqldb-on-macos-lion/
       1. Run sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib
       2. Run sudo ln -s /usr/local/mysql/lib /usr/local/mysql/lib/mysql
    4. Follow the instructions at http://blog.infoentropy.com/MySQL-python_EnvironmentError_mysql_config_not_found:
       1. Edit site.cfg and change the line like "mysql_config = " to "mysql_config = /usr/local/mysql/bin/mysql_config"
    5. Follow the instructions at http://www.mangoorange.com/2008/08/01/installing-python-mysqldb-122-on-mac-os-x/
       1. Change to the directory of the extracted files
       2. Run sudo python setup.py clean
       3. Run sudo python setup.py build
       4. Run sudo python setup.py install
       5. Run python -c 'import MySQLdb'

MS Access database (MDB)¶

• *Access To SQL (Windows)
  • Access To PostgreSQL
    The generated schema requires the following additional transformations:
    1. Replace text BOOLEAN with /*BOOLEAN*/INTEGER
    2. Replace text DOUBLE PRECISION NULL with DOUBLE PRECISION
• mdbtools:
  • Linux: sudo apt-get install mdbtools
  • Mac OS X: The Electrofriend: How to compile mdbtools on Mac OS X 10.4 and 10.5
• How to Open and Edit MS Access Databases from Mac OS X
• Linux MDB driver - alpha version
• To set up in Windows VM from jupiter: "go to Control Panel in your Windows VM (7, I assume) => Programs => Program Features => MS Office Pro Plus 2010 => Change and [...] add Access." (e-mail from Thomas on 2011-10-20)
• Compatibility - Microsoft Access 2000 - CodeWeavers

Google Fusion Tables¶

• "Upload data tables from spreadsheets or CSV files, even KML. Developers can use the Fusion Tables API to in"
• "Visualize it instantly: See the data on a map or as a chart immediately. Use filters for more selective visualizations."
• good for geographic data because of built-in generation of maps

Google Refine¶

• "a power tool for working with messy data, cleaning it up, transforming it from one format into another, extending it with web services, and linking it to databases"

MongoDB¶

• "a scalable, high-performance, open source, document-oriented database"

Data formats¶

Diagrams¶

• Enterprise Architect
  • can develop entire app in it
  • model-based approach
• Visio
  • used for VegBank

XML¶

• XMLSpy
  • used to generate EML diagrams
• oXygen
  • generates tree view diagrams
  • can graphically edit XSDs and then save the changes
  • we have 5 free licenses
• XPath expression builders
  • XMLSpy XPath Analyzer
    • "provides intelligent XPath auto-completion"
  • XMLSpy XPath Builder
    • "select an element or attribute in your XML data file, and the "Copy XPath" command will automatically copy the corresponding XPath expression to the clipboard"
  • Buba XPath builder
    • auto-complete based on XML document it's searching
  • Freeware XML Schema Editor

XML-database mapping¶

• Bourret's XML-ER mapping
• IBM Clio
  • "Clio then also interprets these mappings to construct a set of database queries that transform and integrate source data to conform to the target schema"
  • "For a demo or information on code availability please contact Howard Ho (lastname @ almaden.ibm.com)"
  • last updated 2007
• Altova XMLSpy
• Stylus Studio
• Sybase PowerBuilder
• IBM Enterprise Vision
• Database XML Mapping Export Import (price unknown)
  • Allora Mapper: "The Mapper can automatically create XML schemas from relational databases and vice versa."
• XML Database Tools ($695)
  • MySQL XML Tools: "MySQL tools in Stylus Studio [allows the user to access] MySQL databases as XML."
• Create DDL from an XSD file
• VegBank XSLTs for importing/exporting XML: vegbankdev:/var/lib/tomcat6/webapps/vegbank/xsl/*.xsl
• ER to XML Transformation
• List of object-relational mapping software
• ShreX XPath-based database querying

BSON: Bin­ary JSON¶

• "a bin­ary-en­coded seri­al­iz­a­tion of JSON-like doc­u­ments"

OLAP¶

• "analyze multidimensional data from multiple perspectives"

Maps¶

• Esri Shapefile