Multipoint geometry issue

[Reznik070]

Hi,

When I try to insert a multipoint geometry into a foreign table, I get the following error:

ERROR: error converting SDO_GEOMETRY to geometry: unsupported etype 1 with interpretation 0 in elem_info CONTEXT: converting column "geometry" for foreign table scan of "table", row 13317 SQL state: HV000

However, I have no problems inserting into the original Postgres table.

Is this some kind of limitation of oracle_fwd?

[laurenz]

Not all geometries are supported by oracle_fdw.

Can you give me an Oracle INSERT statement to generate the geometry that causes the problem? Then I can have a closer look.

[Reznik070]

For the geometry column I'm using:

ST_SetSRID(ST_MakePoint( col3::numeric , col4::numeric ),26332)

col 3 & 4 are Easting and Northing coordinates in distance.

[laurenz]

That statement will cause a syntax error. I need a complete statement that i can run on my system to generate such a geometry.

[Reznik070]

INSERT INTO table_name (id, coordinate_no, zone, easting, northing, geometry) select nextval('created_sequence'), CV65, 311548.964, 993040.705, ST_SetSRID(ST_MakePoint( easting , northing),26332)

Unfortunately I can't provide live data due to NDA.

[laurenz]

Are you sure? That doesn't look like a multipoint geometry...

[laurenz]

Also, it doesn't look like Oracle has a function ST_SetSRID:

SQL> INSERT INTO multipoint VALUES (1, ST_SetSRID(ST_MakePoint(15, 45),26332));
INSERT INTO multipoint VALUES (1, ST_SetSRID(ST_MakePoint(15, 45),26332))
                                  *
ERROR at line 1:
ORA-00904: "ST_SETSRID": invalid identifier

[Reznik070]

Well, I have in fact used it in a previous table to insert with no problems. Either I have a geometry problem or there's a limitation to oracle_fdw. The problem is inserting into the foreign table not the main table.

[laurenz]

Hm, I don't have a problem with that geometry.

In Oracle:

CREATE TABLE points(
   id number(5) CONSTRAINT points_pkey PRIMARY KEY,
   p mdsys.sdo_geometry
) SEGMENT CREATION IMMEDIATE;

In PostgreSQL:

test=> CREATE FOREIGN TABLE points(
          id integer OPTIONS (key 'true') NOT NULL,
          p geometry(Point)
       ) SERVER oracle OPTIONS (table 'POINTS');
CREATE FOREIGN TABLE
test=> INSERT INTO points VALUES (1, ST_SetSRID(ST_MakePoint(15, 45),26332));
INSERT 0 1

In Oracle:

SQL> COLUMN p FORMAT a70
SQL> SET LINESIZE 100
SQL> SELECT * FROM points;

    ID P(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATE
---------- ----------------------------------------------------------------------
     1 SDO_GEOMETRY(2001, 26332, SDO_POINT_TYPE(15, 45, NULL), NULL, NULL)

You'll have to give me enough information so that I can reproduce the problem.

[laurenz]

It also works with multipoints:

Oracle:

CREATE TABLE multipoints(
   id number(5) CONSTRAINT multipoints_pkey PRIMARY KEY,
   mp mdsys.sdo_geometry
) SEGMENT CREATION IMMEDIATE;

PostgreSQL:

test=> CREATE FOREIGN TABLE multipoints(
          id integer OPTIONS (key 'true') NOT NULL,
          mp geometry(Multipoint)
       ) SERVER oracle OPTIONS (table 'MULTIPOINTS');
CREATE FOREIGN TABLE
test=> INSERT INTO multipoints VALUES (1, 'SRID=26332;MULTIPOINT(10 10, 11 11, 12 12)');
INSERT 0 1

Oracle:

SQL> COL mp FORMAT a110
SQL> SET LINES 130
SQL> SELECT * FROM multipoints;

    ID MP(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---------- --------------------------------------------------------------------------------------------------------------
     1 SDO_GEOMETRY(2005, 26332, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(10, 10, 11, 11, 12, 12))

[Reznik070]

Thank you. I will take a look at things over again.

[laurenz]

Any news?

[laurenz]

Closing for inactivity.