topic Re: sqlldr / st_geometry wkt in Data Management Questions

sqlldr / st_geometry wkt

runegullstrom — Wed, 16 Oct 2013 20:46:51 GMT

Hi,

it should be possible to load wkt data into st_geometry with sql loader (oracle). I can't get it working though,
does anybody have an example?

regards,

Rune

oracle 11g / sde10

Re: sqlldr / st_geometry wkt

runegullstrom — Fri, 18 Oct 2013 02:35:02 GMT

Maybe some more detail would be helpful:
if my table is this:
CREATE TABLE GEOMETRY_TEST
(
GID INTEGER,
G1 SDE.ST_GEOMETRY
)

And my sql loader ctl script is this:
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE geometry_test
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS (
gid,
G1 EXPRESSION "sde.st_geomfromtext(':g1',0)"
)
BEGINDATA
1|"POINT ( 10.02000000 20.01000000),0"
2|"POINT ( 11.02000000 20.01000000),0"

Then I'm getting this back in my .log file:
Record 1: Rejected - Error on table GEOMETRY_TEST, column G1.
ORA-20003: Geometry type "" is not a valid shape type.
ORA-06512: at "SDE.ST_GEOM_UTIL", line 213
ORA-06512: at "SDE.ST_GEOMFROMTEXT", line 20

I've tried all kind of permutations but sofar without any luck,

regards,

Re: sqlldr / st_geometry wkt

SachinKanaujia — Mon, 21 Oct 2013 19:23:49 GMT

First try to validate the following

1) Setup is correct using the following query

select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

2) You are able to create a table independently with the ST_GEOMETRY column

CREATE TABLE GEOMETRY_TEST
(
GID INTEGER,
G1 SDE.ST_GEOMETRY
)

3) I did not understand why you are using 0 at both the places?

G1 EXPRESSION "sde.st_geomfromtext(':g1',0)" <--- here
)
BEGINDATA
1|"POINT ( 10.02000000 20.01000000),0" <-- here
2|"POINT ( 11.02000000 20.01000000),0" <-- here

Try with this

1|"POINT ( 10.02000000 20.01000000)"
2|"POINT ( 11.02000000 20.01000000)"

Re: sqlldr / st_geometry wkt

VinceAngelo — Mon, 21 Oct 2013 19:54:00 GMT

I think Sachin nailed it -- the trailing ",{SRID}" is not a part of the WKT specification
(even if some implementations of OGC ST_GEOMETRY do support it as an extension).

You really ought to specify an explicit SRID, not a default one. This may help prevent
significant difficulties after the data is loaded.

- V

Re: sqlldr / st_geometry wkt

runegullstrom — Tue, 26 Nov 2013 00:44:14 GMT

Hi,

I use the '0' because sde.st_geomfromtext requires the srid to be defined*
*http://resources.arcgis.com/en/help/main/10.1/index.html#/ST_GeomFromText/006z00000054000000/

Leaving it out results in this message in the sqlloader log:
Record 1: Rejected - Error on table GEOMETRY_TEST, column G1.
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ST_GEOMFROMTEXT'

Still puzzled about how to do this,

regards,

Rune

Re: sqlldr / st_geometry wkt

VinceAngelo — Tue, 26 Nov 2013 00:54:27 GMT

Yes, an SRID is required, but it should be the correct SRID, and it must be *outside* the
WKT string, as in the example above (you must hard-code the SRID in the control file).

The constructor takes two arguments, a string/CLOB, and an integer. Having the integer
inside the string won't work and neither will leaving it out.

- V