English

How To: 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 < OBJECTID
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:
    Geometry

    Oracle:
    ST_Geometry
    SDO_Geometry
  2. In a SQL program, 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 < OBJECTID
    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;
    /

Related Information