GPSeismic FAQs GPSQL

GPSQL FAQs

Can't modify database

Despite what error message number you might see, if you find that you can't modify the database using the modern spreadsheet viewer, the first thing you want to try is this:

Select 'Compact/repair Database' from the File menu of GPSQL. Select the database and choose Access 2000 compatible.

After this procedure, the problem should disappear. If it doesn't, you should check that the database is not set to read-only (right click on the database in Explorer and view Properties), or that it isn't opened exclusively by another application.

If the above fails, on the main GPSQL interface, look for a field type that is suffixed by '(AI)' or '(I)'. This means there is an existing auto-incrementing field or index. If one is present, the modern spreadsheet won't work. The index has to be removed.

x +
Can't open database

This could mean the database is write protected or the database is currenty opened exclusively by another application.

GPSQL uses 'False' for the read-only argument when opening an mdb (because we might eventually write to it), so you cannot open a database which has it's read-only attribute set. For an mdb on a CD, you will have to copy it to your hard drive and change that attribute.

x +
How do I create a swath table?

In GPSQL, you may import a set of swath definitions as a table (or append an existing one). The imported file must be in the form of a comma delimited file and have the following field sequence: Swath, HiTrack1, LoTrack1, HiBin1, LoBin1... The user is first prompted for number of swath definitions and the typical entry is '1'. which would produce a five field table as above. '2' would produce four additional fields describing more high and low track bin values. For example: Swath, HiTrack1, LoTrack1, HiBin1, LoBin1, HiTrack2, LoTrack2, HiBin2, LoBin2

The most common is one set of low/high definitions per swath, but if not, and the swath is not contiguous, you can specify the additional parameters up to 10. Below is a typical file which would be imported:

1,0,5000,1000,2000 2,0,5000,2000,3000
3,0,5000,3000,4000
4,0,5000,4000,5000
5,0,5000,5000,6000
6,0,5000,6000,7000
7,0,5000,7000,8000
8,0,5000,8000,9000
9,0,5000,9000,10000

In the above example, there are 9 swaths identified by the numers 1-9. The first swath is defined by a low track of zero, a high track of 5000, a low bin of 1000 and a high bin of 2000. The remaining swaths are identified similarly. Once imported, a table is created. If the above data was imported, you would have five fields and nine records. The field names are called:

Swath LoTrack1
HoTrack1
LoBin1
HiBin1

The query builder is designed so that it is possible to drag and drop table fields into the specified value textbox. This allows 'BETWEEN' statements to be constructed based on multiple table fields. For example, if two tables are selected and called SWATHS and POSTPLOT, you could build a criteria like:

Select [POSTPLOT].*,[Swaths].* From [POSTPLOT],[Swaths] Where [POSTPLOT].`Track` Between [Swaths].`LoTrack1` And [Swaths].`HiTrack1` And [POSTPLOT].`Bin` Between [Swaths].`LoBin1` And [Swaths].`HiBin1`

When you build such a query, you are cautioned to add a join to the table, but you don't have to here. Trust me! Finally, when you display (or report) this query, you will see that the Swath field will identify each station\'s unique swath number.

x +
Wildcard characters

Starting way back with Version 2004 we began using a different set of database libraries. In fact, we use two different sets. One is called DAO (Data Access Objects) and the new kid on the block is ADO.Net. (ActiveX Data Objects).

DAO, uses the asterisk (*) as a wildcard and a question mark (?) as a single character wildcard.

ADO uses the percentage sign (%) and the underscore (_) for the respective wildcards.

I know, what lame brain would do this differently. I don't know but if you find him, punch him once for me.

Anyway, when you write a query and display it in the new spreadsheet (which uses ADO), we analyze the query and replace any asterisks with percentage signs and any question marks with underscores. At present, we don\'t do anything with underscores and percentage signs because its difficult to figure out what context they are being used in. So how do you write a query to find all records where Descriptor (or any other string field) has an underscore? You would bracket it. So instead of...

Descriptor Like '*_*'

...you would write...

Descriptor Like '*[_]*'

The same holds true for any widcard. For example if you were looking for anything with a question mark, it would be Descriptor Like '*[?]*' ;D'

x +
Recalculating Geoid Heights

Q - I had to recalculate geoid heights..now what?

A - If you have had to recalculate geoid heights in GPSQL for some reason and now need to correct the local heights, do the following:

The fundamental height equation is

WGS84 Height = Local Height + Geoid Height

or

Local Height = WGS 84 Height ' Geoid Height

So, since your WGS84 heights are 'good' and now your Geoid Heights are good, you would select a query in GPSQL that isolates all records you want to update and make the [Postplot]`Local Height` field equivalent to the following expression:

[POSTPLOT].`WGS84 Height` - [POSTPLOT].`Geoid Height`');

x +
Finding swaths geographically

Q - Right now we use a query that selects swaths by defining two easting coordinates. Using this method we have to copy and paste coordinates in the query which is time consuming. Is there an easier way?

A - You say the swaths can be defined geographically by easting. If the swaths are all the same size (width-wise), you are in good shape. If not, read no further.

If your swaths can be defined by easting, you can make a grid definition file that you can use do define the swath a point is in. The graphic here shows a grid definition file in QuikMap where the track spacing was made large enough to incorporate a number of tracks of the actual project (i. e., a swath). As far as bins, one giant bin incorporating the entire project was defined. As you move around in the grid, all points in a swath have the same 're-binned' station number.

You would develop a similar grid definition file for your prospect and in GPSQL, re-bin. Obviously, elect not to change the station number but do add a 'Station (Rebinned)' field. Once done, you have a simple way to query for a swath by using this field. Note that since grid definition files can be oblique, this could be done for projects that were not oriented NS-EW. It does rely on equal swath widths however.

x +

GPSQL FAQs

Can barometers help with height?

With regard to processing data and the use of barometers in height aiding, I would like to say I'm an expert in this but I'm not. But I would like to state what I have discovered. I have looked at the specs of a couple of barometers and what seems to be the accuracy for these systems. The better instruments range around 0.15 to 0.20 hPA (which stands for hecto-Pascals and is also known as millibars).

There's about 1 hPa change in pressure for every 10 meters change in elevation. What I would therefore like to see in a system is an accuracy of about one or two tenths of that which is 0.10 to 0.20 hPa. So to me a system with these kinds of specs sure seems accurate enough to help in processing. Now, the way GPSQL works is you isolate by means of a query each separate GPS traverse. Once the barometric utility dialog is displayed, you select the pressure field and Local Height field. The utility looks for RTK phase shots to use as a 'calibration'. The best scenario that could happen is that each separate traverse starts with a phase shot, ends with one, and has some scattered through the middle of it. Since the phase shots act as a calibration, any system calibration error is removed, so I think you would end up computing heights with accuracies of about 1-1.5 meters and perhaps better if the operator did a good job taking phase shots when they could.

x +
Do you have training videos?

A - We have the following videos.

GPSQL - Overview (10MB)
GPSQL - Building Queries Part 1 (7.3MB)
GPSQL - Building Queries Part 2 (11.7MB)
GPSQL - Building Queries Part 3 (6.8MB)
GPSQL - Editing Using The Modern Spreadsheet (9.2MB)
GPSQL - Editing Using The Classic Spreadsheet (8.7MB)
GPSQL - Finding Duplicates (3.3MB)
GPSQL - Interpolating A Single Line (*.5MB)
GPSQL - Various Query Actions(8MB)
GPSQL - Rebinning (10MB)
GPSQL - Recalculating Offsets (7MB)
GPSQL - Editing Data Using The Update Dialog (10MB)
GPSQL - Creating A Seismic File Of Relative Locations Using A Vibe Template File (3.6MB)

x +
Query for DEM height difference

Q - I have computed DEM heights and now have a Postplot table DEM Height field. How can I write a query that finds large differences between local height and the DEM height?

A - You can copy and paste the query below. The first part is an alias that displays the difference, and the Where clause is an expression which is the difference between the two fields. Note the \'Abs\' keyword. This stands for absolute value and makes sure the evaluated difference is always positive. This is required because some of your differences will be positive and some will be negative.

Select [POSTPLOT].`Local Height` - [POSTPLOT].`DEM Height` AS `Difference`, [POSTPLOT].* From [POSTPLOT] Where (Abs([POSTPLOT].`Local Height`-[POSTPLOT].`DEM Height`))

In the query above, I've used '99' as the maximum difference to allow. You want to change this to your threshold difference, that is, that value that separates what you consider good and bad.

x +
Interpolation problems

Q - I'm having some trouble interpolating - every time I try to interpolate it deletes all the records.

A - Its impossible to tell you what the problem is without having your database and queries, but I will tell you what I think is going on because I have had some clients call me about this before.

My guess is that you have a query that isolates the interpolated points. I think you are interpolating and somehow this query finds the interpolated points. Most of the time, someone comes up with a query that looks for nothing in a particular field (Many fields of the interpolated records are blank).

I think that after interpolation, the points ARE inserted in the database. Maybe your query finds them but maybe it doesn't. Its easy enough to determine this by doing a count on that query after you interpolated. Assuming they are there and you then do your update and find that your query does not show them anymore, then I'd bet you that your query simply has criteria that requires nothing be in the 'Descriptor' field.

In other words, after the update, the records are there. Your query just excludes them, but you think they have been deleted. I've been fooled with this myself.

x +
Skid Report

Q - What I'm trying to do is output a offset report that shows direction to the point using 'N,S,E or W' rather than a positive or negative number.

Inline: a negative value will be an 'E' for east and display E15, a positive number will be 'W' for west and display W15. If the number is less than 10 I would like to show no values or direction.

Cross Line: a negative value would be 'S' for south and display S150, a positive number would be 'N' for north and display N150. Also any number less than 10 would be blank.

A - This is not for the faint at heart. Assuming a NS oriented prospect, copy and paste this query. It is complete and produces two additional alias fields you can use for reporting:

Select IIF (Abs([POSTPLOT].`Offset (Crossline)`) > 10,IIF ([POSTPLOT].`Offset (Crossline)`>= 0,"N","S"),"") & IIF (Abs([POSTPLOT].`Offset (Crossline)`) > 10,Int(Abs([POSTPLOT].`Offset (Crossline)`)),"") AS `OSNS`,IIF (Abs([POSTPLOT].`Offset (Inline)`) > 10,IIF ([POSTPLOT].`Offset (Inline)`>= 0,"W","E"),"") & IIF (Abs([POSTPLOT].`Offset (Inline)`) > 10,Int(Abs([POSTPLOT].`Offset (Inline)`)),"") AS `OSEW`,[POSTPLOT].* From [POSTPLOT] Where ([POSTPLOT].`Track` MOD 2<>0) And ([POSTPLOT].`Station (text)` Not Like '*x*') And ([POSTPLOT].`Station (text)` Not Like '*CHK*') And ([POSTPLOT].`Station (value)`>0)

A little complicated I know but effective. If you look closely, you can tweak the number for where you get blanks.

x +
Finding Along Line Height Spikes

You can find the difference between the field values of any two consecutive records including height. Create a query that has all records you want to examine. Then, select that query and choose 'Delta Values' from the Modifications menu. This action displays the field selection dialog. The default fields will difference the Local Height.

Once this utility does its thing, when you display your query, scroll all the way to the right and you will notice three new fields, 'Station Deltas', 'Previous Station' and 'Delta Value'. What the utility has done is ordered the query by station and then provided the difference between the heights of consecutive records. The 'Station Deltas' field is the difference between adjacent station values. If this number is large, it is probably just indicating a jump from one line to another so the corresponding 'Delta Value' should probably be ignored. You should be able to write a query for very large height differences using the 'Station Deltas' and 'Delta Values' .

x +

GPSQL FAQs

How do I find duplicates

Q - How can I look for duplicates in the database?'

A - Several ways. There is a report in the Report Builder that determines all duplicates based on a user defined key.

A feature is included in the spreadsheet display in which sorting by any field allows the user to launch a small dialog that lists all duplicates based on the sort key. A GOTO button allows the user to quickly move to any duplicate.

A 'Duplicates Manager' utility in the Modifications menu graphically depicts all duplicates for the currently selected query.

The 'Purge Duplicates' utility in the Modifications menu allows the user to find duplicates based on the exact same thing being in three selected fields. This utility is typically used for finding shots that are exact matches (for example, when a data collector file is accidentally loaded twice.)

Note that to find skips, the best way is to select two queries and send to separate layers of QuikMap. Once done the Compare item in the File menu is used.'

x +
How do I find nothing?

Q - How can I include a statement in my query in which I look for records where there is nothing in a particular field?

A - Cool problem. Use the Miscellaneous helper on the Query Building dialog to assist you in entering the correct syntax. It will end up being something like, 'MyField' not like *'

x +
What is a concurrency error?

Starting way back with the version 2004 spreadsheet control, there is a possibility with existing databases that when saving changes you will see a 'concurrency' error. The notes below were in the release notes, but basically, when you see this in a database, select the Table/Repair Times menu item in GPSQL. Choose the table and you will in all likelyhood be fine.

Here is an excerpt from the original release notes:

NOTE 1 - When saving changes in the spreadsheet, the parent database is first examined to see if any changes were made to it after the data was initially fetched from it. If there are changes, an error is displayed which says that a 'Concurrency violation' has occurred. The updates will not be made. For this reason, when editing data in the spreadsheet, it is important not to concurrently edit the database in a different application.

NOTE 2 - When developing this new component, it was noted that a concurrency error could occur even if the database had not been changed. We discovered that the error was occasionally caused by a Time field. Prior to this release, these fields were populated with a high precision time with decimal seconds. The new spreadsheet cannot work with this precision and truncates the decimal portion so that when the user attempts to save, the times appear to have changed and thus the concurrency error. For this reason, a GPSQL menu item which prompts the user for a table has been added. The Time fields in the selected table are converted to a suitable precision for use with the spreadsheet.

x +
Replacing records using a table

Q - Is there a way to Query 'Table A' to find records in 'Table B', that could be appended to 'Table A'. More specifically, what I'm trying to do is Query my POSTPLOT table for bad GPS. Based on the results of that query, I wish to pull all stations with the same Station Value out of a table that has Post Processed records and append them to my POSTPLOT table.

A - Lets say you have two tables, ALPHA and BETA. You want to write a query that selects specific records in ALPHA. You want to find the matching records in BETA and append these to ALPHA.

First, its would be nice if both tables have the same field structure. You can get around this but its easier if they are the same.

We'll add a field to both tables and call it 'RecordSource'. Let's make it string 16 characters long and initialize it with the letter 'A' for ALPHA and 'B' for BETA.

Create a table join query between ALPHA and BETA using the criteria that isolates your specific records from ALPHA. Using this query, select 'Update' from the Modifications menu and change the 'RecordSource' field for BETA to 'C'.

Write a query for BETA to find all 'C' records. Make sure this query is selected and use 'Append/Create Table (wo/mapping)' from Modifications menu to append to APLHA. If field structure not the same use 'Append/Create Table (w/mapping)'

x +
Query that finds several strings

Q - I want to write a query that find Descriptors with the letters 'A, C and D' and those Descriptors that have nothing. But there are 20 different descriptors and its getting really long and complicated.

A - You can accomplish what you want with the following:

Select [POSTPLOT].* From [POSTPLOT] Where ([POSTPLOT].`Descriptor` In ('A','C','D') Or [POSTPLOT].`Descriptor` Not Like '*')

The string helper on the query building dialog can help you by automatically constructing the 'In' criteria.

x +