Approximate steps for performing a MySQL to PostgreSQL conversion and PostGIS set up

sailwx-to-postgres.sh

# Configure Postgres
#

# create a postgres database for the records
createdb sailwx

# make the new database PostGIS compatible (here, against Postgres 8.4)
git clone https://github.com/straup/postgis-tools
cd postgis-tools
sh ./createdb-8.4.sh sailwx sailwx
cd ..

# Set up the Postgres-MySQL transfer, using py-msqyl2pgsql
#

# if pip is installed, you can skip this step:
easy_install pip

# INSTALLATION OPTION #1: install py-msqyl2pgsql via pip
pip install py-mysql2pgsql

# INSTALLATION OPTION #2: use my version which supports downcasing⋅
# column names and schemas for file outputs
git clone https://github.com/scw/py-mysql2pgsql
cd py-mysql2pgsql
python setup.py install

# assuming that you want to dump the results directly to the postgres⋅
# database, you can use the converter directly without any hacking.⋅
# Just run the tool once:
py-mysql2pgsql

# ... and then edit the resulting 'mysql2pgsql.yml' to include the⋅
# correct parameters for the two databases, then on rerunning the script,
# it'll crank away at performing the conversion:
py-mysql2pgsql

# Setting up PostGIS

# here, just setting up a single column, but can do much more
# make a new geometry column on the 'reports' table:
make_geometry=$(cat << EOF
SELECT addGeometryColumn('reports', 'loc', 4326, 'POINT', 2);
UPDATE reports SET loc  = ST_PointFromText('POINT(' || "Longitude" ||' '|| "Latitude" ||')', 4326);

ALTER TABLE reports ALTER COLUMN loc SET NOT NULL;

CREATE INDEX reports_loc_geom_idx ON reports USING gist(loc);
EOF
)

psql sailwx < echo $make_geometry