Subscribe to this thread
Home - General / All posts - converting script to SQL
vincent

1,767 post(s)
#02-Apr-19 21:44

Hi,

I have a file with 50k GPS points from a truck with a "location" field. I wanted to group records by location name, but a same location can be visited at different times, producing a bunch of points each time. I don't want to group all the points from a location if they are not from the same visit in time. I decided to attribute a group number to the points according to the locations and time.

So, I wrote the following javascript to loop through a table :

//javascript

function Main() {

var DCS = Document.ComponentSet;

var drawing = DCS.Item("pts_camions");

var RS = drawing.OwnedTable.RecordSet;

var i= 0;

var group = 1;

var previous_site = "";

var site = ""

RS.Item(0).Data("groupe") = group; // 1

for (i = 1; i< RS.Count ; i++)

{

previous_site = RS.Item(i-1).Data("site");

site =  RS.Item(i).Data("site");

 if (site == previous_site)

 {RS.Item(i).Data("groupe") = group ;}

 else { group += 1;

 RS.Item(i).Data("groupe") = group ; }

}

}

This is working right but, as expected, this is too slow for my need.

So I came with a script calling a few SQL queries :

function Main() {

var DCS = Document.ComponentSet;

var drawing = DCS.Item("pts_camions");

var RS = drawing.OwnedTable.RecordSet;

var i= 0;

var j = 1;

var site = ""

var Q1 = DCS.Item("getSite");

var ID2 = 1;

var previousID = 0;

for (i = 0 ; i < RS.Count; i++)

{

// get "site" from the first item

Q1.Text = 'Select [site] from [pts_camions] where [ID2] = ' + ID2;

Q1.RunEx(1);

site = Q1.Table.RecordSet.Item(0).Data("site");

// get [ID2] value for the next item with a site <> current site

Q1.Text = 'Select min(ID2)  as [First] , [site] from [pts_camions] where [site] <> "' + site + '"  and [groupe] = 0 GROUP BY [site] ORDER BY  [First]';

Q1.RunEx(1);

if (Q1.Table.RecordSet.Count != 0)

{

ID2 =  Q1.Table.RecordSet.Item(0).Data("First");

// update group value

Q1.Text = 'Update [pts_camions] SET [groupe] = ' + j + 'where [ID2] between ' + previousID + ' and '  +  (ID2-1) ;

Q1.RunEx(1);

previousID = ID2;

j += 1;

}

else {break};

}

}

It takes only a few seconds and I'm satisfied with it.

But I now wonder if it is possible to achieve that with one SQL query ? Is it possible to "Group By" features by an attribute without grouping all together if they not subsequent in the table order , producing multiple groups for a same value ?

adamw


8,447 post(s)
online
#09-Apr-19 09:29

First, to help visualize the data, is it that your table looks somewhat like this initially:

#

id2  site group

1    a    0

2    a    0

3    b    0

4    b    0

5    a    0

...and you want to assign group numbers like this:

#

id2  site group

1    a    1 -- first group

2    a    1 --     same group as before, because site did not change

3    b    2 -- new group, because site changed

4    b    2 --     same group as before

5    a    3 -- new group again, but not group 1 which had 'a' before

            --   because group 1 already ended

...as in, the table is ordered by id2 (the first script does not establish the ordering, but gets it de-facto), and we are starting a new group every time site changes?

If so, this is best done using a script. I mean, there's probably a way to write some very convoluted SQL to do what you want in a single statement, but a script is better. This applies to both 8 and 9 currently, although in 9 the script could be much better (and much faster) plus we'll likely extend SQL in 9 to allow doing it all in a query easier - in the future.

vincent

1,767 post(s)
#09-Apr-19 13:13

Thank you Adam. You understood everything right. The order is de-facto and a new group is started at every site change.

I'll keep my 2nd script for now then.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.