PostGIS: get Circle to GeoJSON

Question

CREATE TABLE IF NOT EXISTS public.global_circles (
   name character varying(64),
   geom circle);

INSERT INTO global_circles (name, geom) 
VALUES ('My circle', CIRCLE(POINT(16.56115880637268,46.959564402028334), 4000));

How can I get the "geom" value in GeoJSON format?

Your title and text disagree -- the CIRCLE type is not a PostGIS implementation. — Vince, May 03 '22 at 01:10
@Koba Vince is correct Postgres has circle implemented but POSTGIS does not https://stackoverflow.com/a/43687546/749066 — Mapperz, May 03 '22 at 02:05
On a side note, have a look at this post about the accuracy of a coordinate — JGH, May 03 '22 at 13:09

score 2 · Answer 1 · answered May 03 '22 at 13:03

As said in the comments, circle is a native postgres type that is not related to postgis. If using PostGIS, it is better avoid these native types.

That being said, if you really have to use the circle type, you can convert it to a native polygon which in turn can be converted to a postgis geometry and then can be exported as a geojson:

select st_asGeoJson(CIRCLE(POINT(16.56115880637268,46.959564402028334), 4000)::polygon::geometry);
                                                                                                                                                                                                                            st_asgeojson                                                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"type":"Polygon","coordinates":[[[-3983.438841194,46.959564402],[-3447.540456331,2046.959564402],[-1983.438841194,3511.06117954],[16.561158806,4046.959564402],[2016.561158806,3511.06117954],[3480.662773944,2046.959564402],[4016.561158806,46.959564402],[3480.662773944,-1953.040435598],[2016.561158806,-3417.142050736],[16.561158806,-3953.040435598],[-1983.438841194,-3417.142050736],[-3447.540456331,-1953.040435598],[-3983.438841194,46.959564402]]]}
(1 row)

PS: and of course this assumes that the radius is expressed in the same unit as the point coordinate, which seems unlikely with the given example.

If using a point in lat-long with a radius of 4000 meters, one way of doing it with PostGIS would be to cast to geography first:

select st_asGeoJson(St_Buffer(st_makePoint(16.56115880637268,46.959564402028334)::geography, 4000));
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    st_asgeojson                                                                                                                                                                                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"type":"Polygon","coordinates":[[[16.613712465,46.958835611],[16.612491754,46.951830285],[16.609299221,46.94512255],[16.604258179,46.938970016],[16.597562745,46.933608891],[16.58947033,46.929244935],[16.580291722,46.926045599],[16.57037918,46.924133621],[16.560112962,46.923582349],[16.549886822,46.924412934],[16.540092986,46.92659353],[16.531107204,46.9300405],[16.523274396,46.93462161],[16.516895464,46.940161074],[16.512215741,46.946446268],[16.509415535,46.953235867],[16.50860313,46.960269079],[16.509810522,46.967275657],[16.51299207,46.97398627],[16.518026136,46.98014287],[16.524719661,46.98550863],[16.532815529,46.989877084],[16.542002423,46.993080098],[16.551926824,46.994994372],[16.562206668,46.99554621],[16.572446128,46.994714373],[16.582250948,46.992530899],[16.591243707,46.989079863],[16.599078423,46.984494118],[16.605453903,46.978950156],[16.61012534,46.972661284],[16.612913687,46.965869389],[16.613712465,46.958835611]]]}

Thank you JGH for you answer. I have OpenLayers client side which allows you to draw circles and send them in geoJSON format to the database. At that point I could save them as polygons that look like circles. But when I then have to re-present those circles to the client from the DB, I will see them as polygons. This is a problem. — Koba, May 03 '22 at 15:39
@Koba You can always extract the polygon boundary (in the DB) or draw only its edge (in the client) — JGH, May 03 '22 at 17:28

PostGIS: get Circle to GeoJSON

1 Answers1