jps services
  • Home Page
  • GIS Training Courses
    • QGIS Training Courses
    • ArcGIS Training Courses
    • MapInfo Pro Training Courses >
      • MapBasic Training Course
  • About
  • Blog

QGIS and MySQL Training

21/4/2013

0 Comments

 
Once you have acquired an understanding of the functionality of a GIS system such as Quantum GIS you may have a need to link a relational database file at some point. Whilst attaching a CSV file from a spread sheet is a relatively simple procedure in QGIS, linking relational databases can be somewhat more involved because more parameters are involved. A popular relational database such as MySQL can be linked to QGIS through the standard interface if you have enabled the database server and have access via a password.
A relational database such as MySQL, which is an Oracle product, comes in both open source and commercial versions. MySQL runs on Windows, Linux and Mac operating systems and has developed through many iterations to the current version 5.6. You can access the MySQL database either through a command line option or a more user friendly facility called MySQL Workbench. The Workbench provides a graphical user  interface which helps you in the following tasks.

SQL Development: This option lets you connect to existing databases to run SQL scripts, SQL queries and edit table data.

Data Modelling: With this option you can create and manage models. Forward and reverse engineer, compare and synchronise schemas and view a diagram of your tables and how they relate to each other.

Server Administration: With this option you can configure your database server, create user accounts, browse status variables and server logs. You can also create new server instances, manage imports and exports of data, manage user accounts and assign privileges. You can additionally manage server instances with the options to add, delete and update server instance settings.

The Workbench helps with SQL syntax in that you can view all the standard syntax through the Snippets option. Here you have access to and can view and edit all the main SQL features for creation, viewing and modifying tables. These snippets can then be included in a query window to create the appropriate SQL script. Using snippets ensures that you are using the correct syntax which is absolutely essential when running SQL scripts.

MySQL supports all the standard data types including functionality for storing geometry information such as points, lines and polygons. You can connect a MySQL database which contains all the necessary information to create both an attribute table and a map within a GIS such as QGIS.

In the main menu of QGIS select Layer, then Add Vector Layer option. Select the MySQL database option in the dialogue box and then either create a new connection or select a current connection. If the server is running on your computer select local host for Host option, then update the Database, Port and User name options. (Port is usually 3306 and user name default is root). Enter the password and connect to the database. If you have made a successful connection your map will then be shown as a layer within QGIS.




0 Comments

Your comment will be posted after it is approved.


Leave a Reply.

    Author

    Joe Short BSc has been involved with various mapping solutions for over twenty years.  If you are considering implementing a GIS  or have ArcGIS Pro, MapInfo Pro or QGIS training requirements, jps services would be happy to be of assistance to your organisation. 

    Archives

    April 2020
    March 2020
    October 2019
    September 2019
    August 2019
    July 2019
    March 2019
    November 2018
    October 2018
    August 2018
    July 2018
    November 2017
    October 2017
    September 2017
    July 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    May 2016
    February 2016
    September 2015
    August 2015
    April 2015
    February 2015
    November 2014
    October 2014
    July 2014
    June 2014
    May 2014
    March 2014
    February 2014
    December 2013
    November 2013
    October 2013
    September 2013
    August 2013
    June 2013
    May 2013
    April 2013
    February 2013
    December 2012
    October 2012
    September 2012
    August 2012
    July 2012

    Categories

    All
    Arc Gis
    Arcview
    Autodesk
    Cad
    Gis Training
    Local Government
    Mapbasic
    Mapinfo
    Quantum Gis
    Relational Databases
    Saga Gis

    RSS Feed