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

Question

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

• Inactivated Account: I wonder if geoserver can do this kind of geometry construction on the fly, especially in Oracle...? Would it be better to create the view in Oracle that creates the geometry objects and Geoserver would then be able to display them?
• Ian Turton: you may need the geometry metadata table (docs.geoserver.org/latest/en/user/data/database/…) but I'm not sure how that interacts with GeoServer generated views
• Devdatta Tengshe: What do the Geoserver Logs say?
• user1919: @iant one of the issues was the geometry metada. I have configured this. But in any case this query can not work on the fly. What I did was to create a table "CREATE TABLE AS...", add spatial index and configure the metadata. That worked. Creating a VIEW instead of a table doesnt do the trick cause VIEWS dont support INDEXES; thus I get a missing index error in geoserver.
• Ian Turton: see gis.stackexchange.com/questions/92554/…

Answer 1

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)

Answer 2

According to this thread this is not currently possible in GeoServer.

http://osgeo-org.1560.x6.nabble.com/XY-layers-in-Oracle-td5192640.html