PostGIS - Get geofence polygon points within 100 metres of an existing geofence

Question

Let's say I have a geofence polygon with the following coordinates

POLYGON((-36.861986 174.726745,-36.859241 174.730442,-36.859821 174.730976,-36.862321 174.728156,-36.861986 174.726745))

I'd like to be able to generate another set of polygon points from this that is within a 100 metre radius. Is there any formula to achieve this?

My use case is that if I have an existing geofence polygon, I want to create another geofence polygon surrounding it in a distance of 100 metres.

I'm using the following query to insert data into my table. The columns 'geofence' and 'geofence_buffer' are of type geography. The column with the name 'geography' is of type text. I just use that for a quick reference.

insert into site(name,status,client_id,geofence_type,geography_drawn,geofence,geofence_buffer)
values('Fanshawe','Active',1,1,'POLYGON((-36.849530 174.760984,-36.839295 174.763729,-36.841696 174.779093,-36.854061 174.774291,-36.849530 174.760984))',
      'SRID=4267;POLYGON((-36.849530 174.760984,-36.839295 174.763729,-36.841696 174.779093,-36.854061 174.774291,-36.849530 174.760984))',
      (select ST_Buffer(ST_GeographyFromText('SRID=4267;POLYGON((-36.849530 174.760984,-36.839295 174.763729,-36.841696 174.779093,-36.854061 174.774291,-36.849530 174.760984))'),100)));

Data gets inserted just fine but when I run the following query to find if a point exists within a geofence_buffer, I get the error below.

NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
LINE 1: ...ect * from site where ST_DWithin(geofence_buffer, 'SRID=4267...
                                                             ^
ERROR:  Operation on mixed SRID geometries
SQL state: XX000

The query I use is

select * from site where ST_DWithin(geofence_buffer, 'SRID=4267;POINT(-36.848362 174.764417)',0.0,false);

However, if I query within a geofence like this, then everything works fine.

select * from site where ST_DWithin(geofence, 'SRID=4267;POINT(-36.848362 174.764417)',0.0,false);

Answer 1

First, the coordinates must be expressed as longitude first, then latitude. You would need to swap the coordinates in your input

POLYGON(( 174.726745 -36.861986, 174.730442 -36.859241, 174.730976 -36.859821, 174.728156 -36.862321, 174.726745 -36.861986))

Then you can use ST_BUFFER to create a buffer around your polygon. To specify a distance in meters while having coordinates in lat/long, it is easiest to cast geometry to geography

with src as (select st_geomFromText('POLYGON(( 174.726745 -36.861986, 174.730442 -36.859241, 174.730976 -36.859821, 174.728156 -36.862321, 174.726745 -36.861986))') geom)
select st_buffer(geom::geography,100)::geometry 
from src;

Since your data is not in 4326 but in 4267, and that st_buffer returns geographies in 4326, you would have to reproject:

with src as (select st_geomFromText('POLYGON(( 174.726745 -36.861986, 174.730442 -36.859241, 174.730976 -36.859821, 174.728156 -36.862321, 174.726745 -36.861986))') geom)
select ST_Transform(st_buffer(geom::geography,100)::geometry,4267) 
from src;