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
  • ST_Dumppoints and ST_Equals and gis.stackexchange.com/questions/141790/… – Cyril Dec 12 '18 at 11:13

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.