Skip to content

Instantly share code, notes, and snippets.

@phobson
Created October 5, 2012 18:51
Show Gist options
  • Save phobson/3841661 to your computer and use it in GitHub Desktop.
Save phobson/3841661 to your computer and use it in GitHub Desktop.
Workflow to convert mysql qry to geojson file
#! /usr/bin/env bash
DBHOST=$1
USER=$2
DATABASE=$3
QRYTXT=$4 #(make sure x,y are called longitude and latitude)
OUTFILE=$5
# get data
mysql -h $DBHOST -u $USER -p -e "USE $DATABASE; $QRYTXT;" > tmpfile.txt
# asks for password
# parse out bad values in txt file, conver to csv
perl -ne 's/\t/,/g; print $_;' tmpfile.txt > tmpfile.csv
perl -ne 's/NULL/None/g; print $_;' tmpfile.csv > $OUTFILE.csv
# make a new vrt file for the csv
perl -ne 's/FILENAME/'$OUTFILE'/g; print $_;' template.vrt > $OUTFILE.vrt
# make the geosjon file
# TODO: force to two dimensions
ogr2ogr -f "GeoJSON" $OUTFILE.js $OUTFILE.vrt
# clean up (comment out for debugging)
rm tmpfile.*
<OGRVRTDataSource>
<OGRVRTLayer name="FILENAME">
<SrcDataSource>FILENAME.csv</SrcDataSource>
<SrcLayer>FILENAM
<GeometryType>wkbPoint</GeometryType>
<LayerSRS>WGS84</LayerSRS>
<GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/>
</OGRVRTLayer>
</OGRVRTDataSource>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment