OIR Fleming DB Home Page
FlemingDB (named after Williamina Fleming) offers access to modern all-sky astrometric, photometric, and spectroscopic databases such as Gaia, Tess Input Catalog, PanStarrs, SkyMapper, Rave, Lamost and more. A full list of all catalogs and how they are connected is in Appendix A. The database runs under Postgres and can be queried by various means, each of which will have a description, tutorials and examples:
- SQL queries using shell tools from any CF-managed machine; preferred for long running queries and
those returning > 10,000 rows - A Web tool running the database server named pgAdmin4, preferred for quick queries,
returning less rows, for syntax checks and optimizing long running queries - Any programming language being able to access Postgres databases (Python, Java,
IDL, C/C++ and many more). - FlemingDB is a living catalog, so please check the News
We intend FlemingDB to be a usefull tool for your research. We think we have a good start, but if you are missing anything, have questions or remarks, please email us under DBAdmin.
SQL Primer: querying a single table
SQL Primer: querying multiple tables at the same time
SQL Primer: Crossmatching your stars, part 1
SQL Primer: Importing your stars
dbplot: plotting query results
Python: accessing FlemingDB (read and write)
Python: crossmatching part 2, diagnostic plots
Python: Scanning over big tables
Appendix A: Schemas, Tables and Connections
News
2024/06/11: We added Skymapper DR4 (table: catalogs.skymapper4) to the database. Indices are object_id (primary key), (raj2000, dej2000), (glon, glat), u_psf, v_psf, g_psf, i_psf, r_psf, z_psf and gaia_dr3_id1 (closest Gaia star from DR3). Please let us know if you need other columns indexed. Please note that the table skymapper1 is obsolete and will be deleted within the next 2 or 3 weeks.
2024/05/28: After the upgrade FlemingDB is back up and running.
2024/05/20: Due to upgrading the operating system to Rocky 8 ITS is taking down fleming on May 28th. We will have to re-compile postgres and run some tests afterwards an hope to get it abck up on the same day.
2023/05/03: VST Atlas DR4 added to catalogs (table vst_atlas_dr4), indexed on sourceid, equatorial and galactic coordinates via q3c (use q3c_radia_query). LEt us know if you need other columns indexed.
2022/08/05: Table gaiadr3 indexed for galactic (l, b) and ecliptic (ecl_lon, ecl_lat) coordinates, enabling q3c_radial, elliptic and polygon queries with these coordinates.
2022/05/27: Gaia DR3 is available as gaia3.gaiadr3 now. Indices are on ra, dec (via q3c as usual), phot_g_mean_mag, phot_bp_mean_mag and phot_rp_mean_mag. Please let us know if you need other columns indexed for quick access. source_ids of DR2 and DR3 are connected by gaia3.dr2edr3. It seems cource_ids in DR3 and EDR3 are identical. Currently only the source table of DR3 is available. Please let us know if you need other tables as well.
2022/05/27: FlemingDB is back up and running.
2022/05/23: A new memory block has to be installed to fleming. FlemingDB will be down Friday morning, May 27th.
2022/04/11: FlemingDB is back up and running
2022/04/11: On Monday norning April 11th CF is updating the FlemingDB. We will be down during this time, but expect to be back up by lunch time.
2021/10/01: FlemingDB is back up and running.
2021/09/30: CF will reboot fleming tomorrow morning, 2021/10/01. Flemingdb will be back online during the morning.
2021/07/05: TIC users: the update to TIC 8.2 is complete. About 34 million stars will see updates of corrdinates, stellar characteristics and/or disposition and dupeid. The 8.1 values are available in tables c2000_81, ticlink81, ticvals81, ticaux81; these tables store only affected stars.
2021/05/10: pgAdmin4 updated to version 5.2. If you are using the "Explain" feature to check your SQL commands: you now have to press on a node to see what it is doing, the automatic popup is gone.
2021/01/07: Gaia EDR3 is now available as gaia3.gaiaedr3. Indexes are source_id and the Gaia magnitudes so far - please let us kno if you need other columns to be indexed (ruwe might be needed). In addition gaia3.dr2edr3 links Gaia 2 IDs (column dr2_source_id) to EDR3 IDs (column nhdr2edr3_idx). Please note that this is a neighbourhood table! Means: a DR2 ID can well have more than one DR3 ids associated to it. They come with an angular_distance column (in mas) and a magnitude_difference (mag), so you need to look for the closest match in terms of distance and brightness.
2020/12/28: After CF rebooted fleming on this day, FlemingDB is up and running again. Updating pgadmin4 has been cancelled due to broken updates. We stick with the 4.26.
2020/09/30: FlemingDB and pgAdmin4 are up and running.
2020/09/29: CF will upgrade fleming to the new operating system. We expect to get FlemingDB back up latest the day after.
2020/05/26: Reboot completed, FlemingDB is up and running.
2020/03/30: Tractor files of Legacy Survey DR8 added (table catalogs.ls8_tractor). Please read the Import Notes.
2020/03/02: The migration to Postgres 12 is complete. FlemingDB is up and running again. Please note that initially queries might take longer than usual because the memory buffers are all empty and need to be filled after a restart. In addition postgres "learns" from your queries and it has to learn again.
2020/02/24: tois_mit updated to sector 20.
2020/02/12: FlemingDB got more SSD-space and is up and running again. Please note that I took the opportunity to upgrade pgAdmin4 to the latest version. They slightly changed the look and exchanged some icons, but I think you will find your way around.
February 2020: We migrate FlemingDB from postgres10 to postgres12 which improves runtime for queries on and manaegement of big tables like gaiadr2. FlemingDB will be fully operable during this time, but you might notice that queries are slower at times.
2020/01/30: tois_mit updated with TESS sector 19 targes
2019/12/09: tois_mit updated with December version. dbplot added.
2019/12/05: fits-file importer fits2pg added
2019/11/20: text-file importer txt2pg now understands the format "t" as timestamp without timezone (like 2019-11-20 15:32:33.321, fractional seconds are optional). ticgaia.tois_mit table added: official list of TESS Targets of Interest (TOI) as published by MIT.
2019/10/15: Adding TESS Targerts of Interest (TOIs, table ticgaia.tois), adding observed TESS targets in 2-minute cadence (table: ticgaia.tess2minute)
2019/09/24: Adding Scanning over big tables and official start.
2019/09/10: Added anaconda3 to path and pythonpath in Setup your Account. Previous users: please adjust you settings.
