Insert geometry from XY coordinates using SQL

Summary

In some cases, there may be a need to insert geometries into a feature class using structured query language (SQL). This can be done using a loop or a database cursor in SQL. Sometimes, due to performance, resources, or workflows, creating data via SQL is preferable to creating the data in ArcMap.

-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(*) FROM sde.INPUT_TABLE)
-- Declare an iterator
DECLARE @iterator INT
-- Initialize the iterator
SELECT @iterator = MIN(OBJECTID) FROM SDE.INPUT_TABLE
-- Loop through the rows of a table @myTable
WHILE @iterator is NOT NULL
BEGIN
DECLARE @id as integer
EXEC sde.next_rowid 'sde', 'points', @id OUTPUT;
INSERT INTO sde.POINTS (OBJECTID, Shape)
SELECT
@id,
geometry::STPointFromText('POINT('+str(POINT_X, 38, 8) + ' ' + str(POINT_Y, 38, 8) + ')', 2286) as SHAPE
FROM sde.INPUT_TABLE where objectid = @iterator;

SELECT @iterator= MIN(OBJECTID) FROM SDE.INPUT_TABLE WHERE @iterator < @RowCount
END

Procedure

In the example below, point geometry is inserted into a feature class from an existing database table.

1. Create an empty point feature class in ArcMap using one of the following spatial types.
   
   SQL Server:
   Geometry
   
   Oracle:
   ST_Geometry
   SDO_Geometry
2. In a SQL Server application, create a cursor or loop to iterate through the input database table. Create a geometry for each row.

Note:
This procedure works best if a unique identifier column exists in the input database table, such as a rownum or OID field in SQL Server. This ensures the WHILE loop does not miss any rows that are not unique.

Note:
To insert data into a registered feature class, a unique, not-null value must be specified for the ObjectID column. To do this, the Next_RowID procedure must be used.

-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(*)FROM sde.INPUT_TABLE)
-- Declare an iterator
DECLARE @iterator INT
-- Initialize the iterator
SELECT @iterator = MIN(OBJECTID) FROM SDE.INPUT_TABLE
-- Loop through the rows of a table
WHILE @iterator is NOT NULL
BEGIN
DECLARE @id as integer
EXEC sde.next_rowid 'sde', 'points', @id OUTPUT;
INSERT INTO sde.POINTS (OBJECTID, Shape)
SELECT @id,
geometry::STPointFromText(
'POINT('+str(POINT_X, 38, 8) + ' ' + str(POINT_Y, 38, 8) + ')', 2286) as SHAPE
FROM sde.INPUT_TABLE where objectid = @iterator;

SELECT @iterator= MIN(OBJECTID) FROM SDE.INPUT_TABLE WHERE @iterator < @RowCount
END

SET SERVEROUTPUT ON

-- Declare a cursor
DECLARE
CURSOR points is
select *
from sde.input_table;

--Loop through the cursor
BEGIN
FOR pt IN points LOOP
INSERT INTO SDE.POINTS (OBJECTID, SHAPE) VALUES (
sde.gdb_util.next_rowid('sde', 'points'),
MDSYS.SDO_GEOMETRY(2001,2286,MDSYS.SDO_POINT_TYPE(pt.point_x, pt.point_y,NULL),NULL,NULL)
);
END LOOP;
COMMIT;
END;
/

SET SERVEROUTPUT ON

-- Declare a cursor
DECLARE
CURSOR points is
select *
from sde.input_table;

--Loop through the cursor
BEGIN
FOR pt IN points LOOP
INSERT INTO SDE.POINTS (OBJECTID, SHAPE) VALUES (
sde.gdb_util.next_rowid('sde', 'points'),
sde.ST_Point(pt.point_x,pt.point_y, 2286)
);
END LOOP;
COMMIT;
END;
/