Nested formula in Libre Base query HSQLDB

[cyclops053990]

I can use a formula in a calc spreadsheet that shows the distance between a fixed location and other locations using fixed gps for one and the gps for the others as i copy the formula into the relevant cells.
I have tried to use the following modified formula in a query with no success. The actual figures are the fixed location with the references to the other locations in the relevant columns
=SQRT(((GPS Long - 35.874729)^2)+((GPS Lat - 115.956564)^2))*100
First is this possible and is there a syntax error that will fix it if it is

Thank you

[Wanderer]

@cyclops053990

I have tried to use the following modified formula in a query with no success

Rewrite your function using POWER() and you may need to use quotes around GPS Long if you really have Spaces in your column names in SQL. I use _ instead of space instead.

https://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_numeric_functions

[cpb]

@cyclops053990,

not sure whether you have a stand alone hsqldb embedded database or are connecting to a spreadsheet.
.
take a look at the attachment (hsqldb embedded database).
.
GPS.odb (13.8 KB)

[cyclops053990]

Sorry HSQLDB Embedded

[cyclops053990]

The first query you write keeps generating a syntax error. Cannot seem to correct it.
I also have not done trig since I left school so it will take me a little while to understand what you are suggesting.
The larger one has a couple of letters in it I don’t understand; the letter g is added to the query as is the letter f. Are they functions or what?
I’ll keep going through your Queries until I understand them and can apply them.
Will take a while since I have a more pressing thing to take care of for a few days.
Thanks for your help

Wanderer; I will need to examine the 'POWER" function to apply it. Thanks

[cpb]

@cyclops053990

The first query you write

please reference objects by name, the first query will vary according to sort order.

@cyclops053990

keeps generating a syntax error

it absolutely does not, you must have inadvertently altered something, saved the query and then saved the database.

@cyclops053990

have not done trig since I left school

totally irrelevant.
I spent a few minutes googling, found a number of formulas, tested a few and made my choice based on accuracy.
.
you have not provided a single piece of detail as regards your database:
table name, field names, field types.
a copy of the SQL used in your query.
.
I live in the UK and therefore used UK locations along with their coordinates.
fixed location was ‘Newcastle upon Tyne’, latitude = 54.966667, longitude = -1.600000.
I verified my results here: Distance Calculator
.
I also tested your formula in my query, the results were wildly inaccurate which infers that your spreadsheet is also pushing out garbage.
.
your topic is not complex:
just 3 relevant fields “Location”, “Latitude” and “Longitude”.
if you are unable to integrate the query “qAirMiles” into your db then upload your database.

--qAirMiles
--literal coords are for Newcastle
--shows air miles from Newcastle to all destinations
select
	"Location"
	,
	acos((sin(radians(54.966667)) * sin(radians("Latitude"))) + (cos(radians(54.966667)) *
	cos(radians("Latitude"))) * (cos(radians("Longitude") - radians(-1.600000)))) * 6371 km
	,
	acos((sin(radians(54.966667)) * sin(radians("Latitude"))) + (cos(radians(54.966667)) *
	cos(radians("Latitude"))) * (cos(radians("Longitude") - radians(-1.600000)))) * 3958.8 miles
	,
	sqrt(power(54.966667 - "Latitude",2) + power(-1.6 - "Longitude",2)) * 100 users_formula
from
	"tGPS"
order by km