Splitting up GeoAnalytics connector operations

Sonja_Bauernfeind — Thu, 01 Jul 2021 10:07:34 GMT

Many of the GeoAnalytics can be executed with a split input of the indata table. This article explains which and how to modify the code that the connector produces. Operations that cannot be Splittable are mostly the aggregating and hence not Splittable. When loadable tables are used for input, inline tables are created in loops and can be used for a quick way to split. Of course it's possible to write custom code to do the splitting instead.

Making calls with large indata tables often causes time outs on the server side, splitting is a way around that.

Environment:

Splittable ops	Non-Splittable ops	Special ops, splittable
AddressPointLookup Intersects IpLookup NamedAreaLookup NamedPointLookup PointToAddressLookup Routes TravelAreas Within	Closest Cluster Dissolve Load IntersectsMost Simplify	Binning SpatialIndex

Binning and SpatialIndex

Bining and SpatialIndex differs from other operations, they are not placing any call to the server if the indata are internal geometries, ie lat ,ong points. The operations als produce the same type of results so the resulting tables can be concatenated.

Resolution:

Example, a Within operation

Before the edit

The code as the connector produces it:

/* Generated by Idevio GeoAnalytics for operation Within ---------------------- */ Let [EnclosedInlineTable] = 'POSTCODE' & Chr(9) & 'Postal.Latitude' & Chr(9) & 'Postal.Longitude'; Let numRows = NoOfRows('PostalData'); Let chunkSize = 1000; Let chunks = numRows/chunkSize; For n = 0 to chunks Let chunkText = ''; Let chunk = n*chunkSize; For i = 0 To chunkSize-1 Let row = ''; Let rowNr = chunk+i; Exit for when rowNr >= numRows; For Each f In 'POSTCODE', 'Postal.Latitude', 'Postal.Longitude' row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'PostalData'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b'); Next chunkText = chunkText & Chr(10) & Mid('$(row)', 2); Next [EnclosedInlineTable] = [EnclosedInlineTable] & chunkText; Next chunkText='' Let [EnclosingInlineTable] = 'ClubCode' & Chr(9) & 'Car5mins_TravelArea'; Let numRows = NoOfRows('TravelAreas5'); Let chunkSize = 1000; Let chunks = numRows/chunkSize; For n = 0 to chunks Let chunkText = ''; Let chunk = n*chunkSize; For i = 0 To chunkSize-1 Let row = ''; Let rowNr = chunk+i; Exit for when rowNr >= numRows; For Each f In 'ClubCode', 'Car5mins_TravelArea' row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'TravelAreas5'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b'); Next chunkText = chunkText & Chr(10) & Mid('$(row)', 2); Next [EnclosingInlineTable] = [EnclosingInlineTable] & chunkText; Next chunkText='' [WithinAssociations]: SQL SELECT [POSTCODE], [ClubCode] FROM Within(enclosed='Enclosed', enclosing='Enclosing') DATASOURCE Enclosed INLINE tableName='PostalData', tableFields='POSTCODE,Postal.Latitude,Postal.Longitude', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosedInlineTable)} DATASOURCE Enclosing INLINE tableName='TravelAreas5', tableFields='ClubCode,Car5mins_TravelArea', geometryType='POLYGON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosingInlineTable)} SELECT [POSTCODE], [Enclosed_Geometry] FROM Enclosed SELECT [ClubCode], [Car5mins_TravelArea] FROM Enclosing; [EnclosedInlineTable] = ''; [EnclosingInlineTable] = ''; /* End Idevio GeoAnalytics operation Within ----------------------------------- */

After edit

The header and the call is moved inside of the loop. chunkSize decides how big each split is.

Note that the first inline table now comes after the first one, this to get the call inside of the iteration.

/* Generated by Idevio GeoAnalytics for operation Within ---------------------- */ Let [EnclosingInlineTable] = 'ClubCode' & Chr(9) & 'Car5mins_TravelArea'; Let numRows = NoOfRows('TravelAreas5'); Let chunkSize = 1000; Let chunks = numRows/chunkSize; For n = 0 to chunks Let chunkText = ''; Let chunk = n*chunkSize; For i = 0 To chunkSize-1 Let row = ''; Let rowNr = chunk+i; Exit for when rowNr >= numRows; For Each f In 'ClubCode', 'Car5mins_TravelArea' row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'TravelAreas5'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b'); Next chunkText = chunkText & Chr(10) & Mid('$(row)', 2); Next [EnclosingInlineTable] = [EnclosingInlineTable] & chunkText; Next chunkText='' Let numRows = NoOfRows('PostalData'); Let chunkSize = 1000; Let chunks = numRows/chunkSize; For n = 0 to chunks Let [EnclosedInlineTable] = 'POSTCODE' & Chr(9) & 'Postal.Latitude' & Chr(9) & 'Postal.Longitude'; Let chunkText = ''; Let chunk = n*chunkSize; For i = 0 To chunkSize-1 Let row = ''; Let rowNr = chunk+i; Exit for when rowNr >= numRows; For Each f In 'POSTCODE', 'Postal.Latitude', 'Postal.Longitude' row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'PostalData'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b'); Next chunkText = chunkText & Chr(10) & Mid('$(row)', 2); Next [EnclosedInlineTable] = [EnclosedInlineTable] & chunkText; [WithinAssociations]: SQL SELECT [POSTCODE], [ClubCode] FROM Within(enclosed='Enclosed', enclosing='Enclosing') DATASOURCE Enclosed INLINE tableName='PostalData', tableFields='POSTCODE,Postal.Latitude,Postal.Longitude', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosedInlineTable)} DATASOURCE Enclosing INLINE tableName='TravelAreas5', tableFields='ClubCode,Car5mins_TravelArea', geometryType='POLYGON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosingInlineTable)} SELECT [POSTCODE], [Enclosed_Geometry] FROM Enclosed SELECT [ClubCode], [Car5mins_TravelArea] FROM Enclosing; [EnclosedInlineTable] = ''; [EnclosingInlineTable] = ''; Next chunkText='' /* End Idevio GeoAnalytics operation Within ----------------------------------- */

Example, a AddressPointLookup operation

Before the edit

The code as the connector produces it:

/* Generated by GeoAnalytics for operation AddressPointLookup ---------------------- */ Let [DatasetInlineTable] = 'id' & Chr(9) & 'STREET_NAME' & Chr(9) & 'STREET_NUMBER'; Let numRows = NoOfRows('data'); Let chunkSize = 1000; Let chunks = numRows/chunkSize; For n = 0 to chunks Let chunkText = ''; Let chunk = n*chunkSize; For i = 0 To chunkSize-1 Let row = ''; Let rowNr = chunk+i; Exit for when rowNr >= numRows; For Each f In 'id', 'STREET_NAME', 'STREET_NUMBER' row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'data'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b'); Next chunkText = chunkText & Chr(10) & Mid('$(row)', 2); Next [DatasetInlineTable] = [DatasetInlineTable] & chunkText; Next chunkText='' [AddressPointLookupResult]: SQL SELECT [id], [Dataset_Address], [Dataset_Geometry], [CountryIso2], [Dataset_Adm1Code], [Dataset_City], [Dataset_PostalCode], [Dataset_Street], [Dataset_HouseNumber], [Dataset_Match] FROM AddressPointLookup(searchTextField='', country='"Canada"', stateField='', cityField='"Toronto"', postalCodeField='', streetField='STREET_NAME', houseNumberField='STREET_NUMBER', matchThreshold='0.5', service='default', dataset='Dataset') DATASOURCE Dataset INLINE tableName='data', tableFields='id,STREET_NAME,STREET_NUMBER', geometryType='NONE', loadDistinct='NO', suffix='', crs='Auto' {$(DatasetInlineTable)} ; [DatasetInlineTable] = ''; /* End GeoAnalytics operation AddressPointLookup ----------------------------------- */

After edit

The header and the call is moved inside of the loop. chunkSize decides how big each split is.

/* Generated by GeoAnalytics for operation AddressPointLookup ---------------------- */ Let numRows = NoOfRows('data'); Let chunkSize = 1000; Let chunks = numRows/chunkSize; For n = 0 to chunks Let [DatasetInlineTable] = 'id' & Chr(9) & 'STREET_NAME' & Chr(9) & 'STREET_NUMBER'; Let chunkText = ''; Let chunk = n*chunkSize; For i = 0 To chunkSize-1 Let row = ''; Let rowNr = chunk+i; Exit for when rowNr >= numRows; For Each f In 'id', 'STREET_NAME', 'STREET_NUMBER' row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'data'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b'); Next chunkText = chunkText & Chr(10) & Mid('$(row)', 2); Next [DatasetInlineTable] = [DatasetInlineTable] & chunkText; [AddressPointLookupResult]: SQL SELECT [id], [Dataset_Address], [Dataset_Geometry], [CountryIso2], [Dataset_Adm1Code], [Dataset_City], [Dataset_PostalCode], [Dataset_Street], [Dataset_HouseNumber], [Dataset_Match] FROM AddressPointLookup(searchTextField='', country='"Canada"', stateField='', cityField='"Toronto"', postalCodeField='', streetField='STREET_NAME', houseNumberField='STREET_NUMBER', matchThreshold='0.5', service='default', dataset='Dataset') DATASOURCE Dataset INLINE tableName='data', tableFields='id,STREET_NAME,STREET_NUMBER', geometryType='NONE', loadDistinct='NO', suffix='', crs='Auto' {$(DatasetInlineTable)} ; [DatasetInlineTable] = ''; Next chunkText='' /* End GeoAnalytics operation AddressPointLookup ----------------------------------- */

Re: Splitting up GeoAnalytics connector operations

Emir_Dz — Fri, 05 Apr 2024 07:36:03 GMT

It would be very helpful if you could provide an example for operation Routes

Re: Splitting up GeoAnalytics connector operations

Danica-Cortez — Thu, 09 May 2024 18:24:54 GMT

It would be helpful to have an example for every option that is splittable, especially seeing as official documentation is so poor.

article Splitting up GeoAnalytics connector operations in Official Support Articles

Splitting up GeoAnalytics connector operations

Environment:

Binning and SpatialIndex

Resolution:

Example, a Within operation

Before the edit

After edit

Example, a AddressPointLookup operation

Before the edit

After edit

Re: Splitting up GeoAnalytics connector operations

Re: Splitting up GeoAnalytics connector operations