First and last points of MULTILINE geometries

[Marc Gauthier]

I have a solution to find the first and last points :

- ST_DissolvePoints

- JOIN ElementaryGeometries

- SELECT first

- SELECT last

- JOINT with first and last

This solution is slow, is there an another way to do ?

CREATE TABLE test_geom (

  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

  name TEXT NOT NULL

);

SELECT AddGeometryColumn('test_geom', 'the_geom',  4326, 'MULTILINESTRING', 'XY');

INSERT INTO test_geom (id, name, the_geom)

  VALUES (NULL, 'first point', GeomFromText('MULTILINESTRING((-1.692112963872791 48.0842203647569,-1.691862534548445 48.08558671009925,-1.693001469961217 48.08567968037426,-1.693637028134937 48.08573097271828,-1.695405346995406 48.08587760615198,-1.695067914154873 48.08642783350791),(-1.695055929826023 48.08643690600488,-1.694902580616995 48.08670495488457,-1.694552392330494 48.08729110603903,-1.694203696091799 48.08787612249401,-1.693792007457606 48.08857850816146,-1.693509234503707 48.08906105073505,-1.693148333294791 48.08965299861397,-1.693073653347725 48.0897826475462,-1.69274506252302 48.09032499487955,-1.692656371160365 48.09047667316043,-1.692510859276405 48.09070791886534,-1.692454819591148 48.09079603733889,-1.692203953320565 48.0912247024237,-1.692077526846985 48.09143692929269,-1.691810660609065 48.09188341210539,-1.691724474008298 48.09202747748512,-1.691631377620995 48.09218362637598,-1.691513256075908 48.09238156635723,-1.691210922443961 48.09289601325843,-1.691073376069767 48.09312693138181,-1.691026223008673 48.09320717565415,-1.690745617680641 48.09367889208743,-1.690672675453974 48.09382778005264,-1.69039433073307 48.09439488940395,-1.690325493064592 48.0945360998974,-1.690244529642408 48.09470248047367,-1.690230906815293 48.09472878579794,-1.690063276906741 48.09506928992752,-1.689884766408749 48.09541345722039),(-1.689877856259043 48.09542554068698,-1.689703839998683 48.09576630462527,-1.689511116636704 48.09613036213155,-1.68943772286827 48.09625673752989,-1.689269912754965 48.09654253162039,-1.689026276996238 48.09692797610821,-1.688834793528186 48.09727052390772,-1.688481468095755 48.09785892394811,-1.688198612497307 48.09791873960567,-1.6878712317293 48.09812306334703,-1.687352404821526 48.09844140619002,-1.687009753450372 48.09868926396878,-1.686897620990032 48.09883009146051,-1.686457884419822 48.09950783592696,-1.686256342651537 48.09979285310847,-1.686215682847219 48.0999447130161,-1.68628282463259 48.10026061950569,-1.685698146057934 48.10070072676393,-1.685382893060559 48.1009506805496,-1.68523872490134 48.10105633406934,-1.684627882933065 48.10154363495319,-1.684530853376517 48.10162161722667,-1.68433366847183 48.10183135111162,-1.684178319339715 48.10206083829006,-1.684297842330587 48.10228341718769,-1.684380500710313 48.10374022534554,-1.684446907950076 48.10390703257645,-1.684471726259963 48.10407446212526,-1.684495809881066 48.10449833672348,-1.684530441583576 48.10549684027756,-1.684360882536932 48.10549945811905,-1.684141097054569 48.10556634798911,-1.683809442556963 48.10561848306283,-1.683504627350233 48.1056663051402,-1.683221873993604 48.10574541426467,-1.682989307125778 48.10577741816647,-1.681625040037551 48.10595957818781,-1.681366839204691 48.10599154937446,-1.681175278979825 48.10601651567071,-1.681004379956415 48.10607497148482,-1.680671435103818 48.10674833914034,-1.6804923449891 48.10712255055811,-1.680364599395602 48.10742708072098,-1.680261555723822 48.10750959434263,-1.680122379331213 48.10795298853837,-1.680132398112073 48.10816929961399,-1.680145955492819 48.10860111234141,-1.680152641394242 48.10883365229242,-1.680167434950331 48.10936733723674,-1.680184149888795 48.10985159195895,-1.680202467827091 48.11017699729418,-1.680230235342133 48.11094189195379,-1.680264839197378 48.11176444385142,-1.68028532265879 48.11241376632202,-1.680292663645602 48.11274176379502,-1.680111144935112 48.11316005896928,-1.68008861250356 48.11319423478639,-1.680023796122395 48.11338033282846,-1.67996804463833 48.11345448345933,-1.67986909795068 48.11352931979833,-1.679533296605177 48.11365992865374,-1.6794261651134 48.11369754714591,-1.679381762538874 48.11370257275467,-1.679424953189667 48.11447864127635,-1.679402710740782 48.1145160237476,-1.679206679305023 48.11498640470782,-1.678890461102763 48.11575671346025,-1.67875508549452 48.11626002924261,-1.678735073960784 48.11632199667047,-1.67853384841161 48.1165587034126,-1.678328987613537 48.11677302805807,-1.677743727328926 48.11726245849289,-1.677561962658936 48.11737821070615,-1.676323798119951 48.11822999408254,-1.675765420600373 48.11857455729757,-1.67526495687076 48.11901010139195,-1.675007192707279 48.11918901858474,-1.672827383459268 48.12036548879743,-1.672431444893908 48.12057576366837,-1.672034083988135 48.12064662289508,-1.671806489636657 48.1208403995,-1.671706652753991 48.12097641662774,-1.671647540880651 48.12103138800128,-1.671175545954157 48.12128658781092,-1.67044318577759 48.1216746657961,-1.670208708957321 48.12182794948738,-1.669862627564604 48.12207588554846,-1.669663512059043 48.1222481169495,-1.669435563378043 48.12245585015072,-1.669275243441395 48.1225964658471,-1.668935639891639 48.12295142023076,-1.668754807265537 48.12318406018918,-1.668288594328202 48.12382202066888,-1.668147039946946 48.12399298564442,-1.668070988043398 48.1240733171981,-1.667984969400666 48.124149762341,-1.667521735317708 48.12453762364756,-1.667113550574148 48.12486102413149,-1.666747161743291 48.12515054013785,-1.666112734044445 48.12559469231915,-1.665953411740912 48.12576422944896,-1.665758718721356 48.12602102835862,-1.665437206529921 48.12652328913049,-1.66501214429849 48.12719174977002,-1.6648651962228 48.12742729953112,-1.664422793784296 48.12815224797565,-1.664265001320765 48.12840969255567,-1.664182404584192 48.12850638022138,-1.663695957438336 48.12916981230097,-1.663405867066686 48.12952381218125,-1.663212934380141 48.12981808402779,-1.663084888210012 48.13006788422522,-1.662828654948607 48.13047737016454,-1.663679651026418 48.13077867100246,-1.665187305469084 48.13127004986903,-1.665871588811891 48.13149977916271,-1.666108247713656 48.13161784816133,-1.666408488957225 48.13180092696573,-1.666803136229025 48.13212393686045,-1.667442245101338 48.13263335744639,-1.667814789841185 48.13287143306543,-1.667939146215538 48.13275802936681,-1.668109422367043 48.13267387394709,-1.668288698200789 48.13261832041532,-1.66850170215625 48.13259894687234,-1.669487998259942 48.13264152335607,-1.670526119677965 48.13267233141074,-1.67105142038504 48.13266816781911,-1.672215880134406 48.13266057201705,-1.672972427277106 48.13264378728174,-1.673000802964057 48.13281536370645,-1.673027414385042 48.13319729201796,-1.67295664817665 48.13423761558584,-1.67292330847634 48.13464772713636,-1.672889161710567 48.13520807109268,-1.672834187140091 48.13601601710469,-1.672775460235536 48.1361814757599,-1.67270917746763 48.13629896304676,-1.672679180348768 48.13635704327114,-1.672637087693301 48.13642312426726,-1.672584203432189 48.13649393444819,-1.67252725353168 48.13655525480925,-1.67246730248077 48.13661884246995,-1.672359351326071 48.13671871025724,-1.672314640428597 48.13675593087848,-1.672271042322534 48.136787741338,-1.672226379659582 48.13680779375619,-1.672166083422615 48.13681453439699,-1.672096400458254 48.13682380076555,-1.672000100011777 48.13682234773217,-1.67183309668341 48.13680123569293,-1.671537998641176 48.13676386811294,-1.671424857743337 48.13675344264778,-1.671309828855012 48.13673987513276,-1.671166442385571 48.13673175074067,-1.669925670794444 48.13669415191272,-1.669491497650422 48.13667851773036,-1.668531836610716 48.13664773166929,-1.667716771119812 48.13662072567,-1.667030333618581 48.13659815237743,-1.666959197590532 48.1367329988171,-1.66686551099614 48.13679580553048,-1.666765339142415 48.13685780297854,-1.666091426280288 48.13723917892963,-1.666008576269492 48.13731549285098,-1.66596303808361 48.1373967305546,-1.665868420065119 48.13796274016284,-1.665796596211904 48.13840873817252,-1.665469644428434 48.13858614316041,-1.665404546239411 48.13891170971759,-1.665687306337375 48.13914914349056,-1.665950767513808 48.13901400841162,-1.665991180130421 48.13876990557087))', 4326));

SELECT DateTime('now'), "_sqlite_DissolvePoints() ST_DissolvePoints";

DROP TABLE IF EXISTS 'test_geom_lines';

CREATE TABLE 'test_geom_lines' AS

SELECT id, ST_DissolvePoints(Geometry) AS Geometry_pts

FROM test_geom

;

SELECT DateTime('now'), "_sqlite_DissolvePoints() RecoverGeometryColumn";

SELECT RecoverGeometryColumn('test_geom_lines', 'Geometry_pts', 4326, 'MULTIPOINT', 'XY')

;

SELECT DateTime('now'), "_sqlite_DissolvePoints() table:test_geom_lines nb:" , COUNT(*) FROM test_geom_lines

;

SELECT DateTime('now'), "_sqlite_DissolvePoints() ElementaryGeometries";

DROP TABLE IF EXISTS 'test_geom_eg';

CREATE TABLE 'test_geom_eg' AS

SELECT id, e.geometry AS 'Geometry_eg', e.item_no

FROM test_geom_lines AS t

JOIN ElementaryGeometries AS e ON (e.f_table_name = 'test_geom_lines' AND e.origin_rowid = t.ROWID)

;

SELECT DateTime('now'), "_sqlite_DissolvePoints() table:test_geom_eg nb:" , COUNT(*) FROM test_geom_eg

;

SELECT DateTime('now'), "_sqlite_DissolvePoints() last";

DROP TABLE IF EXISTS test_geom_last;

CREATE TABLE test_geom_last AS

SELECT  id, Geometry_eg AS Geometry_last

FROM test_geom_eg

GROUP BY id

ORDER BY id

;

SELECT DateTime('now'), "_sqlite_DissolvePoints() first";

DROP TABLE IF EXISTS test_geom_first;

CREATE TABLE test_geom_first AS

SELECT  id, Geometry_eg AS Geometry_first

FROM test_geom_eg

WHERE item_no = 0

GROUP BY id

ORDER BY id

;

SELECT DateTime('now'), "_sqlite_DissolvePoints() first last";

DROP TABLE IF EXISTS test_geom_firstlast;

CREATE TABLE test_geom_firstlast AS

SELECT  t1.*, Geometry_first, Geometry_last

FROM test_geom AS t1

LEFT JOIN test_geom_first

USING(id)

LEFT JOIN test_geom_last

USING(id)

;

[a.fu...@lqt.it]

On Sat, 5 Sep 2015 23:03:31 -0700 (PDT), Marc Gauthier wrote:
> I have a solution to find the first and last points :
> - ST_DissolvePoints
> - JOIN ElementaryGeometries
> - SELECT first
> - SELECT last
> - JOINT with first and last
>
> This solution is slow, is there an another way to do ?
>

SELECT ST_StartPoint(ST_GeometryN(the_geom, 1)) AS first_pt,
ST_EndPoint(ST_GeometryN(the_geom, ST_NumGeometries(the_geom)))
AS last_pt
FROM test_geom;

bye sandro

[Marc Gauthier]

Many thanks Sandro

Bye

Marc