Import spatial data from PostGIS into MySQL

Question

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?

Answer 1

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';

Answer 2

Yes me also I do that (geom from postgres -> wkt -> mysql -> geom from mysql).

To complete, I use :

pg_dump --column-inserts --data-only

To get just INSERT as in Mysql. Here it just remains to create the table in MySQL, delete what is before and after my INSERT from Postgres, remove "schema." and hop go in MySQL!