This discussion is archived
3 Replies Latest reply: Nov 12, 2013 2:35 AM by Luc Van Linden RSS

Oracle LRS and Spatial ETL

Paul Dziemiela Journeyer
Currently Being Moderated

Hi folks, 

 

This question is a little off the usual database grind but as the list has been pretty quiet of late thought I'd float it.  I've been looking at the ecosystem of open source spatial etl options and finding in general that the ability to throw about LRS geometry is pretty limited.  I was wondering if anyone might have anything to add or refute to my musings.

 

In an ideal world I would like to just export and import my LRS tables like any other spatial dataset.  The measure information is just another dimension - methinks it should not be a big deal.  To the best of the my knowledge most of the main GIS storage formats fully support LRS geometries:

 

* Oracle Spatial

* PostGIS

* ESRI Shapefiles

* ESRI File Geodatabases

* Spatialite

 

(Note I'd rather chew a mouthful of broken glass than use shapefiles for the ETL)  It seems the issue is the various ETL connections between them.  To start with,  Gdal's ogr engine seems to simply and straightforwardly dump all LRS information.  It might be possible to fake it through by redefining the M as a Z but no idea what you'd do for XYZM.  So it would seem anything using Gdal underneath is out of the question (Geokettle for example). 

 

Does anyone have anything else open source they'd recommend or use for this type of task?  Not to pick on Simon but georaptor seems to have a bug that exporting my XYM to shapefile gets forced into XYZM with the measure in the Z and the M nulled out.  Also I really would prefer a portable format that was not shapefiles.

 

On the enterprise side I have to say that ESRI seems to have this handled - measures move back and forth without any problems I have discovered from format to format.  I am rather impressed how ArcCatalog 10.2 reads and writes Spatialite so nicely - LRS and all.  As a solution it seems to have things covered.  That is the only way I know of at present to load LRS data into Spatialite.  In contrast FME 2013 seems to just drop the M values unceremoniously when I export from Oracle to Spatialite. 

 

So I would appreciate any thoughts you all might have on workflows you've used successfully.  I know that my prejudice against shapefiles is not really useful, but I like Spatialite and it seems a nifty open format for carting around data.  I really don't want all my field names truncated to 10 characters. 

 

Cheers,

Paul

  • 1. Re: Oracle LRS and Spatial ETL
    Luc Van Linden Pro
    Currently Being Moderated

    Hi Paul

     

    Interesting topic, questions and observations!

     

    I understand and agree to reasoning behind the questions and preferences on a "transportable format". !here are a couple of inputs, not all really answers but at least some experiences from my end, hoping these help.

     

    1. 1. In general, should you not already know or have read it, OGC is currently in the progress of defining the spec for a  "GeoPackage (GPKG) data container"  The OGC Seeks Comments on Candidate GeoPackage Standard | OGC(R).

    Which will be sqlite based.  At least a step in the good direction to have get to an official OGC open standard datacontainer "format".

     

    1. 2. OGR does indeed just read the coords as they come, with apparently a maximum of 3 dimensions, although without the notion of a Measure (will be treated as a Zelevation.

     

    Although it does not cover your scenario for LRS XYZM, should one have 2D LRS geometries it is possible to use OGR although with some post processing:

     

    On OGR 1.10.1

     

    OGR2OGR to_the_sqlite_file from_the_Oracle_table  -dim 3 -nlt LINESTRING25D

     

    in Spatialite, you can then perform the 3 following statements (example copied from the tutorial pages)

     

    SELECT DiscardGeometryColumn('test_geom', 'the_geom');

    Update test_geom SET the_geom = CASTTOXYM(the_geom);

    SELECT RecoverGeometryColumn('test_geom', 'the_geom', 4326, 'LINESTRING', 'XYM');

     

    In theory, have not tested this, it could be possible to perform these 3 from the OGRINFO command.

     

    Not a straightforward one, and with some additional overhead but at least a way to do this for XYM LRS.

    And indeed, for the reverse similar overhead of at least setting the sdo_gtype accordingly from 3D 3002  to LRS 3302.

     

     

    Luc

  • 2. Re: Oracle LRS and Spatial ETL
    Paul Dziemiela Journeyer
    Currently Being Moderated

    Thanks for the feedback, Luc!

     

    In reading the OGC specification I note that they are forgoing the M and Z specifications until a later version, e.g. they are "outside the scope".  So out of the box this new portable format will not support LRS.  So probably not the panacea I am rooting about for.  Also it seems kind of unclear if they are endorsing spatialite or proposing something else in sqlite.

     

    Considering the age of OGR, I kind of don't see it being rewritten just to support LRS data.  Messing about swapping M to Z and back to M just seems like a recipe for problems. 

     

    I cannot for the life of me seem to submit that small bug report to Georaptor project at

    https://sourceforge.net/p/georaptor/bugs/

    I am logged into SF but I guess the bug reports are not public?  Or perhaps things are moribund?

     

    I suppose that is about all I have to add, I would not say things look that bright for open source LRS support.  If you need ETL capabilities then ESRI might be the only way to go at the moment unless you write your own. 

     

    Cheers,

    Paul

  • 3. Re: Oracle LRS and Spatial ETL
    Luc Van Linden Pro
    Currently Being Moderated

    Hi Paul

     

    The GDAL/OGR community and developers is/are very active and vibrant to new evolutions. It could be worthwhile to check-in their forum and ask for opinons there. Might well be this is somewhere on a backlog list.

     

    Luc