Spatial SQL - return coordinates in a different projection

[NickHarrison]

Using spatial SQL is it possible to return coordinates in a different SRID than that of the SDE layer?

My data  is stored in a custom transverse mercator WGS84 projection and I want to return lat/long WGS84 with st_x  and st_y.

I'm using ArcSDE 9.2 with Oracle

[VinceAngelo]

http://webhelp.esri.com/arcgisdesktop/9.2/index.cfm?TopicName=ST_Transform

The projection must of course be reversible.

- V

[NickHarrison]

Thanks for the responce. I didn't notice ST_TRANSFORM.

Must the SRID already exist in the SDE.ST_SPATIAL_REFERENCES table? Or is there a list of global SRID values that I can reference?

Fortunately GCS_WGS_1984 is in my ST_SPATIAL_REFERENCES table. But what would I do if it wasn't?


SELECT
st_GeometryType(shape) AS GEOMETRY_TYPE,
round(st_x(shape),3) as X_COORD,
round(st_y(shape),3) AS Y_COORD,
round(st_y(st_transform(shape, 23)),6) as Latitude,
round(st_x(st_transform(shape, 23)),6) as Longitude
FROM CALGIS.PARIS_MONUMENT

[VinceAngelo]

Yes, the SRID must exist.  If it doesn't, create a layer in the desired coordref, then
DROP the table (SRIDs persist).

- V

[NickHarrison]

OK thanks. I have what I need now.

But that's going to make it a little difficult to write a statement that works in different instances (development, test, production, etc). I have checked and the SRIDs are not consistant - which I guess I expected.

I guess I'll have to write some PL_SQL to get the SRID as a variable where SR_NAME = 'GCS_WGS_1984'. Then use that in the st_transform function.

Do you have any other suggestions?

Thanks,

Nick

[VinceAngelo]

I doubt that would work reliably (SR_TEXT is only a part of the SE_COORDREF definition):

 
  1  SELECT count(sr_name) FROM sde.st_spatial_references
  2* WHERE sr_name = 'GCS_WGS_1984'
SQL> /
 
COUNT(SR_NAME)
--------------
            12

It would probably be better to create an application configuration table that would allow
you to control the SRID used by the app (see SDE.SERVER_CONFIG for an example):

 
SQL>  describe sde.server_config
 
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROP_NAME                                 NOT NULL NVARCHAR2(32)
 CHAR_PROP_VALUE                                    NVARCHAR2(512)
 NUM_PROP_VALUE                                     NUMBER(38)

- V

[NickHarrison]

I see what you're getting at. Good idea.

Just out of curiosity is the st_transform only going to work within the extent limits of the SRID?

[VinceAngelo]

Yes.  It's usually wise to define GEOGCS-based coordinate references which are capable
of mapping the full globe (since I don't need to map individual bacteria, I generally use
'-x -210,-120,1000000', which also reduces storage requirements 10-30% and improves
query performance significantly).

- V