I have two polygons (green and red in the image) and want to find the shared border using PostGIS. If I use select st_intersection(green_geometry, red_geometry), PostGIS sometimes returns a point, not a line. I believe this is due to some rounding error that seems to occur more often when comparing short (green) lines to long (red) lines.

two polygons sharing a border

If I zoom in to the maximum level allowed by QGIS, there is no visible gap or overlap between the shared border of the green and red polygons.

Is there a way to somehow allow for some tolerance for rounding differences before or during use of st_intersection()? Is there another, more reliable way to find the shared border line?

The full query:

select st_astext(st_intersection(
st_geomfromewkt('SRID=28992;POLYGON((122287.444 483861.342,122289.289 483853.045,122291.129 483844.768,122292.978 483836.45,122294.818 483828.173,122296.661 483819.885,122298.506 483811.583,122300.343 483803.32,122302.188 483795.023,122304.237 483785.807,122288.319849949 483782.282791304,122283.90428749 483802.098372362,122245.59783068 483974.543624958,122261.498 483978.092,122263.443 483969.304,122265.286 483961.016,122267.135 483952.7,122268.988 483944.363,122270.833 483936.066,122272.677 483927.769,122274.526 483919.453,122276.372 483911.145,122278.219 483902.838,122280.064 483894.541,122281.911 483886.234,122282.679 483882.779,122283.755 483877.937,122285.6 483869.64,122287.444 483861.342))'),
st_geomfromewkt('SRID=28992;POLYGON((122288.634630817 483922.589753878,122275.739227635 483913.992818423,122274.526 483919.453,122288.634630817 483922.589753878))')));
  • 2
    ST_SnapToGrid postgis.net/docs/ST_SnapToGrid.html often helps. – user30184 Oct 25 '17 at 10:51
  • Could you add those two geometries as WKT into your question for testing? – user30184 Oct 25 '17 at 12:51
  • I added an example query, including the eWKTs, to the question. I tried st_snaptogrid() but that did not result in reliable results. I am wondering why the red polygon has so many points, investigating that... – anneb Oct 25 '17 at 14:10
  • 1
    I found that st_intersection(long_slanted_line, short_slanted_line_on_long_slanted_line) mostly returns a point, not a line (st_overlaps returns false). This happens most likely due to rounding errors (or a bigger bug?). – anneb Oct 30 '17 at 13:44

The initial data provided by you, see figure 1

enter image description here

1) Create points from polygons, for which in pgAdmin run 2 query on two tables:

create table long_slanted_line_to_poias SELECT (ST_Dumppoints (geom)). Geom FROM long_slanted_line; create table short_slanted_line_on_long_slanted_line_to_poi as SELECT (ST_Dumppoints (geom)). Geom FROM short_slanted_line_on_long_slanted_line; The result is shown in Figure 2

enter image description here

2) Create a table of points with the following query:

create table exper_poi (    id serial NOT NULL,    name character varying (20),    my_point geometry (Point, 28992),    CONSTRAINT exper_poi_pkey PRIMARY KEY (id) ) WITH (    OIDS = FALSE ); ALTER TABLE exper_poi    OWNER TO postgres;

and copy into it two common points and run the query to create the line:

create table exper_line_common_border as SELECT st_makeline (my_point) FROM exper_poi; The result, see figure 3

enter image description here

I do not know what you will do with the common border, so I stop at this step, good luck

| improve this answer | |
  • Thanks for looking at the problem! I think the key to the answer is "and copy into it two common points and run the query to create the line". However, I do not understand how you found the common points? – anneb May 27 '18 at 23:31
  • The situation is as follows: I have defined a triangle of its point in the figure, then add 2 points to the rectangle figure and as a result these points are common for the two figures – Cyril Mikhalchenko May 28 '18 at 6:52

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.