You are here

GIS Developing Follow Me with PHP--How to input CSV file with WKT into MySQL

Blog Terms: 

If you have one CSV file that containing the WKT format SQL spatial data, how to input the data into MySQL?
For testing, at first create a table containing spatial colomn:

CREATE TABLE `geometry` (
`id` int( 11 ),
`geom` GEOMETRY ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM;

In MySQL's operation software, Admin or PHPMyAdmin, you could run such SQL code to input the data:

"INSERT INTO geometry (id, geom) VALUES ('1', GeomFromText('LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)'));

And it should work.

And Now you have such CSV file in this structure, only for testing here, I am lazy....
1;GeomFromText('LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)')
2;GeomFromText('LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)')
3;GeomFromText('LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)')
4;GeomFromText('LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)')
5;GeomFromText('LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)')
6;GeomFromText('LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)')
.......................................................

If you use File Import Function of MySQL software to load the CSV file, you will meet such error and fail to input data:
Cannot get geometry object from data you send to the GEOMETRY field (1416)

Why?
Because if use File Import Function, MySQL will use such command: LOAD DATA INFILE to load the CSV data. But this command does not work with well-known text (WKT) or well-known binary (WKB) values. Maybe someone will tell you the following method:
1, create one table and set the 'geom' column as TEXT
2, input the CSV file into the table without any error
3, Change the type of 'geom' from TEXT to 'Geometry', by using such SQL commond:
ALTER TABLE `geometry` CHANGE `geom` `geom` GEOMETRY
4, the WKT text value will be transfered to spatial data automatically.
5.......STOP!!!

The method is totally WRONG!! I have tried many times and failed, the WKT text value will be set to NULL value after changed the type of 'geom' from TEXT to GEOMETRY.

But each road leads to Roma, we can read the CSV file line by line and input the data line by line into the MySQL database.

The CSV file is required to change the format of the WKT value like this:

1;LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)
2;LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)
3;LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)
4;LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)
5;LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)
6;LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)
7;LINESTRING(1283074 10562093,1283074 10562093,1283074 10562093)
........................................................

And this this the PHP code

        //Connect the database
        $databaseConnection = @ mysql_connect($databaseHost, $databaseUser, $databasePass);
        @mysql_select_db($databaseDatabase);

        //Open the CSV file
        $handle = fopen ($filename,"r");
        if ($handle)
        {

        //initialize the sql sentence
       //you can define yours own CSV structure here
        $sql="INSERT INTO ". $tablename ."(id, geom)". "VALUES(";

        //read the CSV file line by line and fields is terminated by ';'
        while ($data = fgetcsv ($handle, 10*1024, ';')){
            $num = count ($data);
            for ($c=0; $c < $num; $c++) {
            //if finish to read one line, close the SQL sentence
                if($c==$num-1){
                    $sql .= "'".$data[$c]."')";
                    break;
                }
            //if not, reform the WKT value for inputting
                else{
                    //Geometry is in field 1, id is in filed 0
                    if($c ==1){
                        $data[$c] = "GeomFromText('".$data[$c]."')";
                        $sql .= $data[$c].",";
                    }
                    else
                        $sql .= "'".$data[$c]."',";
                }
            }
           //execute the SQL sentence
            $result = @mysql_query($sql, $databaseConnection);

            //initialize the sql sentence again for next line
            $sql="INSERT INTO ". $tablename ."(id, geom)". "VALUES(";
        }
      }
 //close the CSV file after loading
  fclose ($handle);

Now, check you MySQL database, the spatial information has been stored into the database as spatial data.
You can define the structure of CSV file as you like, enjoy it.   Download the source code and testing data.

Comments

A very valuable tutorial!

Regards

fjb