Convert Oracle Spatial data to geojson

So at some point I needed to convert Oracle Spatial data (SDO) into geojson format. I quickly came across this excellent post from SpatialDB Advisor. Which basically does exactly that. Converts SDO data into geojson. However -as he already states in his post- the function does not support attributes. So based on this original function, I created a package that will create a FULL geojson including attributes using a bit of dynamic SQL (DBMS_SQL package). And in fact, you can select which attributes by providing a select statement as a parameter. I also *think* I fixed a couple of minor bugs in the original sdo2geojson function.

You can find the package (ora2geojson.rar) here. Unzip the file and run the package header  (.pkh) and body (.pkb) files e.g.

SQL>@ora2geojson.pkh

and

SQL>@ora2geojson.pkb

Example:

SELECT ora2geojson.sdo2geojson('select * from v_segments_all_sdo',ROWID, shape) FROM v_segments_all_sdo;

Where v_segments_all is the spatial table or view and shape is the name of the spatial column. The ROWID is needed to collect the attributes. I am sure there are more sufficient ways to do this but this one seemed to work fine for me.

A sample usage and result is shown below:

sdo2geojson

 

12 thoughts on “Convert Oracle Spatial data to geojson

  1. Hi,
    I get “Secure Connection Failed” and “ERR_CONNECTION_RESET” when I click on the link to ora2geojson.rar. Would you know if others are facing this problem? Thanks
    John

  2. SQL> select * from states;

    GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
    —————————————————————————-

    SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(129, 32, NULL), NULL, NULL)

    //////////////

    SQL> SELECT ora2geojson.sdo2geojson(‘select * from states’, ROWID, geom) FROM st
    ates;
    ERROR:
    ORA-21560: argument 2 is null, invalid, or out of range
    ORA-06512: at “SYS.DBMS_LOB”, line 1024
    ORA-06512: at “SRKIM.ORA2GEOJSON”, line 91
    ORA-06512: at “SRKIM.ORA2GEOJSON”, line 393
    ORA-06512: at line 1

    no rows selected

    ///////////////

    I don’t know how to run this, looks like ROWID and geom are treated as null or invalid. But geom is definitely the spatial column name of states table.
    Also, I am not sure what ROWID menas.
    please, can you help me with this? if you get a solution, let me know, thank you.

    1. Apologies for not getting back to you earlier. Haven’t tried it but I guess the problem is that your table does not have any attributes. From what I can see, only contains a spatial column? Try adding a column – in any real scenario you would need to have at least a column with some sort of ID/PK in there

  3. Hi,
    Polygon not works properly.there are points rather than commas between longitude and latitude ,something like this
    {“type”:”MultiPolygon”,”coordinates”: [[[[91.876488072.22.486164347],[91.875900848.22.486171184],[91.875569953.22.486269856],[91.875317185.22.486487274],…….

      1. Hi
        Now its working. I Have changed in this line..

        BEGIN
        EXECUTE IMMEDIATE ‘alter session set NLS_NUMERIC_CHARACTERS=”’ || ‘.,’ || ””;
        v_ret:= ‘[‘ ||
        CASE WHEN p_relative
        THEN round(p_x – v_mbr.sdo_ordinates(1),v_precISion) || ‘,’ || round(p_y – v_mbr.sdo_ordinates(2),v_precISion)
        ELSE round(p_x,v_precISion) || ‘,’ || round(p_y,v_precISion)
        END ||
        ‘]’;

        dbms_output.put_line (‘v_ret ‘ || replace(v_ret, ‘,’,’,’));
        RETURN replace(v_ret, ‘,’,’,’);
        END formatCoord;

        Thank you so much…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s