Search code examples
sqlpostgresqlpostgis

How can i convert my geometry column to unify all my polygons?

Hello I just start to work with postgis,

I need to do those differents steps :

  1. from my geometry column create circle with a radius of 500m
  2. create polygon
  3. with the polygon created I want to unify them all (i have a lot of data)
  4. transform them in geojson

I show u all my try :

select geography(st_transform(geom::geometry,4326),500) from table

select ST_polygon(geom::geometry,2154) from table

select st_union(geom::geometry) from table

select cast ( geom as geography) from table

Nothing work

UPDATE

my geom value look like this geom_value

Solution

  • This can be done in a single query. First create a buffer with ST_Buffer casting the geometry to geography, so that it accepts metres as unit. After that, aggregate the just created polygons with ST_Collect or ST_Union, then finally use ST_AsGeoJSON to display the multipolygon as GeoJSON:

    SELECT 
  ST_AsGeoJSON(
    ST_Union(
      ST_Buffer(
        ST_Transform(geom,4326)::geography,
        500)::geometry))
FROM t;

    Note: the ST_Transform(geom,4326) is necessary as the geometry's CRS is a non lon/lat one, and therefore cannot be directly cast to geography. Transforming it in query time to i.e. WGS84 does the trick.

    Demo: db<>fiddle