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

[anayeaye]

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

[anayeaye]

cc: @smohiudd @briannapagan @freitagb

[anayeaye]

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

[mccabete]

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?

[smohiudd]

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) ?

[mccabete]

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

[mccabete]

We are comfortable dropping it.

[anayeaye]

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

1. Evaluate if the ID or FID column in this new table is suitable for ESRI integration
2. Review the ogr2ogr command and add any adjustments needed for usability
3. Ingest additional test tables for the other table types for integration testing as needed
4. 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.

[mccabete]

@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?

[anayeaye]

@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.

[anayeaye]

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

[anayeaye]

@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

[mccabete]

@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

[mccabete]

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.