0

I am somewhat new to programming and the SQLite world. I am looking for a way to take data from and SQLite DB and populate feature classes. The feature classes are all polygons and the intent is to get their shape from the Well Known Text field within the SQLite DB. I also need to populate two other fields in the feature class where the data is coming from the SQLite DB.

  • Are you trying to do this using ArcGIS for Desktop? – PolyGeo Nov 20 '14 at 20:16
2

Here is an example of how you can use a SQLITE DB to populate a Feature Class using the arcpy module to ArcGIS and Python.

Some assumption I made were:

  • You are using Python
  • You are using the arcpy module
  • You need to create the Feature Class from scratch

Note: The SQLITE DB I used in the code below is created during run-time and lives in RAM. As soon as the execute is over the DB will cease to exist.

I hope this helps!

# necessary modules
import sqlite3 as lite
import arcpy

def main():
    # Creating a SQLITE DB in RAM (You would connect to you SQLITE DB)
    con = lite.connect(':memory:')

    # generate cursor
    cur = con.cursor()

    ############################################################################
    ############################################################################
    # The code below is simply creating a table with records containing WKT and
    # two other fields.

    # create example DB
    cur.execute('''
        CREATE TABLE SomeTable (
            Shape_WKT   TEXT,
            Type        TEXT,
            Owner       TEXT
        ); ''')

    # inserting records to SQLite DB
    cur.execute('''
        INSERT INTO SomeTable
        VALUES (
            'POLYGON((-8.26171875 11.6015625, 3.515625 25.83984375, -17.2265625 41.484375, 23.02734375 41.1328125, 22.1484375 11.25, 8.96484375 1.23046875, 9.31640625 -1.7578125, -8.26171875 11.6015625))',
            'PARCEL',
            'Rodrigues'
        ); ''')
    cur.execute('''
        INSERT INTO SomeTable
        VALUES (
            'POLYGON((-27.7734375 -12.83203125, 7.3828125 -2.63671875, 11.07421875 -3.33984375, 10.72265625 1.7578125, 21.62109375 9.4921875, 22.32421875 -11.77734375, -2.8125 -24.08203125, -27.7734375 -12.83203125))',
            'PARCEL',
            'Garay'
        ); ''')
    cur.execute('''
        INSERT INTO SomeTable
        VALUES (
            'POLYGON((-32.34375 8.96484375, -28.828125 -11.953125, 7.55859375 -1.23046875, -10.8984375 9.66796875, -32.34375 8.96484375))',
            'PARCEL',
            'Erwin'
        ); ''')
    cur.execute('''
        INSERT INTO SomeTable
        VALUES (
            'POLYGON((-32.16796875 11.07421875, -11.25 11.42578125, 0.17578125 25.6640625, -20.390625 42.1875, -34.98046875 23.203125, -32.16796875 11.07421875))',
            'PARCEL',
            'Jacobson'
        ); ''')
    ############################################################################
    ############################################################################

    # Create Feature Class to populate data
    output_path = r'T:\Some\Path'
    out_name = 'Parcel_data'
    geometry_type = 'Polygon'

    result = arcpy.CreateFeatureclass_management(
        output_path,
        out_name,
        geometry_type)

    # get results for feature class (fc)
    # will give you full path of fc
    fc = result.getOutput(0)

    # add fields to newly created fc
    arcpy.AddField_management(
        fc,
        'Type',
        'TEXT' )
    arcpy.AddField_management(
        fc,
        'Owner',
        'TEXT' )

    # Create InsertCursor for newly created Feature Class
    fields = ['SHAPE@WKT', 'Type', 'Owner']
    cursor = arcpy.da.InsertCursor(fc, fields)

    # Query to extract data drom SQLITE DB table
    rows = cur.execute('''
        SELECT
            Shape_WKT,
            Type,
            Owner
        FROM SomeTable''')

    for row in rows:
        cursor.insertRow(row)

    # close cursors
    con.close()
    del cursor

if __name__ == '__main__':
    main()
  • The feature classes are already created. There are a number of fields within the feature classes, one of them being the shape field. SO I want to map the WKT field in the SQLite DB into the shape field within the FC. There are also 2 other fields into the FC. I will see what I can do with this. – ryanhd20 Nov 24 '14 at 14:26
  • And yes, I am doing this with desktop and the arcpy module. – ryanhd20 Nov 24 '14 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.