Solved

SpatiaLite questions

  • 10 October 2017
  • 4 replies
  • 6 views
jorge_rosales
Badge

I need generate a SpatiaLite database that stores a 1:n relationship. The 1 side is a spatial table and the n side is a non spatial table. Firstly I create a template (template.sqlite from template.sql).

If I use only the SpatiaLite writer I get an error with the non spatial table (test1.fmw). Maybe I misunderstand the meaning of wkb_none. If I use a SpatialLite writer for the spatial table and a Sqlite for the non spatial table, everything runs ok but it seems that FME ignores an existing constraint in the template database (test2.fmw).Workspaces: Source data: https://goo.gl/5FfJBs
icon

Best answer by pallegama 11 October 2017, 06:31

View original
1 Attachment

4 replies

pallegama
Badge

Hi @j4,

Looking at the SLQITE3 writer parameters, I noticed that 'Template File' parameter has not been set properly. Could you please set this parameter and try again.

Regards,

Priyantha

jorge_rosales
Badge

Hi @j4,

Looking at the SLQITE3 writer parameters, I noticed that 'Template File' parameter has not been set properly. Could you please set this parameter and try again.

Regards,

Priyantha

Both writers point to the same destination file so if you use two templates files (one per writer) the second will overwrite the database (and data) created by the first one.

 

 

pallegama
Badge
Both writers point to the same destination file so if you use two templates files (one per writer) the second will overwrite the database (and data) created by the first one.

 

 

Hi @j4,

 

 

I found the exact cause for this issue. Unfortunately, I don't have required ArcGIS license to test the workspace with your original input. However, this should resolve the issue.

 

To identify the issue, I have created sample spatial data (ffs) file and attribute data (csv).

 

 

The issue is you have to enable the Foreign key constraint in Sqlite in run time passing the command 'PRAGMA foreign_keys = ON'. ( Please find more information in this regard in the link https://sqlite.org/foreignkeys.html).

 

 

This can be achieved in FME by passing this command to 'SQL To Run Before Writer' parameter in the Sqlite Writer as depicted in the image below.

 

To test the workspace, please run it with provided CSV file which contains a FK value violating the constraint. This should fail the worksapce.

 

Then remove the last record of CSV file (which violate the FK constraint) and run the process again. Then it should run successfully.

 

 

spatialite.zip

 

 

jorge_rosales
Badge

Thank you @pallegama, it works. Maybe the "PRAGMA foreign_keys = on" should be the SpatiaLite Writer default behaviour. I think it would be clearer.

Reply


Community Stats

28,692
Posts
109,433
Replies
36,544
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.