Plotting X, Y coordinates generated from MySQL database in QGIS

Question

Currently running QGIS 1.8.0 on Mac OS X 10.8.4.

I can plot points on a map using the "Add delimited text layer" plugin to get x-y coordinates from a text file. However, I'd like to be able to extract the x-y coordinates from a MySQL database directly rather than having to create an intermediate text file. Can this be done? The database consists of numerous tables containing info about postcodes, eastings, northings, and other information. I can select "Add vector layer" and connect to a MySQL database but I'm not given the option to provide the SQL query necessary to extract the coordinate data or to define which variables should be defined as x and y coordinates.

Answer 1

You can add a virtual ogr layer creating an .ovf file like the following and adding it to QGIS.

<OGRVRTDataSource>
  <OGRVRTLayer name="LAYERNAME">
    <SrcDataSource>MySQL:DBNAME,user=DBUSER,password=DBPASS,host=DBHOST,tables=TABLENAME</SrcDataSource>
        <SrcSQL>SELECT * FROM TABLENAME</SrcSQL>
        <GeometryType>wkbPoint</GeometryType>
        <GeometryField encoding="PointFromColumns" x="X_COLUMN" y="Y_COLUMN"/>
  </OGRVRTLayer>
</OGRVRTDataSource>

Answer 2

I had the same question, and after scouring all documentation and other information I could find on this, I think the answer is that specifying X and Y fields as geometry source is only supported with the Delimited Text Layer driver - although I haven't found any reference which states this explicitly.

In other words, if you want to use a database such as PostgreSQL, SQLite or MySQL for providing your layers, you have to use their respective spatial extensions (PostGIS, SpatiaLite, MySQL Spatial Extensions) in order to provide a geometry field.

One workaround is to create the geometry column on-the-fly (source). For PostGIS:

Select the DBManager in the DataBase menu, then browse to PostGIS /your_connection. Now click on the "SQL WIndow" icon. This open a text editor where you can use any query you like. It is very useful when geometry are not directly accessible. For example, you have a table with id, X and Y columns, but no geometry column. You can use the following query: SELECT id, X, Y, ST_SetSRID(ST_MakePoint(X,Y), your_srid) AS geom FROM your_table

You can test query with F5. There is auto completion. CTE are allowed, but you need to be strict on query formatting (no leading spaces or tabs, neither at the end). When you are satisfied, you can click on the "load as a new layer" box and choose an id and geom column.