Tutorial: Query a feature layer (SQL)

Import modules and log in to portal

1. Import the arcgis.gis module.

   Use dark colors for code blocks

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   
   from arcgis.gis import GIS
   
   

2. Create an anonymous connection to ArcGIS Online to access public data. Since this dataset is public you do not need credentials to access it. If it were a private dataset, you would be required to log in

   Use dark colors for code blocks

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   
   from arcgis.gis import GIS
   
   portal = GIS()
   
   

Access the feature layer by itemId

1. Use the ContentManager class to access the dataset by Item IDAn item ID is a unique identifier representing a single item stored, managed, and accessed in a portal, such as a web map, hosted layer, or file.Learn more.

   Use dark colors for code blocks

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   
   portal = GIS()
   
   parcel_layer_item = portal.content.get("a6fdf2ee0e454393a53ba32b9838b303")
   
   parcel_layer = parcel_layer_item.layers[0]
   
   

Run the query

1. Create a string variable containing the SQL statement. This will query only the features of this feature layer where the UseType attribute is set to Residential. Pass this in the where parmeter to the query() method of the FeatureLayer object. The as_df parameter is set to False so that the results will be returned as FeatureSet. The return_all_records and result_record_cout parameters are used to only return the first 100 records satisfying the query. Set the out_fields as an array of field names to return in the results.

   Use dark colors for code blocks

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   
   parcel_layer_item = portal.content.get("a6fdf2ee0e454393a53ba32b9838b303")
   
   parcel_layer = parcel_layer_item.layers[0]
   
   where_clause = "UseType = 'Residential'"
   
   results = parcel_layer.query(
     where = where_clause,
     as_df = False,
     return_all_records = False,
     result_record_count = 100,
     out_fields = "APN, UseType"
   )
   
   

Display the results

1. Use the map method to create a map widget. Use the add method to add the FeatureSet results to the map contents and the zoom_to_layer() method to set the maps extent so the query results are visible. Import the arcgis.map.popups module and use the PopupInfo class to define and enable the popups. The curly braces in the popup content are templates that will use the field's value at run time.

   Use dark colors for code blocks

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   
   where_clause = "UseType = 'Residential'"
   
   results = parcel_layer.query(
     where = where_clause,
     as_df = False,
     return_all_records = False,
     result_record_count = 100,
     out_fields = "APN, UseType"
   )
   
   map = portal.map()
   from arcgis.map.popups import PopupInfo
   map.content.add(results, popup_info=PopupInfo(title= "{UseType} Parcel", description = "Parcel number: {APN}"))
   map
   map.zoom_to_layer(results)
   
   

2. Optional: Use the export_to_html method to export the current state of the map widget to a static HTML file which can be viewed in any web browser.

   Use dark colors for code blocks

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   
   map = portal.map()
   from arcgis.map.popups import PopupInfo
   map.content.add(results, popup_info=PopupInfo(title= "{UseType} Parcel", description = "Parcel number: {APN}"))
   map
   map.zoom_to_layer(results)
   
   import os
   from os import path, getcwd
   
   export_dir = path.join(getcwd(), "home")
   if not path.isdir(export_dir):
       os.mkdir(export_dir)
   
   export_path = path.join(export_dir, "query-a-feature-layer-sql.html")
   
   map.export_to_html(export_path, title="Query a feature layer (SQL))