postgresqlgeometrypostgisgeojsonwkt

Store circles in Postgres geometry field


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)
);

Solution

  • 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;