[GIS] Generate in oracle sql developer a point with specific SRID from two variables

geoserveroracle-spatial

I have created quite often (in posgis) points using two variables from the table (LAT,LON) and setting the desired SRID.
I am trying to do something similar now with Oracle SQL developer but I keep getting errors when I try to display this as a layer in geoserver.
This is what I try:

SELECT 
  ID,
  CO_NAME,
  ADJUSTMENT_FACTOR, 
  MDSYS.SDO_GEOMETRY(null,2100,SDO_POINT_TYPE(24.004632439599,24.004632439599,NULL),NULL,NULL) AS GEOM
 FROM CENTRAL_OFFICES

Instead of the variables LAT and LNG I have just put two random numbers. Moreover I want to ask how is possible to view in a readable format the generated geometry. Something like: ST_ASTEXT of postgis.

EDIT

I have written this query:

SELECT ID,
  CO_NAME,
  ADJUSTMENT_FACTOR,
  PORT_LOCATION,
  VDSL_ENABLED, 
  SDO_UTIL.TO_WKTGEOMETRY(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

This returns a table with a point geometry in the desired SRID (2100).

Best Answer

Assume that the CENTRAL_OFFICES table looks like this (simplified).

create table central_offices (
  id number primary key,
  lat number,
  lng number
);

Let's fill it with sample data:

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

The select to achieve what you want is then like this (assuming that lat and long are decimal degrees in WGS84 aka GPS coordinates)

select id, 
  sdo_cs.transform (
    sdo_geometry (2001, 4326, sdo_point_type(lng, lat, null), null,null),
    2100
  ).get_wkt()
from central_offices;

Notice this uses the get_wkt() method to get the results in OGC WKT (function SDO_UTIL.TO_WKTGEOMETRY does the same).

IMPORTANT: notice that coordinates are always stored in (X,Y) order, which for geodetic coordinates means (longitude, latitude) - not (latitude,longitude) as originally written.

In your example it looks like the coordinates are stored as strings with a point as decimal separator - something like "39.018483". You can use those strings directly in the SDO_GEOMETRY constructor: the conversion from string to number happens automatically.

BUT: that 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: μη αποδεκτός αριθμός

You could replace '.' with ',' the way you do, but a better way is just to tell the database to use a point as decimal separator:

SQL> alter session set nls_numeric_characters = '.,';
Session altered.

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

TO_NUMBER('39.018483')
----------------------
             39.018483

1 row selected.

Let's try that with the CENTRAL_OFFICES table that uses strings for the coordinates:

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;

Without the separator setting, the query will fail:

select id, 
  sdo_cs.transform (
    sdo_geometry (2001, 4326, sdo_point_type(lng, lat, null), null,null),
    2100
  ).get_wkt()
from central_offices;
ERROR:
ORA-01722: μη αποδεκτός αριθμός

With the setting, it works fine:

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)
Related Question