HOW TO

Insert geometry from XY coordinates using SQL

Last Published: April 25, 2020

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.
SQL Server Geometry sample:
-- 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
Oracle SDO Geometry sample:
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;
/
Oracle ST Geometry sample:
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;
/

Article ID: 000012317

Software:
  • Legacy Products

Receive notifications and find solutions for new or common issues

Get summarized answers and video solutions from our new AI chatbot.

Download the Esri Support App

Related Information

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options