June 12, 2011

Importing Geospatial vector data into a MySQL database

Filed under: Geo,MySQL Tips — Paul Vint @ 8:54 am

Some notes on importing vector data into a MySql Database:

  1. Create a database, in my case I called it "spatialData"
  2. Import vector data from a file: 
    ogr2ogr -f MySQL MySQL:spatialData,user=pvint,password=mypassword March25.kml -update -overwrite
  3. Test it to verify that it stored: 
    ogrinfo MySQL:spatialData,user=pvint,password=mypassword

    This should yield output similar to the following:

    INFO: Open of `MySQL:spatialData,user=pvint,password=mypassword'
      using driver `MySQL' successful.
1: march25
2: points (3D Point)

    To get more detailed information:

    ogrinfo MySQL:spatialData,user=pvint,password=mypassword march25

    Should yield results like:

    INFO: Open of `MySQL:spatialData,user=pvint,password=mypassword'
      using driver `MySQL' successful.

Layer name: march25
Geometry: Unknown (any)
Feature Count: 1
Extent: (-77.595817, 43.988083) - (-77.145567, 44.885050)
Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        TOWGS84[0,0,0,0,0,0,0],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9108"]],
    AUTHORITY["EPSG","4326"]]
FID Column = OGR_FID
Geometry Column = SHAPE
name: String (0.0)
description: String (0.0)
OGRFeature(march25):1
  name (String) = Path
  description (String) =
  LINESTRING (-77.559267 43.9919,-77.5591 43.992433,-77.558833 43.992767,-77.558667 43.993117,-77.558517 43.993333,-77.55795 43.9938,-77.557917 43.994083,-77.55795 43.994617,-77.558 43.995117,-77.558133 43.995917,-77.558183 43.997433,-77.557933 43.998083,-77.557917 43.99825,-77.557667 43.998617,-77.5574 43.998983,-77.557367 43.999283,-77.557617 44.000683,-77.5581 44.001783,-77.558067 44.00245,-77.5581 44.0026,-77.55995 44.002133,-77.560517 44.00195,-77.560967 44.001867,-77.562333 44.001433,-77.56345 44.000933,-77.564133 44.000583,-77.564517 44.000333,-77.565333 43.99995,-77.5661 43.999733,-77.569883 43.9989,-77.571017 43.99875,-77.571683 43.9986,-77.572783 43.998433,-77.574733 43.9979,-77.576233 43.997933,-77.57755 43.997933,-77.579 43.997833,-77.58405 43.9974,-77.58465 43.997283,-77.585 43.997217,-77.585833 43.997033,-77.586067 43.996833,-77.586183 43.9966,-77.5863 43.996467,-77.586583 43.996283,-77.587133 43.996183,-77.587483 43.9961,-77.588 43.996183,-77.58825 43.996233,-77.588517 43.996217,-77.588717 43.996317,-77.588917 43.996467,-77.589317 43.996583,-77.5896 43.996667,-77.589883 43.996717,-77.5901 43.9968,-77.5903 43.9967,-77.590933 43.9964,-77.59075 43.996233,-77.585467 43.999167,-77.584983 43.99935,-77.584467 43.999333,-77.583817 43.999133,-77.583367 43.998933,-77.5827 43.998567,-77.582067 43.998367,-77.580917 43.9979,-77.580583 43.997717,-77.579983 43.99735,-77.579283 43.997083,-77.578867 43.996867,-77.576983 43.995967,-77.576433 43.99575,-77.575583 43.995367,-77.574417 43.994867,-77.573983 43.994667,-77.5736 43.994467,-77.573 43.994133,-77.572383 43.99385,-77.571917 43.993633,-77.570983 43.993167,-77.570617 43.99305,-77.57015 43.9929,-77.568317 43.992667,-77.56735 43.992417,-77.564883 43.9918,-77.564067 43.991467,-77.5635 43.991133,-77.56325 43.990917,-77.563017 43.99075,-77.562717 43.9905,-77.56245 43.99015,-77.561933 43.989467,-77.561783 43.989267,-77.56165 43.989117,-77.561433 43.9889,-77.5614 43.988767,-77.561333 43.98855,-77.561317 43.9883,-77.561367 43.98815,-77.56155 43.988083,-77.595817 44.060583,-77.387567 44.137433,-77.38665 44.137933,-77.48625 44.1042,-77.4912 44.100133,-77.492217 44.099483,-77.355133 44.167617,-77.355233 44.16755,-77.14565 44.884567,-77.145567 44.884433,-77.145783 44.8845,-77.146033 44.88455,-77.145867 44.884733,-77.146333 44.884633,-77.147867 44.884483,-77.148233 44.8844,-77.14845 44.8841,-77.148317 44.884317,-77.1481 44.884433,-77.147633 44.884467,-77.14715 44.8845,-77.146817 44.884467,-77.146583 44.884433,-77.1462 44.884533,-77.145833 44.884783,-77.145617 44.884917,-77.145767 44.88505,-77.145683 44.8845,-77.59375 44.053867,-77.581417 44.048267,-77.580683 44.04825,-77.579733 44.04795,-77.578317 44.049233,-77.570233 44.054,-77.5594 44.063483,-77.546383 44.0698,-77.543833 44.072433,-77.5324 44.078233,-77.53085 44.0785,-77.529317 44.07865,-77.509817 44.08895,-77.4974 44.097283,-77.491917 44.099683,-77.4802 44.107633,-77.474233 44.109867,-77.463933 44.111533,-77.459467 44.111883,-77.453983 44.11325,-77.445183 44.114917,-77.404633 44.127533,-77.390033 44.135967,-77.3876 44.137517,-77.386367 44.138167,-77.385683 44.14165,-77.385383 44.141983,-77.385067 44.1421,-77.389267 44.1553,-77.39015 44.155917,-77.391183 44.156317,-77.388667 44.1591,-77.3878 44.159683,-77.377717 44.1619,-77.366317 44.16335,-77.354833 44.166967,-77.3548 44.167267,-77.354967 44.16765,-77.355317 44.167583,-77.355733 44.168133,-77.35625 44.168533,-77.35735 44.169633,-77.35715 44.16945,-77.355 44.16735,-77.3552 44.16755)

    The import should work with any vector data formats that ogr2ogr accepts (see http://www.gdal.org/ogr/ogr_formats.html

    Unlike

January 12, 2011

Finding duplicates in MySQL

Filed under: MySQL Tips — Tags: , — Paul Vint @ 9:43 am

Quick and easy way to find duplicates in a MySQL table:

Say you have a table called Users that has two columns: Name, and Email and you want to find what emails are duplicated:
SELECT Email,COUNT(Email) AS c FROM Users GROUP BY Email HAVING c > 1

Assuming there are duplicates, you should see something like the following:

+-------------------------+-----+
| Email                   |   c |
+-------------------------+-----+
|                         | 612 |
| someguy@example.com     |   2 |
| anotherone@example.ca   |   2 |
| lastone@example.net     |   2 |
+-------------------------+-----+
4 rows in set (0.04 sec)

The results indicate that the three addresses shown have two occurrences each, and that there are 612 records with no email address.

Unlike

August 2, 2010

Backing up MySQL database by database and table

Filed under: MySQL Tips — Paul Vint @ 9:46 am

Backing up mysql databases using mysqldump is pretty quick and painless:
mysqldump -u username -ppassword -h hostname db_name > backup_file.sql
or
mysqldump -u username -ppassword -h hostname --all-databases > backup_file

The only problem is that it becomes quite cumbersome recover a particular table.

I wrote a little script to backup each table in each database to its own file:

#!/bin/bash

if [ $# -lt 2 ]
then
        echo "Usage: backup_all_tables username password [host]"
        exit
fi

mysql_bin="/usr/bin/mysql"
mysqldump_bin="/usr/bin/mysqldump"
backup_path="/opt/backups/www/"

if [ $# -eq 3 ]
then
        host=$3
else
        host="localhost"
fi

user=$1
pass=$2

for db in `echo "show databases" | $mysql_bin -u $user -p$pass -h $host | grep -v Database | grep -v information_schema`
do
        for table in `echo "show tables" | $mysql_bin -u $user -p$pass -h $host $db | grep -v "Tables_in"`
        do
                echo "Backing up $host.$db.$table to $backup_path${host}_$db.$table.sql"
                $mysqldump_bin -u $user -p$pass -h $host $db $table > ${backup_path}${host}_$db.$table.sql
        done
done

save the script as “backup_all_tables.sh”, edit the $backup_path variable to suit, do a chmod +x backup_all_tables to make it executable, and run it using:
./backup_all tables username password hostname
If the database is on your local machine you can omit the hostname.

This will backup the files in the format “host_db.table.sql”, ie: “localhost_mysql.user.sql”

1 person likes this post.
Unlike

Powered by WordPress