[postgis-devel] [PostGIS] #318: 900913->4326 Transform

[PostGIS]

#318: 900913->4326 Transform
----------------------+-----------------------------------------------------
  Reporter:  pramsey  |       Owner:  mcayland     
      Type:  defect   |      Status:  new          
  Priority:  medium   |   Milestone:  PostGIS 1.4.1
 Component:  postgis  |     Version:  trunk        
Resolution:           |    Keywords:               
----------------------+-----------------------------------------------------
Comment (by arthurnederlof):

 Replying to [comment:9 mcayland]:
 > Hi Arthur,
 >
 > If you look at the thread above, the issue is that so far only Paul can
 reproduce the error. You can help us by giving as much information about
 your system, OS, PostgreSQL version, GEOS version and PROJ version as
 possible, as well as complete test case.
 >
 >
 > HTH,
 >
 > Mark.
 Mark,
 No problem, the probem is very consistent.
 below the enviromnet, testoutput and a small demoprogram.
 The used testcoordinates are from Cologne, germany, but all tested
 coordinates in Europe give the same problem.
 If you need more, please ask.
 Arthur

 system: GNU bash, version 3.2.48(1)-release (x86_64-pc-linux-gnu)
 postgresql:"PostgreSQL 8.3.9 on x86_64-pc-linux-gnu, compiled by GCC
 gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3"
 postgis:"POSTGIS="1.4.1" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec
 2007" USE_STATS"

 Output testprogram:
 15:18:13,445  INFO test::15 - start testrun
  15:18:13,449 DEBUG DbfMaintenance::483 - Access to postGreSql on
 jdbc:postgresql://10.13.54.8:5432/osm, user= arthur
  15:18:13,581 DEBUG test::54 - postgreql version = PostgreSQL 8.3.9 on
 x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4)
 4.3.3
  15:18:13,582 DEBUG test::55 - postgis version = POSTGIS="1.4.1"
 GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS
  15:18:13,582 DEBUG test::60 - sql:select osm_id, name from
 planet_osm_polygon where
 (ST_Contains(way,ST_Transform(ST_SetSRID(ST_MakePoint(6.95,50.9333333),4326),900913)))
 limit 1
  15:18:13,591 DEBUG test::63 - name = Köln, Stadt
  15:18:13,592 DEBUG test::68 - sql:select osm_id, name, ST_AsGeoJSON(
 ST_TransForm(ST_setSRID(St_Box2D(way)::box2d,900913),4326),6 ) as box from
 planet_osm_polygon where
 (ST_Contains(way,ST_Transform(ST_SetSRID(ST_MakePoint(6.95,50.9333333),4326),900913)))
 limit 1
  15:18:13,620 DEBUG test::75 - problem with sql : ERROR: transform:
 couldn't project point (753919 6.59133e+06 0): failed to load NAD27-83
 correction file (-38)
   Hint: PostGIS was unable to transform the point because either no grid
 shift files were found, or the point does not lie within the range for
 which the grid shift is defined. Refer to the ST_Transform() section of
 the PostGIS manual for details on how to configure PostGIS to alter this
 behaviour.
  15:18:13,620 DEBUG DbfMaintenance::507 - disconnected to database.
  15:18:13,621  INFO test::17 - Run postgistest OK

 The testrountine itself:
         public static String demontratePostgisProblem(double lat, double
 lon){
                 Connection conn=DbfMaintenance.getPgDbConn();
                 String sql="";
                 try {
                         Statement stmt= conn.createStatement();
                         stmt = conn.createStatement();
                         ResultSet rset;
                         rset = stmt.executeQuery ("select version() as
 postresqlversion, postgis_full_version() as postgisversion");
                     while (rset.next()) {
                         logger.debug("postgreql version =
 "+rset.getString("postresqlversion"));
                         logger.debug("postgis version =
 "+rset.getString("postgisversion"));
                     }
                         // this does work
                     sql = "select osm_id, name from planet_osm_polygon"+
                           " where
 (ST_Contains(way,ST_Transform(ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326),900913)))
 limit 1";
                     logger.debug("sql:"+sql);
                     rset = stmt.executeQuery (sql);
                     while (rset.next()) {
                         logger.debug("name = "+rset.getString("name"));
                     }
                     //this one gives the error  for 4326,  4267 works ok
                     sql = "select osm_id, name, ST_AsGeoJSON(
 ST_TransForm(ST_setSRID(St_Box2D(way)::box2d,900913),4326),6 ) as box from
 planet_osm_polygon"+
                   " where
 (ST_Contains(way,ST_Transform(ST_SetSRID(ST_MakePoint("+lon+","+lat+"),4326),900913)))
 limit 1";
                     logger.debug("sql:"+sql);
                     rset = stmt.executeQuery (sql);
                     while (rset.next()) {
                         logger.debug("name = "+rset.getString("name"));
                         logger.debug("box = "+rset.getString("box"));
                     }
                 } catch (SQLException e) {
                         logger.debug("problem with sql :
 "+e.getMessage());

                 }
                 DbfMaintenance.closeDbConn(conn);
                 return"OK";
         }

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/318#comment:10>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.