Well, It seems to be impossible to join files to mails. So, here are the
statements :
CREATE TABLE Lignes (lig_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
lig_nom TEXT NOT NULL);
SELECT AddGeometryColumn('Lignes', 'lig_geom', 2154, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex('Lignes', 'lig_geom');
CREATE TABLE Pylones (pyl_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
lig_id INTEGER NOT NULL, pyl_num TEXT NOT NULL, CONSTRAINT FK_Pylones_Lignes
FOREIGN KEY (lig_id) REFERENCES Lignes (lig_id));
SELECT AddGeometryColumn('Pylones', 'pyl_geom', 2154, 'POINT', 'XY');
SELECT CreateSpatialIndex('Pylones', 'pyl_geom');
CREATE INDEX idx_Pylones_lig_id ON Pylones (lig_id);
INSERT INTO Lignes VALUES (NULL, 'Ligne 1',
GeomFromText('LINESTRING(916798.8857 6508700.006676, 917132.628242
6509169.221275, 917228.883041 6509615.499456, 917147.428294 6510600.332165,
917130.811154 6511282.374372, 916891.290563 6511604.151782, 916613.096127
6512076.129893)', 2154));
INSERT INTO Lignes VALUES (NULL, 'Ligne 2',
GeomFromText('LINESTRING(916001.23132 6509898.228866, 916928.18227
6510219.970372, 917674.889861 6509913.815551, 918338.55431 6510407.669698)',
2154));
INSERT INTO Pylones VALUES (NULL, 1, '1', GeomFromText('POINT(916798.8857
6508700.006676)', 2154));
INSERT INTO Pylones VALUES (NULL, 1, '2', GeomFromText('POINT(917132.628242
6509169.221275)', 2154));
INSERT INTO Pylones VALUES (NULL, 1, '3', GeomFromText('POINT(917228.883041
6509615.499456)', 2154));
INSERT INTO Pylones VALUES (NULL, 1, '3 bis',
GeomFromText('POINT(917147.428294 6510600.332165)', 2154));
INSERT INTO Pylones VALUES (NULL, 1, '5', GeomFromText('POINT(917130.811154
6511282.374372)', 2154));
INSERT INTO Pylones VALUES (NULL, 1, '6', GeomFromText('POINT(916891.290563
6511604.151782)', 2154));
INSERT INTO Pylones VALUES (NULL, 1, '7', GeomFromText('POINT(916613.096127
6512076.129893)', 2154));
INSERT INTO Pylones VALUES (NULL, 2, '1', GeomFromText('POINT(916001.23132
6509898.228866)', 2154));
INSERT INTO Pylones VALUES (NULL, 2, '2', GeomFromText('POINT(916928.18227
6510219.970372)', 2154));
INSERT INTO Pylones VALUES (NULL, 2, '3', GeomFromText('POINT(917674.889861
6509913.815551)', 2154));
INSERT INTO Pylones VALUES (NULL, 2, '4', GeomFromText('POINT(918338.55431
6510407.669698)', 2154));
-----Message d'origine-----
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de Eric DAVID
Envoyé : mardi 29 juillet 2014 01:40
À : 'General Discussion of SQLite Database'
Objet : Re: [sqlite] System.Data.Sqlite
Here is the list of all sql statements in the file Base.sql, included INSERT
Statements.
I think the issue comes from the index idx_Pylones_lig_id.
Eric DAVID
-----Message d'origine-----
De : sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] De la part de Joe Mistachkin
Envoyé : lundi 28 juillet 2014 23:59 À : 'General Discussion of SQLite
Database'
Objet : Re: [sqlite] System.Data.Sqlite
Eric DAVID wrote:
>
> Yes, it is this one. With the new script I gave in my last mail it has
> changed to
>
> SELECT * FROM Pylones P
> JOIN Lignes L ON L.lig_id = P.lig_id
> JOIN idx_Pylones_pyl_geom I ON P.pyl_id = I.pkid WHERE L.lig_id = 1;
>
So far, I'm unable to reproduce your results here.
Could you please provide the list of all SQL statements used to get the
database into the state where the issue is seen, including any INSERT
statements used to populate it with the test data.
--
Joe Mistachkin
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
