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

Finde mehr Beiträge mit dem Tag

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

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

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