How to insert POINT type value using PreparedStatement from a java app into Postgresql DB

Question

The following query works in pgAdmin's sql console:

insert into sometable values (DEFAULT,9, 'test content', '(-194.0, 53.0)', '(+144.345345, -453.043534)', '2012-08-24 14:00:00 +02:00', '2012-08-24 14:00:00 +02:00');

Following are the values sent to the server:

nameValuePair.add(new BasicNameValuePair("userid", "9"));
nameValuePair.add(new BasicNameValuePair("content", "test content"));
nameValuePair.add(new BasicNameValuePair("location1", "(-194.0, 53.0)"));
nameValuePair.add(new BasicNameValuePair("location2", "(+134.350, -433.04345)"));
nameValuePair.add(new BasicNameValuePair("date1", "2012-08-24 14:00:00 +02:00"));
nameValuePair.add(new BasicNameValuePair("date2", "2012-08-24 14:00:00 +02:00"));

PreparedStatement (on the server)

psInsert = conn.prepareStatement("insert into OFFERS (USERID, CONTENT, LOCATION1, LOCATION2, DATE1, DATE2) values (?, ?, ?, ?, ?, ?)");

psInsert.setInt(1, userid);
psInsert.setString(2, content);
psInsert.setString(3, location1);
psInsert.setString(4, location);
psInsert.setString(5, date1);
psInsert.setString(6, date2);

psInsert.executeUpdate();

Which results in the following error:

org.postgresql.util.PSQLException: ERROR: column "location1" is of type point but expression is of type character varying
**strong text**Hint: You will need to rewrite or cast the expression.

I have read a few other related posts (on how to insert GeoSpatial/Point values in Postgresql db), but havent been able to solve this problem. Appreciate the help in advance.

score 3 · Answer 1 · answered Dec 13 '13 at 16:37

3

you can easily make:

psInsert = conn.prepareStatement(
"insert into OFFERS (USERID, CONTENT, LOCATION1, LOCATION2, DATE1, DATE2) " + 
"values (?, ?, point(?, ?), point(?, ?), ?, ?)" );

answered Dec 13 '13 at 16:37

Andromida

1,095
1
11
28

score 2 · Answer 2 · answered Sep 08 '13 at 13:51

I would pass the actual x and y coordinates (or parse the String on the server) and use ST_MakePoint

psInsert = conn.prepareStatement(
    "insert into OFFERS (USERID, CONTENT, LOCATION1, LOCATION2, DATE1, DATE2) " + 
    "values (?, ?, ST_MakePoint(?, ?), ST_MakePoint(?, ?), ?, ?)"
);

for each of the POINT columns.

How to insert POINT type value using PreparedStatement from a java app into Postgresql DB

2 Answers2