Any interest in supporting LRS measures? about oracle_fdw HOT 15 CLOSED

Can you explain in more detail what you would like to see, maybe with an example?

I don't know a lot about spatial data, maybe @vmora can comment on that.

from oracle_fdw.

Hi @pauldzy,

I'd like to know how you would translate the LRS translates into posgis geometries.

As far as I understand, LRS is one linestring with several points, or line segments that are defined by curvilinear coordinates on this linestring. To my knowledge, there is no such structure in postgis. There are linear referencing functions, but not an equivalent type (I also don't remember seeing one in OGC SFS).

@vpicavet: your opinion ?

from oracle_fdw.

What is called LRS, is in PostGIS the M coordinate : an additional 4th dimension, which can be anything, time, a specific measure, whatever.
The PostGIS geometry type includes subtypes with M coordinates. e.g. PointM, PointZM, LineStringZM...

Oracle may have special types and ways of dealing with this though.

from oracle_fdw.

Hi folks,

LRS stands for Linear Referencing System and is generally the industry term used in English:
http://postgis.net/docs/reference.html#Linear_Referencing
http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_lrs_concepts.htm
The exception is Esri who sometime refers to it as "Dynamic Segmentation".

If oracle_fdw is only implementing the old Simple Features 1.1.0 spec from 2005 then yes you would not support or acknowledge LRS measures. I did not see that written anywhere though.

However, if you wanted to support something newer, like the current 1.2.1 specification from 2011, then yes LRS measures are part of that.
http://portal.opengeospatial.org/files/?artifact_id=25355

Both Oracle Spatial and PostGIS fully support the storage and utilization of LRS measures. For all intents and purposes they are just 3D geometries marked to be LRS. So an Oracle LRS point would be SDO_GEOMETRY(3301,NULL,SDO_POINT_TYPE(11,22,33),NULL,NULL) where 33 is the measure. For PostGIS, the wkt text is POINTM(11 22 33).

So actually the current support for 3D in oracle_fdw means that the hard work of passing coordinates back and forth is already done. Rather all that is needed is to hand across the gtype correctly. I looked a the current code and see how the gtype is divided to get the dimension and geometry type and this doesn't work when you need the second digit of the gtype to indicate LRS.

Even if its not on your road map maybe you could alter some of the gtype handling such that it would be easier for others to take a stab at forking.

Thanks,
Paul

from oracle_fdw.

After reading up a bit on that, it seems that the second digit of SDO_GTYPE determines which dimension (3 or 4) is the measure dimension. Since oracle_fdw currently only supports two- or three-dimensional geometries, the only interesting value would be 3, right?

Interestingly, the Oracle documentation says:

For a non-LRS geometry, or to accept the Spatial default of the last dimension as the measure for an LRS geometry, specify 0.

That makes me wonder: How do you distinguish a POINT Z (1 2 3) and a POINT M (1 2 3) in Oracle if the second digit is 0?

At any rate, if I get you right, then what you want is that if the secon digit of SDO_GTYPE is 3, the measurement flag is set to "on" in the resulting PostGIS geometry.

That should be possible to do. Is that what you are looking for?

from oracle_fdw.

Hi Laurenz,

Oracle spatial allows three possibilities of XYM, XYZM or XYMZ - creating linear gtypes of 3302, 4402 or 4302. However, there is some consensus that the last option is not worth the bother. Esri's ArcSDE does not support XYMZ and never has. So really the measure should always be in the last position. The non-zero value of the second digit ideally just signals to users that the geometry is LRS.

The documentation you quoted is interesting as that blurb dates back to the early days of spatial when LRS was just implied in the object. For example, the old ArcSDE binary tools would always load LRS data with the second gtype digit being zero. SDE had other ways of tracking that the geometry was LRS. In the database itself third or fourth dimensions were pretty much just ignored. However with 3D data now so much more utilized its become more important to tell the difference between LRS and 3D data. (I kind of think it always was myself)

So exactly what you say is correct in my humble opinion. If you see a 3 in the second gtype position, then transfer it as the "M" version of the geometry and vise-versa. The main thing is you'd have to do that divide the gtype by 1000 thing a bit differently.

Thanks!
Paul

from oracle_fdw.

So now we know the requirements; it seems pretty straightforward.

@vmora, @vpicavet, what is your opinion on that?
I could try to implement that, but I don't know how the measurement flag is set in PostGIS. I can read the source, but you could probably save me the effort.

Or do you want to implement it?

from oracle_fdw.

In ewkbHeaderFill (oracle_gis.c:728) we set the flag for 3D:

const ub1 flags = ((3 == ewkbDimension(session, geom)) ? 0x01 : 0x00 );

The M (measured) flag is 0x02
(the flags are defines in postgis source in liblwgeom/liblwgeom.h:122

If we want to support EITHER 3d or measured, we can replace oracle_gis.c:728 by:

const ub1 flags = (ewkbIsMesured(session, geom) ? 0x02 : (ewkbIs3d(session, geom) ? 0x01 : 0x00 ));

And the reverse operation in setSridAndFlags (oracle_gis.c:788)

gtype = (((ub1)data[0]) & 0x01 ) ? 3000 : 2000; /* 3d/2d */

As far as I understand, the ewkbDimension has also to be modified to return 3 in either case because it's used throughout the code to detect if a third coord must be read. If we want to support ZM coord, we ought to handle the case where ewkbDimension will return 4.

from oracle_fdw.

Hi all,

My reaction is that if the initial poster can get some funding to
solve this issue, that would be the best solution, either for Laurenz or
Vincent to implement it.
This is not a bug but a feature request and Free Software is free as in
speech, not as in beer.

Outside of a contract for this, we would have little time to dedicate to
this development. Laurenz, should you want to dive into it, Vincent
could give you tips and answer your questions though.

Vincent

On 17/08/2015 10:17, Vincent Mora wrote:

In ewkbHeaderFill (oracle_gis.c:728) we set the flag for 3D:

|const ub1 flags = ((3 == ewkbDimension(session, geom)) ? 0x01 : 0x00
); |

The M (measured) flag is 0x02 (the flags are defines in postgis
source in liblwgeom/liblwgeom.h:122

If we want to support EITHER 3d or measured, we can replace
oracle_gis.c:728 by:

|const ub1 flags = (ewkbIsMesured(session, geom) ? 0x02 :
(ewkbIs3d(session, geom) ? 0x01 : 0x00 )); |

And the reverse operation in setSridAndFlags (oracle_gis.c:788)

|gtype = (((ub1)data[0]) & 0x01 ) ? 3000 : 2000; /* 3d/2d */ |

As far as I understand, the ewkbDimension has also to be modified to
return 3 in either case because it's used throughout the code to
detect if a third coord must be read. If we want to support ZM coord,
we ought to handle the case where ewkbDimension will return 4.

— Reply to this email directly or view it on GitHub
#36 (comment).

from oracle_fdw.

Hello,

Hey thanks so much for considering it. I certainly am not out to pile work upon you all, I understand open source. If anything just perhaps being aware of the LRS flag on the gtype and coding things such as that someone could more easily do the work sometime down the road would be swell.

Cheers,
Paul

from oracle_fdw.

No offense taken Paul do not worry. Reporting issues and info is contribution already :)
We at least keep this issue on our wishlist in case we find time and/or funding.

from oracle_fdw.

I tentatively hacked on it a little yesterday, but it would require more code churn than I originally thought.
Oracle considers measured geometries three-dimensional (the measure being the third dimension), while PostGIS considers them two-dimensional, so any code change would have to account for that.

What would be very easy is to translate measured Oracle geometries into normal three-dimensional PostGIS geometries, such that the measure becomes the Z coordinate.

I'll close the issue for now; we understand what would be required, but it would take more than a quick hack.

from oracle_fdw.

Hi @laurenz, I prefer to keep the issue open with the enhancement tag, is there a reason you prefer to close it ?

from oracle_fdw.

You are right, that is better.
This way other people who are interested in the same enhancement have a better chance of seeing it.

from oracle_fdw.

I think I can close the issue now.

from oracle_fdw.