CODEWITHSUNDEEP
phpmysqlpdogis
Phililippe
Phililippe

Reputation: 21

Insert GIS Geometry using prepared Statements

Is there a way to insert GIS data into mysql database (using PHP / PDO) using prepared statements ?

This is failing:

$stmt = $sql->prepare("INSERT INTO geo SET location= :loc");
$sql->execute($stmt,array('loc'=>"PointFromText('POINT(43.5499152 7.0232372)')"));

Where column geo.location has the type of POINT.

Upvotes: 2

Views: 2455

Answers (3)

Glyn
Glyn

Reputation: 1995

I am using MySQL POINT and could not figure out how to do an INSERT of POINT; However, had resolved the UPDATE. So I inserted the new row and then updated the POINT column:

String insertQry = ("INSERT INTO at_group " +
              "(grp_country, grp_state, grp_region, grp_district, " +
              "grp_name, grp_section, grp_sub_section, grp_archived, " +
              "grp_nas) " +
              "VALUES (?,?,?,?,?,?,?,?,?);");

    String updateQry = ("UPDATE at_group " +
              "SET grp_hall_geoPoint=PointFromText(CONCAT('POINT(', ?, ' ', ?, ')')) " +
              "WHERE grp_id=?;");

    try {
        // Get Connection and Statement from DataSource
        c = ds.getConnection();
        ps = c.prepareStatement(insertQry,Statement.RETURN_GENERATED_KEYS);

        try {
            // Create a statement and execute the query on it

            ps.setString(1, country);
            ps.setString(2, state);
            ps.setString(3, region);
            ps.setString(4, district);
            ps.setString(5, group);
            ps.setString(6, section);
            ps.setString(7, subSection);
            ps.setString(8, archiveDate);
            ps.setString(9, nas);

            ps.executeUpdate();

            ps2 = c.prepareStatement(updateQry);
            //Get foreign key for key to update the correct row
            ResultSet rs = ps.getGeneratedKeys();

            if (rs.next()) {
                grp_id = rs.getInt(1);

                ps2.setString(1, latitude);
                ps2.setString(2, longditude);
                ps2.setInt(3, grp_id);

                ps2.executeUpdate();
            };

            // Clean up
            ps.close();
            ps2.close();
            c.close();

        } catch (SQLException se) {
            System.out.println("SQLException in addGroup: " + se.toString());
        } catch (Exception e) {
            System.out.println("Errors occurred in addGroup: " + e.toString());
        }

    } catch (SQLException e1) {
        System.out.println("SQLException in addGroup: " + e1.toString());
        e1.printStackTrace();

    }

Upvotes: 0

user3384231
user3384231

Reputation: 4053

@Glyn: See the following worked for me

insertquery = <do as required>
Your insert query should have the following:
"geometry::STGeomFromText(?,4326))" 


PreparedStatement insert = conn.prepareStatemen(insertquery);

#Point has two points, X and Y. You need to pass those values.
String shape = "POINT ("+x+" "+y+")";
insert.setString(25, shape);

The database that your using, for example MS sql server then your point column should have right datatype which is "Geometry".

Please ask me any further question you may have.

Happy to help.

Upvotes: 0

AlexB
AlexB

Reputation: 734

Is 43.5499152 the latitude and 7.0232372 the longitude? If so, considering that the WKT format for POINT is POINT(X Y) where X is the longitude and Y is the latitude, a valid representation would be:

POINT(7.0232372 43.5499152)

With this in mind, you can try something like:

$stmt = $sql->prepare("INSERT INTO geo SET location = PointFromText('POINT(:lng :lat)')");
$stmt->execute(array('lat' => 43.5499152, 'lng' => 7.0232372));

And if you don't have the latitude and longitude, simply set location to null:

$stmt = $sql->prepare("INSERT INTO geo SET location = null");
$stmt->execute();

Also, you might want to set the SRID, which in your case is 4326:

$stmt = $sql->prepare("INSERT INTO geo SET location = PointFromText('POINT(:lng :lat)', 4326)");
$stmt->execute(array('lat' => 43.5499152, 'lng' => 7.0232372));

Upvotes: 3

Related Questions