1

I've developed the queries to find out intersection points (within 0.2 buffer) between line and polygon and also within adjacent polygons by using PostgreSQL 10 and PostGIS 2.4 database. Line data is around 200,000 and Polygon is around 80,000 rows. The query took long time (aprx 120mins) to produce the result.

Line with Polygon: The situation is:

enter image description here

Query is:

SELECT distinct ST_AsText((ST_Intersection(ST_Buffer(a.geom, 0.2, 'endcap=round join=round'), ST_ExteriorRing(b.geom)))),a.elemid,b.elemid FROM "Line" as a,"Polygon" as b WHERE st_Intersects(a.geom, b.geom)

Result is:

enter image description here

Polygon with Polygon: The situation is:

enter image description here

Query is:

SELECT distinct ST_AsText((ST_Intersection(ST_Buffer(ST_ExteriorRing(a.geom), 0.2, 'endcap=round join=round'), ST_ExteriorRing(b.geom)))), a.elemid, b.elemid FROM "Polygon" as a,"Polygon" as b WHERE st_Intersects(a.geom, b.geom) and a.id != b.id;

below is the sample output with buffer tolerance

enter image description here

I have applied the buffer for the geometries from table 'a' and finding the intersection with the geometries from the table 'b'. Also there is no major difference from the above two queries except the a.id != b.id case.

How can I determine an optimal method to speed up the execution.

  • Did you already create an index on the geometry on both tables and then vacuum both of them? you could try that out. – Marshall Dec 11 '18 at 18:25
  • @Marshall: Yes the spatial index is created automatically since I imported the data from QGIS DB manager tool. But no idea on vacum, kindly tell me that how can we do that? – venkat Dec 12 '18 at 5:22
  • VACUUM ANALYZE [<table>]; (<table> is optional - if omitted, will be run on all tables in the database); VACUUM clears dead row references, ANALYZE updates internal (table) statistics the planner uses to decide how to execute a query. – ThingumaBob Dec 12 '18 at 10:40
  • do you really want to find 'common vertices', i.e. only vertices that are equal (with a tolerance?) in two or more geometries, while simple intersection is of no concern? or do you want to find parts of two geometries that generally intersect (with a tolerance?)? – ThingumaBob Dec 12 '18 at 10:44
  • As I think @ThingumaBob is suggesting, you might want to use something like ST_SnapToGrid which will make equality work better by removing spurious precision in your coordinates. Also, can you show your EXPLAIN. – John Powell Dec 12 '18 at 11:41

Your Answer

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

Browse other questions tagged or ask your own question.