GeoTools
  1. GeoTools
  2. GEOT-2363

Postgres EPSG factory won't work against Postgres 8.3

    Details

    • Type: Bug Bug
    • Status: Open Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.5.3
    • Fix Version/s: 2.5.9
    • Component/s: referencing
    • Labels:
      None

      Description

      Most prepared statements in DirectEpsqFactory and AuthorityCodes won't work against PostgreSQL 8.3 due to a type mismatch: the official EPSG database sql script creates primary keys (xxx_code) as integer columns, but the statements are prepared with setString instead.
      This mismatch usually just disables index usage (on most databases) as it forces the integer column to be "promoted" to a string, but in the case of 8.3 it actually triggers the following error:

      ...
      Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
      	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
      	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
      	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
      	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)
      ...
      

      The fix is easy, the keys are actually numbers, so using setInt(index, Integer.valueOf(code)) makes the postgres factory work again

        Issue Links

          Activity

          Hide
          Martin Desruisseaux added a comment -
          This issue was also reported in GEOT-1835. In GeoTools 2.5, the fix needs to be applied on both AbstractEpsgFactory and DirectEpsgFactory because of the duplication of code introduced by the threaded factories work.

          The French mapping agency (IGN - Institut Géographique Nationale) uses a variant of the EPSG database with their own CRS, in which the keys are Strings. They created a subclass of DirectEpsgFactory for their need. So I must ensure that we can handle both cases, but this probably doesn't apply to GeoTools 2.5.x.

          We can apply the fix you are suggesting for GeoTools 2.5 branch. If you already have a patch at hand, would you like to commit it? Otherwise I can look at that.
          Show
          Martin Desruisseaux added a comment - This issue was also reported in GEOT-1835 . In GeoTools 2.5, the fix needs to be applied on both AbstractEpsgFactory and DirectEpsgFactory because of the duplication of code introduced by the threaded factories work. The French mapping agency (IGN - Institut Géographique Nationale) uses a variant of the EPSG database with their own CRS, in which the keys are Strings. They created a subclass of DirectEpsgFactory for their need. So I must ensure that we can handle both cases, but this probably doesn't apply to GeoTools 2.5.x. We can apply the fix you are suggesting for GeoTools 2.5 branch. If you already have a patch at hand, would you like to commit it? Otherwise I can look at that.
          Hide
          Andrea Aime added a comment -
          Here is a patch that changes all of the setString into a setInt
          Show
          Andrea Aime added a comment - Here is a patch that changes all of the setString into a setInt
          Hide
          Andrea Aime added a comment -
          Hum, looked at the other issue, I can rework the patch to follow that guidance. But if we go that direction, would'nt it be better to have a
          setCode(preparedStatement, int index, String code) and allow subclasses to override it?
          Show
          Andrea Aime added a comment - Hum, looked at the other issue, I can rework the patch to follow that guidance. But if we go that direction, would'nt it be better to have a setCode(preparedStatement, int index, String code) and allow subclasses to override it?
          Hide
          Martin Desruisseaux added a comment -
          Fixed on geotidy.
          Show
          Martin Desruisseaux added a comment - Fixed on geotidy.

            People

            • Assignee:
              Unassigned
              Reporter:
              Andrea Aime
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: