[postgis-users] Convert MULTIPOINT to POLYGON
Ralf
yukyuk at gmx.com
Mon Jul 14 09:28:45 PDT 2008
Hey! Is there a simple way to create a polygon out of a multipoint? I have a table with points (nodes) and another table ways. Every single way is defined through two or more nodes. The two tables are connected through way_nodes (it's an import from openstreetmap.org using osmosis!). I figured out how to get all ways that are closed:
SELECT ways.id FROM ways WHERE ST_IsClosed( (SELECT ST_LineFromMultiPoint( Collect(n.geom) ) FROM nodes n LEFT JOIN way_nodes wn ON n.id=wn.node_id WHERE ways.id=wn.way_id) )
Now I am trying to store these closed ways as a polygon into another, new table. While doing that, I have to convert the MULTIPOINT that is returned by the Collect()-function into a POLYGON:
INSERT INTO way_geometry SELECT ways.id,
( SELECT 'GeomFromText(\'' || replace( astext( Collect(nodes.geom) ), 'MULTIPOINT', 'POLYGON(') || ')\', 4326)' FROM nodes
LEFT JOIN way_nodes ON nodes.id=way_nodes.node_id WHERE way_nodes.way_id=ways.id
)
FROM ways
WHERE ST_IsClosed( (SELECT ST_LineFromMultiPoint( Collect(n.geom) ) FROM nodes n LEFT JOIN way_nodes wn ON n.id=wn.node_id WHERE ways.id=wn.way_id) )
AND ST_NumPoints( (SELECT ST_LineFromMultiPoint( Collect(n.geom) ) FROM nodes n LEFT JOIN way_nodes wn ON n.id=wn.node_id WHERE ways.id=wn.way_id) ) >= 3
;
This always responds the error "parse error - invalid geometry". When I INSERT one of these lines by hand everything works fine. Is there another function to convert a MULTIPOINT into a POLYGON, maybe without using AsText()? Or does anyone have another idea to deal with this?
Thanks a lot, Ralf
More information about the postgis-users
mailing list