Store circles in Postgres geometry field

Question

Ideally it would be something like this, but WKT doesn't have circle type.

ST_GeomFromText('CIRCLE(10 20, 10)',4326)

Although, circle type is listed among geometric types,

circle <(x,y),r> (center point and radius)

I wonder if it's possible to use circle type directly in sql:

update <table>
set the_geom = circle '((10, 20),10)'::geometry
where id = <id>;

But it says SQL Error [42846]: ERROR: cannot cast type circle to geometry.

Using ST_Buffer for storing circles is a kludge so I don't want to use it.

Alternative solution could be jsonb + geojson, but it doesn't support circles either.

UPD: There is my table structure. Currently I'm using longitude/latitude/radius, but I'd like to use either geo_json or the_geom. How could GeoJSON and WKT not support a circle?

CREATE SEQUENCE my_table_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
CREATE TABLE my_table (
    id INT NOT NULL,
    longitude NUMERIC(10, 7) DEFAULT NULL,
    latitude NUMERIC(10, 7) DEFAULT NULL,
    radius INT DEFAULT NULL,
    geo_json JSONB,
    the_geom Geometry DEFAULT NULL, PRIMARY KEY(id)
);

Possible duplicate of [Can I store circle and polygon within the same spatially indexed column in postgis?](http://stackoverflow.com/questions/28178421/can-i-store-circle-and-polygon-within-the-same-spatially-indexed-column-in-postg) — Juan Carlos Oropeza, Apr 28 '17 at 15:58
`circle` is a built-in PostgreSQL type, and it is geometry-based, not spatial. Those types has little to do with PostGIS. — pozs, Apr 28 '17 at 16:02
@JuanCarlosOropeza I specified the error and added more cases. — karser, Apr 28 '17 at 16:04
@pozs right, and I wonder if it's possible to store circle in geometry field without using WKT — karser, Apr 28 '17 at 16:04
@karser the `geometry` type comes from PostGIS. Which does not support circles. The linked question has all information which is available on the subject. — pozs, Apr 28 '17 at 16:06
@JuanCarlosOropeza in the question that you attached they propose to use ST_Buffer which approximates circles to POLYGON that have a lot of points inside, instead of simply storing X, Y, Radius. — karser, Apr 28 '17 at 16:07

score 2 · Accepted Answer · answered Apr 28 '17 at 19:24

Circle is native for postgresql as you can see on the manual documentation.
Geometry is a type related to PostGis Extension, and doesnt have CIRCLE but use polygons with lot of points instead.
Function starting with ST_ are also Postgis functions and work only with Postgis geometry or geography data type

SQL DEMO:

create table points ( p POINT not null); 
create table lines ( l LINE not null);
create table circles ( c CIRCLE not null);        

insert into points (p) values ( POINT(1.2, 123.1) );
insert into lines (l) values ( LINE(POINT(1.2, 123.1), POINT(-5, -123)) );
insert into circles (c) values ( CIRCLE(POINT(1.2, 123.1), 10) );

SELECT * FROM points;    
SELECT * FROM lines;    
SELECT * FROM circles;

Thank you for clarifying everything – karser Apr 30 '17 at 18:23 — karser, Apr 30 '17 at 18:23

score 1 · Answer 2 · answered Mar 27 '21 at 06:37

The GIST index allows you to work efficiently with circles. If that's the only thing you intend to store in this table, then you can do it like this:

CREATE TABLE my_table (
    id INT NOT NULL,
    longitude NUMERIC(10, 7) DEFAULT NULL,
    latitude NUMERIC(10, 7) DEFAULT NULL,
    radius INT DEFAULT NULL,
    geo_json JSONB
);
CREATE INDEX idx_my_table ON my_table USING GIST ( circle( point( latitude, longitude ), radius ));

As others have pointed out, you cannot mix this table with GEOMETRY types, which are incompatible.

In order to utilize the index above, you must express your WHERE criteria in similar terms: circle( point( latitude, longitude ), radius ) or '<( latitude, longitude ), radius >'::circle and use the operators that GIST knows about ... which are listed below. I'm aware that projecting the Euclidian shape of a circle onto a non-Euclidian spherical geometry has limitations, but for index purposes it should work OK with care.

https://www.postgresql.org/docs/current/gist-builtin-opclasses.html

Store circles in Postgres geometry field

2 Answers2