Request for information about DB and API

IU0PHY · 5 April 2022 08:19

Hello everybody,

I was reading some data from the various activations on SOTA database, when it came to my mind to check, as regards my Association, the number of activators and to make a statistic of how many can still be considered “active”.

I found this page:

https://www.sotadata.org.uk/en/roll/activator/uniques

From which I can extract part of the information I need, but then for each OM I should necessarily go and retrieve the details of the activations, to understand when the last activation was made.

So I wondered if there is a programmatic way to do these operations, I started looking for information on any available API, but I only found this Swagger:

https://api2.sota.org.uk/docs/index.html

Very useful and well documented, but there are no methods exposed to be able to ask the questions I indicated earlier.

Do you know if there are other Swagger files, or just other methods to query the DB programmatically and get the information I need?

73 de IU0PHY (ex IW0GTF)

MW0PJE · 5 April 2022 09:04

You can filter the activator unique table by year and see who has been active this year, last year etc. Does that give you the information you want?

IU0PHY · 5 April 2022 19:19

Yes this is a possible solutions, but I want aggregate the data, for example as show in the picture below, in this case get and work all data manually is a very hard work.

HB9DQM · 6 April 2022 06:11

To get the list of activators in the Italian association as JSON:

https://api-db.sota.org.uk/admin/activator_roll?associationID=54

To obtain a list of all activations of an activator given their UserID (from the response of the former API call):

https://api-db.sota.org.uk/admin/activator_log_by_id?year=all&id=5008

VK3ARR · 6 April 2022 06:42

Please don’t do this for all activators at once. I guess you want last activation date of all Italian activators?

| I1ABT    | 2013-12-08 00:00:00 |
| I1KKZ    | 2021-04-03 00:00:00 |
| I1LSN    | 2018-10-03 00:00:00 |
| I1URL    | 2021-10-17 00:00:00 |
| I1UWF    | 2020-09-05 00:00:00 |
| I1WKN    | 2022-03-27 00:00:00 |
| I1YDT    | 2016-07-10 00:00:00 |
| I2IAL    | 2013-07-21 00:00:00 |
| I5EKX    | 2022-03-27 00:00:00 |
| IK1PFW   | 2014-09-21 00:00:00 |
| IK1RAC   | 2022-03-20 00:00:00 |
| ik1sow   | 2021-08-09 00:00:00 |
| IK1TNU   | 2019-02-23 00:00:00 |
| IK1TNU/P | 2022-01-22 00:00:00 |
| IK1VCI   | 2019-03-03 00:00:00 |
| IK1VQO   | 2022-03-03 00:00:00 |
| ik1weg   | 2022-02-27 00:00:00 |
| IK1XPP   | 2020-11-01 00:00:00 |
| IK1YRA   | 2019-05-26 00:00:00 |
| IK1ZYW   | 2012-07-26 00:00:00 |
| IK2CFD   | 2020-08-23 00:00:00 |
| IK2DED   | 2017-10-14 00:00:00 |
| IK2LEY   | 2022-04-03 00:00:00 |
| IK2WQH   | 2021-02-27 00:00:00 |
| IK3ITV   | 2022-02-02 00:00:00 |
| IK5MEL   | 2022-03-23 00:00:00 |
| IK5PWQ   | 2020-08-20 00:00:00 |
| IK5QPS   | 2020-08-20 00:00:00 |
| IK5XCT   | 2019-08-29 00:00:00 |
| IK6BAK   | 2022-02-24 00:00:00 |
| IK6PRN   | 2021-04-25 00:00:00 |
| IK8WCQ   | 2019-06-30 00:00:00 |
| IN3ADF   | 2022-04-03 00:00:00 |
| IN3AQK   | 2021-05-30 00:00:00 |
| IN3CVK   | 2019-08-03 00:00:00 |
| IN3DOV   | 2013-08-02 00:00:00 |
| IN3EBX   | 2020-07-31 00:00:00 |
| IN3EBZ   | 2020-09-19 00:00:00 |
| IN3ECI   | 2015-09-27 00:00:00 |
| IN3ENN   | 2022-01-23 00:00:00 |
| IN3EQL   | 2013-08-17 00:00:00 |
| IN3EYY   | 2021-12-28 00:00:00 |
| IN3IJJ   | 2021-10-03 00:00:00 |
| IN3PEE   | 2022-01-09 00:00:00 |
| IN3ZWF   | 2021-09-25 00:00:00 |
| IQ1TO    | 2022-03-20 00:00:00 |
| IQ8UW    | 2017-09-17 00:00:00 |
| IT9HNQ   | 2022-01-02 00:00:00 |
| IT9JGX   | 2020-09-19 00:00:00 |
| IT9PWM   | 2017-10-16 00:00:00 |
| IU0KTT   | 2022-03-20 00:00:00 |
| IU1AGT   | 2018-02-10 00:00:00 |
| IU1AUG   | 2021-10-22 00:00:00 |
| IU1AUV   | 2017-12-31 00:00:00 |
| IU1BXD   | 2018-07-08 00:00:00 |
| IU1DOF   | 2022-01-22 00:00:00 |
| IU1IAZ   | 2021-08-28 00:00:00 |
| IU1KGS   | 2022-01-06 00:00:00 |
| IU1LCP   | 2022-03-13 00:00:00 |
| IU1LGB   | 2020-07-26 00:00:00 |
| IU1MRY   | 2020-07-22 00:00:00 |
| IU1NLV   | 2021-08-13 00:00:00 |
| IU1OQD   | 2021-09-02 00:00:00 |
| IU1PZJ   | 2022-03-13 00:00:00 |
| IU2EBM   | 2018-01-20 00:00:00 |
| IU2HEE   | 2021-12-28 00:00:00 |
| IU2IGX   | 2020-09-19 00:00:00 |
| IU2IJW   | 2022-02-19 00:00:00 |
| IU2INW   | 2020-01-01 00:00:00 |
| IU2OZE   | 2022-01-02 00:00:00 |
| IU3BPW   | 2015-09-20 00:00:00 |
| IU3DHU   | 2021-08-12 00:00:00 |
| IU3EGX   | 2017-08-30 00:00:00 |
| IU3ETL   | 2015-05-02 00:00:00 |
| IU3GMW   | 2021-07-18 00:00:00 |
| IU3OJA   | 2022-02-26 00:00:00 |
| IU3QEZ   | 2022-04-03 00:00:00 |
| IU4AAJ   | 2021-07-24 00:00:00 |
| IU4FLP   | 2021-08-17 00:00:00 |
| iu4jru   | 2021-07-24 00:00:00 |
| IU5BON   | 2022-03-20 00:00:00 |
| IU5KHP   | 2021-06-04 00:00:00 |
| IU8DME   | 2016-07-03 00:00:00 |
| IV3GVY   | 2022-01-30 00:00:00 |
| iv3hij   | 2021-10-31 00:00:00 |
| IV3JLK   | 2017-07-22 00:00:00 |
| IV3NGF   | 2022-03-27 00:00:00 |
| IV3RJH   | 2015-06-18 00:00:00 |
| IV3XMJ   | 2018-08-15 00:00:00 |
| IV3ZNK   | 2017-03-31 00:00:00 |
| IW0HK    | 2022-03-24 00:00:00 |
| iw0hle   | 2022-03-26 00:00:00 |
| IW1ARE   | 2014-05-01 00:00:00 |
| IW1AU    | 2014-05-10 00:00:00 |
| IW1QIF   | 2018-07-22 00:00:00 |
| IW1QLH   | 2020-09-01 00:00:00 |
| IW1QQD   | 2012-09-19 00:00:00 |
| IW2CZW   | 2021-07-18 00:00:00 |
| IW2NEF   | 2015-09-26 00:00:00 |
| IW2NHE   | 2022-01-23 00:00:00 |
| IW2NRI   | 2017-11-18 00:00:00 |
| IW2OBX   | 2022-01-16 00:00:00 |
| IW2OGY   | 2022-03-31 00:00:00 |
| IW3AGO   | 2022-03-27 00:00:00 |
| IW3BS0   | 2021-06-21 00:00:00 |
| IW3IMM   | 2021-09-30 00:00:00 |
| IW3RPY   | 2017-02-18 00:00:00 |
| IW5CWC   | 2021-10-16 00:00:00 |
| IX1DHM   | 2021-10-28 00:00:00 |
| IX1IHR   | 2022-03-23 00:00:00 |
| IZ0ETE   | 2022-03-29 00:00:00 |
| iz0fyl   | 2022-03-20 00:00:00 |
| IZ0WRS   | 2020-11-15 00:00:00 |
| IZ1AZA   | 2022-02-08 00:00:00 |
| iz1bla   | 2022-03-03 00:00:00 |
| iz1bpn   | 2021-09-19 00:00:00 |
| IZ1DNQ   | 2019-09-29 00:00:00 |
| IZ1ERT   | 2013-09-15 00:00:00 |
| IZ1FUM   | 2020-08-22 00:00:00 |
| IZ1GDB   | 2022-03-20 00:00:00 |
| IZ1KSW   | 2019-01-13 00:00:00 |
| IZ1MHN   | 2011-12-02 00:00:00 |
| IZ1OQU   | 2017-10-22 00:00:00 |
| IZ1RFD   | 2021-03-14 00:00:00 |
| IZ1TRK   | 2018-08-26 00:00:00 |
| IZ1TTR   | 2018-07-22 00:00:00 |
| IZ1TWC   | 2020-11-01 00:00:00 |
| iz1uln   | 2021-07-25 00:00:00 |
| IZ1UMJ   | 2022-03-27 00:00:00 |
| IZ1ZJO   | 2021-10-16 00:00:00 |
| IZ2DQB   | 2014-09-28 00:00:00 |
| IZ2FEB   | 2020-10-17 00:00:00 |
| IZ2FNJ   | 2021-10-17 00:00:00 |
| IZ2JNN   | 2021-02-27 00:00:00 |
| IZ2QGF   | 2022-03-20 00:00:00 |
| IZ2VCF   | 2011-11-12 00:00:00 |
| IZ2YWI   | 2016-11-19 00:00:00 |
| IZ3GME   | 2020-09-19 00:00:00 |
| IZ3GOS   | 2022-02-03 00:00:00 |
| IZ3NVR   | 2022-04-04 00:00:00 |
| IZ3WEU   | 2015-05-10 00:00:00 |
| IZ3XAC   | 2017-06-10 00:00:00 |
| IZ3ZQT   | 2017-06-10 00:00:00 |
| IZ4VQS   | 2022-03-26 00:00:00 |
| IZ5GHD   | 2014-05-18 00:00:00 |
| IZ8EWD   | 2021-08-08 00:00:00 |

VK3ARR · 6 April 2022 06:46

Latest logged chase for users in the I association

| I1ABT     | 2017-08-03 00:00:00 |
| I1NOL     | 2022-01-29 00:00:00 |
| I1WKN     | 2022-03-27 00:00:00 |
| I1YDT     | 2016-07-08 00:00:00 |
| I2CZQ     | 2019-01-18 00:00:00 |
| I2IAL     | 2022-03-18 00:00:00 |
| IK0NOJ    | 2021-10-04 00:00:00 |
| IK1GPG    | 2011-11-01 00:00:00 |
| IK1RAC    | 2022-03-20 00:00:00 |
| ik1sow    | 2021-01-11 00:00:00 |
| IK1VQO    | 2022-03-13 00:00:00 |
| ik1weg    | 2022-03-27 00:00:00 |
| IK1XPP    | 2020-11-01 00:00:00 |
| IK1YRA    | 2019-10-10 00:00:00 |
| IK2CFD    | 2021-10-03 00:00:00 |
| IK2LEY    | 2022-04-03 00:00:00 |
| IK2WQH    | 2021-10-23 00:00:00 |
| IK3DRO    | 2022-03-26 00:00:00 |
| IK4DRY    | 2015-10-05 00:00:00 |
| IK5MEL    | 2022-03-23 00:00:00 |
| IK5QPS    | 2018-06-19 00:00:00 |
| IK6BAK    | 2022-02-24 00:00:00 |
| IN3ADF    | 2022-04-03 00:00:00 |
| IN3AQK    | 2021-10-10 00:00:00 |
| IN3CVK    | 2019-08-03 00:00:00 |
| IN3DOV    | 2013-07-30 00:00:00 |
| IN3EBX    | 2020-09-09 00:00:00 |
| IN3EBZ    | 2021-10-31 00:00:00 |
| IN3ENN    | 2022-02-13 00:00:00 |
| IN3EQL    | 2013-06-11 00:00:00 |
| IN3EYY    | 2022-03-20 00:00:00 |
| IN3GIM    | 2016-08-07 00:00:00 |
| IN3IJJ    | 2021-10-14 00:00:00 |
| IN3NJB    | 2018-01-01 00:00:00 |
| IN3PEE    | 2013-09-15 00:00:00 |
| IN3ZWF    | 2022-03-22 00:00:00 |
| IQ1TO     | 2022-01-30 00:00:00 |
| it9cdu    | 2022-01-08 00:00:00 |
| IT9CHU    | 2011-03-26 00:00:00 |
| IT9ETC    | 2022-01-02 00:00:00 |
| IT9HNQ    | 2022-01-02 00:00:00 |
| IT9PWM    | 2017-03-27 00:00:00 |
| IU0ITX    | 2022-03-26 00:00:00 |
| IU0JZN    | 2019-08-17 00:00:00 |
| IU0KTT    | 2022-02-27 00:00:00 |
| IU1AUG    | 2021-11-21 00:00:00 |
| IU1AUV    | 2016-01-10 00:00:00 |
| IU1DOF    | 2022-01-22 00:00:00 |
| IU1JRA    | 2021-09-02 00:00:00 |
| IU1KGS    | 2022-02-26 00:00:00 |
| IU1LCP    | 2022-03-13 00:00:00 |
| IU1LGB    | 2020-07-26 00:00:00 |
| IU1MRY    | 2020-07-22 00:00:00 |
| IU1NLV    | 2021-12-19 00:00:00 |
| IU1PZJ    | 2022-03-13 00:00:00 |
| IU2HEE    | 2022-02-12 00:00:00 |
| IU2IGX    | 2018-09-15 00:00:00 |
| IU2IJW    | 2022-02-12 00:00:00 |
| IU2OZE    | 2021-07-02 00:00:00 |
| IU3DHU    | 2021-09-12 00:00:00 |
| IU3GMW    | 2021-07-18 00:00:00 |
| IU3QEZ    | 2022-04-03 00:00:00 |
| IU4FLP    | 2021-10-30 00:00:00 |
| iu4jru    | 2022-01-16 00:00:00 |
| IU5BLZ    | 2015-05-01 00:00:00 |
| IU5BON    | 2021-08-09 00:00:00 |
| IU5KHP    | 2021-08-04 00:00:00 |
| IU8DME    | 2016-07-03 00:00:00 |
| iv3fpx    | 2021-08-13 00:00:00 |
| iv3gwx    | 2021-06-18 00:00:00 |
| IV3NGF    | 2022-03-27 00:00:00 |
| IV3RJH    | 2012-10-21 00:00:00 |
| IW0HK     | 2022-04-03 00:00:00 |
| IW1ARE    | 2011-08-15 00:00:00 |
| IW1AU     | 2014-05-10 00:00:00 |
| IW2CZW    | 2021-07-18 00:00:00 |
| IW2OBX    | 2022-03-13 00:00:00 |
| IW2OGY    | 2022-02-19 00:00:00 |
| IW3AGO    | 2022-04-05 00:00:00 |
| IW3BS0    | 2022-03-10 00:00:00 |
| IW3IMM    | 2019-09-15 00:00:00 |
| IW5CWC    | 2022-01-30 00:00:00 |
| IX1IHR    | 2022-04-05 00:00:00 |
| IZ0ETE    | 2022-03-30 00:00:00 |
| IZ0WRS    | 2017-10-13 00:00:00 |
| IZ1AZA    | 2019-07-07 00:00:00 |
| iz1bla    | 2021-12-29 00:00:00 |
| iz1bpn    | 2021-09-19 00:00:00 |
| IZ1DNQ    | 2019-09-15 00:00:00 |
| IZ1GDB    | 2022-03-20 00:00:00 |
| IZ1HVD    | 2022-03-13 00:00:00 |
| IZ1KSW    | 2018-11-03 00:00:00 |
| IZ1OQU    | 2017-08-20 00:00:00 |
| IZ1TRK    | 2021-06-15 00:00:00 |
| IZ1TTR    | 2019-08-06 00:00:00 |
| IZ1TWC    | 2021-10-27 00:00:00 |
| iz1uln    | 2021-07-11 00:00:00 |
| IZ1UMJ    | 2022-01-23 00:00:00 |
| IZ1YUX    | 2021-09-05 00:00:00 |
| IZ1ZJO    | 2021-10-23 00:00:00 |
| IZ2DQB    | 2015-03-01 00:00:00 |
| IZ2FEB    | 2018-09-29 00:00:00 |
| IZ2FNJ    | 2021-12-11 00:00:00 |
| IZ2JNN    | 2021-02-28 00:00:00 |
| IZ2QGF    | 2022-03-20 00:00:00 |
| IZ2YWI    | 2016-08-03 00:00:00 |
| IZ3GME    | 2022-01-30 00:00:00 |
| IZ3NVR    | 2022-04-04 00:00:00 |
| IZ3WEU    | 2012-07-22 00:00:00 |
| IZ3ZQT    | 2017-06-10 00:00:00 |
| IZ8EWD    | 2021-07-29 00:00:00 |
| iz8fav    | 2022-03-21 00:00:00 |

IU0PHY · 7 April 2022 07:42

Is there any documentation for these methods?

VK3ARR · 7 April 2022 09:15

There is but it’s a private API that I reserve the right to change at any moment. Tell me what you want and I will run the query.

IU0PHY · 7 April 2022 17:38

Thanks @VK3ARR, mine was just curiosity, not a necessity so I can easily do without it.

I will take the time to organize the next SOTA activation, which is better!

IK5VYZ · 4 July 2023 13:55

Hi Andrew @VK3ARR,

using the API how can I retrieve the history of the activators for each single reference of my I/TO-* region?

I am the Tuscany SOTA Manager and this information would be useful for engaging colleagues in the activation of new references.

I would do one GET per day no more.

73, Claudio IK5VYZ

M5RJC · 4 July 2023 19:17

Hi

You can also do this via SOTLAS : https://sotl.as/summits/I/TO

Rick

IK5VYZ · 4 July 2023 19:39

Hi Rick,

Thanks for the reply. This is a solution but, I think, that this way I should use the screen scraper method, while it would be more practical to fetch the data via API using Python code and then feed a web page, which will be updated every day.

VK3ARR · 5 July 2023 08:55

api2.sota.org.uk/api/regions/I/TO should get you most of the way. API-DB is undergoing major changes so I won’t point you at that for now.

IK5VYZ · 5 July 2023 09:16

Thanks Andrew ,

I’m doing some testing right now with that API2 endpoint.

Thanks for all the work you do and very valuable and appreciated by the entire SOTA community

IK5VYZ · 5 July 2023 09:29

https://api2.sota.org.uk/api/summits/I/TO-010

Maybe I’m misinterpreting the result.
This reference has 5 activations but activationCount is null

VK3ARR · 5 July 2023 09:52

Not every call populates every field. If it’s not need for display then it typically doesn’t get filled in. Eventually it’ll get rewritten and things like this will get adjusted out.

IK5VYZ · 18 April 2024 07:59

Hello everybody,

how can I retrieve the callsign and date of the first activation of a summit?

73

G8ADD · 18 April 2024 08:10

This information is on each individual summit page.

IK5VYZ · 18 April 2024 08:37

I’m sorry for not explaining my question well.

I meant to retrieve that information with a script, using APIs if possible.

MM0FMF · 18 April 2024 08:40

No it’s not possible Claudio. We don’t store the information so when we display the information for a summit we find all the activators and sort on time/date of activation to show who was first. To return all the first activators would require all 150000+ summits to be considered and would need 150000+ scans of the entire activation table (several million records) and 150000+ sorts of the data.