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)
The simplest would be to have one row per farmer and intensity, so that you are not dependent on the intensity values
select intensity, Codes, sum(area_ha) as Total_area_ha,st_union(geometry) as geometry
from A
group by Codes, intensity
If you want each intensity as a column, you can set the value being given to sum to be either the real value for the corresponding intensity, or 0 for other intensities. You would have to do this for every intensity!
select Codes,
sum(area_ha) as Total_area_ha,
sum(case when intensity=10 then area_ha else 0 end) as area_intensity10,
sum(case when intensity=20 then area_ha else 0 end) as area_intensity20,
st_union(geometry) as geometry
from A
group by Codes
Best Answer
If you want to use just SQL, do it with ogrinfo and -sql paramater.
First command must use the GDAL OGR SQL dialect https://www.gdal.org/ogr_sql.html
Second command must use the GDAL SQLite SQL dialect https://www.gdal.org/ogr_sql_sqlite.html