[Solved][JAVA] H2 Database help

netomx

Hi, I am using a software called Traccar, it is used to track GPS. Several chinese GPS are fixed to send positive longitude, so if I use them in Mexico, they will locate in China. The conf says:

        SELECT id, uniqueId as imei
        FROM devices;
    

    
    
        INSERT INTO positions (device_id, time, valid, latitude, longitude, altitude, speed, course, power, other)
        VALUES (:device_id, :time, :valid, :latitude, -:longitude, :altitude, :speed, :course, :power, :extended_info);
    

I placed the minus ( - ) on longitude, and now it works, but if the device sends the longitude correctly, it will reverse and send the longitude positive... how can I always make it negative? how can I use a variable there? Thanks

raindog308

It's easy to solve this if you want ALL longitudes to be negative:

• a trigger in the DB that checks if the longitude value is > 0 and if so, multiplies by -1
• check constraint to enforce
• or maybe only do the switch if the source is Chinese, etc.
• or code in your Java that does the same before calling SQL

...but presumably you want some pos and some neg, and I don't know how the DB would know which are which...?

netomx

raindog308 said: a trigger in the DB that checks if the longitude value is > 0 and if so, multiplies by -1

I knew I can do this on any other platform, but I don't know H2 nor Java

I did this:

SET @LONG = :longitude;
        CASE WHEN @LONG > 0 THEN @LONG = -@LONG END

I don't really know if this works on H2, and I am still waiting on the GPS to send a new coordinate to check if it worked or not

elgs

or ABS(?)*(-1)

AdamM

The H2 Google Group is pretty active and the committers will probably help you out. But, I think you need to work on forming your question a bit more clearly. I use H2 daily along with Java (it is my main DB) and I am not quite sure what you are exactly asking.

AdamM

Also, I try to avoid DB triggers, as they are always a little strange. If you can swap +/- in application code, as opposed to a db trigger, I think you will have luck.

netomx

@elgs said:
or ABS(?)*(-1)

This worked:

        INSERT INTO positions (device_id, time, valid, latitude, longitude, altitude, speed, course, power, other)
        VALUES (:device_id, :time, :valid, :latitude, ABS(:longitude)*(-1), :altitude, :speed, :course, :power, :extended_info);
    

Thank you!!