HOW TO

Author an attribute rule to reference a feature class in a different data source

Last Published: May 31, 2024

Summary

In some instances, one might want to access feature classes or tables in a different database management system for use in creating attribute rules. For example, one might want to write a Calculation Rule to populate an address field in a point feature class showing the location of pharmacies. The address information will be obtained from another feature class residing in a different database. One way of achieving this goal is establishing database links between the two data stores, which provides a way to access and query feature classes. Note that establishing database links varies across the various database management systems.

In this article, we create a database link between Microsoft SQL Server and PostgreSQL to use a feature class (Pharmacies_Tamale) in an attribute rule. This feature class plays a crucial role of bringing the information needed for the rule. This example uses an enterprise geodatabase in SQL Server database as the working environment, and a geodatabase in PostgreSQL containing an ST_Geometry feature class (Pharmacies_Tamale) to access and use in the rule.

Procedure

Create a Database Link

  1. If necessary, download and install the PostgreSQL ODBC Driver to match the version of PostgreSQL installed on your machine.
  2. Follow the instructions in this article to create a System DSN ODBC Data Source to the PostgreSQL database.
    • Note: For the driver, choose the PostgreSQL ODBS Driver Unicode.
  1. In the dialog box that pops up, provide a Data Source name and Description, specify the Database name, Port, User Name, and Password as connection parameters. Once done, test the connection by clicking the Test button.
    • In this example, the name of the Data Source is PostgreSQL35W and the Database in PostgreSQL that has the pharmacies_tamale feature class is pg_database, as shown in the next image.

PostgreSQL driver setup dialog

  1. Follow the instructions in the article to create a database link in SQL Server Management Studio (SSMS) between SQL Server and the established PostgreSQL data source connection. In the New Linked Server dialog box, shown in the next image, complete the following fields:
    • On the General tab, type the Linked server name and set the Server type to Other data source.
    • On the Security tab, set the connection login option to Be made using this security context and specify the username and password of the Postgres user
    • On the Server Options tab, set RPC and RPC Out to True.
    • Test the connectivity by right-clicking on Linked Server and selecting Test Connection.

Specifying the linked server

Create a View to Access the Feature Class from the Linked Server

  1. In SSMS, click New Query
  2. Review and type the following query to create a view:
-- Setting work environment
Use sqlDatabase
Go
-- Creating a view
Create view DataAdmin.vw_pharmacies_tamale
as
select
	objectid,
	name,
	location,
	type,
	class,
	address,
	globalid,
	geometry::STGeomFromText(st_astext, 32630) AS Shape -- rebuilding geometry
From
	OPENQUERY(
		[LINK_TO_POSTGRESQL],
		'Select
			objectid,
			name,
			location,
			type,
			class,
			address,
			globalid,
			ST_AsText(shape)::text as st_astext -- converting from st_geometry to WKT
			from pg_database.editor.pharmacies_tamale')

SQL query in SSMS

Note: 
PostgreSQL and SQL Server store geometry data using different spatial types. Hence, to access a geometric object from PostgreSQL in SQL Server, we first converted it to the Well-Known Text (WKT) format.

The following syntax represents the ST_Geometry to WKT conversion:

ST_AsText(shape)::text as st_astext

After converting the geometry to WKT, it was reconstructed in SQL Server using the following syntax:

Geometry::STGeomFromText(st_astext, 32630) AS Shape

Also, the OPENQUERY method in the SQL definition allows us to execute a pass-through query on a specified linked server.

  1. Run the query to create the database view.
    • The DataAdmin.vw_pharmacies_tamale view is created and added to the database. Next, access the view in ArcGIS Pro and register it with the geodatabase:
  2. Open the ArcGIS Pro project.
  3. In the Catalog Pane, if necessary, right-click Databases and connect to the database you are working in by establishing a New Database Connection.
  4. Expand the database and confirm that the view you created is available.
  5. In the Catalog pane, in the database, right-click the view and choose Register with Geodatabase.
  6. In the Register with Geodatabase geoprocessing tool pane, set the required parameters and then Run the tool.
    • The view is registered with the geodatabase and is now accessible using Arcade with the $datastore function when setting up an Attribute Rule, as shown in the following example:
var pharma = FeatureSetByName($datastore,"DataAdmin.vw_pharmacies_tamale", ["Address"])
var closestpharma = Intersects(pharma, BufferGeodetic($feature, "1000", "feet"))
var Fpharma = First(closestpharma)
//var Npharma = NearestCoordinate(Fpharma, $feature)
if (Fpharma == null) return {"errormessage": "no pharmacies are closeby"}
return Fpharma.Address 

Arcade expression in Expression Builder

In this article, we explored creating a view in a SQL Server database that references a feature class from a database in another data source (PostgreSQL). After registering the view with the database, we were able to refer to the view via an Arcade expression when setting up an Attribute Rule to generate information based on the attributes in the feature class in PostgreSQL.

Article ID: 000032325

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