Import KML file and update a table in a MySQL database using Laravel

Question

I have a KML file that looks like XML. I need to upload this file and update a MySQL database. The data is like this:

<Document>
    <name>GPSWaypoints-2020-10-16.kml</name>
    <Style id="inline">
        <LineStyle>
            <color>ff0000ff</color>
            <width>2</width>
        </LineStyle>
    </Style>
    <Style id="waypoint">
        <IconStyle>
            <Icon><href>http://maps.google.com/mapfiles/kml/pal4/icon61.png</href></Icon>
        </IconStyle>
    </Style>
    <Folder>
        <name>Waypoints</name>
        <open>1</open>
        <Placemark>
            <name>WPT 0011</name>
            <TimeStamp><when>2020-09-26T08:21:14Z</when></TimeStamp>
            <styleUrl>#waypoint</styleUrl>
            <Point><coordinates>-74.91270224,10.8081995</coordinates></Point>
            <ExtendedData>
                <Data name="accuracy">
                    <displayName>Accuracy</displayName>
                    <value>3.216</value>
                </Data>
                <Data name="provider">
                    <displayName>Provider</displayName>
                    <value>gps</value>
                </Data>
            </ExtendedData>
        </Placemark>
    </Folder>
</Document>
</kml>

I have this method to import the KML file:

public function uploadKML(Request $request)
    {
       $file      = $request->file('kml');
       $filename  = $file->getClientOriginalName();
       $filename  = time() . "." . $request->kml->extension();
       $file->move(public_path('kml'), $filename);
       $path = $request->image->store('public');
       return response()->json(["message" => "Upload Success"]);      
    }

Now with the file after uploading, I need to read the KML and update the coordinates with the same ID of a table.

The ID is in the name field of the KML. For example, in the field <name> WPT 0011 </name> The ID would be 0011, without the WPT.

The update method that I currently have is:

public function update(Request $request, $id)
    {
        $data= Data::findOrFail($id);
        return $data->update($request->all());
    }

And this is my Data Model:

<?php

class Data extends Model
{
    protected $fillable = ['id', 'coordinates' ....];
}

Answer 1

First, give the file to an XML parser (like SimpleXML).

Then, parse out the information you want from the XML file into a variable (an array of XML elements).

Then, update the database using SQL with the array's elements' contents.

Something like this, for one id and several coordinates per KML file (untested, just typing this in to give you an idea):

<?php

// Connect to database here

$KMLFile = "foo.kml";
$xml = simplexml_load_file($KMLFile);
$id = $xml->Document->Folder->Placemark->Name->__toString();
$coordinates = $xml->Document->Folder->Placemark->Point->Coordinates;
for ($i = 0; $i < sizeof($coordinates); $i++) {
  $coordinate = $coordinates[$i]->__toString();

  // You should now have an ID and a coordinate as a string;
  // Do what you need to do with the coordinate string
  // and add the result to your database with SQL

}
?>

This will probably not work as written, but I hope it gives you enough information to build what you want.

You could also parse the coordinates or any other information you want from the KML file by using Regular Expressions or other means, but the "XML way" is the more obvious, elegant, and robust way of doing it. It does require that your KML is well-formed XML, though.