OGR and QGIS MySQL recipes

Due to https://georezo.net/forum/viewtopic.php?pid=352701#p352701 (in French)

Considering an existing MySQL/MariaDB database you have created where you have access, run the SQL commands below

To create table

CREATE TABLE IF NOT EXISTS points_xy_no_geom_col (
    identifier INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    x FLOAT,
    y FLOAT
)  ENGINE=INNODB;

To insert into table

INSERT INTO points_xy_no_geom_col(name, x, y)
VALUES
 ('az', 1.84, 43.23),
 ('by', 2.38, 46.37),
 ('cx', 5.12, 34.24),
 ('dw', -2.5, 44.17);

Within command line, considering GDAL has been installed

# Test to check fine
ogrinfo -so MySQL:mydbname,user=myuser,password=mypassword points_xy_no_geom_col -dialect SQLite -sql "SELECT *, MakePoint(x,y, 4326) AS geom FROM points_xy_no_geom_col"

Create a VRT using SQLite dialect

# Create vrt file
echo '<OGRVRTDataSource>
    <OGRVRTLayer name="points_xy_no_geom_col">
        <SrcDataSource>MySQL:mydbname,user=myuser,password=mypassword</SrcDataSource>
        <SrcSQL dialect="SQlite">SELECT *, MakePoint(x,y, 4326) AS geom FROM points_xy_no_geom_col</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>' >| demo.vrt

Then, add the demo.vrt file as a vector layer within QGIS

Do the same using MySQL native driver to make the query to generate the geometry column

echo "<OGRVRTDataSource>
    <OGRVRTLayer name=\"points_xy_no_geom_col\">
        <SrcDataSource>MySQL:mydbname,user=myuser,password=mypassword</SrcDataSource>
        <SrcSQL>SELECT *, ST_PointFromText(concat('Point(', x, ' ', y, ')'), 4326) AS geom FROM points_xy_no_geom_col</SrcSQL>
    </OGRVRTLayer>
</OGRVRTDataSource>" >| demo_native.vrt

Then, add the demo_native.vrt file as a vector layer within QGIS.

You can also add a geometry column directly with (we reused previous demo table to create the new one)

CREATE TABLE points_xy_with_geom_col AS SELECT * FROM points_xy_no_geom_col;
ALTER TABLE points_xy_with_geom_col ADD COLUMN geom GEOMETRY;
UPDATE points_xy_with_geom_col
SET geom = ST_PointFromText(concat('Point(', x, ' ', y, ')'), 4326);

Then, in QGIS, in the menu “Layer” > “Add Layer” > “Add Vector Layer”, click on database, choose type “MySQL”. In Connections section, click on new to create the connexion to the database and test it. Then, in “Options” section in “TABLES” add the table name e.g points_xy_with_geom_col

To create the table without going through the MySQL commands, you may load a CSV file and export to MySQL

Alternate solution could be to load the table from a CSV (it could be a shapefile or another vector source accepted by GDAL)

echo "name, x, y
az, 1.84, 43.23
by, 2.38, 46.37
cx, 5.12, 34.24
dw, -2.5, 44.17" >| my_csv_sample.csv

ogr2ogr -f MySQL MySQL:mydbname,user=myuser,password=mypassword \
-dialect SQLite \
-sql 'SELECT name, x, y, MakePoint(cast(x AS real), cast(y AS real), 4326) AS geom FROM my_csv_sample' \
-nln another_spatial_table \
-update -overwrite \
-lco SPATIAL_INDEX=NO \
my_csv_sample.csv

You can now add another_spatial_table in QGIS like you’ve done for previous points_xy_with_geom_col