2

I try to insert same values in my table and I write this code:

String sql = "INSERT INTO points (gid, osm_id, name, type, geom) 
         VALUES (?, ?, ?, ?,ST_GeomFromText('POINT(?, ?)', 2100));";
         pst = con.prepareStatement(sql);
         pst.setInt(1, 40001);
         pst.setInt(2, 2);
         pst.setString(3, "General");
         pst.setString(4, "Heller");
         pst.setDouble(5, 69.6);
         pst.setDouble(6, 80.3);

         pst.executeUpdate(); 

but is show me an exception

org.postgresql.util.PSQLException: The column index is out of range: 5, number of columns: 4.

If someone can help me.

Solved it by using:

pst.setString(5,"POINT("+lat+" "+log+")" );

"INSERT INTO points (gid, osm_id, name, type, geom)VALUES (?, ?, ?, ?,ST_GeomFromText(?, 2100));"
  • This is a JDBC issue -- The "?" is supposed to correspond to a column value, not as a generic substitution tool. Instead, use String.format to format the WKT. – Vince Nov 19 '14 at 17:31
  • I try to pass as two double number – vagelis Nov 19 '14 at 17:37
  • Right, but that's not how that functionality is supposed to work. – Vince Nov 19 '14 at 18:32
  • 1
    If lat is latitude and log is longitude, you have to swap them, since the correct axis order for PostGIS is POINT(log lat) – Mike T Nov 20 '14 at 22:01
1

This:

'POINT(?, ?)'

is within a literal string, so placeholders are not interpreted.

You must instead keep the placeholders outside a literal and use concatenation or function call syntax.

In this case there's actually a point(x,y) function, so you can just write:

POINT(?,?)

without the single quotes, but if that weren't the case and you needed to compose a value you'd instead concatenate:

'POINT('||?||','||?||')'

or replace the whole thing with a single parameter:

?

and send the string POINT(blah,blah), composed client-side, to the server.

1

This is sort-of the same answer as this, except adapted a bit more for your situation. Basically, you are mixing SQL parameters in WKT. Since you have numeric data, you shouldn't be making WKT anyway, and directly make a point instead.

String sql = "INSERT INTO points (gid, osm_id, name, type, geom) "
           + "VALUES (?, ?, ?, ?, ST_SetSRID(ST_MakePoint(?, ?), 2100));";
pst = con.prepareStatement(sql);
pst.setInt(1, 40001);
pst.setInt(2, 2);
pst.setString(3, "General");
pst.setString(4, "Heller");
pst.setDouble(5, 69.6); // longitude
pst.setDouble(6, 80.3); // latitude
pst.executeUpdate();

Also, if your gid column is a serial type, you should remove it here and let the database assign it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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