[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] ST_FromBinary not expected
From: Bjornar Skinnes <bjornar_skinnes () trimble ! com>
Date: 2020-08-31 18:41:09
Message-ID: CAAqLGNoUzqC+Uizx4cjxcFqk7MfHPWNebjiO1ekuoGg9hEUxMQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
thank you :) Basically means I'll stick with the WKT reading from the
oracle if I need something at least close to std ;)
man. 31. aug. 2020 kl. 18:44 skrev Paul Ramsey <pramsey@cleverelephant.ca>:
> Oracle uses non-standary WKB type numbers for curves.
>
> https://docs.oracle.com/cd/B19306_01/appdev.102/b14373/constant-values.html
>
> Instead of the preamble looking liks this (hex WKB)
>
> 00 - big endian
> 00000009 - compound curve
> 00000007 - 7 elements
> 00 - big endian
> 00000002 - linestring
> 00000002 - two vertices
> ...etc...
>
> it looks like this
>
> 00 - big endian
> 000f4242 - 1000002, the oracle number for compound curve
> 00000007 - 7 elements
> - wait, missing the endian flag, so not propertly recursive???
> 00000002 - linestring
> 00000002 - two vertices
> ...etc...
>
> So the Oracle thing is just radically non-standard.
> I'm concerned that PostGIS doesn't yell loudly when fed that content
> though, since the type number is so very wrong.
>
> P
>
> > On Aug 31, 2020, at 6:32 AM, Bjornar Skinnes <
> bjornar_skinnes@trimble.com> wrote:
> >
> > ORACLE:
> >
> > SELECT
> > SDO_UTIL.TO_WKTGEOMETRY(
> > SDO_UTIL.FROM_WKBGEOMETRY( utl_encode.base64_decode( utl_raw.cast_to_raw(
> > 'AAAPQkIAAAAHAAAAAgAAAAJAuSqxaGHpKUDWw+D1vl2eQLoaUGI9C/pA1u9vKwIM
> > SgAPQkEAAAADQLoaUGI9C/pA1u9vKwIMSkC6HrsUzOUkQNbwSP7Se+xAuiLzdKuj
> > h0DW8TI9bHIZAAAAAgAAAAJAuiLzdKujh0DW8TI9bHIZQLpXJeNDMNdA1v0dwo9c
> > KQAPQkEAAAAnQLpXJeNDMNdA1v0dwo9cKUC6WybbyQ4ZQNb+BKY8A89Aul8c7YCh
> > e0DW/u6HJtBOQLpk+rFrJEpA1wBUU5HjCEC6arpeJHiFQNcBwdsenydAunBb6JKT
> > JUDXAzhOLRnaQLp12RZ2ZApA1wS3S8an8EC6ezEgsDl7QNcGP+biTj1AuoBd86V+
> > qkDXB9Gp++dtQLqA3EPQVPFA1wf5K2Vt2UC6gVocm0E6QNcIIMSXdCVAuoYEjAQb
> > VEDXCY3zcGUYQLqKhmZVn29A1wsDMy8BdUC6kpqGPzW/QNcN3SxRP81AupoMCCBf
> > 8kDXENJN0vGqQLqeHJ9RoWBA1xKaMibdEUC6ofU/bCaaQNcUacq8UVVAuqWXUucS
> > MEDXFj+9C9L0QLqpBeNDMNdA1xgb1wo9cUC6rEJbEIK0QNcZ/MAJx1JAuq9QYj0L
> > +kDXG+J++dsjQLqxV0BKwU1A1x03hf6ZjEC6s0o9X9zfQNcejmZiNKhAurZqQ8g0
> > 40DXIRcKU1/9QLq5Tpd8iOhA1yOkGI9C/0C6u1/Fd+ZDQNclmlZmwjRAur1T974S
> > H0DXJ5Jul41QQLq/LNcIRnVA1ymKdTRLU0C6wO8ajvd/QNcrg8an755AusKcb6yn
> > OkDXLXyaShfnQLrEOZmI0qJA1y92RZ2ZA0C6xciwGLncQNcxbv7SdDFAusdOFGoa
> > UEDXM2gxJul5QLrIy9pTlG5A1zVgHa8r1EC6ykZmVZ9vQNc3WDEm6XlAusq1W5sG
> > g0DXN+yNARO0QLrLJJuVHFxA1ziA5VwPy0C6zcSQv+MyQNc6x73DdrJAutIQYj0L
> > +kDXPORqesgdAAAAAgAAAAJAutIQYj0L+kDXPORqesgdQLrfhqfe1rtA10IyXjU/
> > fQAPQkEAAAAFQLrfhqfe1rtA10IyXjU/fUC66RUfs2M1QNdHOKu8CEJAuuj4k2P1
> > c0DXTMj1vl2eQLrnxFDOkblA107FGgOkQ0C659N0q6OHQNdQxwo9cKQAAAACAAAA
> > A0C659N0q6OHQNdQxwo9cKRAuvscKOT7mEDXW+2yKNyYQLsk07Y02tNA12xBJuVH
> > Fw=='
> > )))) FROM DUAL;
> >
> > Give as expected:
> > COMPOUNDCURVE (LINESTRING (6442.692999 23311.514999, 6682.313999
> 23485.737), CIRCULARSTRING (6682.313999 23485.737, 6686.73078613848
> 23489.140553113, 6690.950999 23492.784999), LINESTRING (6690.950999
> 23492.784999, 6743.147999 23540.465), CIRCULARSTRING (6743.147999
> 23540.465, 6747.15179115862 23544.0726461446, 6751.112999 23547.726999,
> 6756.97926969181 23553.3176007001, 6762.727999 23559.028999,
> 6768.3590175256 23564.8797714951, 6773.847999 23570.864, 6779.19190503505
> 23576.9984670414, 6784.366999 23583.276, 6784.86040975641 23583.8932736943,
> 6785.351999 23584.511999, 6790.01776147525 23590.21798334, 6794.524999
> 23596.049999, 6802.60361094534 23607.4558299182, 6810.046999 23619.286,
> 6814.11180601301 23626.4093110236, 6817.957999 23633.652999,
> 6821.59110874361 23640.9959134636, 6825.022999 23648.435, 6828.25920203391
> 23655.9492210814, 6831.313999 23663.539, 6833.34082476825 23668.8675533771,
> 6835.289999 23674.224999, 6838.41509677211 23684.3600052297, 6841.306999
> 23694.563999, 6843.37410687801 23702.411523523, 6845.327999 23710.288,
> 6847.17515613289 23718.1634035812, 6848.933999 23726.059, 6850.61107901652
> 23733.9469170793, 6852.224999 23741.847999, 6853.78393702066
> 23749.7343031058, 6855.304999 23757.628, 6856.79630014776 23765.5018117836,
> 6858.274999 23773.378, 6858.70842904004 23775.6961062138, 6859.142999
> 23778.013999, 6861.76783370301 23787.1209572467, 6866.063999 23795.568999),
> LINESTRING (6866.063999 23795.568999, 6879.525999 23816.787),
> CIRCULARSTRING (6879.525999 23816.787, 6889.08251496479 23836.8854818421,
> 6888.970999 23859.139999, 6887.76685801561 23867.0797127823, 6887.825999
> 23875.11), LINESTRING (6887.825999 23875.11, 6907.109999 23919.713999,
> 6948.826999 23985.017999))
> >
> > In postgres / postgis however:
> > Select
> ST_AsText(ST_GeomFromEWKB(ST_AsBinary(ST_GeomFromEWKB(decode('AAAPQkIAAAAHAAAAAgAAAAJAuSqxaGHpKUDWw+D1vl2eQLoaUGI9C/pA1u9vKwIM
>
> > SgAPQkEAAAADQLoaUGI9C/pA1u9vKwIMSkC6HrsUzOUkQNbwSP7Se+xAuiLzdKuj
> > h0DW8TI9bHIZAAAAAgAAAAJAuiLzdKujh0DW8TI9bHIZQLpXJeNDMNdA1v0dwo9c
> > KQAPQkEAAAAnQLpXJeNDMNdA1v0dwo9cKUC6WybbyQ4ZQNb+BKY8A89Aul8c7YCh
> > e0DW/u6HJtBOQLpk+rFrJEpA1wBUU5HjCEC6arpeJHiFQNcBwdsenydAunBb6JKT
> > JUDXAzhOLRnaQLp12RZ2ZApA1wS3S8an8EC6ezEgsDl7QNcGP+biTj1AuoBd86V+
> > qkDXB9Gp++dtQLqA3EPQVPFA1wf5K2Vt2UC6gVocm0E6QNcIIMSXdCVAuoYEjAQb
> > VEDXCY3zcGUYQLqKhmZVn29A1wsDMy8BdUC6kpqGPzW/QNcN3SxRP81AupoMCCBf
> > 8kDXENJN0vGqQLqeHJ9RoWBA1xKaMibdEUC6ofU/bCaaQNcUacq8UVVAuqWXUucS
> > MEDXFj+9C9L0QLqpBeNDMNdA1xgb1wo9cUC6rEJbEIK0QNcZ/MAJx1JAuq9QYj0L
> > +kDXG+J++dsjQLqxV0BKwU1A1x03hf6ZjEC6s0o9X9zfQNcejmZiNKhAurZqQ8g0
> > 40DXIRcKU1/9QLq5Tpd8iOhA1yOkGI9C/0C6u1/Fd+ZDQNclmlZmwjRAur1T974S
> > H0DXJ5Jul41QQLq/LNcIRnVA1ymKdTRLU0C6wO8ajvd/QNcrg8an755AusKcb6yn
> > OkDXLXyaShfnQLrEOZmI0qJA1y92RZ2ZA0C6xciwGLncQNcxbv7SdDFAusdOFGoa
> > UEDXM2gxJul5QLrIy9pTlG5A1zVgHa8r1EC6ykZmVZ9vQNc3WDEm6XlAusq1W5sG
> > g0DXN+yNARO0QLrLJJuVHFxA1ziA5VwPy0C6zcSQv+MyQNc6x73DdrJAutIQYj0L
> > +kDXPORqesgdAAAAAgAAAAJAutIQYj0L+kDXPORqesgdQLrfhqfe1rtA10IyXjU/
> > fQAPQkEAAAAFQLrfhqfe1rtA10IyXjU/fUC66RUfs2M1QNdHOKu8CEJAuuj4k2P1
> > c0DXTMj1vl2eQLrnxFDOkblA107FGgOkQ0C659N0q6OHQNdQxwo9cKQAAAACAAAA
> > A0C659N0q6OHQNdQxwo9cKRAuvscKOT7mEDXW+2yKNyYQLsk07Y02tNA12xBJuVH
> > Fw==', 'base64')))));
> >
> > Gives something weird:
> > LINESTRING(0 6442.692999,23311.514999 6682.313999,23485.737
> 0,6682.313999 23485.737,6686.73078613848 23489.140553113,6690.950999
> 23492.784999,0 6690.950999)
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<div dir="ltr">thank you :) Basically means I'll stick with the WKT reading \
from the oracle if I need something at least close to std ;)</div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">man. 31. aug. 2020 kl. 18:44 \
skrev Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" \
target="_blank">pramsey@cleverelephant.ca</a>>:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex">Oracle uses non-standary WKB type numbers for \
curves. <br> <br>
<a href="https://docs.oracle.com/cd/B19306_01/appdev.102/b14373/constant-values.html" \
rel="noreferrer" target="_blank">https://docs.oracle.com/cd/B19306_01/appdev.102/b14373/constant-values.html</a><br>
<br>
Instead of the preamble looking liks this (hex WKB)<br>
<br>
00 - big endian<br>
00000009 - compound curve<br>
00000007 - 7 elements<br>
00 - big endian<br>
00000002 - linestring<br>
00000002 - two vertices<br>
...etc...<br>
<br>
it looks like this<br>
<br>
00 - big endian<br>
000f4242 - 1000002, the oracle number for compound curve<br>
00000007 - 7 elements<br>
- wait, missing the endian flag, so not propertly recursive???<br>
00000002 - linestring<br>
00000002 - two vertices<br>
...etc...<br>
<br>
So the Oracle thing is just radically non-standard.<br>
I'm concerned that PostGIS doesn't yell loudly when fed that content though, \
since the type number is so very wrong.<br> <br>
P<br>
<br>
> On Aug 31, 2020, at 6:32 AM, Bjornar Skinnes <<a \
href="mailto:bjornar_skinnes@trimble.com" \
target="_blank">bjornar_skinnes@trimble.com</a>> wrote:<br> > <br>
> ORACLE:<br>
> <br>
> SELECT<br>
> SDO_UTIL.TO_WKTGEOMETRY(<br>
> SDO_UTIL.FROM_WKBGEOMETRY( utl_encode.base64_decode( utl_raw.cast_to_raw(<br>
> 'AAAPQkIAAAAHAAAAAgAAAAJAuSqxaGHpKUDWw+D1vl2eQLoaUGI9C/pA1u9vKwIM<br>
> SgAPQkEAAAADQLoaUGI9C/pA1u9vKwIMSkC6HrsUzOUkQNbwSP7Se+xAuiLzdKuj<br>
> h0DW8TI9bHIZAAAAAgAAAAJAuiLzdKujh0DW8TI9bHIZQLpXJeNDMNdA1v0dwo9c<br>
> KQAPQkEAAAAnQLpXJeNDMNdA1v0dwo9cKUC6WybbyQ4ZQNb+BKY8A89Aul8c7YCh<br>
> e0DW/u6HJtBOQLpk+rFrJEpA1wBUU5HjCEC6arpeJHiFQNcBwdsenydAunBb6JKT<br>
> JUDXAzhOLRnaQLp12RZ2ZApA1wS3S8an8EC6ezEgsDl7QNcGP+biTj1AuoBd86V+<br>
> qkDXB9Gp++dtQLqA3EPQVPFA1wf5K2Vt2UC6gVocm0E6QNcIIMSXdCVAuoYEjAQb<br>
> VEDXCY3zcGUYQLqKhmZVn29A1wsDMy8BdUC6kpqGPzW/QNcN3SxRP81AupoMCCBf<br>
> 8kDXENJN0vGqQLqeHJ9RoWBA1xKaMibdEUC6ofU/bCaaQNcUacq8UVVAuqWXUucS<br>
> MEDXFj+9C9L0QLqpBeNDMNdA1xgb1wo9cUC6rEJbEIK0QNcZ/MAJx1JAuq9QYj0L<br>
> +kDXG+J++dsjQLqxV0BKwU1A1x03hf6ZjEC6s0o9X9zfQNcejmZiNKhAurZqQ8g0<br>
> 40DXIRcKU1/9QLq5Tpd8iOhA1yOkGI9C/0C6u1/Fd+ZDQNclmlZmwjRAur1T974S<br>
> H0DXJ5Jul41QQLq/LNcIRnVA1ymKdTRLU0C6wO8ajvd/QNcrg8an755AusKcb6yn<br>
> OkDXLXyaShfnQLrEOZmI0qJA1y92RZ2ZA0C6xciwGLncQNcxbv7SdDFAusdOFGoa<br>
> UEDXM2gxJul5QLrIy9pTlG5A1zVgHa8r1EC6ykZmVZ9vQNc3WDEm6XlAusq1W5sG<br>
> g0DXN+yNARO0QLrLJJuVHFxA1ziA5VwPy0C6zcSQv+MyQNc6x73DdrJAutIQYj0L<br>
> +kDXPORqesgdAAAAAgAAAAJAutIQYj0L+kDXPORqesgdQLrfhqfe1rtA10IyXjU/<br>
> fQAPQkEAAAAFQLrfhqfe1rtA10IyXjU/fUC66RUfs2M1QNdHOKu8CEJAuuj4k2P1<br>
> c0DXTMj1vl2eQLrnxFDOkblA107FGgOkQ0C659N0q6OHQNdQxwo9cKQAAAACAAAA<br>
> A0C659N0q6OHQNdQxwo9cKRAuvscKOT7mEDXW+2yKNyYQLsk07Y02tNA12xBJuVH<br>
> Fw=='<br>
> )))) FROM DUAL;<br>
> <br>
> Give as expected:<br>
> COMPOUNDCURVE (LINESTRING (6442.692999 23311.514999, 6682.313999 23485.737), \
CIRCULARSTRING (6682.313999 23485.737, 6686.73078613848 23489.140553113, 6690.950999 \
23492.784999), LINESTRING (6690.950999 23492.784999, 6743.147999 23540.465), \
CIRCULARSTRING (6743.147999 23540.465, 6747.15179115862 23544.0726461446, 6751.112999 \
23547.726999, 6756.97926969181 23553.3176007001, 6762.727999 23559.028999, \
6768.3590175256 23564.8797714951, 6773.847999 23570.864, 6779.19190503505 \
23576.9984670414, 6784.366999 23583.276, 6784.86040975641 23583.8932736943, \
6785.351999 23584.511999, 6790.01776147525 23590.21798334, 6794.524999 23596.049999, \
6802.60361094534 23607.4558299182, 6810.046999 23619.286, 6814.11180601301 \
23626.4093110236, 6817.957999 23633.652999, 6821.59110874361 23640.9959134636, \
6825.022999 23648.435, 6828.25920203391 23655.9492210814, 6831.313999 23663.539, \
6833.34082476825 23668.8675533771, 6835.289999 23674.224999, 6838.41509677211 \
23684.3600052297, 6841.306999 23694.563999, 6843.37410687801 23702.411523523, \
6845.327999 23710.288, 6847.17515613289 23718.1634035812, 6848.933999 23726.059, \
6850.61107901652 23733.9469170793, 6852.224999 23741.847999, 6853.78393702066 \
23749.7343031058, 6855.304999 23757.628, 6856.79630014776 23765.5018117836, \
6858.274999 23773.378, 6858.70842904004 23775.6961062138, 6859.142999 23778.013999, \
6861.76783370301 23787.1209572467, 6866.063999 23795.568999), LINESTRING (6866.063999 \
23795.568999, 6879.525999 23816.787), CIRCULARSTRING (6879.525999 23816.787, \
6889.08251496479 23836.8854818421, 6888.970999 23859.139999, 6887.76685801561 \
23867.0797127823, 6887.825999 23875.11), LINESTRING (6887.825999 23875.11, \
6907.109999 23919.713999, 6948.826999 23985.017999))<br> > <br>
> In postgres / postgis however:<br>
> Select ST_AsText(ST_GeomFromEWKB(ST_AsBinary(ST_GeomFromEWKB(decode('AAAPQkIAAAAHAAAAAgAAAAJAuSqxaGHpKUDWw+D1vl2eQLoaUGI9C/pA1u9vKwIM<br>
> SgAPQkEAAAADQLoaUGI9C/pA1u9vKwIMSkC6HrsUzOUkQNbwSP7Se+xAuiLzdKuj<br>
> h0DW8TI9bHIZAAAAAgAAAAJAuiLzdKujh0DW8TI9bHIZQLpXJeNDMNdA1v0dwo9c<br>
> KQAPQkEAAAAnQLpXJeNDMNdA1v0dwo9cKUC6WybbyQ4ZQNb+BKY8A89Aul8c7YCh<br>
> e0DW/u6HJtBOQLpk+rFrJEpA1wBUU5HjCEC6arpeJHiFQNcBwdsenydAunBb6JKT<br>
> JUDXAzhOLRnaQLp12RZ2ZApA1wS3S8an8EC6ezEgsDl7QNcGP+biTj1AuoBd86V+<br>
> qkDXB9Gp++dtQLqA3EPQVPFA1wf5K2Vt2UC6gVocm0E6QNcIIMSXdCVAuoYEjAQb<br>
> VEDXCY3zcGUYQLqKhmZVn29A1wsDMy8BdUC6kpqGPzW/QNcN3SxRP81AupoMCCBf<br>
> 8kDXENJN0vGqQLqeHJ9RoWBA1xKaMibdEUC6ofU/bCaaQNcUacq8UVVAuqWXUucS<br>
> MEDXFj+9C9L0QLqpBeNDMNdA1xgb1wo9cUC6rEJbEIK0QNcZ/MAJx1JAuq9QYj0L<br>
> +kDXG+J++dsjQLqxV0BKwU1A1x03hf6ZjEC6s0o9X9zfQNcejmZiNKhAurZqQ8g0<br>
> 40DXIRcKU1/9QLq5Tpd8iOhA1yOkGI9C/0C6u1/Fd+ZDQNclmlZmwjRAur1T974S<br>
> H0DXJ5Jul41QQLq/LNcIRnVA1ymKdTRLU0C6wO8ajvd/QNcrg8an755AusKcb6yn<br>
> OkDXLXyaShfnQLrEOZmI0qJA1y92RZ2ZA0C6xciwGLncQNcxbv7SdDFAusdOFGoa<br>
> UEDXM2gxJul5QLrIy9pTlG5A1zVgHa8r1EC6ykZmVZ9vQNc3WDEm6XlAusq1W5sG<br>
> g0DXN+yNARO0QLrLJJuVHFxA1ziA5VwPy0C6zcSQv+MyQNc6x73DdrJAutIQYj0L<br>
> +kDXPORqesgdAAAAAgAAAAJAutIQYj0L+kDXPORqesgdQLrfhqfe1rtA10IyXjU/<br>
> fQAPQkEAAAAFQLrfhqfe1rtA10IyXjU/fUC66RUfs2M1QNdHOKu8CEJAuuj4k2P1<br>
> c0DXTMj1vl2eQLrnxFDOkblA107FGgOkQ0C659N0q6OHQNdQxwo9cKQAAAACAAAA<br>
> A0C659N0q6OHQNdQxwo9cKRAuvscKOT7mEDXW+2yKNyYQLsk07Y02tNA12xBJuVH<br>
> Fw==', 'base64')))));<br>
> <br>
> Gives something weird:<br>
> LINESTRING(0 6442.692999,23311.514999 6682.313999,23485.737 0,6682.313999 \
23485.737,6686.73078613848 23489.140553113,6690.950999 23492.784999,0 \
6690.950999)<br> > <br>
> <br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> > <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br> <br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic