CODEWITHSUNDEEP
postgresqlgeometrypostgispoint
Borealis
Borealis

Reputation: 8470

How to buffer a point using Postgis?

I'm using Postgres/Postgis on Ubuntu Linux. I am looking to buffer a point (or points) by a certain distance as shown in the image.

enter image description here

I've been able to generate a point as a postgres table pnt:

CREATE TABLE pnt ( 
  p_id INTEGER PRIMARY KEY

);

SELECT AddGeometryColumn('pnt','the_geom','4326','POINT',2);

INSERT INTO pnt(p_id, the_geom)
VALUES(2, ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));

Postgis has a ST_Buffer function that presumably can accomplish the buffer operation, although I am unsure how to apply the syntax to the point created from the above code.

How can I buffer the point by 100 meters to produce a new table called "buffered_points"?

Upvotes: 2

Views: 3799

Answers (1)

Patrick
Patrick

Reputation: 32199

You are using the "old" syntax of PostGIS. Nowadays you would simply create the table like so:

CREATE TABLE pnt ( 
  p_id INTEGER PRIMARY KEY,
  the_geom geography(POINT, 4326)
);

INSERT INTO pnt(p_id, the_geom)
VALUES(2, ST_GeogFromText('POINT(-71.060316 48.432044)'));

The new table you have to create first, much like the pnt table but now with POLYGONs instead of points:

CREATE TABLE buffered_points ( 
  p_id INTEGER PRIMARY KEY,
  the_geom geography(POLYGON, 4326)
);

And then insert the buffers from the pnt table:

INSERT INTO buffered_points(p_id, the_geom)
  SELECT p.p_id, ST_Buffer(p.the_geom, 100)
  FROM pnt p;

Note that I am using the geography type here (long/lat coordinates), because buffering on GPS coordinates (EPSG: 4326) will not produce sensible results.

Results

patrick@puny:~$ psql -d test
psql (9.5.0, server 9.4.5)
Type "help" for help.

test=# \dx
                                      List of installed extensions
   Name    | Version |   Schema   |                             Description
-----------+---------+------------+---------------------------------------------------------------------
 ...
 postgis   | 2.1.8   | public     | PostGIS geometry, geography, and raster spatial types and functions
 ...
(6 rows)
test=# CREATE TABLE pnt (
test(#   p_id INTEGER PRIMARY KEY,
test(#   the_geom geography(POINT, 4326)
test(# );
CREATE TABLE
test=# INSERT INTO pnt(p_id, the_geom)
test-# VALUES(2, ST_GeogFromText('POINT(-71.060316 48.432044)'));
INSERT 0 1
test=# select * from pnt;
 p_id |                      the_geom
------+----------------------------------------------------
    2 | 0101000020E61000003CDBA337DCC351C06D37C1374D374840
(1 row)

test=# CREATE TABLE buffered_points (
test(#   p_id INTEGER PRIMARY KEY,
test(#   the_geom geography(POLYGON, 4326)
test(# );
CREATE TABLE
test=# INSERT INTO buffered_points(p_id, the_geom)
  SELECT p.p_id, ST_Buffer(p.the_geom, 100)
  FROM pnt p;
INSERT 0 1
test=# SELECT * FROM buffered_points;
 p_id |                      the_geom
------+----------------------------------------------------
    2 | 0103000020E610000001000000210 ...
(1 row)

Upvotes: 3

Related Questions