Create new point in feature class with st_geometry based on latitude and longitude

[CraigPatterson]

I need to insert a new record into point feature class using ST_GEOMETRY. The feature classes uses NAD_1983_StatePlane_California_III_FIPS_0403_Feet as it's coordinate system. The data I must insert is coming in with latitude and longitude instead of X/Y coordinates. I believe I need to use the ST_TRANSFORM function to reproject the the lat/long into the x/y, but I'm not sure how to do this.

Here is the code I've created so far:

select sde.st_transform(sde.st_point(37.780262347, -122.468853905, 0), 2) from dual;

When I execute the above statement, I get an error message that is the "Spatial References are not compatible."

The SRID of 0 is the default record in the spatial_reference table, which is GCS_WGS_1984, which I believe I can use to create my point from lat/long.

I appreciate any advice you may have.

Thanks,

Craig

[anthonysanchez]

Hi Craig,
The source and target Datum have to be the same for st_transform.   You'll have to convert the coordinates to something based on nad83 first. You may be able to find a service to do this for you or you can do it from arcmap.

Can you imagine how useful this tool would be if this restriction did not exist?

Anthony

[VinceAngelo]

Melita will correct me if I'm completely off-base, but you may be able to just create an
SRID based on GCS_NAD_1983, and use that for your geographic input.  The difference
between NAD83 and WGS84 is pretty small, though it would certainly be worth the effort
to verify that the shift is within your accuracy requirements (keeping in mind that second
precision is ~30m at the equator, you'd probably have to have hundredths of second
accuracy in your lat/lon values before the datum introduced significant error).

- V

[MelitaKennedy]

Current NAD83 and WGS84 data can generally no be considered coincident. They're getting farther apart with time because of how they are being maintained. NAD83 and its re-adjustments are tied to the North American plate to try to minimize coordinate change over time. If your WGS84 coordinates were not post-processed or acquired while doing real-time processing, the accuracy is probably not enough to matter. If it was real-time or post-processed, it's likely that the coordinates are already in NAD83 (actually one of the re-adjustments) because the control points used are already on NAD83.


Geomofo - As of 10.1, ST_Transform can support geographic (datum) transformations when working with Oracle. The functionality hasn't been ported to the other DBMS yet.