idea FGDB database view — View that selects from an existing view in ArcGIS Pro Ideas

FGDB database view — View that selects from an existing view

Bud — Mon, 15 Jan 2024 02:45:48 GMT

ArcGIS Pro 3.2.1; File Geodatabase:

It would help to be able to create a view on top of an existing view in a file geodatabase.

Currently, in simple cases like SELECT * FROM EXISTING_VIEW, the resulting view has zero rows (incorrect). Whereas in all other geodatabase and database types, a view can select from an existing view without issue.

Use Case:

1. Organization. Break large queries into smaller, more manageable pieces.

2. Also, complex queries like greatest 1 per group (with ties). See: Selecting the most recent records based on unique values in another field and my comment about FGDB view challenges.

For example, get the latest row per species group (with ties), and include all the fields from the original table. That can't be done in a single FGDB view due to various FGDB SQL limitations (update: see follow-up reply below). Instead, I want to use multiple stacked views.

A view that gets the latest row per group (but doesn't include the other fields from the original table):

--latest_vw
select
    t_species,
    max(t_date) as t_date
from
    species_records
group by
    t_species

And then a view on top of that view that takes the fields from the original table and joins to LATEST_VW. The join is based on multiple fields: T_SPECIES=T_SPECIES and T_DATE=T_DATE.

--latest_with_other_fields_vw
select
    species_records.*,
    latest_vw.*
from
    species_records
inner join
    latest_vw
    on species_records.t_species = latest_vw.t_species 
       and species_records.t_date = latest_vw.t_date

That approach works in mobile and enterprise geodatabases but not in file geodatabases since "a view on a view" doesn't seem to be supported; the joined fields from LATEST_VW are completely null, which is incorrect.

If I could create a view on a view, then that would solve the greatest 1 per group (with ties) problem in FGDB SQL.

I've attached sample Excel data that can be loaded into a file geodatabase.

Re: FGDB database views — View that selects from an existing view

MarceloMarques — Sun, 14 Jan 2024 20:37:17 GMT

My 2 cents. Certain SQL operations are only available in a RDBMS (Oracle, SQL Server, PortgreSQL) because the SQL Engine behind the RDBMS that is quite complex. Certain SQL functions in a File Geodatabase might not be available and it will be difficult to implement that because of this reason. Therefore, the best aproach is to move the data from the File Geodatabase to an Enterprise Geodatabase, even Mobile Geodatabase will present some limitations as well, thus move the data to an Enterprise Database (Oracle, SQL Server, PostgreSQL). I hope this helps.

Re: FGDB database views — View that selects from an existing view

Bud — Mon, 15 Jan 2024 02:48:07 GMT

Update

For example, get the latest row per species group, and include all the fields from the original table. That can't be done in a single FGDB view due to various FGDB SQL limitations.

I realize now that is incorrect. It can be done in a single view: Selecting the most recent records based on unique values in another field.

--species_records_latest_vw
select
    *
from
    species_records
inner join
    (select
        t_species,
        max(t_date) as t_date 
    from
        species_records 
    group by
        t_species) l
    on species_records.t_species = l.t_species 
       and species_records.t_date = l.t_date

But this idea is still valid. It would still help to create a view on a view.