Investigate OGC-FID in fires tables for usage in ESRI products

What

Question about identifiers in openveda.cloud/api/features fires* tables:

we used to output an ogc-fid. What happened to that column?. What happened to that column?
I am asking because as we try to work with ESRI products it seems to be looking for a numerical ID, and I was wondering if we could just use that one.
It's possible we replaced it with the primary key

AC

identify appropriate numerical ID in openveda.cloud/api/features eis_fires* tables for use in ESRI products
stretch: if possible, identify previous usage of an OGC FID and why it is no longer present in the current tables

@smohiudd

cc: @smohiudd @briannapagan @freitagb

I am having trouble manually connecting to the private database to get better metrics on the magnitude of change but here are some notes

Existing tables that need to be fixed in place or copied to new tables with OGC FID indexing

Have varchar primarykey (need to be updated for ESRI to use these)

public.eis_fire_lf_fireline_nrt
public.eis_fire_snapshot_fireline_nrt
public.eis_fire_snapshot_newfirepix_nrt
public.eis_fire_lf_newfirepix_nrt
public.eis_fire_lf_perimeter_nrt
public.eis_fire_snapshot_perimeter_nrt

pg_temp.eis_fire_lf_perimeter_nrt_latest (custom view of public.eis_fire_snapshot_perimeter_nrt that is used by the fires tool--we may need to update the tool to point to the base table and remove this view)

Have ogc_fid numeric fields (and should work in ArcGIS no change required)

public.eis_fire_lf_perimeter_archive
public.eis_fire_lf_newfirepix_archive
public.eis_fire_lf_fireline_archive

Airflow patterns that need to be updated

Custom index applied in airflow

https://github.com/NASA-IMPACT/veda-data-airflow/blob/b05037c1ae532fa16da310ab319a1a032b03c5cc/dags/veda_data_pipeline/utils/vector_ingest/handler.py#L185-L192

       f"ALTER table eis_fire_{collection} "
       f"ALTER COLUMN t TYPE TIMESTAMP USING t::timestamp without time zone; "
       f"CREATE INDEX IF NOT EXISTS idx_eis_fire_{collection}_datetime ON eis_fire_{collection}(t);"
       f"CREATE INDEX IF NOT EXISTS idx_eis_fire_{collection}_primarykey ON eis_fire_{collection}(primarykey);"
       f"CREATE INDEX IF NOT EXISTS idx_eis_fire_{collection}_region ON eis_fire_{collection}(region);"

Also the EIS vectors are not using the same ogr to ogr methods as the default vector
https://github.com/NASA-IMPACT/veda-data-airflow/blob/b05037c1ae532fa16da310ab319a1a032b03c5cc/dags/veda_data_pipeline/utils/vector_ingest/handler.py#L143-L201

Hey folks, I was curious if there is either progress on this issue or if the Fire Science teams can help? I ask because switching to a numeric unique ID and publishing a feature service is the gatekeeper to a lot of higher profile Esri-based engagements right now (Operational stakeholders, Living Atlas etc).

The code itself looks pretty simple to change -- but I realize there might be continuity issues with the existing database. What makes this a tricky problem and can we help?

@mccabete

Hey @mccabete, we're currently working through this. The overall changes are relatively straightforward but there are couple things we're currently working through:

making changes to a production database is delicate work. The database is private which means access is more restricted and involved
we'll need to make changes to the ingestion handler to include a numeric id going forward or else new ingestions won't match the updated schema in the database

A question we have for your team is do you still need the string concatenated primary key (example: BorealNA|1002.0|2025-02-23T00:00:00) ?

The primary key is a nice to have, but not a need to have.

We are comfortable dropping it.

Update

For testing ESRI integrations, I have loaded a single recent geopackage file into a new table in our dev instance using the shared ogr2ogr vector ingest DAG. The new table has a numeric FID as well as the varchar concatenated column named primarykey but the new table is not indexed on that column (it probably can be if needed).

Dev features api: https://dev.openveda.cloud/api/features
New collection: public.test3_lf_perimeter_nrt
Sample items in viewer: https://dev.openveda.cloud/api/features/collections/public.test3_lf_perimeter_nrt/items?limit=100
Source geopackage: 2025-09-23 copy of lf_perimeter_nrt_conus.gpkg

Airflow config

veda_vector_ingest DAG config

{
  "collection": "test3_lf_perimeter_nrt",
  "prefix": "test_fire_data/",
  "bucket": "veda-data-store-staging",
  "filename_regex": ".*lf_perimeter_nrt_conus(.*).gpkg",
  "vector": true,
  "invalidate_cloudfront": true,
	"source_projection": "EPSG:9311",
	"target_projection": "EPSG:3857",
  "extra_flags": [
    "-nlt",
    "POLYGON"
  ]
}

ogr2ogr command (inferred)
ogr2ogr -f PostgreSQL <db connection string> <filename.gkpg> -nln test3_lf_perimeter_nrt -s_srs EPSG:9311 -t_srs EPSG:3857 -nlt POLYGON

Caveat
The current custom ingest uses a different method to load data and explicitly sets some indices. We may need to review what indices are set by default in the new tables and make adjustments.

QGIS attributes of a lf perimeter nrt gkpg

Note that some of these fields seem to be empty/no longer used. We can supply additional ogr2ogr commands to filter which columns are included (i.e. -select "column1,column2,column3")

Next short-term steps

Evaluate if the ID or FID column in this new table is suitable for ESRI integration
Review the ogr2ogr command and add any adjustments needed for usability
Ingest additional test tables for the other table types for integration testing as needed
Continue pursuing a method to modify the tables that do not have a FID -OR- to generate new copies of these tables with the appropriate fields/identifiers and the existing data.

@anayeaye

@anayeaye I think you actually got this to a point where you needed me to test this, but then the database integration and subsequent break took over my mental bandwidth. Could you remind me the testing you needed from me?

@botanical

@botanical, just FYI, this older issue is related to the firesdb support work. I think I can repeat my original test in staging instead of dev to get @mccabete something to evaluate FIDs with EGIS. Beyond this testing, we can open new issues to plan the work which could be a Science Support collaboration.

I just confirmed that the accessible rds clone is associated the staging features api here #430 (comment)

@mccabete

@mccabete I copied a new lf perimeter conus nrt gkpg file to a test directory and then re-ran the test above in staging. This is the database with a read replica accessible to you and the EGIS team, I can get you the connection parameters. This is a single file ingest so we don't yet know about continuously appending collections using the generic vector ingest pipeline--but you will have FIDs to work with:

https://staging.openveda.cloud/api/features/collections/public.test3_lf_perimeter_nrt/items

@anayeaye

@anayeaye Good news! (Bad news for planning) using the test example you set up it does look like collections with a numeric ID can be published as Esri feature layers. Which I think means the next step is to see if we can set up a version of the DB that has a numeric id column.

The layer is private to folks outside of EGIS, but this is the successfully published test feature service

@anayeaye

Paraphrasing a conversation @anayeaye and I had on slack: She asked me

"Would it be ok (from the science side)to break collections into specific regions?"

Here is what I hear from the science team:

How would this affect usability? We were concerned about pushing more complexity onto our users. One specific use case was "would people be able to query by a bounding box of North America, or would they need to know each region within North America?".
What are the actual trade-offs with performence? We don't really see that side of it, so we don't have a great idea of what we'd be facilitating with this change. Do we know the source of the slowdowns as they are?
Finally, I heard that if we want to experiment with just one region, CONUS is a region that we need for 80% of our current biggest use cases, so if we were to get one region optimized it would be CONUS.

@zebbecker linking you to this thread in case you want to weigh in on API performance issues.

I'm glad to hear the numeric identifiers generated using our generic/shared TiPg vector ingest DAG worked for the test! Those questions make sense but I would like to clarify what I was speculating on

My question regards how we currently coalesce year to date data from multiple geopackage files into a single nrt collection, perimeters for example:

Each new geopackage contains all perimeters to date, not just new detections, so we are loading the entire year to date table over and over
We are loading several regions into one collection (RDS table)
These files could be delivered and loaded in different order so we cannot assume that each perimeter will have the same identifier (perhaps conus is loaded first in the morning and gets rows 0-5000, borealna is loaded second and gets 5001-end...but then the second set of files are delivered in the opposite order and rows 0-6000 are borealna perimeters). I am pretty sure this is why the varchar primary key was created so that we can guarantee that every perimeter will have the same ID all year.

So my question is whether it would be OK to create one features-api collection for each region that currently delivered within a single goepackage file? As in:
Current collection eis_fire_lf_perimeter_nrt -->

eis_fire_lf_perimeter_conus
eis_fire_lf_perimeter_borealna
eis_fire_lf_perimeter_russiaeast
eis_fire_lf_perimeter_{new region} ...

If the geopackage perimeter order is consistent for the year then we could drop the varchar primary key solution and instead just use the ogr2ogr table load to create the same 0-X identifiers. I think this break down by region would make it easier to add new regions and would also provide some natural partitioning by reducing the overall table sizes.

One collection per region pros:

removes need for varchar persistent id
slighly improved search performance (searching a smaller table containing only one region)
easy to add/schedule new regions
little to no airflow DAG changes needed (all I can think is that we may want a temporary modification to load the data into both the combo varchar and a new single region table)

One collection per region cons:

users who want to collect perimeters from more than one region will need to make one search request per region table
more tables (not necessarily a con)

NOTE!
These are thoughts about how we could move forward with the existing airflow DAGs and EIS FEDS files delivered today. I think a better solution could also include working with algorithm support to begin maintaining a persistent ID before the geopackage files are generated and/or to only deliver new detections to append to the existing tables.

RE Science team questions:

How would this affect usability? We were concerned about pushing more complexity onto our users. One specific use case was "would people be able to query by a bounding box of North America, or would they need to know each region within North America?".

I think users would not need specific geometries but they would need to make more than one search request if they want CONUS and Russia East results.

What are the actual trade-offs with performence? We don't really see that side of it, so we don't have a great idea of what we'd be facilitating with this change. Do we know the source of the slowdowns as they are?

I think the slowdowns are mostly related to the large record count and complex geometries. These are probably issues that will need to be addressed over time in the TiPg/OGC feature server design over time.

Finally, I heard that if we want to experiment with just one region, CONUS is a region that we need for 80% of our current biggest use cases, so if we were to get one region optimized it would be CONUS.

I am not actually sure what the divisions are on the data production side but on the database loading side, I think we can experiment with as many single file to single collection geopackage deliveries you currently have in the pipeline (if all we need to do is schedule airflow to watch for a specific file name pattern and use it to load a single table)

So there seems to be two issues here. The first is performence (ie what we are seeing in #437).

We are concerned about the complexity of too many region-type collection combinations -- seems like we'd go from 8(?) collections to 12*8 collections, which is a lot to push to a user.

Let's talk about this(?) upsert statement. Is that what you are refering to when you say "Each new geopackage contains all perimeters to date"? If yes, how expensive is that upsert statement?

    1. Frankly, *right now* we have a workflow that *could* be modified to have just "insert" statements 90% of the time. _However_ those 10% of instances where we need an upsert are really important (usually correcting for a major data issue). We'd need to be able to have control/ insight into how and when we switch from insert to update and I don't think the ingest is set up for that. 
    2.  We are also redoing/ redesigning a major part of our ingest now that would increase our need for upserting considerably. It's possible that if it's not super expensive to do we might as well leave it as is because we eventually will have an algorithm that needs it frequently.

To your point --figuring out where we fall on upsert/insert is blurring the line between science management and database management questions. I know we want to be careful about staying in work-approved lanes. How can we navigate these trade-offs?

@zebbecker

The second issue is "How do we handle numeric ids for multiple regions simultaneously". After sitting down and talking this over with @zebbecker this morning, I have come to appreciate how much of a beast this problem is.

First, Esri seems even more specific about IDS than I realized. My interpretation of this documentation and this documentation is that Esri needs a numeric, iterating, ideally 32-bit numeric id. Possibly a 53-bit would work, or a 64-bit, but it's a little unclear.

The iteration -as you may have been pointing out earlier- I think is the real challenge. We began by brainstorming ways we could just hash elements of our existing primarykey. But if the ID needs to iterate then that technique is out.

What if we just focused on adding a numeric id to just CONUS? ie demo a region-specific collection. Our Esri-specific needs are 99% CONUS specific. It hurts my heart to not have a general solution for this, but I think having a single region:

doesn't overwhelm our users. We could also get some feedback on if they hate it/love it
gives us something actionable faster (and the fire season will start any second)
lets us kick the numeric ID per region question down the road.

@botanical

Managing persistent incrementing numeric ids with multiple input files is a beast. We also have the limitations of our existing features api and loading pipelines (it's not so much the tech but the budget for new development is tricky). I really like the proposal of improving the CONUS collections for now--I think this is doable with our existing infrastructure and, when successful, will support arguments for why we should improve algorithms and upsert capabilities.

I propose we make a change to our airflow EIS vector pipeline to:

Continue running all EIS* vector ingests as they currently operate (varchar and all)
Introduce a second event that will load the CONUS data using the generic vector load pipeline (we get a numeric FID) into new CONUS collections.

We will still need a bit of time to hack the process to handle one file two ways but I think that is something we could reasonable scope and add to the Data Services objectives for the next PI (we are just wrapping the current PI up right now).
cc @botanical

Minor update that doesn't affect the game plan: I re-confirmed with Cole Walts that ArcPro can't ingest the API as a service directly. I try to check every once and a while to see if that capability has changed, and it still hasn't.

Hey folks, any update on the timeline here?

Navigation Menu

Repository navigation

Investigate OGC-FID in fires tables for usage in ESRI products #422

What

AC

Existing tables that need to be fixed in place or copied to new tables with OGC FID indexing

Have varchar primarykey (need to be updated for ESRI to use these)

Have ogc_fid numeric fields (and should work in ArcGIS no change required)

Airflow patterns that need to be updated

Custom index applied in airflow

Update

Airflow config

QGIS attributes of a lf perimeter nrt gkpg

Next short-term steps

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Investigate OGC-FID in fires tables for usage in ESRI products #422

Description

What

AC

Activity

anayeaye commented on Sep 5, 2025

anayeaye commented on Sep 18, 2025

Existing tables that need to be fixed in place or copied to new tables with OGC FID indexing

Have varchar primarykey (need to be updated for ESRI to use these)

Have ogc_fid numeric fields (and should work in ArcGIS no change required)

Airflow patterns that need to be updated

Custom index applied in airflow

mccabete commented on Sep 24, 2025

smohiudd commented on Sep 24, 2025

mccabete commented on Sep 24, 2025

mccabete commented on Sep 24, 2025

anayeaye commented on Sep 24, 2025

Update

Airflow config

QGIS attributes of a lf perimeter nrt gkpg

Next short-term steps

mccabete commented on Mar 23, 2026

anayeaye commented on Apr 2, 2026

anayeaye commented on Apr 2, 2026

anayeaye commented on Apr 3, 2026

mccabete commented on Apr 8, 2026

mccabete commented on Apr 8, 2026

anayeaye commented on Apr 8, 2026

mccabete commented on Apr 9, 2026

mccabete commented on Apr 9, 2026

anayeaye commented on Apr 9, 2026

mccabete commented on Apr 20, 2026

mccabete commented on Apr 27, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions