Equivalent spatial types for insert statement

Question

I have a number of files containing vast amounts of Insert statements (which were generated by Toad for Oracle) which I need to run on a Postgresql database.

Sounds simple I know but there are also oracle specific spatial data types in there which are hampering my efforts. I tried to use a number of tools for this from SwisSQL to SDO2Shp to migrate the data and none have been any help whatsoever so my only plan left to try is to come up with a C# program to open the file, replace the oracle specific types with types that will work in postgis and then save the file again. The problem is I have no idea which types I could substitute with the Oracle ones or the format or syntax I must use.

I am very new to postgresql and postgis and my oracle knowledge is also limited as I had previously used SQL Server.

Here is an example of the Insert statement. They will all have the same format as this as the tables are the same but with different data for different zoom levels on a map.

Insert into CLUSTER_1000M
(CLUSTER_ID, CELL_GEOM, CELL_CENTROID)
Values
(4410928, 
"MDSYS"."SDO_GEOMETRY"(2003,81989,NULL,
"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,1003,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
"MDSYS"."SDO_ORDINATE_ARRAY"(80000,106280,81000,107280,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)), 
"MDSYS"."SDO_GEOMETRY"(2001,81989,
"MDSYS"."SDO_POINT_TYPE"(80500,106780,NULL),NULL,NULL));

How can I get this into a format that will work with postgis?

score 1 · Accepted Answer · answered Mar 19 '12 at 13:03

I have no idea on how the Oracle GIS implementation works:

But looking at the data, I don't think conversion will be possible (it might be, but the effort might be huge).

Look at the way PostGIS defines Geometry

INSERT INTO geotable ( the_geom, the_name )
  VALUES ( ST_GeomFromText('POINT(-126.4 45.32)', 312), 'A Place');

PostGIS follows standards on how to display/store the data and offers methods do assist the developer to do so. This conversion is mostly with functions that have a *from* in their name. So to create the proper data from a line, the output is similar to aline

SELECT ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))) AS aline,
        ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('POINT(1 2)'))) IS NULL AS null_return;

aline                            | null_return
------------------------------------------------
010200000002000000000000000000F ... | t

Judging from your example output from Oracle, the format is pretty different and might not be convertable (if Oracle isn't offerent something that is able to stick to the standard).

On the other hand, when looking at the Oracle example

INSERT INTO cola_markets VALUES(
  1,
  'cola_a',
  SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
    SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
          -- define rectangle (lower left and upper right) with
          -- Cartesian-coordinate data
  )
);

you might be able to replace some of the Oracle names with the ones for PostGIS, so SDO_ORDINATE_ARRAY(1,1, 5,7) might turn into something like ST_GeomFromText(LINESTRING(1 1, 5 7))

Equivalent spatial types for insert statement

1 Answers1