[GIS] Plotting X, Y coordinates generated from MySQL database in QGIS

coordinatesMySQLqgis

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.

Best Answer

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>

Related Question