Import spatial data from PostGIS into MySQL

I have a PostgreSQL DB that contains KML data in one column of a table. I query it with postGIS commands with a query like this:

SELECT ST_geomFromKML(geometry) 
    ST_Intersects(ST_SetSRID(ST_Buffer(ST_MakePoint(11.255492,43.779251),0.002), 4326), ST_GeomFromKML(geometry)) as intersect,
    ST_SetSRID(ST_Buffer(ST_MakePoint(11.255492,43.779251),0.002), 4326)
FROM mydb 
WHERE 
    ST_Intersects(ST_SetSRID(ST_Buffer( ST_MakePoint(11.255492,43.779251),0.002), 4326), ST_GeomFromKML(geometry))
LIMIT 1

in the geometry column the data are stored as KML like this:

<Polygon><outerBoundaryIs><LinearRing><coordinates>8.198905,40.667052 8.201007,40.667052 8.201007,40.665738 8.20127,40.665738 8.20127,40.664688 8.201532,40.664688 8.201532,40.663111 8.20127,40.663111 8.199956,40.663111 8.199956,40.663374 8.199693,40.663374 8.199693,40.664425 8.197591,40.664425 8.197591,40.665476 8.198905,40.665476 8.199168,40.665476 8.199168,40.666789 8.198905,40.666789 8.198905,40.667052</coordinates></LinearRing></outerBoundaryIs></Polygon>

so I use ST_geomFromKML to convert data to geometry then I search for intersection of a circle I create around the point.

I wanted to migrate the database to MySQL and I wanted to use its spatial functions, but I don't find a way to use/convert the KML data inside MySQL as I do with PostGIS.

Is there a way to do it?

Solution

I guess it would be worth trying to export your geometries in a format that can be read by MySQL, e.g. WKT (Well Known Text). By your question I assume you're indeed storing the geometries as KML in either a text or a xml column, so I believe this here will help you:

Test Data

CREATE TABLE t (kml TEXT);
INSERT INTO t VALUES ('<Point><coordinates>8.54,47.36</coordinates></Point>');

Export as CSV to the standard output (client)

COPY (SELECT ST_AsText(ST_geomFromKML(kml)) AS geom FROM t) TO STDOUT CSV;

query returned copy data:
POINT(8.54 47.36)

Export as CSV into a file in the server - keep in mind that the system user postgres needs to have writing permission in the given directory.

COPY (SELECT ST_AsText(ST_geomFromKML(kml)) AS geom FROM t) TO '/path/to/file.csv';