[Postgres+SDE] ST_Transform with different spatial references

[FlorianCADOZ]

Hi everyone, I need help !!

I'm under PostgreSQL with ArcSDE and I have to reproject a data from WGS84 to RGF93 (French system with a different geographic coordinate system than WGS84)...

The solution is maybe here : ArcGIS Help (10.2, 10.2.1, and 10.2.2) ​

But on this page, there is no explanation for my problem with PostgreSQL ?!!!

Is anyone has a solution ?

Thx a lot for answers !

[CarrieDavis]

Hi Florian,

This is possible to reproject using the Project tool in ArcGIS Desktop.  You will need to establish a data connection with your Postgres (sde) or POSTGIS database and select to use a Geographic Transformation such as RGF_1993_To_WGS_1984_1 to transform the data from WGS94 to GCS_RGF_1993.  Transformations are available out of the box and there is a list of options for RGF93 under Projected Coordinate Systems > National Grids > France.  ArcGIS Help (10.2, 10.2.1, and 10.2.2)  This process will require you to write to a new feature class to update the xy values.

[FazertyCazerty]

Hi Carrie,

Thx for your answer but I need to project from WGS84 (SRID : 4326) to GCS_RGF_1993 (SRID : 2154)...not form 2154 to 4326... And in ArcGIS, this projection is more difficult because the transformation is :

WGS_1984_To_NTF_NTv2 + NTF_To_RGF_1993_1

In fact, I'm using an ETL for scheduling some SQL Queries, those queries are launched from this ETL into an SDE database under postgres. That's why I'm not searching for an answer with ArcGIS but with ArcSDE and the ST_Transform query ! BUT, this query doesn't work under postgres for source and destination spatial references do not have the same geographic coordinate system...

In ArcGIS, for this problem, the transformation used is WGS_1984_To_NTF_NTv2 + NTF_To_RGF_1993_1 but I don't know how to use it with ArcSDE...

Thx a lot for your answers

[NeilAyres]

That's a so called "composite" transform, a sort of 2 stage process. And this will work using the project tool.

But sometimes, Arc makes some strange guesses as to correct composites.

But as you want this to happen in Postgres...

A quick check of the book (geographic_tansformations.pdf, in the documentation folder of the install), says there is a direct way to go from WGS84 to RGF_1993, it has WKID (I presume this is the same as a SRID) of 1671.

But looking at the parameters for this, they are all 0 (zero). So RGF is already "functionally" equivalent. They are the same.

So you may not have to do anything to your data, to get them to overlay properly.

[FazertyCazerty]

Hi Neil,

Thank you for your anwser

I already take a look into the geographic_transformations_10.1.pdf (didn't find the 10.3 version), and the transformation with the WKID 1671 is for RGF_1993_To_WGS_1984_1...me I need to go from WGS84 to RGF93 !

Moreover, I'm not sure that I can specify a geogtrans_id (WKID) with Postgres...It's said there : Aide ArcGIS (10.2, 10.2.1 et 10.2.2) (Take a look on the second sample)

And maybe it's okay for the overlay but I need to put everything in L93 for the consistency of my database !

[NeilAyres]

Florian,

that's the name of it. Arc ( and I would hope SDE) knows how to use them backwards and forwards.

[NeilAyres]

And, if they are "functionally equivalent", ie they overlay. Just redefine the WGS84 based data to be RGF. No transform is necessary anyway.

[FazertyCazerty]

Yeah but the problem is the same :

Under Postgres : No transformation available when source and destination spatial references do not have the same geographic coordinate system...

[FazertyCazerty]

So I should use an SDE fonction like set SRID 2154 on every 4326 data ?

[NeilAyres]

I would look at the data first!

Does it make sense? Does it (the WGS84 based data) overlay nicely with other RGF defined data?

And, I thought the WKID / SRID was 1671.