Solved

how to set up a pre query for sde database

gisbradokla
Rank
Badge +10

I am creating an automation to delete a large number of features from sde. The features have a grouping attribute that i am hoping will help make this doable by automating to fme server and passing one group at a time while changing the flag upon completion. then on to the next group in the next automation job.

There are aver 77million features in the egdb (sde). I have the grouping attribute in another database (sql flat table). and i am able to query that data quickly with e.g. [path_windows_unc] LIKE $(path_windows_unc)

the same query on the sde table takes hours. I am unable to see the sde database when i try to set up a pre-fetch with sqlexecutor or databasejoiner, inlinequerier. How can i optimize getting the data needed without reading all 77million features?

icon

Best answer by gisbradokla 9 August 2022, 20:46

View original

4 replies

markw
Badge

Hi @Brad Nesom​! I believe you're on the right track with your workflow ideas so far. Is it feasible to run one job per feature class, and delete the features of interest on that basis? This would likely involve the Automations Writer, but you'd want to be conscious of the number of jobs this would generate (based the number of feature classes in your SDE). To make this process as efficient as possible, you'll likely want to use the SQLExecutor so that the database is doing the bulk of the work and FME Server is simply "submitting requests" for these deletes. GROUP BY and WHERE clauses can help narrow down the queries so you aren't reading excessive amounts of data per job. You may also be able to use attributes/parameters in the SQL queries to automatically fill table info for the feature class you want to clean up for that job.

gisbradokla
Rank
Badge +10

Hi @Brad Nesom​! I believe you're on the right track with your workflow ideas so far. Is it feasible to run one job per feature class, and delete the features of interest on that basis? This would likely involve the Automations Writer, but you'd want to be conscious of the number of jobs this would generate (based the number of feature classes in your SDE). To make this process as efficient as possible, you'll likely want to use the SQLExecutor so that the database is doing the bulk of the work and FME Server is simply "submitting requests" for these deletes. GROUP BY and WHERE clauses can help narrow down the queries so you aren't reading excessive amounts of data per job. You may also be able to use attributes/parameters in the SQL queries to automatically fill table info for the feature class you want to clean up for that job.

for some strange reason the sqlexecutor is not working. it completes successfully (i don't get any indication of an error) but the log shows 0 features and when i look at sde the features still exist. I also tried the sde writer directly as well as the databaseupdater but none of the 3 are actually deleting the features.

markw
Badge

for some strange reason the sqlexecutor is not working. it completes successfully (i don't get any indication of an error) but the log shows 0 features and when i look at sde the features still exist. I also tried the sde writer directly as well as the databaseupdater but none of the 3 are actually deleting the features.

Hi Brad, sorry for the delayed response! The SQLExecutor succeeding but not returning anything is expected when running statements like DELETE or DROP TABLE, because these queries aren't designed to return any results. To check why the features aren't being deleted, it may be an issue with a WHERE clause in your SQL. Both the SQLExector and DatabaseDeleter should work.

gisbradokla
Rank
Badge +10

finally got this working. it came down to whether or not the automation was passing the value in quotes (double quotes i believe)

I had to do a string replacer. then pass the value into the featureReader and add single quotes to the query string.

Reply


Community Stats

28,723
Posts
109,511
Replies
36,560
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.