Database Reference
In-Depth Information
Converting polygons to linestrings
Toaccomplishthis,we'llneedtoextractjusttheportionsofthepolygonswewantus-
ing
ST_ExteriorRing
,convertthosepartstopointsusing
ST_DumpPoints
,and
thenconnectthosepointsbackintolineslikea"connect-the-dots"coloringbookus-
ing
ST_MakeLine
.
Breakingitdownfurther,
ST_ExteriorRing (the_geom)
willgrabjusttheouter
boundaryofourpolygons.But
ST_ExteriorRing
returnspolygons,soweneedto
takethatoutputandcreatealinefromit.Theeasiestwaytodothisistoconvertitto
pointsusing
ST_DumpPoints
andthenconnectthosepoints.Bydefault,the
Dump
function returns an object called a
geometry_dump
, which is not just simple geo-
metrybutthegeometryincombinationwithanarrayofintegers.Theeasiestwayto
returnthegeometryaloneistoleveragetheobjectnotationtoextractjustthegeo-
metry portion of
geometry_dump
as follows:
(ST_DumpPoints(geom)).geom
Piecingthegeometrybacktogetherwith
ST_ExteriorRing
isdoneusingthefol-
lowing query:
SELECT
(ST_DumpPoints(ST_ExteriorRing(geom))).geom
Thisshouldgiveusalistingofpointsinorderfromtheexteriorringsofallthepoints
fromwhichwewanttoconstructourlinesusingST_MakeLine,asshowninthefol-
lowing query:
SELECT ST_MakeLine(geom) FROM (
SELECT
(ST_DumpPoints(ST_ExteriorRing(geom))).geom
) AS linpoints
Since the preceding approach is a process we may want to use in many other
places, it might be prudent to create a function from this using the following query: