nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

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

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

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

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

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

- 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

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

The projection must of course be reversible.

- V

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.