1

I want to load spatial data into MySQL using a CSV I have generated from a PostgreSQL table and when loading it into MySQL database using phpmyadmin it complains "Cannot get geometry object from data you send to the GEOMETRY field." How best can I export PostGIS geometry into MySQL using a CSV without generating errors?

I have created a MySQL table as

CREATE TABLE cadastre (
  gid int(11) auto_increment NOT NULL,  
  geom MultiPolygon NOT NULL,  
  lpkey varchar(50),   
  SPATIAL INDEX(geom),   
  PRIMARY KEY (`gid`)  
 ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

3 Answers 3

3

The simplest way would be to convert your geometry to Well Known Text (WKT) in PostGIS by adding another column called WKT and fill it using

ST_AsText(the_geom)

Then on the MySQL side, convert the WKT to a geometry object using GeomFromText:

UPDATE My_Table SET the_mysql_geom = GeomFromText(wkt_from_csv, SRID)

Here is a good reference for the reverse of what you are looking for (though PostGIS and MySQL follow the same standard, so the syntax is the same).

0

If you can access one server from the other you can use ogr2ogr to do a direct export from PostGIS / import into MySQL.

MySQL ogr driver page

PostGIS ogr driver page

0

ENGINE=MyISAM needs to be ENGINE=innoDB. MyISAM does not support spacial data or function calls.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.