Display oracle spatial data on a leaflet map

I wrote previously about how to convert oracle spatial data into GeoJSON. In this post I will take this  a step further and display that GeoJSONdata onto a leaflet map. A note before that though: The main sdo2geojson function return every record on the spatial table as a valid GeoJSON. That’s great, but not enough if you want to display the WHOLE table as a GeoJSON Feature collection. So I created a new sdo2geojson_partial function which will return an…erm… partial (and therefore not valid) GeoJSON strings which I could then concatenate through code to a valid feature collection object. You can download the updated oracle package here

The end result of this exercise would be to create a simple web site with a leaflet map that will display an oracle spatial layer at the time of load.

So I created a simple ASP.NET web site to demonstrate this. The main.aspx page is shown below.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />

    <!-- Bootstrap -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/css/bootstrap.min.css" />
    <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7.1/leaflet.css" />

    <style type="text/css">
        #map {
            height: 600px;
            width: 100%;

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.1/js/bootstrap.min.js"></script>
    <script type="text/javascript" src="http://cdn.leafletjs.com/leaflet-0.7.1/leaflet.js"></script>
    <script src="scripts/map_functions.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            map_props = init_map(40.587914266, 22.953796219, 13);
            map = map_props[0];
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-6">
                    <div class="panel panel-default">
                        <div class="panel-heading">Map</div>
                        <div class="panel-body">
                            <div id="map"></div>
                <div class="col-md-6">
                    <div class="panel panel-default">
                        <div class="panel-heading">Another panel</div>
                        <div class="panel-body">

The bulk of the above markup has to do with the layout of the page using bootstrap and the setting of the leaflet map. I won’t go into any details about them, you can find out how to create the maps through their excellent tutorials or creating web sites using bootstrap in w3schools

Notice however the link to scripts/map_functions.js. This is the bit of javascript that makes a jQuery AJAX call, to a web method which in turns call the oracle function to create the GeoJSON objects.

function init_map(lon, lat, zoomlevel) {
    map = L.map('map').setView([lon, lat], zoomlevel);
    var mapbox = L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
        attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors',
        id: 'examples.map-i875mjb7'

    var url = "../Services/GetGeoJsonData.asmx/getJsonFromOra";

        type: "POST",
        url: url,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        data: "{'table_name':'v_sdo_ksim_wgs84'}",
        success: function (geodata) {
            var obj = jQuery.parseJSON(geodata.d);
            var smallIcon = L.Icon.extend({
                options: {
                    iconSize: [29, 25],
                    iconUrl: '../img/sign1.png'
            geojson = L.geoJson(obj, {
                pointToLayer: function (feature, latlng) {
                    return L.marker(latlng, { icon: new smallIcon({ iconUrl: '../img/sign.png' }) });
                onEachFeature: onEachFeature
        error: function (xmlHttpRequest, textStatus, errorThrown) {

    return [map];
function onEachFeature(feature, layer) {
    layer.bindPopup('<a href="#" class="speciallink">' + feature.properties.EVA_EVENT_DESCRIPTION) + '</a.';
function zoomToFeature(e) {

In a nutshell, the script calls through GetGeoJsonData.asmx web service, the getJsonFromOra method method (line 8) using a spatial table name as a parameter called v_sdo_ksim_wgs84 (line 15). In turn the getJsonFromOra method calls the oracle function using a spatial table name as a parameter and returns a valid GeoJSON feature collection string.

    [ScriptMethod(UseHttpGet = false, ResponseFormat = ResponseFormat.Json)]
    public string getJsonFromOra(string table_name)
        string json = string.Empty;
        string connectionString = "User Id=user;Password=pwd;Data Source=xe";
        string jprefix = "{\"type\": \"FeatureCollection\",  \"features\": [";
        OracleConnection oraCon = new OracleConnection();
        oraCon.ConnectionString = connectionString;
        string select = "select ora2geojson.sdo2geojson_partial('select *  from " + table_name + "',ROWID, shape) FROM " + table_name;
        OracleCommand oraCommand = new OracleCommand(select, oraCon);
            OracleDataReader Orareader = oraCommand.ExecuteReader();

            while (Orareader.Read())
                json = json + "{" + Orareader[0].ToString() + ",";
            json = json.TrimEnd(',');
            return jprefix + json + "]}";
        catch (Exception)

The end results is a map like the one below. The traffic signs you see are oracle spatial features.


You can download the whole solution here. Remember to change the spatial table name to suit your settings.

Happy coding!


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.





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:



Keeping Oracle and ESRI spatial metadata (MBR) in sync

This is a little script which comes very useful when the extents of your Oracle spatial layer are changed. It calculates the MBR and  uses these values to update the ArcSDE metadata (SDE.LAYERS and SDE.SPATIAL_REFERENCES):

UPDATE: Amend script to update the USER_SDO_GEOM_METADATA and use a cursor.

 mbr mdsys.sdo_geometry;
 cursor cs1 is
  select table_name, column_name
  from user_sdo_geom_metadata
  where table_name like '%COLA%'; 
 -- Number of dimensions
 idim integer;
   FOR each_rec in cs1
      -- Get the extent
      SELECT count(*) into idim
       FROM user_sdo_geom_metadata usgm,
              TABLE(usgm.diminfo) dim
        WHERE table_name = each_rec.table_name;
       IF idim = 2 THEN
         -- Update diminfo
         UPDATE user_sdo_geom_metadata
         SET diminfo= SDO_DIM_ARRAY(   -- 20X20 grid
              SDO_DIM_ELEMENT('X', mbr.sdo_ordinates(1), mbr.sdo_ordinates(3), 0.005),
              SDO_DIM_ELEMENT('Y', mbr.sdo_ordinates(2), mbr.sdo_ordinates(4), 0.005))
              WHERE table_name= each_rec.table_name;
        ELSE -- assume 3 dims, 3rd is the M. Default to 0, 999999999, 0.005
          UPDATE user_sdo_geom_metadata
          SET diminfo= SDO_DIM_ARRAY(   -- 20X20 grid
               SDO_DIM_ELEMENT('X', mbr.sdo_ordinates(1), mbr.sdo_ordinates(3), 0.005),
               SDO_DIM_ELEMENT('Y', mbr.sdo_ordinates(2), mbr.sdo_ordinates(4), 0.005),
               SDO_DIM_ELEMENT('M', 0, 999999999, 0.005))
           WHERE table_name= each_rec.table_name;
         END IF;
       -- Update SDE.LAYERS
         update sde.layers
         set minx=mbr.sdo_ordinates(1),
         where table_name=tabName;
       -- Update SDE.Spatial References
         update sde.spatial_references
         set falsex=mbr.sdo_ordinates(1),
          where srid in (
           (select srid from sde.layers
            where table_name=tabName));

Retrieve the start and end points of a polyline in Oracle

Strangely enough no such function exists (or at least I couldn’t find it).. PostGIS has ST_StartPoint and ST_EndPoint, SQL Server 2008 Spatial has STStartPoint and STEndPoint but not Oracle. It does however has SDO_LRS.GEOM_SEGMENT_START_PT and SDO_LRS.GEOM_SEGMENT_END_PT but this applies (it seems) only for measured polylines – and you need to have the full Oracle Spatial installed. With versions that only have Locator you are out of luck.

So putting my thrifty hat again, I put together 2 simple functions (with the very imaginative names get_start_point and get_end_point) that do just that and included them in the package. Functions should work with both 2D and 3D data. You can download the update “thrifty” package here (or from the box widget on the right).

Shp2ora and Ora2shp: Utilities for importing and exporting shapefiles to Oracle

Yes, another one of those. Its not exactly a novel idea as there are a few converters around for moving shapefile data to and from Oracle:

  • If you are an ESRI/ArcSDE user you have the sde2shp and shp2sde commands.
  • Oracle provides two options: a command line program, shp2sdo to load shapefiles into Oracle, and its java equivalent through Mapbuilder
  • Most (all?) GIS’ that can read Oracle Spatial data, include options to save the Oracle layer to a shapefile.

Plenty of choice you probably think. Well, I was still not happy. First of all I didn’t always have ArcSDE on all machines I was working on, secondly installing Mapbuilder means you need to go through the 18.2MB download, plus it will only LOAD shapefiles into Oracle and not the other way around and thirdly, I didn’t want to go through the whole process of downloading a fully-blown desktop GIS (say QGIS) just for the sake of converting a spatial table into a shapefile. Not to mention that you have to configure the client GIS first in order to connect to Oracle BEFORE you can even display the layer – so you can then export it. Too much hassle for a relative easy task.

So I decided to create my own little command-line utilities to do just that. You can download the full source code and executables from the Box.Net widget on the left side of this post (ora2shp.rar).

After compiling the project, you will find two executables under the Debug folders of Ora2shp and shape2ora projects: Ora2shp.exe and shp2ora.exe respectively,

Ora2shp syntax is: ora2shp <username/password>@dbalias> <spatial_table_name> <PK_col> <shape_col> <shapefile> ["optional_where_clause"]

If you use the where clause you should NOT put the WHERE keyword.

Shp2orashp2ora <username/password>@dbalias> <spatial_table_name> <shape_col> <shapefile> <srid>

If you don’t specify a SRID it will default to null. If the spatial table already exists records will get appended. If it doesn’t it will get created and will also create the oracle metadata records record (USER_SDO_GEOM_METADATA table) and spatial index.

It should deal with all shapes (multipart, Z, M) APART from multipatch.

Note that you will need Oracle client 10.2 installed to compile the programs as-is. Otherwise change the reference to the Oracle client of your choice. You will know if you have the wrong Oracle client version if you get an error about Oracle.DataAccess not being the same version as Oracle Client.

When running the programs any errors should appear on the console and the full stack trace will be written to orashp_err.log file, located at the same folder as the executables.

Note that  performance is not great- especially when creating the shapefile. It took something less than 15mins to create a point shapefile of around 66K records. (around 5M of shapefiles). But feel free to improve it and I would very much appreciate to get back to me if you do!

Validating Oracle Spatial layers for ArcSDE

A ‘valid geometry’ is an interesting term. It depends on the program that performs the validation. Take for example Oracle and ArcSDE. ArcSDE has a much more rigid validation regime and hence a feature that is ‘valid’ for Oracle, may be ‘corrupt’ for ArcSDE. On top of that, ArcMap has the incredibly annoying habit of stopping the map rendering as soon as it encounters an invalid geometry. So if you are unlucky enough to have the first geometry in your layer being flagged as invalid, (regardless if the rest of your 999,999 records are valid) you will get a nice, big, empty map.

I get this problem a lot as I often work with Oracle spatial layers which are created outside the warm and fuzzy environment of ArcGIS.

So I put together some tips and tricks to try when either your layer is only half-drawn or not drawn at all. Scripts are mainly focused on measured, linear geometries but could be easily adapted for other geometry types.

The first thing to do is to validate the table using Oracle’s SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT routine. The routine will create a table which hold any invalid geometries and the specific error.

You will need to create the validation table first such as:

create table  validation_tab ( sdo_rowid ROWID, result varchar2(1000));

The run the validation:


sdo_geom.validate_layer_with_context(‘MY_SPATIAL_TABLE’, ‘SHAPE’, ‘VALIDATION_TAB’);


The figure below displays the contents of the validation_tab table. In this case we have 4 records with a ORA-13011 error- value is out of range, which probably means that these shapes are outside the layer’s extent.


In case of ORA-13556 errors (adjacent points in geometry are redundant), you can use this script to fix them (or change the cursor to join to the validation table as below:)

select sdo_rowid, shape from my_spatial_tab a, validation_tab b 
where a.rowid = b.sdo_rowid
and result like ‘13356%’;

The script will remove any duplicate vertices (or within of 0.005m of each other) on the geometries in the table.

CURSOR cs is
SELECT pk_id, shape FROM my_spatial_table;
a_geom mdsys.sdo_geometry;
FOR irec in cs loop
a_geom := SDO_UTIL.REMOVE_DUPLICATE_VERTICES(irec.shape, 0.005);
update my_spatial_table
set shape= a_geom
where pk_id = irec.pk_id;



The following script will flash out and fix any geometries where the coordinate count is less than 6 (less than 2 points basically, since we are talking about 3D shapes-X,Y,M)

i number;
_id, shape FROM my_spatial_table) LOOP
i := 1;
if (rec.shape.sdo_ordinates.count <6) then
DBMS_OUTPUT.PUT_LINE ('Invalid geometry ID: ' || rec.pk_id || ' ' || rec.shape.sdo_ordinates.count);
end if;


Another useful function is the SDO_TUNE_MIX_INFO which will tell you whether all geometries in the layer are of the same type:

SQL> set serverout on;


This will return something like:

Total number of geometries: 24294
Point geometries:        0  (0%)
Curvestring geometries:   24294  (100%)
Polygon geometries:      0  (0%)
Complex geometries:      0  (0%)

PL/SQL procedure successfully completed.

And for more fine tuning on the geometry types, use this select statement to find out whether all geometries are of the same SDO_GTYPE@

select a.shape.sdo_gtype, count(*) from my_spatial_table a group by a.shape.sdo_gtype

After all said and done though, the final check will always be ArcSDE’s own validation command: sdelayer –o feature_info with the –r invalid switch which would give you the definitive answer of whether ArcMap will display the layer. For more information on this command you can refer to ArcGIS Online Help.

SQL Server 2008 Spatial and Oracle Spatial comparison and cheat sheet

This is a brief comparison between SQL Server 2008 and Oracle’s spatial functions along with relevant links and simple examples. It is by no means exhaustive but it should help people when migrating.

Note that all Oracle examples and links here refer to the 10g R2 release.

UPDATED: Added link for updated version of SQLSpatialTools from Geographica.

Spatial Data

Feature Oracle 10g SQL Server 2008
Spatial Data types

Defined by implementing the SDO_GEOMETRY type:

CREATE TYPE sdo_geometry AS OBJECT (

Defines two types of spatial data depending whether they contain geographic (round-earth coordinate system) or geometric (Euclidean/flat coordinate system) data: Geometry and Geography. Both types are implemented as a .NET common language runtime (CLR) data types.

Spatial Tables Statement to create a spatial table:
  shape SDO_GEOMETRY);

Statement to create a spatial table (geometry):

CREATE TABLE sp_table 
(sp_id int IDENTITY (1,1), shape geometry);

Statement to create a spatial table (geography):

CREATE TABLE sp_table 
(sp_id int IDENTITY (1,1),shape geography);

Geometry Metadata For spatial tables to perform correctly, Oracle requires the update of the geometry metadata views. These views describe the dimensions, lower and upper bounds, and tolerance in each dimension for each spatial table and are stored in a global table owned by the MDSYS user.

The main view is USER_SDO_GEOM_METADATA which contains metadata information for all spatial tables owned by the user (schema).

Example statement to insert data into the USER_SDO_GEOM_METADATA view:

INSERT INTO user_sdo_geom_metadata
SDO_DIM_ARRAY(    SDO_DIM_ELEMENT(‘X’, 0, 20, 0.005), 
SDO_DIM_ELEMENT(‘Y’, 0, 20, 0.005)), NULL);

SQL Server does not have an equivalent of Oracle’s geometry metadata views
Spatial Indexes Supports R-tree and Quadtree indexing. However, Oracle states that:

the use of quadtree indexes is discouraged, and you are strongly encouraged to use R-tree indexing

For more information about Oracle’s spatial indexing click here.

Creating a spatial (R-tree) index:

CREATE INDEX sp_table_spidx   ON sp_table(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Uses B-tree indexing. For more information on how spatial indexing is handled, click here.

Creating spatial indexing syntax varies depending whether the column is of type Geometry or Geography.

Creating a spatial index on a Geography column:

CREATE SPATIAL INDEX sp_table_spidx    ON sp_table(shape);

Creating a spatial index on a Geometry column:

CREATE SPATIAL INDEX sp_table_spidx ON sp_table(shape) WITH (
BOUNDING_BOX = ( 0, 0, 500, 200 ), 

More examples can be found here

OGG Compliance Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0. Conforms to the Open Geospatial Consortium (OGC) Simple Features for SQL Specification version 1.1.0

Spatial object


More geometry examples here

(Notice that 4326 is the SRID)

1. Using the SDO_GEOMETRY type:

insert into sdo_points (sp_id, shape)
values (1, SDO_GEOMETRY(
      SDO_POINT_TYPE(40, 22, NULL),  NULL,   NULL));

2. Using the Well-Known text (WKT) syntax:


(Notice that 4326 is the SRID)

1. Using the Well-Known text (WKT) syntax (STPointFromText function):

INSERT INTO GeomTable (geom)

VALUES (geography::STPointFromText(‘POINT(40 22)’, 4326))

2. Using the geometry::Point or geography::Point syntax:

INSERT INTO GeogTable (geog) VALUES (geography::Point(40, 22, 4326))

3. Using the GML syntax (GeomFromGml function)

INSERT INTO GeomTable (geom)
     VALUES (geography::GeomFromGml(
‘<Point xmlns="
<pos>40 22</pos></Point>’
, 4326))

       Simple Lines

Using the SDO_GEOMETRY type:

    SDO_ORDINATE_ARRAY(411392.088118, 4493608.698554, 411436.431582, 4493649.698236)));

(You can also create line geometries using the WKT syntax as above)

Using the Well-Known text (WKT) syntax (STLineFromText function):

INSERT INTO GeogTable (geog)
VALUES (geography::STLineFromText(‘LINESTRING(-122.360 47.656,
-122.343 47.656 )’, 4326));

(You can also create line geometries using the WKT and GML syntax as above)

       Simple Polygons

Using the SDO_GEOMETRY type:

2003, — 2-dimensional polygon
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), — one polygon (exterior polygon ring)
MDSYS.SDO_ORDINATE_ARRAY(5,1, 8,1, 8,6, 5,7, 5,1)));

(You can also create polygon geometries using the WKT syntax as above)

Using the Well-Known text (WKT) syntax (STPolyFromText function):

INSERT INTO GeogTable (geog) VALUES (geography::STPolyFromText(‘POLYGON((-122.358 47.653,
-122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’
, 4326))

(You can also create polygon geometries using the WKT and GML syntax as above)

3D/4D support

Both DBs support 3D and 4D (X,Y,Z,M) shapes

Loader and conversion  Tools Oracle makes available for download from its website two utilities to convert shapefile to oracle spatial tables. The first one is a command line utility shp2sdo.exe,and the second a Java shapefile converter. You can find more information about these utilities here. There is a free utility called Shape2Sql available from the SharpGIS website

Spatial Functions

The table list some commonly used spatial functions.

Function Description Oracle 10g

Spatial Functions see also the
SDO_GEOM package
SQL Server 2008

All Geography functions
All Geometry functions

Retrieve the spatial extent of a given table(Minimum Bounding Rectangle (MBR)) Use the SDO_AGGR_MBR function, e.g.:

SELECT SDO_AGGR_MBR(shape) FROM cola_markets;

Does not contain a similar function in the standard install. You will have to use the GeographyUnionAggregate and GeometryEnvelopeAggregate functions available in the SQL Server Spatial Tools CRL package available from Codeplex.

select state, dbo.
GeometryEnvelopeAggregate(shape_geom) from zipcodes

Identify objects within a distance

Use the SDO_WITHIN_DISTANCE function e.g.:

SELECT c.name FROM cola_markets c WHERE SDO_WITHIN_DISTANCE(c.shape,
    SDO_ORDINATE_ARRAY(4,6, 8,8)),
  ‘distance=10’) = ‘TRUE’;

Use the STDistance (Geography) or STDistance (Geometry) e.g.:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText(‘POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))’, 0);
SET @h = geometry::STGeomFromText(‘POINT(10 10)’, 0);
SELECT @g.STDistance(@h);

Create a buffer around a spatial object Use the SDO_GEOM.SDO_BUFFER function e.g.:

SELECT c.name, SDO_GEOM.SDO_BUFFER(c.shape, m.diminfo, 1)
  FROM cola_markets c, user_sdo_geom_metadata m
  WHERE m.table_name = ‘COLA_MARKETS’  AND m.column_name = ‘SHAPE’
  AND c.name = ‘cola_a’;

Use the STBuffer (Geography) or STBuffer (geometry) e.g.:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘LINESTRING(0 0, 4 0)’, 0);
SELECT @g.STBuffer(1).ToString();

Get the length of an object Use the SDO_GEOM.SDO_LENGTH function, e.g.:

SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo)
  FROM cola_markets c, user_sdo_geom_metadata m
  WHERE m.table_name = ‘COLA_MARKETS’ AND m.column_name = ‘SHAPE’;

Use the STLength (Geography) or STLength (Geometry) e.g.:


DECLARE @g geography;
SET @g = geography::STGeomFromText(‘LINESTRING(-122.360 47.656, -122.343 47.656)’, 4326);
SELECT @g.STLength();

Get the area of a polygon   Use the STArea (Geography) or STArea (Geometry) e.g.:

DECLARE @g geography;
SET @g = geography::STGeomFromText(‘POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))’, 4326);
SELECT @g.STArea();

Check the validity of an object Use the SDO_GEOM. VALIDATE_GEOMETRY_WITH_CONTEXT function e.g.:

   FROM cola_markets c WHERE c.name = ‘cola_invalid_geom’;

Use the STIsValid (Geometry) e.g.:

DECLARE @g geometry;
SET @g = geometry::STGeomFromText(‘LINESTRING(0 0, 2 2, 1 0)’, 0);
SELECT @g.STIsValid();

The Geography data type does not –for reasons unknown to me- include a STIsValid function.

Linear Referencing Contains a large number of functions for linear referencing. Lots of examples can be found here SQL Server does not support any true Linear referencing functions. SQL Server Spatial Tools however, provides some rudimentary linear referencing functions such as LocateAlongGeog and LocateAlongGeom which will return the point at a given instance along a linear object.
UPDATED: Geographica provides an updated version of SQLSpatialTools which includes a new function to display a linear event as well (a “LocateLineAlongGeom” function but called CreateLinearReferenceFeature. More details can be found at this address: http://geographika.co.uk/linear-referencing-in-sql-server-2008