sqlite -> store lat/lon as attribute in output?

[jgrn307]

We're working with the sqlite output format with the goal of loading it into a geospatial DB (mongoDB). I saw that the sqlite inputs into FVS allow the lat/lon to be included, but am I correct in noticing those lat/lon do not get carried into the outputs of the sqlite table? If so, would you consider adding the lat/lon to the outputs as well (copied from the input table, if available?)

Related: is there a way to store arbitrary metadata (say, in JSON format) in the inputs and then have FVS copy that data to the outputs?

[wagnerds]

Due to the original intention of FVS and the inventory methods in practice during the development of FVS, FVS is a distance independent model. There is currently no ability or plans to track latitude and longitude of trees throughout the simulation process.

With regard to FVS copying attributes from the input to the output, your best bet may be to join the data from the input and output tables in your own processes. This would need to be done at the stand level rather than tree level as there is no way to track individual trees from input to output.

[ghost]

Item 1: Not all of the inputs are copied to the outputs. However, the inputs can be queried by you and thereby linked to the outputs. Start with an attach statement:

attach database "[path name to FVS_Data.db]" as input;

Then, you could join outputs to inputs using the StandID or Stand_CN fields (not that they are spelled with underscore and without underscore depending on the table.

So, say you have a run with the run title called "Run 1", you can get a list of the stands and case ID's for this run:

Select CaseID,StandID from FVS_Cases where RunTitle="Run 1";

You can use the select in a where clause, like this:

select * from FVS_Summary2

JOIN (select Stand_ID,Latitude,Longitude,ElevFT

  from input.FVS_StandInit)

ON Stand_ID = StandID

where CaseID in (Select CaseID from FVS_Cases where RunTitle="Run 1");

Note that the "attach" statement shown above is automatically done if you use the "custom query" tab, if you run the query from the sqlite3 command line, you need to include it.

As for adding JSON output, I don't think it is needed as the current meta data in the sqlite database is adequate. Please post suggestions for more metadata! What do you have in mind?

Cheers, Nick

On Thu, Jul 27, 2023 at 2:52 PM jgrn307 @.***> wrote:

We're working with the sqlite output format with the goal of loading it into a geospatial DB (mongoDB). I saw that the sqlite inputs into FVS allow the lat/lon to be included, but am I correct in noticing those lat/lon do not get carried into the outputs of the sqlite table? If so, would you consider adding the lat/lon to the outputs as well (copied from the input table, if available?)

Related: is there a way to store arbitrary metadata (say, in JSON format) in the inputs and then have FVS copy that data to the outputs?

— Reply to this email directly, view it on GitHub https://github.com/USDAForestService/ForestVegetationSimulator/issues/33, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABVTTTPTOWYW2T26X5EGV5TXSLPIBANCNFSM6AAAAAA22VRWPI . You are receiving this because you are subscribed to this thread.Message ID: @.***>

-- Nicholas L. Crookston Forestry Research Consultant Moscow Idaho USA