1

When I run the sql statement below through psql it works fine, but when I try to run the same query by building it with a preparedstatement, it fails.

INSERT INTO Hvbp 
  (provider_number, weighted_clinical_process, 
   weighted_patience_experience, total_performance_score, 
   coordinates, latitude, longitude, address, city, state, zip) 
VALUES 
('010092', 43.909090909091, 13.5, 57.409090909091, 
 'POINT(33.206201 -87.525480)', 33.206200613000476, 
 -87.52548020899968, '809 UNIVERSITY BOULEVARD EAST', 'TUSCALOOSA', 'AL', '');

The error I keep getting is

org.postgresql.util.PSQLException: ERROR: column "coordinates" is of type geography but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 203

The coordinates column is of type GEOGRAPHY(POINT)

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Hank
  • 3,367
  • 10
  • 48
  • 86
  • 2
    Leave out the single quotes around `'POINT(33.206201 -87.525480)'`. That's a function call, not a string literal –  Aug 30 '13 at 20:22
  • @a_horse_with_no_name, this doesn't actually work with the Postgres JDBC driver. `statement.setString(1,"POINT(1 2)");` results in a complaint of `ERROR: column "geography" is of type geography but expression is of type character...` – lreeder Sep 07 '13 at 04:27
  • 2
    Your SQL statement does not make use of parameters, that's why I wrote that. For a PreparedStatement you neeed `point(?,?)` in the SQL string (*without* single quotes) and *two* `setFloat()` calls to supply the values for each `?` –  Sep 07 '13 at 06:14
  • ireeder, it should be "POINT"(1,2), not "POINT(1 2)" as those are entirely different things. – Chris Travers Nov 12 '13 at 08:06

2 Answers2

2

I know this is an old problem, but I just spend most of the day debugging the same basic problem and finally found a fix

What you're trying to do, is provide the POINT using WKT and have the server automatically convert that into a Geometry.

And as you've found that works if you include the WKT inside the body of the SQL, but fails if you use a parameter on a prepared statement.

There are 3 options for fixing it:

  1. Use st_GeographyFromText in your SQL like so:

    INSERT INTO Hvbp(coordinates) VALUES( st_GeographyFromText(?) )

    And then set your parameters as WKT with setString.
    Depending on the frameworks involved, that might not be possible for you.

  2. Use setObject on the preparedStatement instead of setString. For example:

    ps.setObject(1, "POINT(33.206201 -87.525480)", java.sql.Types.OTHER )

  3. Change your JDBC driver settings to send strings as unspecified type, and then the server will do type conversions for you. To do that you want to change your JDBC URL to something like

    "jdbc:postgresql:my_db?stringtype=unspecified"
Tim
  • 6,406
  • 22
  • 34
0

@Tim - thank you for your help with a similar problem - I had to write ST_GeometryFromText into my database and the JDBC Driver threw a similar exception as @Hanks got.

For further reference and clarification for others - this is my result using Java with JDBC:

INSERT INTO streets.points ( point_id, the_geom ) 
        VALUES( ?, ST_GeomFromText( ? , 25832)  );

And the inserted Geometry-String looked like that:

POINT(33.206201 -87.525480)
leole
  • 439
  • 9
  • 16