More

Force St_split() to return geometries in specific pattern


I'm Using PostGISst_split()to split a polygon geometry by a linestring. Then I usest_dump()to return each of the splitted geometry and it is working fine. But the issue is I want to force st_dump() to return in a specific pattern. For example if the line is vertical, return left geometry first and then right as shown below.

While if the linestring is horizantal, return upper geometry first then lower geometry.

Currently, it return geometry randomly.

Below is the snippet which return me splitted geometries

SELECT ((ST_Dump(ST_Split(circle, line))).geom) As wkt FROM (SELECT ST_GeomFromText('LINESTRING(73.3864598921383 33.8985381624948,73.3962348946526 33.8646669883339)',4326) As line, ST_GeomFromText('POLYGON((73.3733940124512 33.8850949267837,73.4058380126953 33.8936448555238,73.41313362121582 33.87818310806669,73.377857208252 33.8708431412792,73.3735656738281 33.8772567393002,73.3733940124512 33.8850949267837))',4326) As circle) As foo;

You've got few ways to do this:

  1. Draw the point in upper left corner of geometry and order by distance from this point

  2. Order by added coordinates of upper left vertex of both geometries

    Select * from ( SELECT ((ST_Dump(ST_Split(circle, line))).geom) As wkt FROM ( SELECT ST_GeomFromText('LINESTRING(73.3864598921383 33.8985381624948,73.3962348946526 33.8646669883339)',4326) As line, ST_GeomFromText('POLYGON((73.3733940124512 33.8850949267837,73.4058380126953 33.8936448555238,73.41313362121582 33.87818310806669,73.377857208252 33.8708431412792,73.3735656738281 33.8772567393002,73.3733940124512 33.8850949267837))',4326) As circle ) As foo ) as aaa order by st_xmin(aaa.wkt), st_ymax(aaa.wkt)
  3. Order by X of coordinates then by y of coordinates of both geometries

    Select * from ( SELECT ((ST_Dump(ST_Split(circle, line))).geom) As wkt FROM ( SELECT ST_GeomFromText('LINESTRING(73.3864598921383 33.8985381624948,73.3962348946526 33.8646669883339)',4326) As line, ST_GeomFromText('POLYGON((73.3733940124512 33.8850949267837,73.4058380126953 33.8936448555238,73.41313362121582 33.87818310806669,73.377857208252 33.8708431412792,73.3735656738281 33.8772567393002,73.3733940124512 33.8850949267837))',4326) As circle ) As foo ) as aaa order by st_xmin(aaa.wkt), st_ymax(aaa.wkt)


Watch the video: One Hour at Bootcamp: Classification and Regression Trees CART Workshop (October 2021).