Preamble: Spatial is not my career focus. It is an interesting application but is not something I hope I will not need to completely understand in order to complete this one task.
I know Google maps can find the point by entering X=6473340, Y=1847642
I have multiple sources of data coming in that all send in Longitude and Latitude except this one provider
Task description:
1. Receive data in a .csv format that has a key column and X and Y coordinates in separate columns
i.e.
| Incident_Number | X Coord | Y Coord |
| 170515000096 | 6473340 | 1847642 |
2. Convert the X Coord and Y Coord to Longitude and Latuitude to use in a conformed dimension
Software available:
Oracle data warehouse, 11.1.7 (including MDSYS, packages, etc)
ODI 11g (ELT)
OBIEE, 11.1.7
Accomplished so far:
1. Multiple silos of location data received from multiple outside sources
a. All location data prior to the latest has been in Longitude and Latitude
2. Have completed the Mapviewer configurations and successfully plotted the Fact and Dimensional data in OBIEE map presentations.
3. I have tried deciphering and locating how to do the task by reading https://docs.oracle.com/cd/B28359_01/appdev.111/b28400.pdf but my head started hurting.
4. Multiple Google searches return not straightforward conversion routines (must be complicated so that is why I am here asking this question)
5. Got hopeful when I found this code:
select
sdo_lrs.locate_pt(sdo_lrs.convert_to_lrs_geom(sdo_cs.transform(shape,8307)),0.02).sdo_ordinates
from
TEST_SCHEMA.TEST_TABLE
where
route='ABC'
and
segmentnum='101.1';
6. Hopes of simple dashed when I realized from reading the above doc (see 3) that I will probably need to create the dimension with an SDO geometry field, spatial index etc.
and start over because of 1 data provider that is upgrading in a year and will provide in Longitude/Latitude at that time
QUESTION:
1. Is there a repeatable process that can be utilized with the software I have available(see 2 above).
2. If there is a process, what is it given the data available(see 1 above).
If you need to ask me questions for clarification I will answer them as clearly as I am able but they will come from a Layman's perspective of Spatial applications.