Wenn EXISTS nicht funktioniert: File-Geodatabases und korrelierte Unterabfragen

JoshuaBixby

<\/HEAD>

In \/blogs\/tilting\/2016\/09\/27\/performance-at-a-price-file-geodatabases-and-sql-support?sr=search&searchId=c3a45d0d-24f6-481b-bcf7-cfc7493b25fc&searchIndex=0<\/A>, lenke ich die Aufmerksamkeit auf den SQL-Support-Kompromiss, den Esri bei der Entwicklung der file geodatabase (FGDB) als Ersatz für die personal geodatabase (PGDB) eingegangen ist.  In diesem Beitrag werden mehrere Links bereitgestellt für diejenigen, die mehr über SQL-Unterstützung in file geodatabases erfahren möchten.  Obwohl es viele Überschneidungen im Inhalt zwischen den verschiedenen Quellen\/Links gibt, gibt es auch wichtige Aussagen, die nur an einer oder anderen Stelle existieren, und das Wissen darum kann wichtig sein, wenn man Fehler behebt oder versucht, unerklärliche Ergebnisse aus Daten zu verstehen, die in file geodatabases gespeichert sind.<\/P>

<\/P>

Ein Bereich, in dem Benutzer mit file geodatabases und SQL in Schwierigkeiten geraten können, ist die EXISTS-Bedingung oder der EXISTS-Operator.  Wenn man sich das SQL Reference (FileGDB_SQL.htm) in der File Geodatabase API (

@Esri Downloads <\/A>) oder die SQL reference for query expressions used in ArcGIS<\/A> ansieht:<\/P>

[NOT] EXISTS<\/P><\/TD>

Gibt TRUE zurück, wenn die Unterabfrage mindestens einen Datensatz zurückgibt, andernfalls FALSE. Zum Beispiel gibt dieser Ausdruck TRUE zurück, wenn das Feld OBJECTID den Wert 50 enthält:<\/P> EXISTS (SELECT * FROM parcels WHERE "OBJECTID" = 50)<\/PRE>EXISTS wird nur in file, personal und ArcSDE geodatabases unterstützt.<\/TD><\/TR><\/TBODY><\/TABLE><\/BLOCKQUOTE><\/P>Da SQL EXISTS in der file geodatabase unterstützt wird, wie gerät man dann damit in Schwierigkeiten?  Leider ist es einfacher als man denkt, und die Antwort sind korrelierte Unterabfragen.<\/P><\/P>Korrelierte Unterabfragen sind beim Arbeiten mit EXISTS ziemlich häufig, tatsächlich so häufig, dass Microsofts

EXISTS (Transact-SQL)<\/A>-Dokumentation und Oracles EXISTS Condition <\/A>-Dokumentation beide mindestens ein Codebeispiel mit einer korrelierten Unterabfrage verwenden.  Einfach ausgedrückt ist eine korrelierte Unterabfrage eine Unterabfrage, die sich auf eine oder mehrere Tabellen in der äußeren Abfrage bezieht.  Die Theorie und Empirie korrelierter Unterabfragen geht weit über diesen Blogbeitrag hinaus, aber ich möchte erwähnen, dass korrelierte Unterabfragen einige JOIN-ähnliche Eigenschaften oder zumindest Erscheinungen haben.<\/P>

<\/P>

Das folgende Beispiel ist adaptiert von

Select Max value arcpy<\/A>0;, derselben GeoNet-Frage, die mich vor vielen Monaten dazu brachte, dieses Problem zu recherchieren.  Beginnen wir mit zwei Basistabellen (TableA und TableB), jede mit einem Text- und einem Integer-Feld, wobei eine Tabelle eine Teilmenge der Datensätze der anderen Tabelle enthält.<\/P>

<\/P>

<\/P>

- und version-Spalte in TableB haben. Die korrekte Tabellenansicht ist im obigen Screenshot dargestellt. Aus den Ergebnissen des Codes ist ersichtlich, dass keine Datensätze aus der file geodatabase zurückgegeben werden. <SPAN class="operator token">>><\/><SPAN class="operator token">><\/> fgdb <SPAN class="operator token">=<\/> <SPAN class="comment token"># Pfad zur file geodatabase<\/>\n<SPAN class="operator token">>><\/><SPAN class="operator token">><\/> pgdb <SPAN class="operator token">=<\/> <SPAN class="comment token"># Pfad zur personal geodatabase<\/>\n<SPAN class="operator token">>><\/><SPAN class="operator token">><\/> egdb <SPAN class="operator token">=<\/> <SPAN class="comment token"># Pfad zur enterprise geodatabase, im Beispiel wird SQL Server verwendet<\/>\n<SPAN class="operator token">>><\/><SPAN class="operator token">><\/> gpkg <SPAN class="operator token">=<\/> <SPAN class="comment token"># Pfad zum GeoPackage<\/>\n<SPAN class="operator token">>><\/><SPAN class="operator token">><\/> gdbs <SPAN class="operator token">=<\/> <SPAN class="punctuation token">(<\/>fgdb<SPAN class="punctuation token">,<\/> pgdb<SPAN class="punctuation token">,<\/> egdb<SPAN class="punctuation token">,<\/> gpkg<SPAN class="punctuation token">)<\/>\n<SPAN class="operator token">>><\/><SPAN class="operator token">><\/> \n<SPAN class="operator token">>><\/><SPAN class="operator token">><\/> values <SPAN class="operator token">=<\/> <SPAN class="punctuation token">(<\/><SPAN class="punctuation token">(<\/><SPAN class="string token">"A"<\/><SPAN class="punctuation token">,<\/> <SPAN class="number token">1<\/><SPAN class="punctuation token">)<\/><SPAN class="punctuation token">,<\/> <SPAN class="punctuation token">(<\/><SPAN class="string token">"A"<\/><SPAN class="punctuation token">,<\/> <SPAN class="number token">2<\/><SPAN class="punctuation token">)<\/><SPAN class="punctuation token">,<\/> <SPAN class="punctuation token">(<\/><SPAN class="string token">"B"<\/><SPAN class="punctuation token">,<\/> <SPAN class="number token">1<\/><SPAN class="punctuation token">)<\/><SPAN class="punctuation token">,<\/> <SPAN class="punctuation token">(<\/><SPAN class("string")token>"B"<spanclass("punctuation")token>,spanclass("number")token>2spanclass("punctuation")token>)spanclass("punctuation")token>,
spanclass("punctuation")token>...spanclass("punctuation")token>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;spanclass("punctuation")token>(spanclass("string")token>"C"spanclass("punctuation")token>,spanclass("number")token>2spanclass("punctuation")token>)spanclass("punctuation")token>,spanclass("punctuation")token>(spanclass("string")token>"C"spanclass("punctuation")token>,spanclass("number")token>3spanclass("punctuation")token>)spanclass("punctuation")token>,spanclass("punctuation")token>(spanclass("string")token>"D"spanclass("punctuation")token>, Nonespanclass("punctuation")token>)spanclass("punctuation")token>,spanclass("punctuation")token>(spanclass("string")token>"E"spanclass("punctuation")token>,spanclass("number")token>1spanclass("punctuation")token>)spanclass("punctuation")token>)
spanclass("punctuation")token>...
<spanclass(“operator”)token>>>
<spanclass(“keyword”)token>for</span> gdb <spanclass(“keyword”)token>in</span> gdbs<spanclass(“punctuation”)token>:  class="punctuation token">)<\/SPAN><SPAN class="punctuation token">:<\/SPAN>
<SPAN class="punctuation token">.<\/SPAN><SPAN class="punctuation token">.<\/SPAN><SPAN class="punctuation token">.<\/SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table <SPAN class="operator token">=<\/SPAN> arcpy<SPAN class="punctuation token">.<\/SPAN>CreateTable_management<SPAN class="punctuation token">(<\/SPAN>gdb<SPAN class="punctuation token">, <\/SPAN>table_names<SPAN class="punctuation token">[<\/SPAN>i<SPAN class="number token">-1<\/SPAN><SPAN class="punctuation token">]<\/SPAN><SPAN class="punctuation token">)<\/SPAN>
<SPAN class="punctuation token">.<\/SPAN><SPAN class="punctuation token">.<\/SPAN><SPAN class="punctuation token">.<\/SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.<\/SPAN>AddField_management<SPAN class="punctuation token">(<\/SPAN>table<SPAN class="punctuation token">, <\/SPAN><SPAN class="string token">"id"<\/SPAN><SPAN class="punctuation token">, <\/SPAN><SPAN class="string token">"TEXT"<\/SPAN><SPAN class="punctuation token">)<\/SPAN>
<SPAN class="punctuation token">.<\/SPAN><SPAN class="punctuation token">.<\/SPAN><SPAN class="punctuation token">.<\/SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.<\/SPAN>AddField_management<SPAN class="punctuation token">(<\/Span>table<SPAN class="punctuation token">, <\/Span><SPAN class="string token">"version"<\/Span><SPAN class="punctuation token">, <\/Span><SPAN class="string token">"LONG"<\/Span><SPAN class="punctuation token">)<\/Span>&nbsp;&nbsp; 
<SPAN class="punctuation token">.<\/Span><SPAN class="punctuation token">.<\/Span><SPAN class="punctuation token">.<\/Span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span class="keyword token">with</span> arcpy<SPAN class="punctuation token">.<\/Span>da<SPAn class="punctuation token">.<\/Span>InsertCursor<SPAn class="punctuation token">(<SPAn>table<SPAn>, <SPAn>(<SPAn>"id"<SPAn>, "version")</SPAn>) <span class="keyword token">as</span> cur:<SPAn>
<SPAn>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <span class="keyword token">for</span> value <span class="keyword token">in</span> values[::<SPAn>i]:
<SPAn>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cur.insertRow(value)
<SPAn>&nbsp;&nbsp;&nbsp;&nbsp; 
<SPAn>&nbsp;&nbsp;&nbsp;&nbsp; qry = (<SPAn>"EXISTS (SELECT 1"
<SPAn>&nbsp;&nbsp;&nbsp;&nbsp; & nbsp; FROM TableB"
<SPAn>& nbsp; WHERE TableB.id = TableA.id"
<SPAn>& nbsp; AND TableB.version = TableA.version)"
<SPAn>& nbsp; )

&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
table_view = arcpy.MakeTableView_management(table, "table_view", qry)
print arcpy.GetCount_management(table_view)
arcpy.Delete_management(table_view)
0
3
3
3
>> > ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<span class="line-numbers-rows"><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span>‍</span><span >< < EM O J I _ 31 > > </ span > < span > << EM O J I _ 32 > > </ span > < span > << EM O J I _ 33 > > </ span > < / SPAN > < / CODE > < / PRE > < P > Das obige Beispielcode erstellt die zwei grundlegenden Tabellen in einer file geodatabase, personal geodatabase, enterprise geodatabase (<em>I used SQL Server</em>) und einem <a href= "http://www.geopackage.org/" rel= "nofollow noopener noreferrer" target= "_blank" >GeoPackage</a>. Der Code erstellt dann für jede der Geodatabases eine Tabellenansicht, indem EXISTS mit einer einfachen korrelierten Unterabfrage verwendet wird, um die Datensätze aus TableA auszuwählen, die eine übereinstimmende <code style= "font-family: courier new,courier,monospace;" >id

Warum gibt die file geodatabase also keine Datensätze zurück? Werden Unterabfragen nicht unterstützt? Da in der Dokumentation zu EXISTS Unterabfragen erwähnt werden, scheint es, dass sie zumindest teilweise unterstützt werden müssen. Werfen wir einen genaueren Blick auf den Abschnitt Subqueries des SQL reference for query expressions used in ArcGIS:

Unterabfragen

Hinweis:<\/H5>Coverages, Shapefiles und andere dateibasierte Datenquellen außerhalb der Geodatabase unterstützen keine Unterabfragen. Unterabfragen, die auf versionierten ArcSDE Feature-Classes und Tabellen ausgeführt werden, geben keine Features zurück, die in den Delta-Tabellen gespeichert sind. File-Geodatabases bieten die in diesem Abschnitt erläuterte eingeschränkte Unterstützung für Unterabfragen, während Personal- und ArcSDE-Geodatabases volle Unterstützung bieten. Für Informationen zum vollständigen Satz der Unterabfragefunktionen von Personal- und ArcSDE-Geodatabases konsultieren Sie bitte Ihre DBMS-Dokumentation.<\/P><\/DIV><\/P><\/P><\/P><\/P>Eine Unterabfrage ist eine Abfrage, die innerhalb einer anderen Abfrage verschachtelt ist. Sie kann verwendet werden, um Prädikats- oder Aggregatfunktionen anzuwenden oder um Daten mit Werten zu vergleichen, die in einer anderen Tabelle gespeichert sind....<\/P><\/BLOCKQUOTE><\/P>Die Unterstützung von Unterabfragen in File-Geodatabases beschränkt sich auf Folgendes:<\/P>IN-Prädikat. Zum Beispiel:<\/LI><\/UL>"COUNTRY_NAME" NOT IN (SELECT "COUNTRY_NAME" FROM indep_countries)<\/PRE>Skalare Unterabfragen mit Vergleichsoperatoren. Eine skalare Unterabfrage gibt einen einzelnen Wert zurück. Zum Beispiel:<\/LI><\/UL>"GDP2006" > (SELECT MAX("GDP2005") FROM countries)<\/PRE>Für File-Geodatabases können die Mengenfunktionen AVG, COUNT, MIN, MAX und SUM nur innerhalb skalare Unterabfragen verwendet werden.<\/SPAN><\/P>EXISTS-Prädikat. Zum Beispiel:<\/LI><\/UL>EXISTS (SELECT * FROM indep_countries WHERE "COUNTRY_NAME" = 'Mexico')<\/PRE><\/BLOCKQUOTE><\/P>Die Dokumentation gibt an, dass es eine „eingeschränkte Unterstützung“ für Unterabfragen gibt, erwähnt aber auch, dass das EXISTS-Prädikat unterstützt wird.  Wenn Unterabfragen nicht das Problem sind, könnten korrelierte Unterabfragen das Problem sein.  Schließlich verwendet keines der EXISTS-Beispiele in der gesamten Dokumentation eine korrelierte Unterabfrage.  Leider bleibt uns nur das Vermuten, da korrelierte Unterabfragen in keiner der verfügbaren File-Geodatabase-Dokumentationen explizit erwähnt werden.<\/P><\/P>Anhand dieses einfachen Beispiels wird deutlich, dass die File-Geodatabase bei Verwendung von EXISTS mit korrelierten Unterabfragen falsche Ergebnisse liefert.  Sind die falschen Ergebnisse ein Fehler oder eine Einschränkung der SQL-Unterstützung in der File-Geodatabase?  Selbst wenn man Letzteres argumentieren wollte, besteht weiterhin das Problem, dass der Benutzer falsche Ergebnisse erhält anstatt einer Fehlermeldung, was eigentlich der Fall sein sollte, wenn korrelierte Unterabfragen nicht unterstützt werden.<\/P><\/BODY><\/HTML>

LanceShipman

File Geodatabase does not support correlated subqueries,