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

Question

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).

• Vince: You're not specifying an SRID in object creation. Please edit the question to report the error message contents exactly. Did you mean to specify X == Y for the coordinate?
• user1919: As far I read in documentation the SRID is the second parameter; thus 2100. The first parameter is the type of geometry. In any case I have edited my question in order to show how the query creates the points transformed in the desired SRID (2100). Also the points are in readable format.

Answer 1

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)

Answer 2

I have written this query:

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

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

• Mintx: If you feel your converted point geometry is not in the right spot, check select mdsys.sdo_cs.determine_chain(null, null, 4326, 2100) from dual and make sure code 1272 is mentioned somewhere in the transform chain.
• Albert Godfrind: The "2100" in this shape is the geometry type. 2100 is not a valid type. For a 2D point, the proper type is 2001 (2 stands for 2D and the final 01 means it is a point). 4326 is the SRID. Here it means that the coordinates are in WGS84 (GPS) coordinates. Finally the coordinates are always in (X,Y) sequence, i.e. (longitude,latitude) - not (latitude,longitude) as written.
• user1919: @Albert Godfrind Seen and fixed