HOW TO
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.
-- 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')
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.
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
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.
Get help from ArcGIS experts
Download the Esri Support App