Create a geoserver SQL view from Oracle SQL using a lat and lng column

I have a table in a Oracle SQL database in which I have a polygon geometry and two more columns of latitude and longitude. What I need is to show the points of lat and lng in a layer in geoserver.

I have done similar thing using PostGIS but in now with Oracle I face an error. First I have create a query which selects the two coloumns, transforms them to the desired SRID (EPSG:2100), REPLACE the '.' with the ',' and CAST them to float (cause they are characters).

When I execute the query in the Oracle SQL developer it seems that it works correctly. Also if I copy and paste teh coords they are shown in the correct position on the map.

But when I se my query in geoserver in order to create a sql view layer, although the geometry column is recognized (also the SRID), when I click on the "compute BBOX" option I get a geoserver error.

As I said I did something similar with postGIS and it was working fine. I also tried to use the same table (but its existing geometry; the polygon) and it works also great.

So I guess it must be related with my query but I can not guess what can cause this "unexpected geoserver error".

Thats my query:

SELECT ID, CO_NAME, ADJUSTMENT_FACTOR, PORT_LOCATION, VDSL_ENABLED,
SDO_CS.TRANSFORM(SDO_GEOMETRY(2100,4326,SDO_POINT_TYPE(
CAST(REPLACE(CENTRAL_OFFICES.LAT, '.', ',') AS FLOAT),  
CAST(REPLACE(CENTRAL_OFFICES.LNG, '.', ',') AS FLOAT),NULL),NULL,NULL),2100) AS geom
FROM CENTRAL_OFFICES

Answer

Let's assume that your table is like this:

create table central_offices (
  id number primary key,
  lat varchar2(20),

  lng varchar2(20)
);

insert into central_offices (id, lat, lng) values (1, '39.018483', '22.9983436');
commit;

Your strings are stored with a point as decimal separator. When you use this string in a statement that expects a number, Oracle will automatically do the proper type casting and conversion, i.e. parse the string into a number.

BUT: that parsing happens in the context of the locale you use in your session. Since 2100 is a Greek system, I assume you are in Greece, and so use a Greek locale where the decimal separator is a comma. If you try converting the above string to a number, you will get the following error:

SQL> select to_number('39.018483') from dual;
select to_number('39.018483') from dual

                 *
ERROR at line 1:
ORA-01722: μη αποδεκτός αριθμός

Replacing ',' with '.' on the fly is a valid solution. A simpler approach is just to override the locale for your session:

alter session set nls_numeric_characters = '.,';

select id, 
  sdo_cs.transform (
    sdo_geometry (2001, 4326, sdo_point_type(lng, lat, null), null,null),

    2100
  ).get_wkt()
from central_offices;

  ID SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(LNG,LAT,NULL),NULL,NULL)
---- -------------------------------------------------------------------------------
   1 POINT (413136.397473566 4319017.56676377)