Die Auswahl eines guten SQL Server 2008 spatial index mit großen Polygonen

Ich bin ein bisschen Spaß versuchen zu Holen eine anständige SQL Server 2008 spatial-index-setup für einen Datensatz ich bin den Umgang mit.

Wird der Datensatz Polygone repräsentieren die Konturen über die ganze Welt. Es gibt 106,000 Zeilen in der Tabelle, die Polygone werden in einer geometrie-Feld.

Das Problem, das ich habe, ist, dass viele der Polygone, die decken einen großen Teil der Welt. Diese scheint es sehr schwer zu bekommen, die einen räumlichen index, beseitigen viele von Zeilen im primären filter. Betrachten Sie zum Beispiel die folgende Abfrage:

SELECT "ID","CODE","geom".STAsBinary() as "geom" FROM "dbo"."ContA"
WHERE "geom".Filter(
  geometry::STGeomFromText('POLYGON ((-142.03193662573682 59.53396984952896,
    -142.03193662573682 59.88928136451884,
    -141.32743833481925 59.88928136451884,
    -141.32743833481925 59.53396984952896,
    -142.03193662573682 59.53396984952896))', 4326)
) = 1

Dies ist das Abfragen einer Fläche, die Sie schneidet mit nur zwei der Polygone in der Tabelle. Egal, welche Kombination von räumlichen index-Einstellungen wählte ich, dass Filter() gibt immer etwa 60.000 Zeilen.

Ersetzen der Filter() mit STIntersects() natürlich gibt nur die zwei Polygone, die ich will, aber natürlich viel länger dauert (Filter() ist 6 Sekunden, STIntersects() ist 12 Sekunden).

Kann mir jemand irgendwelche Tipps, ob es einen räumlichen index-setup, das ist wahrscheinlich zu verbessern, auf 60.000 Zeilen oder ist mein Datensatz nicht nur eine gute Partie für SQL Server spatial indexing ?

Mehr info:

Wie vorgeschlagen, ich aufteilen der Polygone, mit einem 4x4-raster auf der ganzen Welt. Ich konnte nicht sehen, einen Weg, es zu tun mit QGIS, so schrieb ich meine eigene Abfrage, um es zu tun. Zuerst habe ich definiert 16 bounding boxes, das erste sah so aus:

declare  @box1 geometry = geometry::STGeomFromText('POLYGON ((
-180 90,
-90 90,
-90 45,
-180 45,
-180 90))', 4326)

Dann habe ich jede bounding box auswählen und abschneiden der Polygone durchschnitten, box:

insert ContASplit
select CODE, geom.STIntersection(@box1), CODE_DESC from ContA
where geom.STIntersects(@box1) = 1

Hatte ich natürlich diese für alle 16 bounding-Boxen in das 4x4-raster. Das Endergebnis ist, dass ich eine neue Tabelle mit ~107,000 Zeilen (was bestätigt, dass ich nicht wirklich viele riesige Polygone).

Habe ich ein räumlicher index mit 1024 Zellen pro Objekt und niedrig,niedrig,niedrig,niedrig für die Zellen pro Ebene.

Allerdings sehr seltsam diese neue Tabelle mit der split-Polygone noch führt die gleiche wie die alte. Tun die .Filter oben aufgeführten noch gibt ~60,000 Zeilen. Ich verstehe wirklich nicht, überhaupt, klar ich verstehe nicht, wie mit dem räumlichen index die tatsächlich funktionieren.

Paradoxerweise, während .Filter() noch zurück ~60,000 Zeilen, es hat eine verbesserte Leistung. Die .Filter() nimmt nun rund 2 Sekunden, anstatt 6 und die .STIntersects() dauert jetzt 6 Sekunden an, anstatt 12.

Verlangt wie hier ist ein Beispiel für den SQL-Code für den index:

CREATE SPATIAL INDEX [contasplit_sidx] ON [dbo].[ContASplit] 
(
    [geom]
)USING  GEOMETRY_GRID 
WITH (
BOUNDING_BOX =(-90, -180, 90, 180),
GRIDS =(LEVEL_1 = LOW,LEVEL_2 = LOW,LEVEL_3 = LOW,LEVEL_4 = LOW), 
CELLS_PER_OBJECT = 1024,
PAD_INDEX  = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Aber denken Sie daran, ich habe versucht, eine ganze Reihe von verschiedenen Einstellungen für die Gitter und Zellen-pro-Objekt, mit dem gleichen Ergebnis jedes mal.

Hier sind die Ergebnisse der Laufenden sp_help_spatial_geometry_index, dies ist auf meiner split-dataset, in dem kein einzelnes polygon nimmt mehr als 1/16tel der Welt:

Base_Table_Rows 215138
Bounding_Box_xmin -90
Bounding_Box_ymin -180
Bounding_Box_xmax 90
Bounding_Box_ymax 180
Grid_Size_Level_1 64
Grid_Size_Level_2 64
Grid_Size_Level_3 64
Grid_Size_Level_4 64
Cells_Per_Object-16
Total_Primary_Index_Rows 378650
Total_Primary_Index_Pages 1129
Average_Number_Of_Index_Rows_Per_base_row 1
Total_Number_Of_ObjectCells_In_Level0_for_querysample 1
Total_Number_Of_ObjectCells_In_Level0_in_index 60956
Total_Number_Of_ObjectCells_In_Level1_in_index 361
Total_Number_Of_ObjectCells_In_Level2_in_index 2935
Total_Number_Of_ObjectCells_In_Level3_in_index 32420
Total_Number_Of_ObjectCells_In_Level4_in_index 281978
Total_Number_Of_Interior_ObjectCells_in_level2_in_index 1
Total_Number_Of_Interior_ObjectCells_in_level3_in_index 49
Total_Number_Of_Interior_ObjectCells_in_level4_in_index 4236
Total_Number_Of_Intersecting_Objectcells_in_level1_in_index 29
Total_Number_Of_Intersecting_Objectcells_in_level2_in_index 1294
Total_Number_Of_Intersecting_Objectcells_in_level3_in_index 29680
Total_Number_Of_Intersecting_Objectcells_in_level4_in_index 251517
Total_Number_Of_Border_ObjectCells_in_level0_for_querysample 1
Total_Number_Of_Border_ObjectCells_in_level0_in_index 60956
Total_Number_Of_Border_ObjectCells_in_level1_in_index 332
Total_Number_Of_Border_ObjectCells_in_level2_in_index 1640
Total_Number_Of_Border_ObjectCells_in_level3_in_index 2691
Total_Number_Of_Border_ObjectCells_in_level4_in_index 26225
Interior_To_Total_Cells_Normalized_to_leaf_grid_percentage 0.004852925
Intersecting_To_Total_Cells_Normalized_to_leaf_grid_percentage 0.288147586
Border_To_Total_Cells_Normalized_to_leaf_grid_percentage 99.70699949
Average_Cells_Per_Object_Normalized_to_leaf_grid 405.7282349
Average_Objects_PerLeaf_GridCell 0.002464704
Number_Of_SRIDs_Found 1
Width_Of_Cell_In_Level1 2.8125
Width_Of_Cell_In_Level2 0.043945313
Width_Of_Cell_In_Level3 0.000686646
Width_Of_Cell_In_Level4 1.07 E-05
Height_Of_Cell_In_Level1 5.625
Height_Of_Cell_In_Level2 0.087890625
Height_Of_Cell_In_Level3 0.001373291
Height_Of_Cell_In_Level4 2.15 E-05
Area_Of_Cell_In_Level1 1012.5
Area_Of_Cell_In_Level2 15.8203125
Area_Of_Cell_In_Level3 0.247192383
Area_Of_Cell_In_Level4 0.003862381
CellArea_To_BoundingBoxArea_Percentage_in_level1 1.5625
CellArea_To_BoundingBoxArea_Percentage_in_level2 0.024414063
CellArea_To_BoundingBoxArea_Percentage_in_level3 0.00038147
CellArea_To_BoundingBoxArea_Percentage_in_level4 5.96 E-06
Number_Of_Rows_Selected_By_Primary_filter 60956
Number_Of_Rows_Selected_By_Internal_filter 0
Number_Of_Times_Secondary_Filter_is_called 60956
Number_Of_Rows_Output 2
Percentage_Of_Rows_NotSelected_By_primary_filter 71.66655821
Percentage_Of_Primary_Filter_Rows_selected_by_internal_filter 0
Internal_Filter_Efficiency 0
Primary_Filter_Efficiency 0.003281055

"Base_Table_Rows 215138" macht nicht viel Sinn für mich, es gibt 107,000 Zeilen in der Tabelle, nicht 215,000

Beim Rendern des Datensatzes sieht wie folgt aus:
alt-text http://norman.cx/photos/links/wms.png

Die weitere Forschung:

Ich weiterhin verwirrt durch die schlechte Leistung des primären filters mit diesen Daten. Also machte ich einen test, um zu sehen, wie genau meine Daten teilt. Mit meiner ursprünglichen, ungeteilten Funktionen, die ich Hinzugefügt ein "Zellen" - Spalte der Tabelle. Ich lief dann 16 Abfragen zu zählen, wie viele Zellen in einem 4 x 4-raster die Funktion überspannt. Also lief ich eine Abfrage wie diese für jede Zelle:

declare  @box1 geometry = geometry::STGeomFromText('POLYGON ((
-180 90,
-90 90,
-90 45,
-180 45,
-180 90))', 4326)
update ContA set cells = cells + 1 where
geom.STIntersects(@box1) = 1

Wenn ich dann in das "Zellen" - Spalte in der Tabelle gibt es nur 672 features in meinem Datensatz, die sich mit mehr als 1 Zelle innerhalb des 4 x 4-raster. So wie auf der Erde, ganz wörtlich, kann der primäre filter wieder mit 60.000 Funktionen, die für eine Abfrage auf der Suche an einem kleinen 200-Meilen-breites Rechteck ?

In diesem Punkt sieht es aus wie ich könnte schreiben, dass meine eigenen Schema-index, die besser funktionieren würde, wie die SQL-Server ist die Durchführung für diese Funktionen.

Der räumliche index wird definitiv in der Abfrage verwendet? blogs.msdn.com/b/isaac/archive/2008/08/29/...
Ja, der räumliche index wird definitiv verwendet.
Es klingt, als hätten Sie eine Vielzahl von polygon-Größen. Wenn Sie nur die Daten angezeigt, dann sollten Sie das erstellen einer Kachel-cache als ein einmaliger Prozess, oder konvertieren Sie Ihre Daten in einem raster-dataset.
Leider in der situation, ich bin mit dem dataset ist es nicht praktikabel, erbringen die gesamte Kachel-cache bis zu der Ebene, die die Benutzer werden in der Lage sein zu Zoomen.
Ich würde empfehlen, sich in Kontakt mit den Entwicklern von SQL Server 2008 Spatial. Sie oft post auf den Geodaten-blogs und scheinen offen für Kommentare etc. Versuchen Sie die "E-Mail-Blog-Autor" - button im blogs.msdn.com/b/isaac/archive/2008/03/01/...

InformationsquelleAutor andynormancx | 2010-05-27

  1. 12

    In Ihrem index Abfrage, die Sie verwenden:

    CREATE SPATIAL INDEX [contasplit_sidx] ON [dbo].[ContASplit] 
(
    [geom]
)USING  GEOMETRY_GRID 
WITH (
BOUNDING_BOX =(-90, -180, 90, 180),
...

    Die BOUNDING_BOX daher Karten zu:

    xmin = -90
ymin = -180
xmax = 90
ymax = 180
    • Längegrades (-180 bis
      180 - Ausweisung von Osten /Westen des
      Meridian) sollte anzeigen, X
    • Breitengrad (-90 bis 90 - Benennung, wie
      weit nördlich oder südlich des Äquators)
      sollte die Karte zu Y

    So erstellen Sie die BOUNDING_BOX für die Welt, die Sie verwenden sollten:

    CREATE SPATIAL INDEX [contasplit_sidx] ON [dbo].[ContASplit] 
(
    [geom]
)USING  GEOMETRY_GRID 
WITH (
BOUNDING_BOX =(-180, -90, 180, 90),
...

    Diese soll einen index erstellen, das genau auf Ihre Daten und bedeutet, dass alle Ihre Funktionen sind abgedeckt durch den index.

    Danke, danke, danke. Es scheint, dass das tool, das ich verwendet, um den import der shape-Dateien erstellt, die Indizes mit dem falschen bounding box zunächst und ich war sklavisch kopieren den gleichen Fehler immer da.

    InformationsquelleAutor geographika

  2. 5

    Splitting Daten

    Wenn die Abfrage für die Anzeige von Daten, dann könntest du split up Ihre große Polygone mit einem raster. Diese wäre dann sehr schnell abrufen mit einem index. Sie können die Konturen, so dass die Funktionen würde sehen immer noch zusammenhängend.

    Meisten kommerziellen GIS-Pakete haben die Werkzeuge, um eine split-polygon-dataset von einem anderen. Suche nach tools, die Kreuzungen.

    Wenn Sie mit OpenSource-dann haben Sie einen Blick auf QGIS und http://www.ftools.ca die "durchführen, geoprocessing Operationen, einschließlich Kreuzungen, differenzierende, Gewerkschaften, löst sich, und clipping". Ich habe nicht verwendet die letztere selbst.

    Haben Sie einen Blick auf: http://postgis.refractions.net/docs/ch04.html#id2790790 dafür, warum die großen features sind schlecht.

    Filter und Schneidet

    Es gibt mehr auf der Filter-Klausel hier - http://blogs.msdn.com/b/isaac/archive/2010/03/04/filter-one-odd-duck.aspx

    Räumliche Indizes

    Etwas anderes zu prüfen, ist, dass der räumliche index tatsächlich verwendet wird in der Abfrage-plan. Sie können erzwingen, dass die Abfrage verwendet den index mit der Klausel:

    http://blogs.msdn.com/b/isaac/archive/2008/08/29/is-my-spatial-index-being-used.aspx

    Mehr details auf Indizes unten:

    http://blogs.msdn.com/b/isaac/archive/2009/05/28/sql-server-spatial-indexing.aspx

    Auch versuchen, sp_help_spatial_geometry_index für Ihre Daten zu sehen, welche Einstellungen für das Geo-index

    http://msdn.microsoft.com/en-us/library/cc627426.aspx

    Läuft das SP mit einigen test-geometrie produziert alle Arten von Statistiken, um zu versuchen und passen Sie Ihren index auf Ihre Daten. Eine vollständige Liste der Eigenschaften ist bei http://msdn.microsoft.com/en-us/library/cc627425.aspx

    Dazu gehören Werte wie:

    • CellArea_To_BoundingBoxArea_Percentage_in_level1
    • Number_Of_Rows_Selected_By_Primary_filter

    Durcheinander Geometrie

    Sich aus den Ergebnissen der sp_help_spatial_geometry_index wie es aussieht, haben Sie möglicherweise Probleme mit der geometrie selbst, anstatt den räumlichen index.

    Den Base_Table_Rows Graf sieht einen Fehler - http://connect.microsoft.com/SQLServer/feedback/details/475838/number-of-rows-in-base-table-incorrect-in-sp-help-spatial-geography-index-xml
    Es kann sich lohnen, Neuerstellung der Tabelle /Datenbank und versucht, den index von Grund auf neu.

    Total_Number_Of_ObjectCells_In_Level0_in_index 60956 ist eine Menge von Funktionen, zurück auf Ebene 0. Es ist wahrscheinlich, dass Sie entweder außerhalb des räumlichen index, Ausmaß oder null-Werte. Es läuft dann mit dem Intersect (Number_Of_Times_Secondary_Filter_is_called 60956) auf alle diese Eigenschaften, die erklären würden, warum es langsam ist. Auch wenn die docs behaupten, keine Leistung Treffer für null features, ich glaube, es hat noch die Aufzeichnungen aus, auch wenn kein schneiden ausgeführt wird.

    NULL und leere Instanzen sind gezählt
    auf Ebene 0 wird aber keine Auswirkungen
    Leistung. Ebene 0 wie viele
    Zellen als NULL und leere Instanzen an
    die Basis-Tabelle.

    Den Primary_Filter_Efficiency von 0.003281055 ich glaube, dass zeigt 0.03% Effizienz!

    Ein paar Dinge zu versuchen:

    1. Etwas komisch aus SELECT * FROM sys.spatial_indexes?

    2. Den MakeValid Aussage:

      UPDATE MyTable SET GeomFieldName = GeomFieldName.MakeValid()

    3. Reset /überprüfen SRID:

      UPDATE MyTable SET GeomFieldName.STSrid = 4326

    4. Fügen Sie in einige Felder zeigen die Ausmaße Ihrer Funktionen. Dies könnte darauf hinweisen, dass Fragen /NULL-Geometrien.

      ALTER TABLE MyTable
      HINZUFÜGEN von Zucker ALS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((1)).STX,0)) BEIBEHALTEN
      ALTER TABLE MyTable
      HINZUFÜGEN MinY ALS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((1)).SCHWEINESTALL,0)) BEIBEHALTEN
      ALTER TABLE MyTable
      HINZUFÜGEN MaxX ALS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((3)).STX,0)) BEIBEHALTEN
      ALTER TABLE MyTable
      HINZUFÜGEN MaxY AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((3)).SCHWEINESTALL,0)) BEIBEHALTEN

    Die Abfrage wird erzeugt durch GeoServer, dass ich mit tiles erzeugen, die gerendert werden mit den Bing-maps. Aufteilung der Polygone eine Sache war, hatte ich mir schon angesehen, aber gebracht hatte, Weg, so weit wie ich weiß, es wird mich ein oder zwei Tage, um perfekt die Abfrage/ein tool bauen, um es zu tun. Die Abfragezeit für den Filter ist um 50% niedriger als der von der Abfrage Zeit für STIntersects.
    Ich bin immer noch verwundert, warum die Filter gibt ganz so viele Datensätze. Es gibt viele große Polygone in den Daten gesetzt, sondern die Beschreibung, wie die SQL-Indizes arbeiten, würde führen mich zu erwarten, ein paar tausend zurück durch den filter, nicht für 60.000+ (60% des gesamten Daten-set).
    Ich habe nur eine kurze Anfrage genau zu sehen, wie viele wirklich große Polygone, die ich habe. Blick auf den Bereich der Hüllkurve jedes polygon nur 3 von 106,000 Polygone besetzen mehr als 50% der Erde, und nur 23 von Ihnen sind größer als eins-acht der Welt.
    Merkte ich, dass ich missverstanden Ihre Frage über die query-Zeit. Kombinieren eines Filters mit einer STIntersects dauert der gleichen Zeit wie ein STIntersects von selbst, ob Sie kombinieren Sie mit einer sub-Abfrage oder hinzufügen von sowohl der WHERE-Klausel. Dies überrascht nicht, denn was ich gelesen habe sagt, dass STIntersects hat einen Filter intern sowieso, wenn es einen räumlichen index zur Verfügung. Nur tun, einen Filter nehmen Sie 6 Sekunden, alles was mit hinzufügen STIntersects dauert 12 Sekunden.
    Ich habe Hinzugefügt, links zu, wie der Filter arbeitet, und die Aufteilung der Polygone - es sollte keine Notwendigkeit, um Ihre eigenen tools entwickeln, um dies zu tun.

    InformationsquelleAutor geographika

  3. 0

    Habe auch ich fand es sehr schwierig zu "ERRATEN", was eine entsprechende räumliche index für eine bestimmte Tabelle, von Geometrien. Ich habe versucht, mehr zu machen, Vermutungen über die sp_help_spatial_geometry_index gespeicherten Prozedur. All dies Tat, war mir sagen, wie schlecht meine räumlichen index ausgeführt wurde, nach jedem "RATEN". Auch wenn ich beschränkte meine Optionen durch nur unter Berücksichtigung 2-8 CELLS_PER_OBJECT, dass allein gibt 567 Permutationen (3 Arten gewählt 4 mal = 81. Dann multiplizieren von 7 CELLS_PER_OBJECT-Optionen). Ich beschloss, dass ich gehen zu lassen, SQL-server tun das Experimentieren für mich und geben Sie mir einige empirische Evidenz. Ich erstellt eine gespeicherte Prozedur, die würde Spinnen durch den Permutationen und den Wiederaufbau der räumliche index für eine Tabelle mit räumlichen Daten für jede. Dann wäre es test der query-performance von jeder permutation der räumlichen Indexes mit Hilfe von zwei mitgelieferten geometry-Instanzen. Ich wählte eine geometrie-Instanz, die den gesamten Datensatz und dann eine andere Instanz, die einen kleineren Teil der Daten festgelegt. Der proc verwendet STIntersect (4-mal in jeder Instanz und zeichnet dann die Ergebnisse in einer Tabelle. Sie können dann die Abfrage die Tabelle mit den Ergebnissen finden Sie heraus, welche räumlichen index durchgeführt, die am besten auf Ihre jeweiligen Datensatz. Versuchen Sie es und lassen Sie mich wissen, wenn Sie irgendwelche Verbesserungsvorschläge oder Anmerkungen.

    Erstellen der proc mit dieser https://gist.github.com/anonymous/5322650. Richten Sie dann eine Ausführung der Anweisung in diesem Beispiel gilt:

    /* set up some strings to be used to create geometry instances when our test spatial queries run */ 
DECLARE @ada VARCHAR(MAX) 
SET @ada = 'GEOMETRY::STGeomFromText(''POLYGON ((2422068 527322, 2422068 781170, 2565405 781170, 2565405 527322, 2422068 527322))'', 0)'
DECLARE @mer VARCHAR(MAX) 
SET @mer = 'GEOMETRY::STGeomFromText(''POLYGON ((2451235 696087, 2451235 721632, 2473697 721632, 2473697 696087, 2451235 696087))'', 0)'
DECLARE @mer1 VARCHAR(MAX) 
SET @mer1 = 'GEOMETRY::STGeomFromText(''POLYGON ((244386 712283, 2443866 717980, 2454872 717980, 2454872 712283, 244386 712283))'', 0)'
DECLARE @mer2 VARCHAR(MAX) 
SET @mer2 = 'GEOMETRY::STGeomFromText(''POLYGON ((2434259 687278, 2434259 701994, 2449657 701994, 2449657 687278, 2434259 687278))'', 0)'


EXEC gis.sp_tune_spatial_index 'PARCEL_ADA', 'S104_idx', 2, 8, @ada, @mer1 
GO

    ANMERKUNG: Offensichtlich, Wiederaufbau einer räumlichen index 567 mal eine lange Zeit dauern wird. Kick it off " - Befehl-Linie oder einfach nur laufen lassen, während Sie andere Dinge zu tun. Wenn es ist ein dataset, das Sie benutzen oft und die Geometrien ähnlich bleiben, es lohnt sich die Zeit es braucht, um führen Sie den proc. Ergebnisse Tabelle zeigt die performance in Millisekunden.

    Danke für die Antwort! Statt Dropbox verwenden (die ist gesperrt, von vielen Arbeitgebern), ist es möglich, den Kern der gespeicherten Prozedur hier? Oder, sollten Sie überlegen, mit Github Gist (gist.github.com) als alternative hosting-Standort.
    Danke für die Anregung. Gerade aktualisiert, der Link zu dieser Website.
    genial! Ich hackte bis Ihre gespeicherte Prozedur, um einige weitere Ausgabe: geonet.esri.com/blogs/HackingArcSDE/2015/07/07/...

    InformationsquelleAutor GreenGeo

Schreibe einen Kommentar