Android Question SQLite with multiple Conditions

Terradrones · Dec 19, 2022

Hi All

I need a push\help again please.

On a Road Construction we have what we call "Batterboards" (some people call it Slope Stakes).

I have designed the Input Activity, such that the Surveyor selects the Side (Left or Right...ComboBox), the Condition (Cut or Fill...ComboBox), the BatterSet (you can have than 1 Batter Slope on the same Cross-section...ComboBox), the Stake (Textbox), Slope(Textbox) and the Appendage Data.

Once the Surveyor stores all the Design Data and goes back to that Activity, all the Stakes entered that conform to the Side, Condition and Set will be displayed in a Combobox.

How can I do this?

I know how to do this for a Single Condition.

B4X:

[/
Sub VerNo_SelectedIndexChanged (Index As Int)
    Try
        WorkItem = VerNo.SelectedItem
        Query = "SELECT * FROM VerPi WHERE Stake = ?"
        ResultSet =CGlobals.SQL1.ExecQuery2(Query, Array As String (WorkItem))
        ResultSet.NextRow
        VerStake.Text = NumberFormat2(ResultSet.GetDouble("Stake"),1,3,3,False)
        VPILevel.Text = NumberFormat2(ResultSet.GetDouble("VPiLevel"),1,3,3,False)
        Length1.Text = NumberFormat2(ResultSet.GetDouble("Length1"),1,3,3,False)
        Length2.Text = NumberFormat2(ResultSet.GetDouble("Length2"),1,3,3,False)
        CurveType.SelectedIndex=ResultSet.GetString("CType")
    Catch
        Log(LastException)
        CurveType.SelectedIndex=0
    End Try
    ResultSet.Close
End Sub
]

In VB.net, I did it as such:

[CODE=b4x][/
   Sub GetBatter()
        Cmd = Con.CreateCommand()
        Cmd.CommandText = "Select * From Batter "
        'Cmd.CommandText = Text
        Reader = Cmd.ExecuteReader()
        SelectStake.Items.Clear() : SelectStake.Text = ""
        While (Reader.Read())
            If Reader.GetValue(0) = Side.Text And Reader.GetValue(1) = Cond.Text And Reader.GetValue(3) = BatterSet.Text Then
                SelectStake.Items.Add(Reader.GetValue(2))
            End If
        End While
        Reader.Close()
    End Sub
]

emexes · Dec 19, 2022

1/ that naked ResultSet.NextRow in your single-condition example, that assumes True and relies on Catching False, is a bit of a worry

2/ SQL condition can handle AND so could do something like:

B4X:

Dim Query As String = "SELECT * FROM VerPi WHERE Stake = ? AND Side = ? AND Cond = ?"
ResultSet = CGlobals.SQL1.ExecQuery2( Query, Array As String(WorkItem, LookForSide, LookForCond) )
Do While ResultSet.NextRow
    'add descriptive fields from row to selection list
    'eg from VB code:
    SelectStake.Items.Add(Reader.GetValue(2))
Loop

'if need to do something special if no matches found, then something like:
If SelectStake.Size = 0 Then
    'something special if no matches found
End If

emexes · Dec 19, 2022

Lol I was thinking about this while out shopping just now.

I have it pictured as:
- you have a site with lots of batterboards,
- each workset has a number of batterboards
- batterboards can be in more than one workset

and I'm wondering:

- is a workset always exactly 4 batterboards, or can it be 3 or 5 (or 6...) ?
- can a single batterboard have more than one elevation/height? (eg, top and bottom of a vertical retaining wall?)
- is there such a thing as a workset, or is my mental picture off the track and in the weeds?
- how do you link which batterboards are associated with which worksets?

Terradrones · Dec 19, 2022

emexes said:
Lol I was thinking about this while out shopping just now.

I have it pictured as:
- you have a site with lots of batterboards,
- each workset has a number of batterboards
- batterboards can be in more than one workset

and I'm wondering:

- is a workset always exactly 4 batterboards, or can it be 3 or 5 (or 6...) ?
- can a single batterboard have more than one elevation/height? (eg, top and bottom of a vertical retaining wall?)
- is there such a thing as a workset, or is my mental picture off the track and in the weeds?
- how do you link which batterboards are associated with which worksets?

Hi Emexes

Let's take a Road Project as an example. The finished road surface has a certain width and is at a certain height or depth from the original ground surface. Depending on the depth (Cut Condition) or height (Fill Condition) and the Gradient of side slopes, the Toe position of the Fill or Cut will vary continuously from the Stakeline.

In the simplest Design a workset will have 4 sets of data: The Start Stake, Test Height, Batterslope, Appendage Width & Appendage Height for the 4 options of Side (left or right) and Condition (cut or fill).

The Surveyor takes a reading on the ground with either a Total Station or GPS and the program will determine on which side of the Stakeline the reading was taken, at which Stakevalue the reading was taken and then calculate whether the Design is in a Cut or Fill condition. Using these values the program will select the correct workset and calculate the other setting out info.

In South Africa we normally use a Batterslope (Sideslope) of 1:2 for both Cut and Fill conditions.

But things can get complicated. Let's assume that our road goes through a cut condition where the top 2m is very soft material, the next 3m is intermediate material and the balance of the Cut is solid rock.

The Engineers decide to have the first 2m at a slope of 1:5 (very flat), the next 3m a slope of 1:2 and the rest of the cutting through the rock at 1:0.5 (very steep). I have 2 input variables: "Sets" & "Test Height"....Set 1= Test Height of 2m; Set 2 = Test Height of 5m; Set 3 = Test Height of 99m (rest of cut depth). This will have to be done for left and right.

Now the Workset increases to 6 for cut conditions plus the 2 for fill conditions which gives us a total of 8 worksets.

Kind Regards
Michael

emexes · Dec 19, 2022

Yikes, I never realised earthworks were so complicated = I have a new respect for 'dozer drivers etc.?

I have so many questions, but probably the key one is: can we interpret "In VB.net, I did it as such" as meaning that you already have a database design that works?

Or do you have ~~changes~~ improvements to the design in mind, now that the VB.net design has been ~~used on~~ tested by real-world jobs?

emexes · Dec 19, 2022

Is your database coming out of eg Civil 3D?

How (or does) your app relate to Terradrones? Or is this the Terradrones company branching out into a new field?

Are we only looking at roads at the moment, or will un-road-like areas soon be needed? Presumably the staking and cut/fill instructions would be different for roads vs un-road-like areas.

Terradrones · Dec 19, 2022

I am a Surveyor and a Civil Engineer. Terradrones is a Company that I established about 5 years ago (see www.terradrones.net). I use Drones to do Topographical Surveys, which I use in my Civil Designs or in surveying work for other Companies.

The Database design is already there for a whole Project, consisting of a SQLite File and in this File, I have different Tables for each Geometric Design Entity (Horizontal and Vertical Alignment, Crossfalls, Batters, Coordinates, Tin Model, Terraces, etc.)

For all Projects that consist of Geometric Parameters, like roads, runways, canals, etc. can be used with this program.

For "Un-road" Projects, like a Golf Course, one can use a "Tin Model". The Designer will give a file consisting of a whole lot of X, Y & Z Points. In Ceaser you can triangulate these Points....connect the Points in sets of 3. In the Field the Surveyor can hold the GPS Rover (or Total Station) anywhere in the area and the program will check in which Triangle the Rover is. Using the 3 vertexes of that Triangle, the program can calculate the Elevation of that point and display how much you must move up or down to reach the Design Elevation.

I also have a "Terrace" option in the program.

I have in my old Windows Mobile program the option to import a 3D DXF Drawing. The Surveyor can then tap on the CAD Entities to set them out. I want to port this also over to Ceaser Android version.

There are 2 options of how one can get the Design Data into the program...either by punching it in manually or importing it from other programs. At the moment I can import from Civil Designer, Roadmaker and Modelmaker.

The Ceaser program is already 30 years old. I first started off with the old Psion MX Workabout (DOS), then moved over to Windows Mobile. Now I am busy with the Android version. I tried at first to do it with Kotlin, but what a nightmare! Erel's B4A is the best and easiest.

emexes · Dec 19, 2022

Terradrones said:
The Database design is already there for a whole Project, consisting of a SQLite File and in this File, I have different Tables for each Geometric Design Entity (Horizontal and Vertical Alignment, Crossfalls, Batters, Coordinates, Tin Model, Terraces, etc.)

That is happy news! ? (for me, at least - my head was swimming trying to understand the connections between database table elements)

Has the actual original query "SQLite with multiple Conditions" "How can I do this?" been resolved? or at least heading in a positive direction, eg:

emexes said:
2/ SQL condition can handle AND so could do something like:

Terradrones · Dec 20, 2022

emexes said:
That is happy news! ? (for me, at least - my head was swimming trying to understand the connections between database table elements)

Has the actual original query "SQLite with multiple Conditions" "How can I do this?" been resolved? or at least heading in a positive direction, eg:

Hi

This seems to be working:

B4X:

[/
Sub LoadBatter
    Dim i As Int
    
    SelectStake.cmbBox.clear
    
    Dim Items8 As List
    Items8.Initialize
    Dim cs As CSBuilder
    Dim l8 As List
    l8.Initialize
    Dim Query As String = "SELECT * FROM Batter WHERE Side = ? AND Cond = ? AND Set1 = ?"
    ResultSet = CGlobals.SQL1.ExecQuery2( Query, Array As String(Side.SelectedItem, Cond.SelectedItem, BatterSet.SelectedItem) )
    Do While ResultSet.NextRow
        Items8.Add(NumberFormat2(ResultSet.GetDouble("Stake"),1,3,3,False))
    Loop
    Dim cs As CSBuilder
    Dim l8 As List
    l8.Initialize
    For i =0 To Items8.Size -1
        l8.Add(cs.Initialize.size(18).Typeface(Typeface.DEFAULT_BOLD).Append(Items8.Get(i)).PopAll)
    Next
    SelectStake.SetItems(l8)
    SelectStake.SelectedIndex=-1
    ResultSet.Close
End Sub
]

Thank you.

emexes · Dec 20, 2022

Terradrones said:
This seems to be working:

Usually that'd be good enough for me, but I'm wondering: why is there an intermediate list l8? Is it a global that's used elsewhere too? And cs and l8 are Dimmed twice and l8 initialized twice, which is no real problem other than taking up space on the screen.

Also, maybe split it into two Subs like FindBatters(Side, Cond, BatterSet) As List and AddListToComboBox(ComboBox, ItemList) As ComboBox. The first Sub will probably only be used once, but the second Sub might be useful in other parts of your program too.

emexes · Dec 20, 2022

B4X:

Sub LoadBatter

    Dim cs As CSBuilder

    '*** make StakeList of matching stakes ***

    Dim StakeList As List
    StakeList.Initialize

    Dim Query As String = "SELECT * FROM Batter WHERE Side = ? AND Cond = ? AND Set1 = ?"
    ResultSet = CGlobals.SQL1.ExecQuery2( Query, Array As String(Side.SelectedItem, Cond.SelectedItem, BatterSet.SelectedItem) )
    
    Do While ResultSet.NextRow
        Dim StakeNumber As Double = ResultSet.GetDouble("Stake")
        Dim StakeNumberFormatted As String = NumberFormat2(StakeNumber, 1, 3, 3, False)
        StakeList.Add( cs.Initialize.size(18).Typeface(Typeface.DEFAULT_BOLD).Append(StakeNumberFormatted).PopAll )
    Loop

    ResultSet.Close

    '*** add those matching stakes to SelectStake combobox ***

    SelectStake.cmbBox.clear
    SelectStake.SetItems(StakeList)
    SelectStake.SelectedIndex = -1
    
End Sub

klaus · Dec 20, 2022

When i read Ceasar and Windows Mobile i said to myself, this reminds me something.
And i found that you were member of this forum quite some years ago, Basic4PPC at this time, with name Ceasar.
I suppose that you remember THIS.

emexes · Dec 20, 2022

klaus said:
And i found that you were member of this forum quite some years ago, Basic4PPC at this time, with name Ceasar.

ceaser said:
there are still some hicups that I need to sort out. Every surveyor that is using the old program is breathing down my neck to test out the new program.

Life must be pretty good now that those humps are history, right? ? ?

Terradrones · Dec 20, 2022

emexes said:
Usually that'd be good enough for me, but I'm wondering: why is there an intermediate list l8? Is it a global that's used elsewhere too? And cs and l8 are Dimmed twice and l8 initialized twice, which is no real problem other than taking up space on the screen.

Also, maybe split it into two Subs like FindBatters(Side, Cond, BatterSet) As List and AddListToComboBox(ComboBox, ItemList) As ComboBox. The first Sub will probably only be used once, but the second Sub might be useful in other parts of your program too.

Hi, that method was shown to me by some member here. As the program is used in the Field, I have everything in Bold Font and that method is used to show the Text in the Combobox in Bold. The double Dim is a typo mistake.

Terradrones · Dec 20, 2022

emexes said:
Life must be pretty good now that those humps are history, right? ??

Hi Klaus, yes I tried to rewrite the DOS version of Ceaser from the Psion MX Workabout to a Windows Mobile Platform using B4PPC, but then moved over to VB.net Compact, which was very succesfull. I started rewriting the Windows Mobile version to the Android Platform about a year ago using Kotlin, but what a nightmare using Kotlin. I looked around on the Internet and found B4A, which for me is the best. Erel has done a very good job.

Mahares · Dec 20, 2022

Terradrones said:
This seems to be working:

Glad your code in post 9 worked for you. But, it can further be simplified. You do not need the list Items8. See how I would have coded it.

B4X:

Sub LoadBatter    
    SelectStake.cmbBox.clear  
    Dim cs As CSBuilder
    Dim l8 As List
    l8.Initialize
    Dim Query As String = "SELECT * FROM Batter WHERE Side = ? AND Cond = ? AND Set1 = ?"
    ResultSet = CGlobals.SQL1.ExecQuery2( Query, Array As String(Side.SelectedItem, Cond.SelectedItem, BatterSet.SelectedItem) )
    Do While ResultSet.NextRow
        l8.Add(cs.Initialize.size(18).Typeface(Typeface.DEFAULT_BOLD).Append(NumberFormat2(ResultSet.GetDouble("Stake"),1,3,3,False)).PopAll)
    Loop
    SelectStake.SetItems(l8)
    SelectStake.SelectedIndex=-1
    ResultSet.Close
End Sub

By the way as a side minor note @William Lancee mentioned to you in another one of your threads that you are not using code tags correctly. You are still not using them properly.

emexes · Dec 20, 2022

Terradrones said:
As the program is used in the Field, I have everything in Bold Font and that method is used to show the Text in the Combobox in Bold.

If everything is in Bold, and you have multiple ComboBoxes, then a single general routine that:

1/ takes a list of strings,
2/ bolds them, and
3/ adds them to a designated ComboBox,

might get quite a workout, and leave the calling code less cluttered too. Bonus!

btw is it B4xComboBox that you're using? or some other custom view?

Android Question SQLite with multiple Conditions

Terradrones

Active Member

emexes

Expert

emexes

Expert

Terradrones

Active Member

emexes

Expert

emexes

Expert

Terradrones

Active Member

emexes

Expert

Terradrones

Active Member

emexes

Expert

emexes

Expert

klaus

Expert

emexes

Expert

Terradrones

Active Member

Terradrones

Active Member

Mahares

Expert

emexes

Expert

Similar Threads

Android Question SQLite with multiple Conditions

Active Member

Expert

Expert

Active Member

Expert

Expert

Active Member

Expert

Active Member

Expert

Expert

Expert

Expert

Active Member

Active Member

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency