How can I INSERT INTO using results of a query?

[PaulFrank]

Hello,

Is it possible to use an st_geometry function that is part of a SELECT statement to INSERT records into an Oracle SDE geodatabase (12g)?  I have constructed the following statement from which I receive Error ORA-00917; missing comma.

INSERT INTO gis_table
(objectid, gis_table_id, shape)
VALUES (1, 2, (sde.st_geometry ('polygon((SELECT sde.st_astext(sde.st_buffer(l.shape, 500)) as shapebuffer FROM location.address_point l where l.place_id = 802689))', 2277))

 The part of the query

SELECT sde.st_astext(sde.st_buffer(l.shape, 500)) as shapebuffer FROM location.address_point l where l.place_id = 802689

returns and Oracle type CLOB, which turns out to actually be POLYGON (( 3112886.18568189 10066866.95637797, 3112885.11514598 10066899.65808421, etc etc)), So I see I may be duplicating the keyword POLYGON, but I have changed that and I get other errors.  Or I freely admit I may be using the wrong functions or process to do this.

Thanks in advance of the great answers I am sure to get...

Paul Frank

[ChristianWells]

I tried to simplify this down to just the ST functions, but this might help get us in the right direction. Here is how I did an INSERT INTO SELECT with an ST_BUFFER:

insert into gis_table (shape) select sde.st_buffer(l.shape, 0.05) from location l;‍‍

If I add in attribute records it changes slightly:

insert into gis_table (objectid, gis_table_id,shape) select 1,2,sde.st_buffer(l.shape, 0.005) from location l;‍

In the original script, there are some duplication of the ST calls with the ST_GEOMETRY + ST_ASTEXT function. There are 2 reasons I would avoid the additional call for WKT.

1. ST_BUFFER returns an ST_GEOMETRY object and can be reused in your insert statement without rework
2. The WKT is returned as a Character Large Object (CLOB), which can be unwieldy and quite large to store in memory - this becomes more important as you scale your SQL requests.

[CraigSwadner]

Have you tried a select into statement?

SELECT *

INTO newtable

FROM oldtable

WHERE condition;

Craig Swadner (GIS Coordinator)

City of Cape Coral

1015 Cultural Park Blvd.

Cape Coral, Fl 33990

Did you know the best way to report issues to ITS is to use the Service Desk system? This will ensure someone in ITS gets your request and allows us to track the progress. Please enter all your issues and/or questions by clicking https://breeze.cape.capecoral.net

[PaulFrank]

Thanks everyone.  I modified the query to this and good news - I dont receive a syntax error, just ORA-01031: insufficient privileges!  I suspect I dont have those for the geometry_st functions?  I am asking my IT about that.  BTW, I also tried it without the where clause as suggested, and I get the same error.

INSERT INTO planningcadastre.demolition_notifications_gis
(OBJECTID, NOTIFICATION_ID, SHAPE)
select 1,2,sde.st_buffer(l.shape, 500) from location.address_point l where l.place_id = 802689;

[ChristianWells]

Glad to hear we made some progress! On the insufficient permissions it might be the tables from the LOCATION or PLANNINGCADASTRE schema. Can you verify that you can do a SELECT one each table without the ST call? Can you verify you can do an INSERT on DEMOLITION_NOTIFICATIONS_GIS without the ST call?

[PaulFrank]

Yes I am still seeing the error when I try to insert a buffer manually, so that would seem to be the issue, although I tried to add a fairly large polygon that I shortened for viewing purposes:

INSERT INTO planningcadastre.demolition_notifications_gis
(OBJECTID, NOTIFICATION_ID, SHAPE)
VALUES (4, 5, sde.st_geometry('polygon ((3122386.18571073 10066866.95637797, 3122364.77499239 10067520.98755021, 3122300.63437265 10068172.21820313, 3122194.03845723 10068817.85962838, 3122045.44393815 10069455.14673963, 3121855.48696898 10070081.35119589, 3121624.98119640 10070693.79058780, ...3122300.63437265 10065561.69455279, 3122364.77499239 10066212.92520571, 3122386.18571073 10066866.95637797)',2277))