topic Re: [Postgres+SDE] ST_Transform with different spatial references in Data Management Questions

[Postgres+SDE] ST_Transform with different spatial references

FlorianCADOZ — Fri, 03 Apr 2015 12:49:24 GMT

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 !

Re: [Postgres+SDE] ST_Transform with different spatial references

CarrieDavis — Mon, 06 Apr 2015 20:58:30 GMT

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.

Re: [Postgres+SDE] ST_Transform with different spatial references

FazertyCazerty — Tue, 07 Apr 2015 07:47:21 GMT

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

Re: [Postgres+SDE] ST_Transform with different spatial references

NeilAyres — Tue, 07 Apr 2015 14:23:27 GMT

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.

Re: [Postgres+SDE] ST_Transform with different spatial references

FazertyCazerty — Tue, 07 Apr 2015 14:46:45 GMT

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 !

Re: [Postgres+SDE] ST_Transform with different spatial references

NeilAyres — Tue, 07 Apr 2015 14:53:49 GMT

Florian,

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

Re: [Postgres+SDE] ST_Transform with different spatial references

NeilAyres — Tue, 07 Apr 2015 14:55:33 GMT

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

Re: [Postgres+SDE] ST_Transform with different spatial references

FazertyCazerty — Tue, 07 Apr 2015 15:11:49 GMT

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...

Re: [Postgres+SDE] ST_Transform with different spatial references

FazertyCazerty — Tue, 07 Apr 2015 15:13:36 GMT

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

Re: [Postgres+SDE] ST_Transform with different spatial references

NeilAyres — Tue, 07 Apr 2015 15:55:06 GMT

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.

Re: [Postgres+SDE] ST_Transform with different spatial references

StevenEmbree_II — Tue, 07 Apr 2015 23:01:10 GMT

Florian,

It appears as though you're using an SDE geodatabase within PostgreSQL. Please correct me if I'm wrong. If you are, using the ST_TRANSFORM is not recommended, as it does not update all of the required SDE repository tables. To correctly change the spatial references, you should use tools available within ArcGIS Desktop to accomplish that task. Please see the following:

ST_TRANSFORM

ST_Transform—Help | ArcGIS for Desktop

I hope that helps!

Re: [Postgres+SDE] ST_Transform with different spatial references

CarrieDavis — Wed, 08 Apr 2015 16:07:44 GMT

Florian,

I'm not familiar with a way to automate the re projection process with SQL. The project tool is available with the arcpy python library if you need to automate the process. There is a python sample contained in the 10.3 documentation. Project—Help | ArcGIS for Desktop .

Re: [Postgres+SDE] ST_Transform with different spatial references

FazertyCazerty — Thu, 09 Apr 2015 08:08:51 GMT

Hi everyone,

- Neil Ayres :

No it doesn't overlay because : the origin data are lon/lat in WGS84 (SRID : 4326), my database is in L93 (SRID : 2154) and I would like to inject the origin data (in WGS84) into a layer (in L93), so yes it makes sense to reproject the lon/lat from WGS84 (SRID : 4326) to L93 (SRID : 2154), maybe with a transformation like WGS84_1984_To_RGF_1993 or the transformation used in ArcGIS which is WGS_1984_To_NTF_NTv2+NTF_To_RGF_1993_1 but certainly not RGF_1993_To_WGS_1984 (SRID-transformation : 1671) because if for reproject from WGS84 to RGF93, i'm not sure that if the 1671-transformation worked, ArcMap would prefer to use the WGS_1984_To_NTF_NTv2+NTF_To_RGF_1993_1 transformation rather than the RGF_1993_To_WGS_1984 which looks simpler...

- Steven Embree II :

Yes I'm using ArcSDE within Postgresql, but I have to use the SQL/SDE queries without using ArcMap because my goal is to schedulling some tasks like : Creating point from lon/lat WGS84 data --> Reprojecting it in L93 --> Insert it into my sde/PG database...

I don't understand you're message, are you telling me that the sde queries malfunction ?

- Carrie Davis :

No my script is in Java and it lunch some queries in XPath and SQL sometimes, on the other side, I really don't know Python and I really would like to do this in SQL...

Is it me or this transformation is not possible in SQL with the sde.st_transform under Postgres ?

Thx everyone for your reponses

Re: [Postgres+SDE] ST_Transform with different spatial references

CarrieDavis — Thu, 09 Apr 2015 18:22:59 GMT

Hi Florian,

You are correct. Applying a transformation is not possible with the ST_transform. Sorry to inform you that this would limit your ability to do this re-project in SQL. There is a Java API but this is not my expertise.

ArcGIS Runtime for Java 10.2.4 API Reference