Bsrgvty

Fight a biblical flood apart from building barriers

Will replacing a fake visa with a different fake visa cause me problems when applying for a legal study permit?

Can a corpse possessed by a Dybbuk be turned via Turn Undead?

Can I toggle Do Not Disturb on/off on my Mac as easily as I can on my iPhone?

What is this unknown executable on my boot volume? Is it Malicious?

Parallel resistance in electric circuits

Does an oscilloscope subtract voltages as phasors?

Why is Kirchoff's loop rule true in a DC circuit?

Were Roman public roads build by private companies?

Relocation error, error code (127) after last updates

Why does Coq include let-expressions in its core language

Resume: How to quantify my contributions as a software engineer?

Random point on a sphere

Splice or replace

How to say "quirky" in German without sounding derogatory?

How can I fix a framing mistake so I can drywall?

Evidence that matrix multiplication cannot be done in O(n^2 poly(log(n))) time

What's the biggest organic molecule that could have a smell?

Is there any way to land a rover on the Moon without using any thrusters?

Closer slanted parallel symbol

Should I leave the first authorship of our paper to the student who did the project whereas I solved it?

What are uses of the byte after BRK instruction on 6502?

How unbalanced coaxial cables are used for broadcasting TV signals without any problems?

Do ibuprofen or paracetamol cause hearing loss?

Spatial joining two line layers and checking intersection in PostgreSQL/PostGIS

ST_Intersects incorrectly returning false in PostGISPostgis ST_Intersects performanceReduce query time when using both MySQL and PostgreSQL with PostGISUpdating table setting a CASE with ST_IntersectionHow to intersect two polygon-layers in PostgreSQL/PostGIS?Difference between PostGIS ST_Intersects vs '=', QGIS and ArcGIS 'select by location'Intersection between an angle and a polygon (postgreSQL + postGIS or Python2.7 + shapely + psycopg2)Selecting points having 1, 3 or more lines intersecting using PostGISSlow PostGIS line intersection even with indexes

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I have two line layers in PostgreSQL/PostGIS. I want to the select data which are not intersecting. When I am writing this query it is taking a lot of time. How can I make it faster and see the result?

SELECT Geometry into Qlayer5
FROM qlayer4, egmap_20170316_tmc_polyline
WHERE st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

and I have also tried using right join but none of them giving output.

SELECT Geometry INTO Qlayer5
FROM qlayer4
RIGHT JOIN egmap_20170316_tmc_polyline ON
st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

postgis intersection st-intersects

share|improve this question

edited Apr 15 at 5:33

Taras

3,64433 gold badges99 silver badges3636 bronze badges

asked Apr 15 at 3:49

poonam patelpoonam patel

5366 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  non-intersections are tricky; your query will result in a carestian product of both tables, that's why it is so slow. your join condition matches every pair of lines that do not intersect; so if table A has 1 row and table B 100, with no intersection between them, you will get 100 rows returned!
  
  – ThingumaBob
  Apr 15 at 8:33
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  ...or better, when table A has 3 rows and B 100, with no intersection, your query will return 300 rows. similar, with 1000 in A and 1000 in B, Postgres fetches 1000000 rows after computing each of them...
  
  – ThingumaBob
  Apr 15 at 9:01
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ya understand but i dont find any other way to filter out non intersected lines.i am trying your queries now .Do you have any suggestions?
  
  – poonam patel
  Apr 15 at 9:13
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  my suggestion is my answer. I'm just trying to explain why your query will fail with useless results after a lifetime of execution...,)
  
  – ThingumaBob
  Apr 15 at 9:17
  

  
  
  
  

add a comment
 | 

2

I have two line layers in PostgreSQL/PostGIS. I want to the select data which are not intersecting. When I am writing this query it is taking a lot of time. How can I make it faster and see the result?

SELECT Geometry into Qlayer5
FROM qlayer4, egmap_20170316_tmc_polyline
WHERE st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

and I have also tried using right join but none of them giving output.

SELECT Geometry INTO Qlayer5
FROM qlayer4
RIGHT JOIN egmap_20170316_tmc_polyline ON
st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

postgis intersection st-intersects

share|improve this question

edited Apr 15 at 5:33

Taras

3,64433 gold badges99 silver badges3636 bronze badges

asked Apr 15 at 3:49

poonam patelpoonam patel

5366 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  non-intersections are tricky; your query will result in a carestian product of both tables, that's why it is so slow. your join condition matches every pair of lines that do not intersect; so if table A has 1 row and table B 100, with no intersection between them, you will get 100 rows returned!
  
  – ThingumaBob
  Apr 15 at 8:33
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  ...or better, when table A has 3 rows and B 100, with no intersection, your query will return 300 rows. similar, with 1000 in A and 1000 in B, Postgres fetches 1000000 rows after computing each of them...
  
  – ThingumaBob
  Apr 15 at 9:01
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ya understand but i dont find any other way to filter out non intersected lines.i am trying your queries now .Do you have any suggestions?
  
  – poonam patel
  Apr 15 at 9:13
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  my suggestion is my answer. I'm just trying to explain why your query will fail with useless results after a lifetime of execution...,)
  
  – ThingumaBob
  Apr 15 at 9:17
  

  
  
  
  

add a comment
 | 

I have two line layers in PostgreSQL/PostGIS. I want to the select data which are not intersecting. When I am writing this query it is taking a lot of time. How can I make it faster and see the result?

SELECT Geometry into Qlayer5
FROM qlayer4, egmap_20170316_tmc_polyline
WHERE st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

and I have also tried using right join but none of them giving output.

SELECT Geometry INTO Qlayer5
FROM qlayer4
RIGHT JOIN egmap_20170316_tmc_polyline ON
st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

postgis intersection st-intersects

share|improve this question

edited Apr 15 at 5:33

Taras

3,64433 gold badges99 silver badges3636 bronze badges

asked Apr 15 at 3:49

poonam patelpoonam patel

5366 bronze badges

SELECT Geometry into Qlayer5
FROM qlayer4, egmap_20170316_tmc_polyline
WHERE st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

SELECT Geometry INTO Qlayer5
FROM qlayer4
RIGHT JOIN egmap_20170316_tmc_polyline ON
st_intersects(qlayer59.Geometry,egmap_20170316_tmc_polyline.geom) = false 

share|improve this question

edited Apr 15 at 5:33

Taras

3,64433 gold badges99 silver badges3636 bronze badges

asked Apr 15 at 3:49

poonam patelpoonam patel

5366 bronze badges

share|improve this question

edited Apr 15 at 5:33

Taras

3,64433 gold badges99 silver badges3636 bronze badges

asked Apr 15 at 3:49

poonam patelpoonam patel

5366 bronze badges

edited Apr 15 at 5:33

Taras

3,64433 gold badges99 silver badges3636 bronze badges

edited Apr 15 at 5:33

Taras

3,64433 gold badges99 silver badges3636 bronze badges

asked Apr 15 at 3:49

poonam patelpoonam patel

5366 bronze badges

asked Apr 15 at 3:49

poonam patelpoonam patel

5366 bronze badges

2 Answers
2

active

oldest

votes

5

Non-intersections are tricky. To avoid a cartesian product of the joined tables you need to either

• 
  

  check against a collection/union of the joined tables' geometries:

  
  
  

  WITH
   col AS (
   SELECT ST_Collect(geom) AS geom
   FROM egmap_20170316_tmc_polyline
   )
  SELECT *
  FROM qlayer4 AS a
  JOIN col AS b
   ON NOT ST_Intersects(a.geom, b.geom)
  ;
  

  
  
  

  which will be rather inefficient for larger tables (indexes are less effective and the amount of vertices to traverse each time is extremely high)

  
  


• 
  

  or better, check against existence of intersections:

  
  
  

  SELECT *
  FROM qlayer4 AS a
  WHERE NOT EXISTS (
   SELECT 1
   FROM egmap_20170316_tmc_polyline AS b
   WHERE ST_Intersects(a.geom, b.geom)
  );
  

  
  
  

  which will at least use the index efficiently

  
  

Note that, even with the more optimized second query, this is a heavy operation and will likely take some time.

share|improve this answer

edited Apr 15 at 9:22

answered Apr 15 at 8:54

ThingumaBobThingumaBob

8,07411 gold badge55 silver badges2626 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you genius. second query solved my problem almost
  
  – poonam patel
  Apr 16 at 3:19
  

  
  
  
  

add a comment
 | 

0

Try this first for seeing the result of your query:

SELECT a.Geometry
FROM qlayer4 AS a,
egmap_20170316_tmc_polyline AS b 
WHERE
st_intersects(a.Geometry, b.geom) = false;

If it returns the expected result, insert the "INTO Qlayer5" statement.

share|improve this answer

answered Apr 15 at 5:22

blabbathblabbath

77233 silver badges1818 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you @blabbath. This query i have tried earlier .it did not finish till two hours so, i stopped.
  
  – poonam patel
  Apr 15 at 6:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @poonampatel How many objects are in your tables? Have you set a spatial index?
  
  – blabbath
  Apr 15 at 7:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes i have set spatial index .i have quite a huge data.
  
  – poonam patel
  Apr 15 at 7:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Then, depending on your hardware specs and tuning parameters, 2 hours might just not be enough time.
  
  – blabbath
  Apr 15 at 7:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I also think the same @blabbath. that is why i am looking for efficient solution
  
  – poonam patel
  Apr 15 at 8:33
  

  
  
  
  

add a comment
 | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f318777%2fspatial-joining-two-line-layers-and-checking-intersection-in-postgresql-postgis%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

5

Non-intersections are tricky. To avoid a cartesian product of the joined tables you need to either

• 
  

  check against a collection/union of the joined tables' geometries:

  
  
  

  WITH
   col AS (
   SELECT ST_Collect(geom) AS geom
   FROM egmap_20170316_tmc_polyline
   )
  SELECT *
  FROM qlayer4 AS a
  JOIN col AS b
   ON NOT ST_Intersects(a.geom, b.geom)
  ;
  

  
  
  

  which will be rather inefficient for larger tables (indexes are less effective and the amount of vertices to traverse each time is extremely high)

  
  


• 
  

  or better, check against existence of intersections:

  
  
  

  SELECT *
  FROM qlayer4 AS a
  WHERE NOT EXISTS (
   SELECT 1
   FROM egmap_20170316_tmc_polyline AS b
   WHERE ST_Intersects(a.geom, b.geom)
  );
  

  
  
  

  which will at least use the index efficiently

  
  

Note that, even with the more optimized second query, this is a heavy operation and will likely take some time.

share|improve this answer

edited Apr 15 at 9:22

answered Apr 15 at 8:54

ThingumaBobThingumaBob

8,07411 gold badge55 silver badges2626 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you genius. second query solved my problem almost
  
  – poonam patel
  Apr 16 at 3:19
  

  
  
  
  

add a comment
 | 

5

Non-intersections are tricky. To avoid a cartesian product of the joined tables you need to either

• 
  

  check against a collection/union of the joined tables' geometries:

  
  
  

  WITH
   col AS (
   SELECT ST_Collect(geom) AS geom
   FROM egmap_20170316_tmc_polyline
   )
  SELECT *
  FROM qlayer4 AS a
  JOIN col AS b
   ON NOT ST_Intersects(a.geom, b.geom)
  ;
  

  
  
  

  which will be rather inefficient for larger tables (indexes are less effective and the amount of vertices to traverse each time is extremely high)

  
  


• 
  

  or better, check against existence of intersections:

  
  
  

  SELECT *
  FROM qlayer4 AS a
  WHERE NOT EXISTS (
   SELECT 1
   FROM egmap_20170316_tmc_polyline AS b
   WHERE ST_Intersects(a.geom, b.geom)
  );
  

  
  
  

  which will at least use the index efficiently

  
  

Note that, even with the more optimized second query, this is a heavy operation and will likely take some time.

share|improve this answer

edited Apr 15 at 9:22

answered Apr 15 at 8:54

ThingumaBobThingumaBob

8,07411 gold badge55 silver badges2626 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you genius. second query solved my problem almost
  
  – poonam patel
  Apr 16 at 3:19
  

  
  
  
  

add a comment
 | 

Non-intersections are tricky. To avoid a cartesian product of the joined tables you need to either

• 
  

  check against a collection/union of the joined tables' geometries:

  
  
  

  WITH
   col AS (
   SELECT ST_Collect(geom) AS geom
   FROM egmap_20170316_tmc_polyline
   )
  SELECT *
  FROM qlayer4 AS a
  JOIN col AS b
   ON NOT ST_Intersects(a.geom, b.geom)
  ;
  

  
  
  

  which will be rather inefficient for larger tables (indexes are less effective and the amount of vertices to traverse each time is extremely high)

  
  


• 
  

  or better, check against existence of intersections:

  
  
  

  SELECT *
  FROM qlayer4 AS a
  WHERE NOT EXISTS (
   SELECT 1
   FROM egmap_20170316_tmc_polyline AS b
   WHERE ST_Intersects(a.geom, b.geom)
  );
  

  
  
  

  which will at least use the index efficiently

  
  

Note that, even with the more optimized second query, this is a heavy operation and will likely take some time.

share|improve this answer

edited Apr 15 at 9:22

answered Apr 15 at 8:54

ThingumaBobThingumaBob

8,07411 gold badge55 silver badges2626 bronze badges

share|improve this answer

edited Apr 15 at 9:22

answered Apr 15 at 8:54

ThingumaBobThingumaBob

8,07411 gold badge55 silver badges2626 bronze badges

answered Apr 15 at 8:54

ThingumaBobThingumaBob

8,07411 gold badge55 silver badges2626 bronze badges

answered Apr 15 at 8:54

ThingumaBobThingumaBob

8,07411 gold badge55 silver badges2626 bronze badges

0

Try this first for seeing the result of your query:

SELECT a.Geometry
FROM qlayer4 AS a,
egmap_20170316_tmc_polyline AS b 
WHERE
st_intersects(a.Geometry, b.geom) = false;

If it returns the expected result, insert the "INTO Qlayer5" statement.

share|improve this answer

answered Apr 15 at 5:22

blabbathblabbath

77233 silver badges1818 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you @blabbath. This query i have tried earlier .it did not finish till two hours so, i stopped.
  
  – poonam patel
  Apr 15 at 6:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @poonampatel How many objects are in your tables? Have you set a spatial index?
  
  – blabbath
  Apr 15 at 7:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes i have set spatial index .i have quite a huge data.
  
  – poonam patel
  Apr 15 at 7:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Then, depending on your hardware specs and tuning parameters, 2 hours might just not be enough time.
  
  – blabbath
  Apr 15 at 7:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I also think the same @blabbath. that is why i am looking for efficient solution
  
  – poonam patel
  Apr 15 at 8:33
  

  
  
  
  

add a comment
 | 

0

Try this first for seeing the result of your query:

SELECT a.Geometry
FROM qlayer4 AS a,
egmap_20170316_tmc_polyline AS b 
WHERE
st_intersects(a.Geometry, b.geom) = false;

If it returns the expected result, insert the "INTO Qlayer5" statement.

share|improve this answer

answered Apr 15 at 5:22

blabbathblabbath

77233 silver badges1818 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you @blabbath. This query i have tried earlier .it did not finish till two hours so, i stopped.
  
  – poonam patel
  Apr 15 at 6:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @poonampatel How many objects are in your tables? Have you set a spatial index?
  
  – blabbath
  Apr 15 at 7:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes i have set spatial index .i have quite a huge data.
  
  – poonam patel
  Apr 15 at 7:38
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Then, depending on your hardware specs and tuning parameters, 2 hours might just not be enough time.
  
  – blabbath
  Apr 15 at 7:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I also think the same @blabbath. that is why i am looking for efficient solution
  
  – poonam patel
  Apr 15 at 8:33
  

  
  
  
  

add a comment
 | 

Try this first for seeing the result of your query:

SELECT a.Geometry
FROM qlayer4 AS a,
egmap_20170316_tmc_polyline AS b 
WHERE
st_intersects(a.Geometry, b.geom) = false;

If it returns the expected result, insert the "INTO Qlayer5" statement.

share|improve this answer

answered Apr 15 at 5:22

blabbathblabbath

77233 silver badges1818 bronze badges

SELECT a.Geometry
FROM qlayer4 AS a,
egmap_20170316_tmc_polyline AS b 
WHERE
st_intersects(a.Geometry, b.geom) = false;

share|improve this answer

answered Apr 15 at 5:22

blabbathblabbath

77233 silver badges1818 bronze badges

answered Apr 15 at 5:22

blabbathblabbath

77233 silver badges1818 bronze badges

answered Apr 15 at 5:22

blabbathblabbath

77233 silver badges1818 bronze badges