| ST_Collect | ST_Force_Collection | ST_MakeGeomColl | |
|---|---|---|---|
| Purpose | Returns a geometry from the collection of others geometries. | Forces the geometry to be a GEOMETRYCOLLECTION. | Constructs a GeometryCollection object from a set of Geometries. |
| Synopsis |
|
|
|
| Input geometries |
|
|
|
| Example | SELECT ST_AsText(ST_Collect(
ST_GeomFromText(' POLYGON ((3 2, 7 7, 7 2, 3 2))'),
ST_GeomFromText(' POLYGON ((5 5, 9 10, 9 5, 5 5))')
));
--Result
MULTIPOLYGON (((30 20, 30 70, 70 70, 70 20, 30 20)),
((50 50, 50 100, 90 100, 90 50, 50 50))) |
SELECT ST_AsText(
ST_Force_Collection
( ST_GeomFromText(' POLYGON ((3 2, 7 7, 7 2, 3 2))' )
));
--Result
GEOMETRYCOLLECTION (POLYGON ((30 20, 30 70, 70 70, 70 20, 30 20)))
|
SELECT ST_AsText( ST_MakeGeomColl ( st_accumagg(geom)))
as geomcollect from (select array_get((
st_geomfromtext(' POLYGON ((3 2, 7 7, 7 2, 3 2))'),
st_geomfromtext(' POLYGON ((5 5, 9 10, 9 5, 5 5))')
),x)::binary as geom
from (select x from system_range(1,2) as foo) as table);
--Result
GEOMETRYCOLLECTION (POLYGON ((30 20, 30 70, 70 70, 70 20, 30 20)),
POLYGON ((50 50, 50 100, 90 100, 90 50, 50 50))) |
| Result | MULTI* GEOMETRYCOLLECTION |
GEOMETRYCOLLECTION | GEOMETRYCOLLECTION |
Find the lines that are within a distance of 1 meter to the "A" line. In this example, the quickest and most intuive way to find it is using a clause "WHERE ST_Distance (A,B) < 1".
 |
In indexable queries the Bounding Boxes are used to reduce the searching area. First step is to expand the BBox.
SELECT ST_AsText(ST_Expand(A,1)) FROM
(SELECT ST_GeomFromText('LINESTRING (1 1, 2 3, 3 2, 4 4)')
AS A) as foo;
--Result
POLYGON ((0 0, 0 5, 5 5, 5 0, 0 0)) |  |
Next, ST_Expand (A,1) && B is used to do indexable queries. In the following query, we check if the Bounding Boxes overlap.
SELECT ((ST_Expand(A,1) && B)) as AB,
((ST_Expand(A,1) && C)) as AC
FROM (SELECT ST_GeomFromText('LINESTRING (1 1, 2 3, 3 2, 4 4)') as A,
ST_GeomFromText('LINESTRING (4 3, 5 2, 7 2, 7 2)') as B,
ST_GeomFromText('LINESTRING (4 5, 5 6, 7 4)') as C ) as foo;
--Result:
ab | ac
----+----
t | t
(1 row) |
The last step is to check , just in the overlapping BBoxes, if the geometries are really in a distance < 1 m.
SELECT ((ST_Expand(A,1) && B) and ST_Distance(A,B)<1) as AB,
((ST_Expand(A,1) && C) and ST_Distance(A,C)<1) as AC
FROM (SELECT ST_GeomFromText('LINESTRING (1 1, 2 3, 3 2, 4 4)') as A,
ST_GeomFromText('LINESTRING (4 3, 5 2, 7 2, 7 2)') as B,
ST_GeomFromText('LINESTRING (4 5, 5 6, 7 4)') as C ) as foo;
--Result
ab | ac
----+----
t | f
(1 row)![[Note]](images/note.png) | |
ST_DWithin (A,B,1) is equivalent to ((ST_Expand(A,1) && B) and ST_Distance(A,B)<1), with the advantage of which it has a much simpler syntax |
The following example shows the same query with the ST_DWithin function
SELECT ST_DWithin (A,B,1) as AB, ST_DWithin (A,C,1) as AC
FROM (SELECT ST_GeomFromText('LINESTRING (1 1, 2 3, 3 2, 4 4)') as A,
ST_GeomFromText('LINESTRING (4 3, 5 2, 7 2, 7 2)') as B,
ST_GeomFromText('LINESTRING (4 5, 5 6, 7 4)') as C ) as foo;ST_Dump, ST_DumpRings and ST_DumpPoints are functions that takes some geometries and returns a set of geometries. The behaviour of these functions is differentent in Jaspa for PostgreSQL and Jaspa for H2.
Simple entity
There are two ways of using ST_Dump
From ST_Dump
SELECT * from st_dump(st_geomfromewkt('MULTIPOINT (1 2, 3 4, 5 6)')); SELECT st_astext(a) from st_dump(st_geomfromewkt('MULTIPOINT (1 2, 3 4, 5 6)')) as a;SELECT ST_Dump
SELECT st_dump(st_geomfromewkt('MULTIPOINT (1 2, 3 4, 5 6)')); SELECT ST_AsText(st_dump(st_geomfromewkt('MULTIPOINT (1 2, 3 4, 5 6)')));
Table
The use of ST_Dump with a set of geometries from a table appears in the following example:
select st_dump(geom) from table;
Simple entity
The utilization of ST_Dump with a single geometry appears in the following example:
SELECT geom FROM ST_Dump('select st_geomfromewkt(''MULTIPOINT (1 2, 3 4, 5 6)'')');Table
The use of ST_Dump with a set of geometries from a table appears in the following example:
select geom from st_dump('select <geometry_column> as geom from <table>')You can also recover other fields from the dumped geometries. Just keep in mind that always the first argument of the ST_Dump select clause must be of geometry type. The next example illustrates it:
select a, geom from st_dump('select <geometry_column> as geom, <table_column> as a from <table>')