CODEWITHSUNDEEP
postgresqlgispostgis
Ben
Ben

Reputation: 675

Convert Points to Polygon using PostGIS

I want to create a polygon table using PostGIS. Each row in table 'point' has three-point ID. Table 'point_location' has the location information of points. I googled this question but did not find the answer. What's wrong with the following code?

SELECT ST_GeomFromText('POLYGON((' || b.x || ' ' || b.y || ',' || c.x || ' ' || c.y || ',' || d.x || ' ' || d.y || ',' || b.x || ' ' || b.y'))',4326) 
AS polygon
FROM point a, point_location b, point_location c, point_location d
WHERE a.p1=b.point_id AND a.p2=c.point_id AND a.p3=d.point_id

Upvotes: 5

Views: 17210

Answers (1)

Mike T
Mike T

Reputation: 43622

A better way to construct a polygon from points is to use PostGIS' geometry constructors. This way, you avoid the need to convert binary → text → binary (WKB → WKT → WKB), which is slower, lossy, and is prone to text formatting distractions as evidenced by the missing ||. For example, try:

SELECT ST_MakePolygon(ST_MakeLine(ARRAY[b, c, d, b]))
FROM point a, point_location b, point_location c, point_location d
WHERE a.p1=b.point_id and a.p2=c.point_id and a.p3=d.point_id

Upvotes: 14

Related Questions