1

I have GeoJson data. I would like to convert this GeoJson data into sdo_geometry object type and insert Oracle spatial database. Which columns represent geojson data in oracle sdo_geometry object.

For example, My GeoJson Data is

  {"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[410694.5082851944,4466102.355016577]},"properties":null}]}

sdo_geometry in Oracle is consisted of columns that are named SDO_GTYPE, GEOLOC.SDO_SRID, GEOLOC.SDO_POINT, SDO_ELEM_INFO,SDO_ORDINATES.

How Could I populate columns which belong to sdo_geometry object in Oracle using GeoJson object.

Geojson objects might be different, so it couldn't always point type and because of this, geojson object could have different values.

  • You may need to first mention the version of Oracle you are using. There is no GeoJSON support before 12c. – pauldzy Dec 6 '17 at 11:55
1

Oracle 12.1 comes with built-in support for JSON. Oracle 12.2 adds built-in support for GeoJSON: function sdo_util.from_geojson() will take a GeoJSON geometry and turn it into an SDO_GEOMETRY object. For example:

select sdo_util.from_geojson('{"type":"Point","coordinates":[410694.5082851944,4466102.355016577]}') from dual;

SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(410694.508, 4466102.36, NULL), NULL, NULL)

Notice the coordinate system is automatically set to 4326. That is because the GeoJSON standard only understands WGS84 longitude/latitude coordinates. You can however specify an explicit SRID for Oracle:

select sdo_util.from_geojson('{"type":"Point","coordinates":[410694.5082851944,4466102.355016577]}',null,2154) from dual;

SDO_GEOMETRY(2001, 2154, SDO_POINT_TYPE(410694.508, 4466102.36, NULL), NULL, NULL)

Converting a GeoJSON feature - i.e. a geometry with properties requires more work. Consider the following table that contains GeoJSON features (one feature per row):

create table geojson_table (geojson clob check (geojson is json));
insert into geojson_table(geojson)
values ('{"type":"Feature","properties":{"CITY":"New York", "STATE_ABRV":"NY","POP90":7322564,"RANK90":1}, "geometry":{ "type": "Point", "coordinates": [-73.943849, 40.6698] }}');
commit;

You can read those features like this:

select 
  g.geojson.properties.City as city,
  g.geojson.properties.State as state,
  g.geojson.properties.Population as population, 
  g.geojson.properties.Rank as rank,
  json_value(geojson, '$.geometry' returning sdo_geometry) as geometry
from geojson_table g;

New York   NY    7322564    1 SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.943849, 40.6698, NULL), NULL, NULL)

Converting a complete GeoJSON document (i.e. a feature collection) requires more parsing, that can all be done using the standard JSON facilities of the database ...

However since you are on an older version, your best approach is to use GDAL. The following loads a complete GeoJSON collection. It creates the database table, populates it and creates the spatial index

ogr2ogr -f OCI OCI:scott/tiger@localhost:1521/orcl122: us_cities.json -nln us_cities_p -lco DIM=2 -lco SRID=4326 

Notice that you need to explicitly specify the dimensionality (2D - otherwise you get a 3D table) and coordinate system. Also specify an explicit name for the output table (otherwise it gets a default name OGRGEOJSON)

0

I am assuming you are using 12c or newer.

Given your GeoJSON feature collection you need to deconstruct the collection into features and then further to the geometry using external or Oracle JSON tools. Then once you have the geometry object use SDO_UTIL.FROM_GEOJSON to convert to SDO_GEOMETRY.

  • thanks paul for your help. but pl-sql developer couldn't find that method. – vahitdurmus Dec 6 '17 at 12:18
  • Well, back to my first comment. What version of Oracle are you using? – pauldzy Dec 6 '17 at 12:48
  • Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production – vahitdurmus Dec 6 '17 at 13:18
  • Well, then I do not know of an in-database solution. You'd need to export the GeoJSON out via a GIS ETL tool such as GDAL or FME and then import the results back in as SDO_GEOMETRY. A clever trick might be to set up a foreign data wrapper with PostgreSQL using the Oracle_FDW and then convert the data on the pg side and reflect it back into Oracle. laurenz.github.io/oracle_fdw – pauldzy Dec 6 '17 at 19:12
  • thank you your interest. I solved problem with hard-code. firstly I have picked up values in geojson data and filled inside sql query. – vahitdurmus Dec 7 '17 at 6:19
0
  [WebMethod]
            public string insertdraw(string pType,string pCoordinate)
            {
                int SDO_GTYPE = 0;
                string sdo_geometry_command_text = "";
                switch(pType)
                {

                    case "Point":
                        SDO_GTYPE = 2001;
                        sdo_geometry_command_text = "MDSYS.SDO_GEOMETRY(" + SDO_GTYPE + "," + 5255 + ",SDO_POINT_TYPE("+pCoordinate+",null),null,null)";
                        break;
                    case "LineString":
                        SDO_GTYPE = 2002;
                        sdo_geometry_command_text = "MDSYS.SDO_GEOMETRY(" + SDO_GTYPE + "," + 5255 + ",MDSYS.SDO_POINT_TYPE(0,0,null),MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(" + pCoordinate + "))";
                        break;
                    case "Polygon":
                        SDO_GTYPE = 2003;
                        sdo_geometry_command_text = "MDSYS.SDO_GEOMETRY(" + SDO_GTYPE + "," + 5255 + ",null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(" + pCoordinate + "))";
                        break;
                    default:
                        break;
                }
                try
                {
                    Connect();
                    OracleCommand cmd=new OracleCommand();               
                    cmd.Connection = conn;
                    cmd.CommandText = "select  max(d.id) from tablename d ";
                    int x = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.CommandText = "Insert into tablename(ID,Geoloc) VALUES(" (x + 1) + ","+sdo_geometry_command_text)";
                    int result= cmd.ExecuteNonQuery();
                    if (result != 0 && result != -1)
                    {
                        return "success:"+(x + 1).ToString();
                    }
                    else
                        return "failed";      
                }
                catch (OracleException e)
                {
                    return "oracleexception in insertdraw function";
                }

            }

This Web Service method was writen by c#. when a drawing was end in openlayers, after took draw feature, it can be called and can perform insert spatial data in oracle spatial database. Happy Coding..

0

As of Oracle 12c, Oracle Spatial includes in-database native GeoJSON support. Please refer to: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/using-GeoJSON-geographic-data.html#GUID-2AD827B4-871E-4652-87F3-AC33FE7839AE

With the latest release, 18.1, Oracle Spatial and Graph supports the use of JSON objects to store, index, and manage geographic data that is in JSON (JavaScript Object Notation) format. You can convert data from JSON objects to Oracle Spatial SDO_GEOMETRY objects, and from SDO_GEOMETRY objects to JSON objects.

JSON support, introduced in Release 18.1, substantially expands the GeoJSON support available in the previous release, in that it supports a larger range of geometries, including 2D and 3D, solid, surface, and LRS geometries. While the Spatial and Graph GeoJSON-specific APIs are still supported, you are encouraged to use the more comprehensive JSON support.

SDO_UTIL.FROM_JSON, which converts a JSON object to an SDO_GEOMETRY object, can also read and convert GeoJSON objects.

SDO_UTIL.TO_JSON and SDO_UTIL.TO_JSON_VARCHAR, which convert an SDO_GEOMETRY object to a JSON object (CLOB or VARCHAR2, respectively), can convert any geometry supported by Spatial and Graph.

Refer to section 1.14 of the documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/spatial-concepts.html#GUID-0D253291-6FB9-4DBF-BD84-925E7E9C93D0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.