How to access spots that were entered into the database after my previous query.

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

How to access spots that were entered into the database after my previous query.

VK2AZ
Hi all,
I am developing a web application in PHP that queries the WSPR Daemon database every 6 mins to retrieve entries for a single station.

Is there a way to ensure that I only query the entries that were added since my last query?

I suppose I am looking for some sort of sequence number to be returned so I can use that on subsequent calls.

I assume that the date/time field will not be adequate for this because some spots may be delayed and I would miss them.

Any help or advice would be appreciated.

Regards

Hilary
VK2AZ
https://VKSpotter.com

--
Reply | Threaded
Open this post in threaded view
|

Re: How to access spots that were entered into the database after my previous query.

Gwyn
Administrator
Hello Hilary
Great to hear you are developing an App to access the database. I'm not personally competent in PHP so the following will be the general points. Rob is coming toward the end of major upgrades to hardware, so there may be consequent changes, but if so we will post them here.

1. the URL to use is logs1.wsprdaemon.org and the port is the default for postgresql 5432
2. the database name is wsprnet
3. the table is spots
4. the read-only userid is wdread for testing - email me at gwyn@autonomousanalytics.com for the password. I can also provide you with your own userid and password with read (select) rights.
5. Here, not very pretty, is the structure of the spots table - you will see Spotnum - which is what Rob gets from wsprnet.
                                            Table "public.spots"
    Column    |            Type             | Collation | Nullable |                 Default                  
--------------+-----------------------------+-----------+----------+------------------------------------------
 wd_time      | timestamp without time zone |           | not null |
 Spotnum      | bigint                      |           | not null | nextval('"spots_Spotnum_seq"'::regclass)
 Date         | integer                     |           | not null | 0
 Reporter     | text                        |           |          | NULL::bpchar
 ReporterGrid | character(6)                |           |          | NULL::bpchar
 dB           | smallint                    |           |          |
 MHz          | double precision            |           |          |
 CallSign     | text                        |           |          | NULL::bpchar
 Grid         | character(6)                |           |          | NULL::bpchar
 Power        | smallint                    |           |          |
 Drift        | smallint                    |           |          |
 distance     | smallint                    |           |          |
 azimuth      | smallint                    |           |          |
 Band         | smallint                    |           |          |
 version      | character(10)               |           |          | NULL::bpchar
 code         | smallint                    |           | not null | '0'::smallint
 wd_band      | text                        |           | not null |
 wd_c2_noise  | real                        |           |          |
 wd_rms_noise | real                        |           |          |
 wd_rx_az     | real                        |           |          |
 wd_rx_lat    | real                        |           |          |
 wd_rx_lon    | real                        |           |          |
 wd_tx_az     | real                        |           |          |
 wd_tx_lat    | real                        |           |          |
 wd_tx_lon    | real                        |           |          |
 wd_v_lat     | real                        |           |          |
 wd_v_lon     | real                        |           |          |

Let me know if you need any additional information. I will update the Timescale notes on this website once things are in steady-state.
best wishes
Gwyn G3ZIL
Reply | Threaded
Open this post in threaded view
|

Re: How to access spots that were entered into the database after my previous query.

VK2AZ
Thank you for the info Gwyn,
I will send you an email.

Is this a new database?
I don't see it mentioned anywhere else?

I have things working on the "test" database mentioned.

Still have to iron out a few things which seem to be specific to PHP.

If anyone else has working PHP code I would be interested in sharing experiences.

Regards

Hilary

--
Rob
Reply | Threaded
Open this post in threaded view
|

Re: How to access spots that were entered into the database after my previous query.

Rob
Hi Hilary,

This is a newly deployed server which has been authorized by wsprnet.org to clone the wsprnet.org spot database into a Timescale/Postgres DB running on to a high performance server.  That wsprdaemon.org spots database entries follow those in wsprnet.org by 30-60 seconds.

Phil VK7JJ has a version of his web page which uses our TS DB at: http://wsprd.vk7jj.com/
Also, Peter VK2TPM has a version of his ios application 'WSPR Watch' which uses our DB:  https://apps.apple.com/au/app/wspr-watch/id532487317

It is our hope that providing developers like you a full SQL API to the wsprnet data will enable new insights into radio transmission systems.

Rob


On Thu, Aug 20, 2020 at 1:21 AM VK2AZ [via wsprdaemon] <[hidden email]> wrote:
Thank you for the info Gwyn,
I will send you an email.

Is this a new database?
I don't see it mentioned anywhere else?

I have things working on the "test" database mentioned.

Still have to iron out a few things which seem to be specific to PHP.

If anyone else has working PHP code I would be interested in sharing experiences.

Regards

Hilary

--


To start a new topic under wsprdaemon, email [hidden email]
To unsubscribe from wsprdaemon, click here.
NAML


--
Rob Robinett
AI6VN
mobile: +1 650 218 8896