Plotting lat/long stored as text in Oracle DB using report builder/SSRS r2?

Question

We use an Oracle 10g database. However we write reports using SSRS or report builder, using the Oracle connection type to the datbase. In oracle we have 2 text fields, one for lat and one for long. (I am in the US and we use WGS84 coordinates). Also, I only have SELECT rights on the oracle server. I cannot manipulate the database. I can create functions if that helps.

I need to plot these on a point layer in an SSRS map. However, when I use the following code in my select statement, SSRS throws an error:

Unsupported Oracle Data type USERDEFINED encountered

SDO_GEOMETRY('POINT(' || Longitude || ' ' || Latitude || ')',4326) as Coordinates

If I use the SQL code below I get an error that the FROM keyword is not found (makes sense since oracle doesn't use ::)

  geography::STPointFromText('POINT(' + 
  CAST(longitude AS VARCHAR(20)) + ' ' + 
  CAST(latitude AS VARCHAR(20)) + ')', 4326)

How can I use the lat and long stored in Oracle text fields as geo data types that SSRS can use to plot the point? (NOT SQL Server... there is no SQL Server, only an SSRS server)

Answer 1

Sorry, but I am not familiar at all with SSRS. I doubt that it understands the Oracle spatial object types. Then again, your Oracle database does not seem to have the SDO_GEOMETRY type defined. This is probably because it was created without that support. You can verify that by connecting using sqlplus and doing:

describe sdo_geometry

and/or ask the DBA to check for the presence of the MDSYS user, and the presence of the SDO_GEOMETRY type (owned by MDSYS).

As for the SQL Server syntax, this will not work, since you do not have any SQL Server database. Are you sure SRSS does not have a way to let you plot geographic points by just supplying the longitude and latitude coordinates as numbers ?

Answer 2

If you have SSRS that implies you must have a SQL database instance to support it. I would copy the oracle data into a table in that SQL instance (e.g. using SSIS), and then update a geography column in that SQL table from the text lat and long. You will probably also want a spatial index on top of that for performance.