Opened 6 months ago

Closed 6 months ago

#3738 closed defect (fixed)

raster: Using -s without -Y in raster2pgsql transforms raster data instead of setting srid

Reported by: robe Owned by: robe
Priority: medium Milestone: PostGIS 2.3.3
Component: raster Version: 2.3.x
Keywords: Cc:

Description (last modified by robe)

I haven't tested this myself, but on postgis users list, two people mentioned this behavior:

https://lists.osgeo.org/pipermail/postgis-users/2017-April/042020.html

2017-04-11 14:25 GMT+02:00 Tumasgiu Rossini <rossini.t at gmail.com>:

> Hi,
>
> I think I found the probelm, it is the -s option which make the sql
> applying a st_transfrom on the raster to reproject it into the desired CRS
> before inserting it in the table, thus creating an in-db raster.
>

As Pierre noted, best thing to do is use the same reprojection syntax and shp2pgsql -s from_srid:to_srid (this might work already) have to test. and forbit transform with out-db. Since there is no point if its going to end up being in-db.

https://lists.osgeo.org/pipermail/postgis-users/2017-April/042050.html

Change History (9)

comment:1 Changed 6 months ago by robe

Summary: Using -s and -R together transforms raster data instead of setting sridUsing -s and -R together in raster2pgsql transforms raster data instead of setting srid

comment:2 Changed 6 months ago by robe

Description: modified (diff)
Owner: changed from dustymugs to robe

comment:3 Changed 6 months ago by robe

Summary: Using -s and -R together in raster2pgsql transforms raster data instead of setting sridraster: Using -s and -R together in raster2pgsql transforms raster data instead of setting srid

comment:4 Changed 6 months ago by robe

quick scan of raster2pgsql http://postgis.net/docs/doxygen/2.4/db/dd0/raster2pgsql_8c_source.html,

looks like we already support the -s srid_from:srid_to syntax for transformation.

2312                 if (CSEQUAL(argv[i], "-s") && i < argc - 1) {
 2313                         optarg = argv[++i];
 2314                         ptr = strchr(optarg, ':');
 2315                         if (ptr) {
 2316                                 *ptr++ = '\0';
 2317                                 sscanf(optarg, "%d", &config->srid);
 2318                                 sscanf(ptr, "%d", &config->out_srid);
 2319                         } else {
 2320                                 config->srid = config->out_srid = atoi(optarg);
 2321                         }
 2322                 }

Thought haven't verified it works and the documentation of it is commented out:

      /*
  333         printf(_(
  334                 "  -s [<from>:]<srid> Set the SRID field. Defaults to %d.\n"
  335                 "     Optionally reprojects from given SRID (cannot be used with -Y).\n"
  336                 "     Raster's metadata will be checked to determine an appropriate SRID.\n"
  337                 "     If a srid of %d is provided (either as from or as target).\n"
  338         ), SRID_UNKNOWN, SRID_UNKNOWN);
  339         */
  340         printf(_(
  341                 "  -s <srid> Set the SRID field. Defaults to %d. If SRID not\n"
  342                 "     provided or is %d, raster's metadata will be checked to\n"
  343                 "     determine an appropriate SRID.\n"
  344         ), SRID_UNKNOWN, SRID_UNKNOWN);

comment:5 Changed 6 months ago by robe

Okay this is clearly a bug and I think affects more than outdb. On some tests I did, it seems if you don't use -Y and you use -s, it attempts to do a ST_Transform.

If you use a -s without a -Y and happen to specify an srid that is different from the one it detected from the meta data of the raster, then it attempts to transform from the inferred srid to the new one.

So work around for out-of-db to get it to do the right thing is this:

raster2pgsql -C -t 500x500 -R -Y -s 26918  -F -I C:/gisdata/*.jp2 nj_aerials

-- I had an aerial with no metadata for the srid (would normally come in as unknown), as long as I have a -Y in there it just does a setsrid. If I leave out the Y, I get an error on insert.

If I do in-db then the from_srid:to_srid seems broken. It's like it's traying to use the unknown srid as the from instead of what I specified.

anyway I have a patch for this. I plan to backport the part about -s srid doing a transform instead of set.

I think there are some loose ends with the ST_Transform logic, and since the transform feature was never announced, I may just keep that fix for 2.4 and just flag it as a new feature and keep the 2.3 transform logic broken.

comment:6 Changed 6 months ago by robe

Summary: raster: Using -s and -R together in raster2pgsql transforms raster data instead of setting sridraster: Using -s without -Y in raster2pgsql transforms raster data instead of setting srid

comment:7 Changed 6 months ago by robe

In 15361:

Get rid of logic that set out_srid to same as input srid and change to not try to transform when out_srid is unknown.
References #3738 for PostGIS 2.4 trunk

comment:8 Changed 6 months ago by robe

Milestone: PostGIS 2.4.0PostGIS 2.3.3

comment:9 Changed 6 months ago by robe

Resolution: fixed
Status: newclosed

In 15362:

Get rid of logic that set out_srid to same as input srid and change to not try to transform when out_srid is unknown.
Closes #3738 for PostGIS 2.3

Note: See TracTickets for help on using tickets.