0

I have a point feature called site_investigation and another point feature called sample_location which are in a Spatialite database. They have a 1:N relationship and the primary key is report_id.

CREATE TABLE site_investigation (
        fid         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        report_id   TEXT UNIQUE NOT NULL,
        geometry    POINT
);
CREATE TABLE sample_location (
        fid            INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        report_id      TEXT NOT NULL,
        official_name  TEXT UNIQUE NOT NULL,
        geometry    POINT
        FOREIGN KEY(report_id) REFERENCES site_investigation(shz_report_id) ON DELETE CASCADE
);

I would like to create a table of lines that connects site_investigation to sample_location to form a "starburst" display. I'm a Spatialite novice.

  • Are you just looking for something to draw a picture of the database relationship (like old versions of Access did)? Or are you trying to build a foreign key relationship? Or are you trying to show a "starburst" on the map? Can you explain how QGIS fits into this? – BradHards Apr 10 '14 at 23:09
  • I'm looking to create a "starburst" on the map. I'm using QGIS to view my data. I would also be interested in having another Spatialite table that contains these lines. – cgs_bob Apr 10 '14 at 23:27
1

I'll show how to do this in just spatialite.

Lets start with creating the same schema as you showed in the question:

-- The schema provided in the question
CREATE TABLE site_investigation (
        fid         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        report_id   TEXT UNIQUE NOT NULL
);
SELECT AddGeometryColumn('site_investigation', 'geometry', -1, 'POINT', 'XY');

CREATE TABLE sample_location (
        fid            INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        report_id      TEXT NOT NULL,
        official_name  TEXT UNIQUE NOT NULL
);
SELECT AddGeometryColumn('sample_location', 'geometry', -1, 'POINT', 'XY');

Note that I've removed the foreign key (since it had a typo, and sqlite ignores them by default anyway).

We'll need some sample data. The data obviously isn't what you are using, but it should be illustrative. I also didn't have the SRID, so I went with -1 (unknown).

-- Fill in some sample data
INSERT INTO site_investigation (report_id, geometry) VALUES ("siteA", MakePoint(5, 10));
INSERT INTO site_investigation (report_id, geometry) VALUES ("siteB", MakePoint(10, 20));

INSERT INTO sample_location(report_id, official_name, geometry) VALUES ("siteA", "sample1", MakePoint (5, 8));
INSERT INTO sample_location(report_id, official_name, geometry) VALUES ("siteA", "sample2", MakePoint (5, 12));
INSERT INTO sample_location(report_id, official_name, geometry) VALUES ("siteA", "sample3", MakePoint (6, 7));
INSERT INTO sample_location(report_id, official_name, geometry) VALUES ("siteA", "sample4", MakePoint (6, 3));

INSERT INTO sample_location(report_id, official_name, geometry) VALUES ("siteB", "sample6", MakePoint (10, 16));
INSERT INTO sample_location(report_id, official_name, geometry) VALUES ("siteB", "sample7", MakePoint (10, 25));
INSERT INTO sample_location(report_id, official_name, geometry) VALUES ("siteB", "sample8", MakePoint (12, 21));

To make it prettier, I'll turn on some formatting options:

.headers on
.mode column 
.width 10 25 25

Note that none of that affects the SQL, just how output is shown. Ignore if if you like.

When writing SQL commands, I recommend simple first, and slow build up:

-- Test basic operation
SELECT sample_location.official_name AS Name, AsEwkt(site_investigation.geometry) AS SitePosition, AsEwkt(sample_location.geometry) AS SamplePosition FROM site_investigation, sample_location WHERE site_investigation.report_id = sample_location.report_id;

That produces the following:

Name        SitePosition               SamplePosition           
----------  -------------------------  -------------------------
sample1     SRID=-1;POINT(5 10)        SRID=-1;POINT(5 8)       
sample2     SRID=-1;POINT(5 10)        SRID=-1;POINT(5 12)      
sample3     SRID=-1;POINT(5 10)        SRID=-1;POINT(6 7)       
sample4     SRID=-1;POINT(5 10)        SRID=-1;POINT(6 3)       
sample6     SRID=-1;POINT(10 20)       SRID=-1;POINT(10 16)     
sample7     SRID=-1;POINT(10 20)       SRID=-1;POINT(10 25)     
sample8     SRID=-1;POINT(10 20)       SRID=-1;POINT(12 21) 

Now we can change the two points into a line:

-- Change column width for layout
.width 10 40
-- Make line
SELECT sample_location.official_name AS Name, AsEwkt(MakeLine(site_investigation.geometry, sample_location.geometry)) AS link_line FROM site_investigation, sample_location WHERE site_investigation.report_id = sample_location.report_id;

That produces:

Name        link_line                               
----------  ----------------------------------------
sample1     SRID=-1;LINESTRING(5 10,5 8)            
sample2     SRID=-1;LINESTRING(5 10,5 12)           
sample3     SRID=-1;LINESTRING(5 10,6 7)            
sample4     SRID=-1;LINESTRING(5 10,6 3)            
sample6     SRID=-1;LINESTRING(10 20,10 16)         
sample7     SRID=-1;LINESTRING(10 20,10 25)         
sample8     SRID=-1;LINESTRING(10 20,12 21)

Which is pretty much what we want. You could just use that as a VIEW, but you asked for a table, so lets go with that:

-- Create new table
CREATE TABLE site_links (
        fid            INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        official_name  TEXT UNIQUE NOT NULL
);
SELECT AddGeometryColumn('site_links', 'linkgeom', -1, 'LINESTRING', 'XY');

To populate the table, we use the same SELECT (less the AsEwkt() formatting function), just putting an INSERT INTO around it:

INSERT INTO site_links (official_name, linkgeom) SELECT sample_location.official_name, MakeLine(site_investigation.geometry, sample_location.geometry) FROM site_investigation, sample_location WHERE site_investigation.report_id = sample_location.report_id;

Now we check the results:

SELECT official_name, AsEwkt(linkgeom) FROM site_links;

Which produces:

official_n  AsEwkt(linkgeom)                        
----------  ----------------------------------------
sample1     SRID=-1;LINESTRING(5 10,5 8)            
sample2     SRID=-1;LINESTRING(5 10,5 12)           
sample3     SRID=-1;LINESTRING(5 10,6 7)            
sample4     SRID=-1;LINESTRING(5 10,6 3)            
sample6     SRID=-1;LINESTRING(10 20,10 16)         
sample7     SRID=-1;LINESTRING(10 20,10 25)         
sample8     SRID=-1;LINESTRING(10 20,12 21) 
  • Thank you so much! That was exactly what I was looking for. I should have asked for a view, but I'll see if I can do it myself. – cgs_bob Apr 12 '14 at 2:32
  • A view would be just the same, however instead of CREATE TABLE and INSERT INTO, it is going to be CREATE VIEW site_links AS SELECT sample_location.official_name AS official_name, MakeLine(...)... – BradHards Apr 12 '14 at 3:45
  • I just tried to make a view, but it does not show up in qgis. After looking on SE, I see I had to do something like: INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid,f_table_name, f_geometry_column,read_only) values 'site_links','geom','rowid','site_investigation','geometry',1). Now qgis thinks this view is a point feature. – cgs_bob Apr 12 '14 at 6:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.